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
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
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
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
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
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
-
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
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
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
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
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
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
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
-
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
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
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
16 matches
Mail list logo