Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread James Smith
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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Cutter (CFRelated)
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

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread James Smith
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

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Dave Watts
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

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread James Smith
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... cfquery name=getGenres dbtype=query SELECT DISTINCT GenreDisplayName,GenreDisplayID FROM getGenres /cfquery In CF and that takes about a

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Ben Forta
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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Doug Brown
- From: James Smith [EMAIL PROTECTED] To: CF-Talk cf-talk@houseoffusion.com 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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Jochem van Dieten
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_Genres

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Denny Valliant
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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Tom Chiverton
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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Jim Wright
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

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Dave Watts
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

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread James Smith
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

RE: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread Andy Matthews
- 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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread James Mc
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

Re: Another MSSQL issue, why is DISTINCT so slow?

2006-10-04 Thread James Mc
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