Re: Another MSSQL issue, why is DISTINCT so slow?
I'm not a SQL expert, by any means, but wouldn't it run faster if you optimized your 'JOIN's a little bit? Seems like you're leaving it up to SQL, right now, to determine on the fly whether you need an INNER, OUTER, LEFT or RIGHT JOIN? You know your data structure, so you should be able to figure out which type of JOIN is necessary in each statement. By explicitly defining it you decrease the server overhead because it doesn't have to figure it out for you. But, I could be wrong. Like I said, I'm no SQL expert. There are many here better qualified. Cutter ___ http://blog.cutterscrossing.com James Smith wrote: > I am running the following query... > > SELECT DISTINCT gd.GenreDisplayName, gd.GenreDisplayID > FROM ProductData.dbo.t_PI_GenresDisplay gd > JOIN ProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON > (gd.GenreDisplayID = lggd.GenreDisplayID) > JOIN ProductData.dbo.t_PI_GenresgON > (lggd.GenreID = g.GenreID) > JOIN ProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID > = lmg.GenreID) > JOIN ProductData.dbo.t_PI_Main mON > (lmg.ProductID = m.ProductID) > WHEREm.checkEdited = 1 > ANDm.ProductGroupID = 3 > ANDgd.GenreDisplayID != 1 > ORDER BY GenreDisplayName > > And it takes 1m 15s to return the 46 rows in the result set. > > If I remove the DISTINCT constraint it returns 2732 rows in under a second. > How can it posibly take so long to remove the duplicates? > > What can I do to speed this up as it is a requirement but 1:15 is > unacceptable. > > -- > Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255361 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Another MSSQL issue, why is DISTINCT so slow?
> I'm not a SQL expert, by any means, but wouldn't it run > faster if you optimized your 'JOIN's a little bit? Seems like > you're leaving it up to SQL, right now, to determine on the > fly whether you need an INNER, OUTER, LEFT or RIGHT JOIN? You > know your data structure, so you should be able to figure out > which type of JOIN is necessary in each statement. > By explicitly defining it you decrease the server overhead > because it doesn't have to figure it out for you. I wasn't hopefull but I tested it anyway, still takes under a second without the DISTINCT and the exact same 1:15 with it. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255363 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Another MSSQL issue, why is DISTINCT so slow?
> If I remove the DISTINCT constraint it returns 2732 rows in > under a second. How can it posibly take so long to remove the > duplicates? This kind of operation is almost always significantly slower. Your query can take advantage of indexes, but DISTINCT requires an examination of individual values. Your execution plan will tell you why in more detail. > What can I do to speed this up as it is a requirement but > 1:15 is unacceptable. I don't know if there's much you can do, but your execution plan should give you some hints if there is. Otherwise, just for comparison, you might try filtering the records in CF instead of SQL. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255364 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Another MSSQL issue, why is DISTINCT so slow?
What makes this silly is that if I run the query without the DISTINCT it takes a fraction of a second to return a couple of thousand rows. I can then do a... SELECT DISTINCT GenreDisplayName,GenreDisplayID FROM getGenres In CF and that takes about a second, so why is it taking 1:15 for MSSQL do do it in one operation? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255365 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Another MSSQL issue, why is DISTINCT so slow?
Have you tried defining an index on GenreDisplayName? --- Ben -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 04, 2006 7:08 AM To: CF-Talk Subject: RE: Another MSSQL issue, why is DISTINCT so slow? What makes this silly is that if I run the query without the DISTINCT it takes a fraction of a second to return a couple of thousand rows. I can then do a... SELECT DISTINCT GenreDisplayName,GenreDisplayID FROM getGenres In CF and that takes about a second, so why is it taking 1:15 for MSSQL do do it in one operation? -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255368 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Another MSSQL issue, why is DISTINCT so slow?
It is really easy for CF using QoQ, since the heavy lifting was already done by sql. The results of your first query was already there for CF to play with, and it did not have to go back to sql for more results. Anyway, that is why is is faster for CF sometimes. - Original Message - From: "James Smith" <[EMAIL PROTECTED]> To: "CF-Talk" Sent: Wednesday, October 04, 2006 5:08 AM Subject: RE: Another MSSQL issue, why is DISTINCT so slow? > What makes this silly is that if I run the query without the DISTINCT it > takes a fraction of a second to return a couple of thousand rows. I can > then do a... > > > SELECT DISTINCT GenreDisplayName,GenreDisplayID > FROM getGenres > > > In CF and that takes about a second, so why is it taking 1:15 for MSSQL do > do it in one operation? > > -- > Jay > > > ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255373 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Another MSSQL issue, why is DISTINCT so slow?
James Smith wrote: > > SELECT DISTINCT gd.GenreDisplayName, gd.GenreDisplayID > FROM ProductData.dbo.t_PI_GenresDisplay gd > JOIN ProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON > (gd.GenreDisplayID = lggd.GenreDisplayID) > JOIN ProductData.dbo.t_PI_GenresgON > (lggd.GenreID = g.GenreID) > JOIN ProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID > = lmg.GenreID) > JOIN ProductData.dbo.t_PI_Main mON > (lmg.ProductID = m.ProductID) > WHEREm.checkEdited = 1 > ANDm.ProductGroupID = 3 > ANDgd.GenreDisplayID != 1 > ORDER BY GenreDisplayName > > And it takes 1m 15s to return the 46 rows in the result set. > > If I remove the DISTINCT constraint it returns 2732 rows in under a second. > How can it posibly take so long to remove the duplicates? Most likely the execution paths between both queries are completely different. ISTM that without the DISTINCT the query would be driven of an index scan on t_PI_Main because there are two filters on that (m.checkEdited = 1 and m.ProductGroupID = 3). But when you add the DISTINCT you have three filters on t_PI_GenresDisplay (GenreDisplayID != 1, the ORDER BY and the DISTINCT) and the query is driven from t_PI_GenresDisplay. But that is just speculation, the proof is in the execution plan of the query. Anyhow, the question you need to ask yourself is why you need the DISTINCT. Somewhere, in some table or some JOIN the duplicates are introduced. Find that place and push the DISTINCT down to there. For instance, if the duplicates are introduced in t_PI_Main, your query should be: SELECT gd.GenreDisplayName, gd.GenreDisplayID FROM ProductData.dbo.t_PI_GenresDisplay gd JOIN ProductData.t_PI_L_GenresGenresDisplay lggd ON (gd.GenreDisplayID = lggd.GenreDisplayID) JOIN ProductData.dbo.t_PI_Genres g ON (lggd.GenreID = g.GenreID) JOIN ProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID = lmg.GenreID) JOIN ( SELECT DISTINCT ProductID FROM ProductData.dbo.t_PI_Main WHERE m.checkEdited = 1 ANDm.ProductGroupID = 3 ) m ON (lmg.ProductID = m.ProductID) WHEREgd.GenreDisplayID != 1 ORDER BY GenreDisplayName Jochem ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255375 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Another MSSQL issue, why is DISTINCT so slow?
On 10/4/06, James Smith <[EMAIL PROTECTED]> wrote: > In CF and that takes about a second, so why is it taking 1:15 for MSSQL do > do it in one operation? Theoretically, that's what query analizers are for. Shooting from the hip, I'll second the index suggestion. Shooting from the head, I'll second the execution plan* checking suggestion. *EXPLAIN SELECT blah.baah blah.baaah (IIRC, if it fits the db.) =] There is webinfo on how to read this info and maximize your speed too. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255377 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Another MSSQL issue, why is DISTINCT so slow?
On Wednesday 04 October 2006 12:05, Dave Watts wrote: > > If I remove the DISTINCT constraint it returns 2732 rows in > > under a second. How can it posibly take so long to remove the > > duplicates? > > This kind of operation is almost always significantly slower. Your query > can take advantage of indexes, but DISTINCT requires an examination of > individual values. Your execution plan will tell you why in more detail. Does MSSQL not support unique indexes ? -- Tom Chiverton Helping to efficiently introduce B2B architectures This email is sent for and on behalf of Halliwells LLP. Halliwells LLP is a limited liability partnership registered in England and Wales under registered number OC307980 whose registered office address is at St James's Court Brown Street Manchester M2 2JF. A list of members is available for inspection at the registered office. Any reference to a partner in relation to Halliwells LLP means a member of Halliwells LLP. Regulated by the Law Society. CONFIDENTIALITY This email is intended only for the use of the addressee named above and may be confidential or legally privileged. If you are not the addressee you must not read it and must not use any information contained in nor copy it nor inform any person other than Halliwells LLP or the addressee of its existence or contents. If you have received this email in error please delete it and notify Halliwells LLP IT Department on 0870 365 8008. For more information about Halliwells LLP visit www.halliwells.com. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255379 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Another MSSQL issue, why is DISTINCT so slow?
James Smith wrote: > > In CF and that takes about a second, so why is it taking 1:15 for MSSQL do > do it in one operation? > One thing to keep in mind is that the SQL you write and send to SQL Server is being interpreted by their query optimization code...which is written by humans and fallible...generally it works real well, but there are a lot of moving parts with SQL...and sometimes the optimization does just the opposite. I've had queries that returned in a couple seconds go up to a couple of minutes with the application of certain service packs with optimization code "upgrades" (much more of an issue with SQL 7). One more vote for reviewing the execution plan...it may give you insight into the mind of the optimizer. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255384 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Another MSSQL issue, why is DISTINCT so slow?
> Does MSSQL not support unique indexes ? Yes, it does. However, it's unlikely that a unique index covers all the fields used by the query in this case, I think. I didn't read the original query too closely - I'm checking mail using a portable device - but it appears that the two columns are a possible candidate for a cover index. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255388 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Another MSSQL issue, why is DISTINCT so slow?
> Have you tried defining an index on GenreDisplayName? > > --- Ben That was the first thing I tried, no improvement. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255391 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
RE: Another MSSQL issue, why is DISTINCT so slow?
Have you indexed the GenreDisplayName column? That should make a huge difference in query times. -Original Message- From: James Smith [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 04, 2006 6:01 AM To: CF-Talk Subject: RE: Another MSSQL issue, why is DISTINCT so slow? > I'm not a SQL expert, by any means, but wouldn't it run > faster if you optimized your 'JOIN's a little bit? Seems like > you're leaving it up to SQL, right now, to determine on the > fly whether you need an INNER, OUTER, LEFT or RIGHT JOIN? You > know your data structure, so you should be able to figure out > which type of JOIN is necessary in each statement. > By explicitly defining it you decrease the server overhead > because it doesn't have to figure it out for you. I wasn't hopefull but I tested it anyway, still takes under a second without the DISTINCT and the exact same 1:15 with it. -- Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Another MSSQL issue, why is DISTINCT so slow?
>>How can it posibly take so long to remove the duplicates? I would check for the existence of appropriate indexes in the database. -- ___ REUSE CODE! Use custom tags; See http://www.contentbox.com/claude/customtags/tagstore.cfm (Please send any spam to this address: [EMAIL PROTECTED]) Thanks. ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255411 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Another MSSQL issue, why is DISTINCT so slow?
Try this: SELECT DISTINCT gd.GenreDisplayName, gd.GenreDisplayID FROMProductData.dbo.t_PI_GenresDisplay gd JOINProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON gd.GenreDisplayID = lggd.GenreDisplayID JOINProductData.dbo.t_PI_Genres g ON (lggd.GenreID = g.GenreID) JOINProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID= lmg.GenreID) JOINProductData.dbo.t_PI_Main m ON (lmg.ProductID = m.ProductID) WHEREm.checkEdited = 1 ANDm.ProductGroupID = 3 ANDgd.GenreDisplayID != 1 group by gd.GenreDisplayName, gd.GenreDisplayID ORDER BY GenreDisplayName >I am running the following query... > >SELECT DISTINCT gd.GenreDisplayName, gd.GenreDisplayID >FROM ProductData.dbo.t_PI_GenresDisplay gd > JOIN ProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON >(gd.GenreDisplayID = lggd.GenreDisplayID) > JOIN ProductData.dbo.t_PI_GenresgON >(lggd.GenreID = g.GenreID) > JOIN ProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID >= lmg.GenreID) > JOIN ProductData.dbo.t_PI_Main mON >(lmg.ProductID = m.ProductID) >WHEREm.checkEdited = 1 > ANDm.ProductGroupID = 3 > ANDgd.GenreDisplayID != 1 >ORDER BY GenreDisplayName > >And it takes 1m 15s to return the 46 rows in the result set. > >If I remove the DISTINCT constraint it returns 2732 rows in under a second. >How can it posibly take so long to remove the duplicates? > >What can I do to speed this up as it is a requirement but 1:15 is >unacceptable. > >-- >Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255415 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Another MSSQL issue, why is DISTINCT so slow?
Sorry about that. Try this SELECT gd.GenreDisplayName, gd.GenreDisplayID FROMProductData.dbo.t_PI_GenresDisplay gd JOINProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON gd.GenreDisplayID = lggd.GenreDisplayID JOINProductData.dbo.t_PI_Genres g ON (lggd.GenreID = g.GenreID) JOINProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID= lmg.GenreID) JOINProductData.dbo.t_PI_Main m ON (lmg.ProductID = m.ProductID) WHEREm.checkEdited = 1 ANDm.ProductGroupID = 3 ANDgd.GenreDisplayID != 1 group by gd.GenreDisplayName, gd.GenreDisplayID ORDER BY GenreDisplayName >I am running the following query... > >SELECT DISTINCT gd.GenreDisplayName, gd.GenreDisplayID >FROM ProductData.dbo.t_PI_GenresDisplay gd > JOIN ProductData.dbo.t_PI_L_GenresGenresDisplay lggd ON >(gd.GenreDisplayID = lggd.GenreDisplayID) > JOIN ProductData.dbo.t_PI_GenresgON >(lggd.GenreID = g.GenreID) > JOIN ProductData.dbo.t_PI_L_MainGenres lmg ON (g.GenreID >= lmg.GenreID) > JOIN ProductData.dbo.t_PI_Main mON >(lmg.ProductID = m.ProductID) >WHEREm.checkEdited = 1 > ANDm.ProductGroupID = 3 > ANDgd.GenreDisplayID != 1 >ORDER BY GenreDisplayName > >And it takes 1m 15s to return the 46 rows in the result set. > >If I remove the DISTINCT constraint it returns 2732 rows in under a second. >How can it posibly take so long to remove the duplicates? > >What can I do to speed this up as it is a requirement but 1:15 is >unacceptable. > >-- >Jay ~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255416 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
