Multiple Fields in DISTINCT or GROUP BY causes problems

[BlakeK]

I have an interesting problem with the following query...

SELECT DISTINCT tbl_insite_manufacturer_def.insite_manufacturer_id, 
tbl_insite_manufacturer_def.manufacturer_name 
FROM tbl_server_installs, tbl_insite_feu_radio_events, 
tbl_insite_feus, tbl_insite_manufacturer_def
WHERE tbl_server_installs.server_install_id = tbl_insite_feu_radio_events.server_install_id 
AND tbl_insite_feu_radio_events.insite_feu_id = tbl_insite_feus.insite_feu_id 
AND tbl_insite_feus.insite_manufacturer_id = tbl_insite_manufacturer_def.insite_manufacturer_id 
AND tbl_insite_feus.insite_manufacturer_id <> 1 
AND tbl_insite_feu_radio_events.server_install_id IN (1419,1473) 
ORDER BY tbl_insite_manufacturer_def.manufacturer_name

If I run that query, even when I am the only user in the system, the processors on the SQL server spike to 100% and the query sits and runs forever (never let it run longer than 5 minutes before killing it).

I tried using a GROUP BY instead of a DISTINCT...

SELECT tbl_insite_manufacturer_def.insite_manufacturer_id, 
tbl_insite_manufacturer_def.manufacturer_name 
FROM tbl_server_installs, tbl_insite_feu_radio_events, 
tbl_insite_feus, tbl_insite_manufacturer_def
WHERE tbl_server_installs.server_install_id = tbl_insite_feu_radio_events.server_install_id 
AND tbl_insite_feu_radio_events.insite_feu_id = tbl_insite_feus.insite_feu_id 
AND tbl_insite_feus.insite_manufacturer_id = tbl_insite_manufacturer_def.insite_manufacturer_id 
AND tbl_insite_feus.insite_manufacturer_id <> 1 
AND tbl_insite_feu_radio_events.server_install_id IN (1419,1473) 
GROUP BY tbl_insite_manufacturer_def.insite_manufacturer_id, 
tbl_insite_manufacturer_def.manufacturer_name 
ORDER BY tbl_insite_manufacturer_def.manufacturer_name

And it has the same result.

Now here comes the interesting part.
If I remove either of the fields from the SELECT clause, the query runs in under 2 seconds.
So, this query...

SELECT DISTINCT tbl_insite_manufacturer_def.manufacturer_name 
FROM tbl_server_installs, tbl_insite_feu_radio_events, 
tbl_insite_feus, tbl_insite_manufacturer_def
WHERE tbl_server_installs.server_install_id = tbl_insite_feu_radio_events.server_install_id 
AND tbl_insite_feu_radio_events.insite_feu_id = tbl_insite_feus.insite_feu_id 
AND tbl_insite_feus.insite_manufacturer_id = tbl_insite_manufacturer_def.insite_manufacturer_id 
AND tbl_insite_feus.insite_manufacturer_id <> 1 
AND tbl_insite_feu_radio_events.server_install_id IN (1419,1473) 
ORDER BY tbl_insite_manufacturer_def.manufacturer_name

And this query...

SELECT DISTINCT tbl_insite_manufacturer_def.insite_manufacturer_id
FROM tbl_server_installs, tbl_insite_feu_radio_events, 
tbl_insite_feus, tbl_insite_manufacturer_def
WHERE tbl_server_installs.server_install_id = tbl_insite_feu_radio_events.server_install_id 
AND tbl_insite_feu_radio_events.insite_feu_id = tbl_insite_feus.insite_feu_id 
AND tbl_insite_feus.insite_manufacturer_id = tbl_insite_manufacturer_def.insite_manufacturer_id 
AND tbl_insite_feus.insite_manufacturer_id <> 1 
AND tbl_insite_feu_radio_events.server_install_id IN (1419,1473) 
ORDER BY tbl_insite_manufacturer_def.insite_manufacturer_id

As well as the same single field SELECTs suing GROUP BY instead of DISTINCT all return results almost instantly. In addition, there is no noticeable spike in the SQL server processor usage when running those single SELECT queries.
I am confused!
FYI, I ran the original query through the Index Tuning Wizard and it had no recommendations.
Also FYI, if you run the original query without DISTINCT or GROUP BY, the results set is just over 1 million rows. The number of distinct rows that result out of those million plus rows is 3 rows. The single select queries on just the id and then the name field return the correct 3 rows. The original query without the DISTINCT or GROUP BY that returns over 1 million rows runs in about 8 seconds.
A final note, I have also written the query to join the table by using INNER JOINs rather than WHERE clause criteria, and the results are the same.
I'm kind of out of ideas on this one.
Any thoughts on what else to investigate, I would appreciate it.

 

[Catadmin]

Have you checked the Display Estimated Execution Plan for this query (unaltered) yet? Then compare it against the altered query to see if there's a difference in the plans?

I had a problem with a 58% Table Scan issue yesterday when I was looking for particular fields. It might be you're stuck in Table Scan mode which is causing the spike.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!