Search Results

Search found 8340 results on 334 pages for 'merge join'.

Page 95/334 | < Previous Page | 91 92 93 94 95 96 97 98 99 100 101 102  | Next Page >

  • Lesser Known NHibernate Session Methods

    - by Ricardo Peres
    The NHibernate ISession, the core of NHibernate usage, has some methods which are quite misunderstood and underused, to name a few, Merge, Persist, Replicate and SaveOrUpdateCopy. Their purpose is: Merge: copies properties from a transient entity to an eventually loaded entity with the same id in the first level cache; if there is no loaded entity with the same id, one will be loaded and placed in the first level cache first; if using version, the transient entity must have the same version as in the database; Persist: similar to Save or SaveOrUpdate, attaches a maybe new entity to the session, but does not generate an INSERT or UPDATE immediately and thus the entity does not get a database-generated id, it will only get it at flush time; Replicate: copies an instance from one session to another session, perhaps from a different session factory; SaveOrUpdateCopy: attaches a transient entity to the session and tries to save it. Here are some samples of its use. ISession session = ...; AuthorDetails existingDetails = session.Get<AuthorDetails>(1); //loads an entity and places it in the first level cache AuthorDetails detachedDetails = new AuthorDetails { ID = existingDetails.ID, Name = "Changed Name" }; //a detached entity with the same ID as the existing one Object mergedDetails = session.Merge(detachedDetails); //merges the Name property from the detached entity into the existing one; the detached entity does not get attached session.Flush(); //saves the existingDetails entity, since it is now dirty, due to the change in the Name property AuthorDetails details = ...; ISession session = ...; session.Persist(details); //details.ID is still 0 session.Flush(); //saves the details entity now and fetches its id ISessionFactory factory1 = ...; ISessionFactory factory2 = ...; ISession session1 = factory1.OpenSession(); ISession session2 = factory2.OpenSession(); AuthorDetails existingDetails = session1.Get<AuthorDetails>(1); //loads an entity session2.Replicate(existingDetails, ReplicationMode.Overwrite); //saves it into another session, overwriting any possibly existing one with the same id; other options are Ignore, where any existing record with the same id is left untouched, Exception, where an exception is thrown if there is a record with the same id and LatestVersion, where the latest version wins SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/2.0.320/scripts/clipboard.swf'; SyntaxHighlighter.brushes.CSharp.aliases = ['c#', 'c-sharp', 'csharp']; SyntaxHighlighter.all();

    Read the article

  • To branch or not to branch?

    - by Idsa
    Till recently my development workflow was the following: Get the feature from product owner Make a branch (if feature is more than 1 day) Implement it in a branch Merge changes from main branch to my branch (to reduce conflicts during backward merging) Merge my branch back to main branch Sometimes there were problems with merging, but in general I liked it. But recently I see more and more followers of idea to not make branches as it makes more difficult to practice continuous integration, continuous delivery, etc. And it sounds especially funny from people with distributed VCS background who were talking so much about great merging implementations of Git, Mercurial, etc. So the question is should we use branches nowadays?

    Read the article

  • Stale statistics on a newly created temporary table in a stored procedure can lead to poor performance

    - by sqlworkshops
    When you create a temporary table you expect a new table with no past history (statistics based on past existence), this is not true if you have less than 6 updates to the temporary table. This might lead to poor performance of queries which are sensitive to the content of temporary tables.I was optimizing SQL Server Performance at one of my customers who provides search functionality on their website. They use stored procedure with temporary table for the search. The performance of the search depended on who searched what in the past, option (recompile) by itself had no effect. Sometimes a simple search led to timeout because of non-optimal plan usage due to this behavior. This is not a plan caching issue rather temporary table statistics caching issue, which was part of the temporary object caching feature that was introduced in SQL Server 2005 and is also present in SQL Server 2008 and SQL Server 2012. In this customer case we implemented a workaround to avoid this issue (see below for example for workarounds).When temporary tables are cached, the statistics are not newly created rather cached from the past and updated based on automatic update statistics threshold. Caching temporary tables/objects is good for performance, but caching stale statistics from the past is not optimal.We can work around this issue by disabling temporary table caching by explicitly executing a DDL statement on the temporary table. One possibility is to execute an alter table statement, but this can lead to duplicate constraint name error on concurrent stored procedure execution. The other way to work around this is to create an index.I think there might be many customers in such a situation without knowing that stale statistics are being cached along with temporary table leading to poor performance.Ideal solution is to have more aggressive statistics update when the temporary table has less number of rows when temporary table caching is used. I will open a connect item to report this issue.Meanwhile you can mitigate the issue by creating an index on the temporary table. You can monitor active temporary tables using Windows Server Performance Monitor counter: SQL Server: General Statistics->Active Temp Tables. The script to understand the issue and the workaround is listed below:set nocount onset statistics time offset statistics io offdrop table tab7gocreate table tab7 (c1 int primary key clustered, c2 int, c3 char(200))gocreate index test on tab7(c2, c1, c3)gobegin trandeclare @i intset @i = 1while @i <= 50000begininsert into tab7 values (@i, 1, ‘a’)set @i = @i + 1endcommit trangoinsert into tab7 values (50001, 1, ‘a’)gocheckpointgodrop proc test_slowgocreate proc test_slow @i intasbegindeclare @j intcreate table #temp1 (c1 int primary key)insert into #temp1 (c1) select @iselect @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)endgodbcc dropcleanbuffersset statistics time onset statistics io ongo–high reads as expected for parameter ’1'exec test_slow 1godbcc dropcleanbuffersgo–high reads that are not expected for parameter ’2'exec test_slow 2godrop proc test_with_recompilegocreate proc test_with_recompile @i intasbegindeclare @j intcreate table #temp1 (c1 int primary key)insert into #temp1 (c1) select @iselect @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)option (recompile)endgodbcc dropcleanbuffersset statistics time onset statistics io ongo–high reads as expected for parameter ’1'exec test_with_recompile 1godbcc dropcleanbuffersgo–high reads that are not expected for parameter ’2'–low reads on 3rd execution as expected for parameter ’2'exec test_with_recompile 2godrop proc test_with_alter_table_recompilegocreate proc test_with_alter_table_recompile @i intasbegindeclare @j intcreate table #temp1 (c1 int primary key)–to avoid caching of temporary tables one can create a constraint–but this might lead to duplicate constraint name error on concurrent usagealter table #temp1 add constraint test123 unique(c1)insert into #temp1 (c1) select @iselect @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)option (recompile)endgodbcc dropcleanbuffersset statistics time onset statistics io ongo–high reads as expected for parameter ’1'exec test_with_alter_table_recompile 1godbcc dropcleanbuffersgo–low reads as expected for parameter ’2'exec test_with_alter_table_recompile 2godrop proc test_with_index_recompilegocreate proc test_with_index_recompile @i intasbegindeclare @j intcreate table #temp1 (c1 int primary key)–to avoid caching of temporary tables one can create an indexcreate index test on #temp1(c1)insert into #temp1 (c1) select @iselect @j = t7.c1 from tab7 t7 inner join #temp1 t on (t7.c2 = t.c1)option (recompile)endgoset statistics time onset statistics io ondbcc dropcleanbuffersgo–high reads as expected for parameter ’1'exec test_with_index_recompile 1godbcc dropcleanbuffersgo–low reads as expected for parameter ’2'exec test_with_index_recompile 2go

    Read the article

  • CodePlex Daily Summary for Thursday, June 05, 2014

    CodePlex Daily Summary for Thursday, June 05, 2014Popular Releases51Degrees - Device Detection and Redirection: 3.1.2.3: Version 3.1 HighlightsDevice detection algorithm is over 100 times faster. Regular expressions and levenshtein distance calculations are no longer used. The device detection algorithm performance is no longer limited by the number of device combinations contained in the dataset. Two modes of operation are available: Memory – the detection data set is loaded into memory and there is no continuous connection to the source data file. Slower initialisation time but faster detection performanc...CS-Script for Notepad++ (C# intellisense and code execution): Release v1.0.27.0: CodeMap now indicates the type name for all members Implemented running scripts 'as administrator'. Just add '//css_npp asadmin' to the script and run it as usual. 'Prepare script for distribution' now aggregates script dependency assemblies. Various improvements in CodeSnipptet, Autcompletion and MethodInfo interactions with each other. Added printing line number for the entries in CodeMap (subject of configuration value) Improved debugging step indication for classless scripts ...Load Runner - HTTP Pressure Test Tool: Load Runner 1.1: 1. added support for measuring total in time / hits / traffic / requests 2. abstracted log, default log to console / text file 3. refactored codeKartris E-commerce: Kartris v2.6003: Bug fixes: Fixed issue where category could not be saved if parent categories not altered Updated split string function to 500 chars otherwise problems with attribute filtering in PowerPack Improvements: If a user has group pricing below that available through qty discounts, that will show in place of the qty discountMagicaVoxel: MagicaVoxel Renderer ver 0.01: MagicaVoxel Renderer (Win 0.01)ClosedXML - The easy way to OpenXML: ClosedXML 0.72.3: 70426e13c415 ClosedXML for .Net 4.0 now uses Open XML SDK 2.5 b9ef53a6654f Merge branch 'master' of https://git01.codeplex.com/forks/vbjay/closedxml 727714e86416 Fix range.Merge(Boolean) for .Net 3.5 eb1ed478e50e Make public range.Merge(Boolean checkIntersects) 6284cf3c3991 More performance improvements when saving.MCE Controller: MCE Controller V1.8.6: BETA. Adds option to disable all internal commands. If the "Disable Internal Commands" checkbox in settings is checked, MCE Controller will disable all internally defined commands (e.g. VK key codes, single characters, mouse commands, etc...) and only respond to commands defined in the MCEControl.commmands file. Attempts to fix inability for clients to reconnect after they forcibly close the connection. Build 1.8.6.37445Visual F# Tools: Daily Builds Preview 06-04-2014: This preview is released for use under a proprietary license.SEToolbox: SEToolbox 01.032.018 Release 1: Added ability to merge/join two ships, regardless of origin. Added Language selection menu to set display text language (for SE resources only), and fixed inherent issues. Added full support for Dedicated Servers, allowing use on PC without Steam present, and only the Dedicated Server install. Added Browse button for used specified save game selection in Load dialog. Installation of this version will replace older version.Service monitor: Version 3.1: Added the ability of the tool to restart itself in 'Admin' mode without UAC prompt. Note that this only works after the tool has run in 'Admin' mode at least once before. This is only supported on Vista, Windows 7 or later. See my blog post on how it is done.DNN Blog: 06.00.07: Highlights: Enhancement: Option to show management panel on child modules Fix: Changed SPROC and code to ensure the right people are notified of pending comments. (CP-24018) Fix: Fix to have notification actions authentication assume the right module id so these will work from the messaging center (CP-24019) Fix: Fix to issue in categories in a post that will not save when no categories and no tags selectedTEncoder: 4.0.0: --4.0.0 -Added: Video downloader -Added: Total progress will be updated more smoothly -Added: MP4Box progress will be shown -Added: A tool to create gif image from video -Added: An option to disable trimming -Added: Audio track option won't be used for mpeg sources as default -Fixed: Subtitle position wasn't used -Fixed: Duration info in the file list wasn't updated after trimming -Updated: FFMpegQuickMon: Version 3.14 (Pie release): This is unofficially the 'Pie' release. There are two big changes.1. 'Presets' - basically templates. Future releases might build on this to allow users to add more presets. 2. MSI Installer now allows you to choose components (in case you don't want all collectors etc.). This means you don't have to download separate components anymore (AllAgents.zip still included in case you want to use them separately) Some other changes:1. Add/changed default file extension for monitor packs to *.qmp (...VeraCrypt: VeraCrypt version 1.0d: Changes between 1.0c and 1.0d (03 June 2014) : Correct issue while creating hidden operating system. Minor fixes (look at git history for more details).Keepass2Android: 0.9.4-pre1: added plug-in support: See settings for how to get plug-ins! published QR plug-in (scan passwords, display passwords as QR code, transfer entries to other KP2A devices) published InputStick plugin (transfer credentials to your PC via bluetooth - requires InputStick USB stick) Third party apps can now simply implement querying KP2A for credentials. Are you a developer? Please add this to your app if suitable! added TOTP support (compatible with KeeOTP and TrayTotp) app should no l...Microsoft Web Protection Library: AntiXss Library 4.3.0: Download from nuget or the Microsoft Download Center This issue finally addresses the over zealous behaviour of the HTML Sanitizer which should now function as expected once again. HTML encoding has been changed to safelist a few more characters for webforms compatibility. This will be the last version of AntiXSS that contains a sanitizer. Any new releases will be encoding libraries only. We recommend you explore other sanitizer options, for example AntiSamy https://www.owasp.org/index....Z SqlBulkCopy Extensions: SqlBulkCopy Extensions 1.0.0: SqlBulkCopy Extensions provide MUST-HAVE methods with outstanding performance missing from the SqlBulkCopy class like Delete, Update, Merge, Upsert. Compatible with .NET 2.0, SQL Server 2000, SQL Azure and more! Bulk MethodsBulkDelete BulkInsert BulkMerge BulkUpdate BulkUpsert Utility MethodsGetSqlConnection GetSqlTransaction You like this library? Find out how and why you should support Z Project Become a Memberhttp://zzzproject.com/resources/images/all/become-a-member.png|ht...Tweetinvi a friendly Twitter C# API: Tweetinvi 0.9.3.x: Timelines- Added all the parameters available from the Timeline Endpoints in Tweetinvi. - This is available for HomeTimeline, UserTimeline, MentionsTimeline // Simple query var tweets = Timeline.GetHomeTimeline(); // Create a parameter for queries with specific parameters var timelineParameter = Timeline.CreateHomeTimelineRequestParameter(); timelineParameter.ExcludeReplies = true; timelineParameter.TrimUser = true; var tweets = Timeline.GetHomeTimeline(timelineParameter); Tweetinvi 0.9.3.1...Sandcastle Help File Builder: Help File Builder and Tools v2014.5.31.0: General InformationIMPORTANT: On some systems, the content of the ZIP file is blocked and the installer may fail to run. Before extracting it, right click on the ZIP file, select Properties, and click on the Unblock button if it is present in the lower right corner of the General tab in the properties dialog. This release completes removal of the branding transformations and implements the new VS2013 presentation style that utilizes the new lightweight website format. Several breaking cha...Magick.NET: Magick.NET 6.8.9.101: Magick.NET linked with ImageMagick 6.8.9.1. Breaking changes: - Int/short Set methods of WritablePixelCollection are now unsigned. - The Q16 build no longer uses HDRI, switch to the new Q16-HDRI build if you need HDRI.New Projects12306helper: 12306 ????2112110044: dasda2112110202: asdfsdfsdfsd2112110298: TieuDan2112110315: vftgcdxfrBaidu PCS: BaiduPCSExcel2Sobek: Here the project Excel2Sobek is hosted. Excel2Sobek is a preprocessor in Excel's VBA for the SOBEK 2 hydrological and hydraulic simulation model by Deltares.Help Desk Pro: Manage a business of customer serviceMARGYE: MARGYE CMS CMR E-CommerceMemberBS: MemberBS20140605MiKroTik API: Api untuk menghubungkan aplikasi anda dengan router Mikrotik.MiniProfiler + Log4Net: Use MiniProfiler and Log4Net in ASP.NET projectStore Apps Unity App Base for Prism: This simple library provides an Application Base for a Windows Store application that automatically connects Unity to provide injection as the container.SuperCaptcha for MVC: Custom captcha control for MVCTC760240: myappwithfailuresWindows Phone 8 Dilbert Comic Reader: Basic WP8.1 app that displays comics from http://www.dilbert.com.x86 Proved: Prove what you run!

    Read the article

  • How can I refactor a code base while others rapidly commit to it?

    - by Incognito
    I'm on a private project that eventually will become open source. We have a few team members, talented enough with the technologies to build apps, but not dedicated developers who can write clean/beautiful and most importantly long-term maintainable code. I've set out to refactor the code base, but it's a bit unwieldy as someone in the team out in another country I'm not in regular contact with could be updating this totally separate thing. I know one solution is to communicate rapidly or adopt better PM practices, but we're just not that big yet. I just want to clean up the code and merge nicely into what he has updated. Would a branch be a suitable plan? A best-effort-merge? Something else?

    Read the article

  • Would this be the equivalent of creating a branch, while working with a detached head in Git?

    - by Geo
    Let's say I checked out a version different than HEAD. Let's say I made some commits, and so an anonymous branch was created. Afterwards I may have checked out a different branch, so now the only way to get to my commits is via reflog. If do this: >> git reflog | grep -i mycommit sha1hash >> git branch reattaching >> git cherry-pick hash_of_commits >> git checkout master >> git merge reattaching Is it the equivalent of: >> git reflog | grep -i mycommit sha1hash >> git branch reattaching sha1hash >> git checkout master >> git merge reattaching What happens to the detached head commits, as I think that via cherry-picking, they will exist in 2 places. Will they forever remain in my repository?

    Read the article

  • NHibernate Query across multiple tables

    - by Dai Bok
    I am using NHibernate, and am trying to figure out how to write a query, that searchs all the names of my entities, and lists the results. As a simple example, I have the following objects; public class Cat { public string name {get; set;} } public class Dog { public string name {get; set;} } public class Owner { public string firstname {get; set;} public string lastname {get; set;} } Eventaully I want to create a query , say for example, which and returns all the pet owners with an name containing "ted", OR pets with a name containing "ted". Here is an example of the SQL I want to execute: SELECT TOP 10 d.*, c.*, o.* FROM owners AS o INNER JOIN dogs AS d ON o.id = d.ownerId INNER JOIN cats AS c ON o.id = c.ownerId WHERE o.lastname like '%ted%' OR o.firstname like '%ted%' OR c.name like '%ted%' OR d.name like '%ted%' When I do it using Criteria like this: var criteria = session.CreateCriteria<Owner>() .Add( Restrictions.Disjunction() .Add(Restrictions.Like("FirstName", keyword, MatchMode.Anywhere)) .Add(Restrictions.Like("LastName", keyword, MatchMode.Anywhere)) ) .CreateCriteria("Dog").Add(Restrictions.Like("Name", keyword, MatchMode.Anywhere)) .CreateCriteria("Cat").Add(Restrictions.Like("Name", keyword, MatchMode.Anywhere)); return criteria.List<Owner>(); The following query is generated: SELECT TOP 10 d.*, c.*, o.* FROM owners AS o INNER JOIN dogs AS d ON o.id = d.ownerId INNER JOIN cats AS c ON o.id = c.ownerId WHERE o.lastname like '%ted%' OR o.firstname like '%ted%' AND d.name like '%ted%' AND c.name like '%ted%' How can I adjust my query so that the .CreateCriteria("Dog") and .CreateCriteria("Cat") generate an OR instead of the AND? thanks for your help.

    Read the article

  • Common Table Expressions slow when using a table variable

    - by Phil Haselden
    I have been experimenting with the following (simplified) CTE. When using a table variable () the query runs for minutes before I cancel it. Any of the other commented out methods return in less than a second. If I replace the whole WHERE clause with an INNER JOIN it is fast as well. Any ideas why using a table variable would run so slowly? FWIW: The database contains 2.5 million records and the inner query returns 2 records. CREATE TABLE #rootTempTable (RootID int PRIMARY KEY) INSERT INTO #rootTempTable VALUES (1360); DECLARE @rootTableVar TABLE (RootID int PRIMARY KEY); INSERT INTO @rootTableVar VALUES (1360); WITH My_CTE AS ( SELECT ROW_NUMBER() OVER(ORDER BY d.DocumentID) rownum, d.DocumentID, d.Title FROM [Document] d WHERE d.LocationID IN ( SELECT LocationID FROM Location JOIN @rootTableVar rtv ON Location.RootID = rtv.RootID -- VERY SLOW! --JOIN #rootTempTable tt ON Location.RootID = tt.RootID -- Fast --JOIN (SELECT 1360 as RootID) AS rt ON Location.RootID = rt.RootID -- Fast --WHERE RootID = 1360 -- Fast ) ) SELECT * FROM My_CTE WHERE (rownum > 0) AND (rownum <= 100) ORDER BY rownum

    Read the article

  • Nhibernate: distinct results in second level Collection

    - by Miguel Marques
    I have an object model like this: class EntityA { ... IList<EntityB> BList; ... } class EntityB { ... IList<EntityC> CList; } I have to fetch all the colelctions (Blist in EntityA and CList in EntityB), because if they all will be needed to make some operations, if i don't eager load them i will have the select n+1 problem. So the query was this: select a from EntityA a left join fetch a.BList b left join fetch b.CList c The fist problem i faced with this query, was the return of duplicates from the DB, i had EntityA duplicates, because of the left join fetch with BList. A quick read through the hibernate documentation and there were some solutions, first i tried the distinct keyword that supposelly wouldn't replicate the SQL distinct keyword except in some cases, maybe this was one of those cases because i had a SQL error saying that i cannot select distict text columns (column [Observations] in EntityA table). So i used one of the other solutions: query.SetResultTransformer(new DistinctRootEntityResultTransformer()); This worked fine. But the result of the operations were still not passing the tests. I checked further and i found out that now there were duplicates of EntityB, because of the left join fetch with CList. The question is, how can i use the distinct in a second level collection? I searched and i only find solutions for the root entity's direct child collection, but never for the second level child collections... Thank you for your time

    Read the article

  • count on LINQ union

    - by brechtvhb
    I'm having this link statement: List<UserGroup> domains = UserRepository.Instance.UserIsAdminOf(currentUser.User_ID); query = (from doc in _db.Repository<Document>() join uug in _db.Repository<User_UserGroup>() on doc.DocumentFrom equals uug.User_ID where domains.Contains(uug.UserGroup) select doc) .Union(from doc in _db.Repository<Document>() join uug in _db.Repository<User_UserGroup>() on doc.DocumentTo equals uug.User_ID where domains.Contains(uug.UserGroup) select doc); Running this statement doesn't cause any problems. But when I want to count the resultset the query suddenly runs quite slow. totalRecords = query.Count(); The result of this query is : SELECT COUNT([t5].[DocumentID]) FROM ( SELECT [t4].[DocumentID], [t4].[DocumentFrom], [t4].[DocumentTo] FROM ( SELECT [t0].[DocumentID], [t0].[DocumentFrom], [t0].[DocumentTo FROM [dbo].[Document] AS [t0] INNER JOIN [dbo].[User_UserGroup] AS [t1] ON [t0].[DocumentFrom] = [t1].[User_ID] WHERE ([t1].[UserGroupID] = 2) OR ([t1].[UserGroupID] = 3) OR ([t1].[UserGroupID] = 6) UNION SELECT [t2].[DocumentID], [t2].[DocumentFrom], [t2].[DocumentTo] FROM [dbo].[Document] AS [t2] INNER JOIN [dbo].[User_UserGroup] AS [t3] ON [t2].[DocumentTo] = [t3].[User_ID] WHERE ([t3].[UserGroupID] = 2) OR ([t3].[UserGroupID] = 3) OR ([t3].[UserGroupID] = 6) ) AS [t4] ) AS [t5] Can anyone help me to improve the speed of the count query? Thanks in advance!

    Read the article

  • Counting the number of objects that meets a certain criteria

    - by Candy Chiu
    The title doesn't tell the complete story. Please read the message. I have two objects: Adult and Child. Child has a boolean field isMale, and a reference to Adult. Adult doesn't reference Child. public class Adult { long id; } public class Child { long id; boolean isMale; Adult parent; } I want to create a query to list the number of sons each adult has including adults who don't have any sons. I tried: Query 1 SELECT adult, COUNT(child) FROM Child child RIGHT OUTER JOIN child.parent as adult WHERE child.isMale='true' GROUP BY adult which translates to sql select adult.id as col_0_0_, count(child.id) as col_1_0_, ... {omit properties} from Child child right outer join Adult adult on child.parentId=adult.id where child.isMale = 'true' group by adult.id Query 1 doesn't pick up adults that don't have any sons. Query 2: SELECT adult, COUNT(child.isMale) FROM Child child RIGHT OUTER JOIN child.parent as adult GROUP BY adult translates to sql: select adult.id as col_0_0_, count(child.id) as col_1_0_, ... {omit properties} from Child child right outer join Adult adult on child.parentId=adult.id group by adult.id Query 2 doesn't have the right count of sons. Basically COUNT doesn't evaluate isMale. The where clause in Query 1 filtered out Adults with no sons. How do I build a HQL or a Criteria query for this use case? Thanks.

    Read the article

  • Linq: the linked objects are null, why?

    - by user46503
    Hello, I have several linked tables (entities). I'm trying to get the entities using the following linq: ObjectQuery<Location> locations = context.Location; ObjectQuery<ProductPrice> productPrice = context.ProductPrice; ObjectQuery<Product> products = context.Product; IQueryable<ProductPrice> res1 = from pp in productPrice join loc in locations on pp.Location equals loc join prod in products on pp.Product equals prod where prod.Title.ToLower().IndexOf(Word.ToLower()) > -1 select pp; This query returns 2 records, ProductPrice objects that have linked object Location and Product but they are null and I cannot understand why. If I try to fill them in the linq as below: res = from pp in productPrice join loc in locations on pp.Location equals loc join prod in products on pp.Product equals prod where prod.Title.ToLower().IndexOf(Word.ToLower()) > -1 select new ProductPrice { ProductPriceId = pp.ProductPriceId, Product = prod }; I have the exception "The entity or complex type 'PBExplorerData.ProductPrice' cannot be constructed in a LINQ to Entities query" Could someone please explain me what happens and what I need to do? Thanks

    Read the article

  • MySQL & PHP - Creating Multiple Parent Child Relations

    - by Ashok
    Hi, I'm trying to build a navigation system using categories table with hierarchies. Normally, the table would be defined as follows: id (int) - Primary key name (varchar) - Name of the Category parentid (int) - Parent ID of this Category referenced to same table (Self Join) But the catch is that I require that a category can be child to multiple parent categories.. Just like a Has and Belongs to Many (HABTM) relation. I know that if there are two tables, categories & items, we use a join table categories_items to list the HABTM relations. But here i'm not having two tables but only table but should somehow show HABTM relations to itself. Is this be possible using a single table? If yes, How? If not possible, what rules (table naming, fields) should I follow while creating the additional join table? I'm trying to achieve this using CakePHP, If someone can provide CakePHP solution for this problem, that would be awesome. Even if that's not possible, any solution about creating join table is appreciated. Thanks for your time.

    Read the article

  • Impact of ordering of correlated subqueries within a projection

    - by Michael Petito
    I'm noticing something a bit unexpected with how SQL Server (SQL Server 2008 in this case) treats correlated subqueries within a select statement. My assumption was that a query plan should not be affected by the mere order in which subqueries (or columns, for that matter) are written within the projection clause of the select statement. However, this does not appear to be the case. Consider the following two queries, which are identical except for the ordering of the subqueries within the CTE: --query 1: subquery for Color is second WITH vw AS ( SELECT p.[ID], (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName], (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color] FROM Person p ) SELECT ID, Color, FirstName FROM vw WHERE Color = 'Gray'; --query 2: subquery for Color is first WITH vw AS ( SELECT p.[ID], (SELECT TOP(1) [Color] FROM [Preference] WHERE p.ID = ID AND [Color] IS NOT NULL ORDER BY [LastModified] DESC) [Color], (SELECT TOP(1) [FirstName] FROM [Preference] WHERE p.ID = ID AND [FirstName] IS NOT NULL ORDER BY [LastModified] DESC) [FirstName] FROM Person p ) SELECT ID, Color, FirstName FROM vw WHERE Color = 'Gray'; If you look at the two query plans, you'll see that an outer join is used for each subquery and that the order of the joins is the same as the order the subqueries are written. There is a filter applied to the result of the outer join for color, to filter out rows where the color is not 'Gray'. (It's odd to me that SQL would use an outer join for the color subquery since I have a non-null constraint on the result of the color subquery, but OK.) Most of the rows are removed by the color filter. The result is that query 2 is significantly cheaper than query 1 because fewer rows are involved with the second join. All reasons for constructing such a statement aside, is this an expected behavior? Shouldn't SQL server opt to move the filter as early as possible in the query plan, regardless of the order the subqueries are written?

    Read the article

  • ruby on rails delajed_job failing with rvm

    - by mottalrd
    I have delayed_job installed and I start the daemon to run the jobs with this ruby script require 'rubygems' require 'daemon_spawn' $: << '.' RAILS_ROOT = File.expand_path(File.join(File.dirname(__FILE__), '..')) class DelayedJobWorker < DaemonSpawn::Base def start(args) ENV['RAILS_ENV'] ||= args.first || 'development' Dir.chdir RAILS_ROOT require File.join('config', 'environment') Delayed::Worker.new.start end def stop system("kill `cat #{RAILS_ROOT}/tmp/pids/delayed_job.pid`") end end DelayedJobWorker.spawn!(:log_file => File.join(RAILS_ROOT, "log", "delayed_job.log"), :pid_file => File.join(RAILS_ROOT, 'tmp', 'pids', 'delayed_job.pid'), :sync_log => true, :working_dir => RAILS_ROOT) If I run the command with rvmsudo it works perfectly If I simply use the ruby command without rvm it fails and this is the output, but I have no idea why this happens. Could you give me some clue? Thank you user@mysystem:~/redeal.it/application$ ruby script/delayed_job start production /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:16:in `kill': Operation not permitted (Errno::EPERM) from /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:16:in `alive?' from /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:125:in `alive?' from /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:176:in `block in start' from /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:176:in `select' from /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:176:in `start' from /usr/local/rvm/gems/ruby-1.9.3-p194/gems/daemon-spawn-0.4.2/lib/daemon_spawn.rb:165:in `spawn!' from script/delayed_job:37:in `<main>'

    Read the article

  • Does query plan optimizer works well with joined/filtered table-valued functions?

    - by smoothdeveloper
    In SQLSERVER 2005, I'm using table-valued function as a convenient way to perform arbitrary aggregation on subset data from large table (passing date range or such parameters). I'm using theses inside larger queries as joined computations and I'm wondering if the query plan optimizer work well with them in every condition or if I'm better to unnest such computation in my larger queries. Does query plan optimizer unnest table-valued functions if it make sense? If it doesn't, what do you recommend to avoid code duplication that would occur by manually unnesting them? If it does, how do you identify that from the execution plan? code sample: create table dbo.customers ( [key] uniqueidentifier , constraint pk_dbo_customers primary key ([key]) ) go /* assume large amount of data */ create table dbo.point_of_sales ( [key] uniqueidentifier , customer_key uniqueidentifier , constraint pk_dbo_point_of_sales primary key ([key]) ) go create table dbo.product_ranges ( [key] uniqueidentifier , constraint pk_dbo_product_ranges primary key ([key]) ) go create table dbo.products ( [key] uniqueidentifier , product_range_key uniqueidentifier , release_date datetime , constraint pk_dbo_products primary key ([key]) , constraint fk_dbo_products_product_range_key foreign key (product_range_key) references dbo.product_ranges ([key]) ) go . /* assume large amount of data */ create table dbo.sales_history ( [key] uniqueidentifier , product_key uniqueidentifier , point_of_sale_key uniqueidentifier , accounting_date datetime , amount money , quantity int , constraint pk_dbo_sales_history primary key ([key]) , constraint fk_dbo_sales_history_product_key foreign key (product_key) references dbo.products ([key]) , constraint fk_dbo_sales_history_point_of_sale_key foreign key (point_of_sale_key) references dbo.point_of_sales ([key]) ) go create function dbo.f_sales_history_..snip.._date_range ( @accountingdatelowerbound datetime, @accountingdateupperbound datetime ) returns table as return ( select pos.customer_key , sh.product_key , sum(sh.amount) amount , sum(sh.quantity) quantity from dbo.point_of_sales pos inner join dbo.sales_history sh on sh.point_of_sale_key = pos.[key] where sh.accounting_date between @accountingdatelowerbound and @accountingdateupperbound group by pos.customer_key , sh.product_key ) go -- TODO: insert some data -- this is a table containing a selection of product ranges declare @selectedproductranges table([key] uniqueidentifier) -- this is a table containing a selection of customers declare @selectedcustomers table([key] uniqueidentifier) declare @low datetime , @up datetime -- TODO: set top query parameters . select saleshistory.customer_key , saleshistory.product_key , saleshistory.amount , saleshistory.quantity from dbo.products p inner join @selectedproductranges productrangeselection on p.product_range_key = productrangeselection.[key] inner join @selectedcustomers customerselection on 1 = 1 inner join dbo.f_sales_history_..snip.._date_range(@low, @up) saleshistory on saleshistory.product_key = p.[key] and saleshistory.customer_key = customerselection.[key] I hope the sample makes sense. Much thanks for your help!

    Read the article

  • django inner redirects

    - by Zayatzz
    Hello I have one project that in my own development computer (uses mod_wsgi to serve the project) caused no problems. In live server (uses mod_fastcgi) it generates 500 though. my url conf is like this: # -*- coding: utf-8 -*- from django.conf.urls.defaults import * # Uncomment the next two lines to enable the admin: from django.contrib import admin admin.autodiscover() urlpatterns = patterns('', url(r'^admin/', include(admin.site.urls)), url(r'^', include('jalka.game.urls')), ) and # -*- coding: utf-8 -*- from django.conf.urls.defaults import * from django.contrib.auth import views as auth_views urlpatterns = patterns('jalka.game.views', url(r'^$', view = 'front', name = 'front',), url(r'^ennusta/(?P<game_id>\d+)/$', view = 'ennusta', name = 'ennusta',), url(r'^login/$', auth_views.login, {'template_name': 'game/login.html'}, name='auth_login'), url(r'^logout/$', auth_views.logout, {'template_name': 'game/logout.html'}, name='auth_logout'), url(r'^arvuta/$', view = 'arvuta', name = 'arvuta',), ) and .htaccess is like that: Options +FollowSymLinks RewriteEngine on RewriteOptions MaxRedirects=10 # RewriteCond %{HTTP_HOST} . RewriteCond %{HTTP_HOST} ^www\.domain\.com RewriteRule (.*) http://domain.com/$1 [R=301,L] AddHandler fastcgi-script .fcgi RewriteCond %{HTTP_HOST} ^jalka\.domain\.com$ [NC] RewriteCond %{REQUEST_FILENAME} !-f RewriteRule ^(.*) cgi-bin/fifa2010.fcgi/$1 [QSA,L] RewriteCond %{HTTP_HOST} ^subdomain\.otherdomain\.eu$ [NC] RewriteCond %{REQUEST_FILENAME} !-f RewriteRule ^(.*) cgi-bin/django.fcgi/$1 [QSA,L] Notice, that i have also other project set up with same .htaccess and that one is running just fine with more complex urls and views fifa2010.fcgi: #!/usr/local/bin/python # -*- coding: utf-8 -*- import sys, os DOMAIN = "domain.com" APPNAME = "jalka" PREFIX = "/www/apache/domains/www.%s" % (DOMAIN,) # Add a custom Python path. sys.path.insert(0, os.path.join(PREFIX, "htdocs/django/Django-1.2.1")) sys.path.insert(0, os.path.join(PREFIX, "htdocs")) sys.path.insert(0, os.path.join(PREFIX, "htdocs/jalka")) # Switch to the directory of your project. (Optional.) os.chdir(os.path.join(PREFIX, "htdocs", APPNAME)) # Set the DJANGO_SETTINGS_MODULE environment variable. os.environ['DJANGO_SETTINGS_MODULE'] = "%s.settings" % (APPNAME,) from django.core.servers.fastcgi import runfastcgi runfastcgi(method="threaded", daemonize="false") Alan

    Read the article

  • Avoiding Duplicate Data in DB (for use with Rails)

    - by ants
    I have five tables that I am trying to get to work nicely together but may need some help. I have three main tables: accounts members and roles. With two join tables account_members and account_member_roles. The accounts and members table are joined by account_members (fk account_id and member_id) table. The other 2 tables are the problem (roles and account_member_roles). A member of an account can have more than one role and I have the account_member_roles (fk account_member_id and role_id) table joining the account_members join table and the roles table. That seems logical but can you have a relationship with a join table? What I'd like to be able to do is when creaeting an account, for instance, I would like @account.save to include the roles and update the account_member_roles table neatly ..... but through the account_members join table. I've tried ..... accept_nested_attributes_for :members, :account_member_roles in the account.rb but I get ..... ActiveRecord::HasManyThroughCantAssociateThroughHasManyReflection (Cannot modify association 'Account#account_member_roles' because the source reflection class 'AccountMemberRole' is associated to 'AccountMember' via :has_many.) upon trying to save a record. Any advice on how I should approach this? CIA -ants

    Read the article

  • Improving SQL Code

    - by jeremib
    I'm using Pervasive SQL. I have the following UNION of mulitple SQL statements. Is there a way to clean this up, especially the Pay Date an the Loc No fields that are selected in each statement. Is there a way to pull this out and have only one place to need to change those two fields? ( SELECT '23400' as Gl_Number, y.Plan as Description, 0 as Hours, ROUND(SUM(Ee_Curr),2) as Debit, 0 as Credit FROM "PR_YLOC" y LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No) WHERE y.Loc_No = 1041 AND s.Pay_Date = '2010-04-02' AND y.Code IN (100, 105, 110) AND y.Type = 3 GROUP BY y.Plan ) UNION ( SELECT '72000' as Gl_Number, y.Plan, 0, ROUND(SUM(Er_Curr),2), 0 FROM "PR_YLOC" y LEFT JOIN PR_SUMM s ON (s.Summ_No = y.Summ_No) WHERE y.Loc_No = 1041 AND s.Pay_Date = '2010-04-02' AND y.Code IN (100, 105, 110) AND y.Type = 3 GROUP BY y.Plan ) UNION ( SELECT '24800', c.Plan, 0, ROUND(SUM(Ee_Amt),2), 0 FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 100 GROUP BY c.Plan ) UNION ( SELECT '24800', c.Plan, 0, 0, ROUND(SUM(Ee_Amt),2) FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 115 GROUP BY c.Plan ) UNION ( SELECT '24150', c.Plan, 0, 0, ROUND(SUM(Ee_Amt),2) FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 241 GROUP BY c.Plan ) UNION ( SELECT '24150', c.Plan, 0, ROUND(SUM(Ee_Amt),2), 0 FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 239 GROUP BY c.Plan ) UNION ( SELECT '24120', c.Plan, 0, ROUND(SUM(Ee_Amt),2), 0 FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 230 GROUP BY c.Plan ) UNION ( SELECT '24100', c.Plan, 0, ROUND(SUM(Ee_Amt),2), 0 FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 225 GROUP BY c.Plan ) UNION ( SELECT '23800', c.Plan, 0, ROUND(SUM(Ee_Amt),2), 0 FROM "PR_CDED" c WHERE Pay_Date = '2010-04-02' AND Loc_No = 1041 AND Code = 245 GROUP BY c.Plan ) UNION ( select m.Def_Dept as Gl_Number, t.Short_Desc, (SELECT SUM(Hours) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Hours, (SELECT SUM(Pay_Amt) FROM pr_earn en WHERE en.Loc_No = e.Loc_No AND en.Emp_No = e.Emp_No AND en.Pay_Date = e.Pay_Date AND en.Pay_Code = e.Pay_Code) as Debit, 0 from pr_earn e left join pr_mast m on (e.Loc_No = m.Loc_No and e.Emp_No = m.Emp_No) left join pr_ptype t ON (t.Code = e.Pay_Code) where e.loc_no = 1041 and e.pay_date = '2010-04-02' group by m.Def_Dept, t.Short_Desc ) Thanks

    Read the article

  • SQL group results as a column array

    - by Radek
    Hi guys, this is an SQL question and don't know which type of JOIN, GROUP BY etc. to use, it is for a chat program where messages are related to rooms and each day in a room is linked to a transcript etc. Basically, when outputting my transcripts, I need to show which users have chatted on that transcript. At the moment I link them through the messages like so: SELECT rooms.id, rooms.name, niceDate, room_transcripts.date, long FROM room_transcripts JOIN rooms ON room_transcripts.room=rooms.id JOIN transcript_users ON transcript_users.room=rooms.id AND transcript_users.date=room_transcripts.date JOIN users ON transcript_users.user=users.id WHERE room_transcripts.deleted=0 AND rooms.id IN (1,2) ORDER BY room_transcripts.id DESC, long ASC The result set looks like this: Array ( [0] => Array ( [id] => 2 [name] => Room 2 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Jerry Seinfeld ) [1] => Array ( [id] => 1 [name] => Room 1 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Jerry Seinfeld ) [2] => Array ( [id] => 1 [name] => Room 1 [niceDate] => Wednesday, April 14 [date] => 2010-04-14 [long] => Test Users ) ) I would like though for each element in the array to represent one transcript entry and for the users to be grouped in an array as the entry's element. So 'long' will be an array listing all the names. Can this be done? At the moment I just append the names and when the transcript date and room changes I echo them retrospectively, but I will do the same for files and highlighted messages and it's messy. Thanks.

    Read the article

  • SQL Query Returning Duplicate Results

    - by Jesse Bunch
    Hi, I've been working out this query now for a while and I thought I had it where I wanted it, but apparently not. There are two records in the database (orders). The query should return two different rows, but instead returns two rows that have exactly the same values. I think it may be something to do with the GROUP BY or derived tables I'm using but my eyes are tired and not seeing the problem. Can any of you help? Thanks in advance. SELECT orders.billerID, orders.invoiceDate, orders.txnID, orders.bName, orders.bStreet1, orders.bStreet2, orders.bCity, orders.bState, orders.bZip, orders.bCountry, orders.sName, orders.sStreet1, orders.sStreet2, orders.sCity, orders.sState, orders.sZip, orders.sCountry, orders.paymentType, orders.invoiceNotes, orders.pFee, orders.shipping, orders.tax, orders.reasonCode, orders.txnType, orders.customerID, customers.firstName AS firstName, customers.lastName AS lastName, customers.businessName AS businessName, orderStatus.statusName AS orderStatus, IFNULL(orderItems.itemTotal, 0.00) + orders.shipping + orders.tax AS orderTotal, IFNULL(orderItems.itemTotal, 0.00) + orders.shipping + orders.tax - IFNULL(payments.totalPayments, 0.00) AS orderBalance FROM orders LEFT JOIN customers ON orders.customerID = customers.id LEFT JOIN orderStatus ON orders.orderStatus = orderStatus.id LEFT JOIN ( SELECT orderItems.orderID, SUM(orderItems.itemPrice * orderItems.itemQuantity) as itemTotal FROM orderItems GROUP BY orderItems.orderID ) orderItems ON orderItems.orderID = orders.id LEFT JOIN ( SELECT payments.orderID, SUM(payments.amount) as totalPayments FROM payments GROUP BY payments.orderID ) payments ON payments.orderID = orders.id

    Read the article

  • Python __subclasses__() not listing subclasses

    - by Mridang Agarwalla
    I cant seem to list all derived classes using the __subclasses__() method. Here's my directory layout: import.py backends __init__.py --digger __init__.py base.py test.py --plugins plugina_plugin.py From import.py i'm calling test.py. test.py in turn iterates over all the files in the plugins directory and loads all of them. test.py looks like this: import os import sys import re sys.path.append(os.path.join(os.path.abspath(os.path.dirname(os.path.abspath( __file__ ))))) sys.path.append(os.path.join(os.path.abspath(os.path.dirname(os.path.abspath( __file__ ))), 'plugins')) from base import BasePlugin class TestImport: def __init__(self): print 'heeeeello' PLUGIN_DIRECTORY = os.path.join(os.path.abspath(os.path.dirname(os.path.abspath( __file__ ))), 'plugins') for filename in os.listdir (PLUGIN_DIRECTORY): # Ignore subfolders if os.path.isdir (os.path.join(PLUGIN_DIRECTORY, filename)): continue else: if re.match(r".*?_plugin\.py$", filename): print ('Initialising plugin : ' + filename) __import__(re.sub(r".py", r"", filename)) print ('Plugin system initialized') print BasePlugin.__subclasses__() The problem us that the __subclasses__() method doesn't show any derived classes. All plugins in the plugins directory derive from a base class in the base.py file. base.py looks like this: class BasePlugin(object): """ Base """ def __init__(self): pass plugina_plugin.py looks like this: from base import BasePlugin class PluginA(BasePlugin): """ Plugin A """ def __init__(self): pass Could anyone help me out with this? Whatm am i doing wrong? I've racked my brains over this but I cant seem to figure it out Thanks.

    Read the article

  • JSON VIEW using GROUP_CONCAT question

    - by Dan Beam
    Hey DBAs and overall smart dudes. I have a question for you. We use MySQL VIEWs to format our data as JSON when it's returned (as a BLOB), which is convenient (though not particularly nice on performance, but we already know this). But, I can't seem to get a particular query working right now (each row contains NULL when it should contain a created JSON object with the values of multiple JOINs). Here's the general idea: SELECT CONCAT( "{", "\"some_list\":[", GROUP_CONCAT( DISTINCT t1.id ), "],", "\"other_list\":[", GROUP_CONCAT( DISTINCT t2.id ), "],", "}" ) cool_json FROM table_name tn INNER JOIN ( some_table st ) ON st.some_id = tn.id LEFT JOIN ( another_table at, another_one ao, used_multiple_times t1 ) ON st.id = at.some_id AND at.different_id = ao.different_id AND ao.different_id = t1.id LEFT JOIN ( another_table2 at2, another_one2 ao2, used_multiple_times t2 ) ON st.id = at2.some_id AND at2.different_id = ao2.different_id AND ao2.different_id = t2.id GROUP BY tn.id ORDER BY tn.name Anybody know the problem here? Am I missing something I should be grouping by? It was working when I was only doing 1 LEFT JOIN & GROUP_CONCAT, but now with multiple JOINs / GROUP_CONCATs it's messing it up. When I move the GROUP_CONCATs from the "cool_json" field they work as expected, but I'd like my data formatted as JSON so I can decode it server-side or client-side in one step.

    Read the article

  • postgres - ERROR: operator does not exist

    - by cino21122
    Again, I have a function that works fine locally, but moving it online yields a big fat error... Taking a cue from a response in which someone had pointed out the number of arguments I was passing wasn't accurate, I double-checked in this situation to be certain that I am passing 5 arguments to the function itself... Query failed: ERROR: operator does not exist: point <@> point HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. The query is this: BEGIN; SELECT zip_proximity_sum('zc', (SELECT g.lat FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1), (SELECT g.lon FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1), (SELECT m.zip FROM geocoded g LEFT JOIN masterfile m ON g.recordid = m.id WHERE m.zip = '10050' ORDER BY m.id LIMIT 1) ,10); The PG function is this: CREATE OR REPLACE FUNCTION zip_proximity_sum(refcursor, numeric, numeric, character, numeric) RETURNS refcursor AS $BODY$ BEGIN OPEN $1 FOR SELECT r.zip, point($2,$3) <@> point(g.lat, g.lon) AS distance FROM geocoded g LEFT JOIN masterfile r ON g.recordid = r.id WHERE (geo_distance( point($2,$3),point(g.lat,g.lon)) < $5) ORDER BY r.zip, distance; RETURN $1; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100;

    Read the article

  • Query with many CASE statements - optimization

    - by Nemanja Vujacic
    Hi guys, I have one very dirty query that per sure can be optimized because there are so many CASE statements in it! SELECT (CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id WHEN 2 THEN fw.fw_id WHEN 3 THEN s.sw_Id WHEN 4 THEN id.ia_id END) as Deal_Id, max(CASE pa.KplusTable_Id WHEN 1 THEN sp.Trans_Id WHEN 2 THEN fw.Trans_Id WHEN 3 THEN s.Trans_Id WHEN 4 THEN id.Trans_Id END) as TransId_CurrentMax INTO #MaxRazlicitOdNull FROM #PotencijalniAktuelni pa LEFT JOIN kplus_sp sp (nolock) on sp.sp_id=pa.Deal_Id AND pa.KplusTable_Id=1 LEFT JOIN kplus_fw fw (nolock) on fw.fw_id=pa.Deal_Id AND pa.KplusTable_Id=2 LEFT JOIN dev_sw s (nolock) on s.sw_Id=pa.Deal_Id AND pa.KplusTable_Id=3 LEFT JOIN kplus_ia id (nolock) on id.ia_id=pa.Deal_Id AND pa.KplusTable_Id=4 WHERE isnull(CASE pa.KplusTable_Id WHEN 1 THEN sp.BROJ_TIKETA WHEN 2 THEN fw.BROJ_TIKETA WHEN 3 THEN s.tiket WHEN 4 THEN id.BROJ_TIKETA END, '')<>'' GROUP BY CASE pa.KplusTable_Id WHEN 1 THEN sp.sp_id WHEN 2 THEN fw.fw_id WHEN 3 THEN s.sw_Id WHEN 4 THEN id.ia_id END Because I have same condition couple times, do you have idea how to optimize query, make it simpler and better. All suggestions are welcome! TnX in advance! Nemanja

    Read the article

< Previous Page | 91 92 93 94 95 96 97 98 99 100 101 102  | Next Page >