SELECT COUNT DISTINCT query very slow

Hello!
In my MS-SQL 2005 Express database I have a table "Asphalt" and a number of tables with information for certain asphalttests. These tables (e.g. AV_Hohlraumgehalt,...) have a column "FID_asphalt" that references the ID of the asphalt that was tested. This column accepts NULL-values.
What I want to do is to get the number of all the tests for each asphalt.
The attached query worked very well, as long as the number of entries in the test-tables was small. Now I have between 500 and 1000 entries in each test-table, and the query takes ~45 seconds to complete.
Is there any way to speed up the query? It mustn't take longer then 1-2 seconds to get the result, which I use in a C# Win-Forms program.
Please help.
Thanks a lot!
Xandolph
SELECT	Asphalt.ID_asphalt,
		COUNT(DISTINCT AV_Hohlraumgehalt.ID_AV_hohlraumgehalt) AS AnzahlHohlraum, 
        COUNT(DISTINCT AV_Raumdichte.ID_AV_raumdichte) AS AnzahlRaumdichte,
		COUNT(DISTINCT AV_Triax.ID_AV_triax) AS AnzahlTriax, 
        COUNT(DISTINCT AV_UTST.ID_AV_UTST) AS AnzahlUTST,
		COUNT(DISTINCT AV_TSRST.ID_AV_TSRST) AS AnzahlTSRST, 
        COUNT(DISTINCT AV_Ermuedung4PBB.ID_AV_ermuedung_4PBB) AS AnzahlFAT4PBB, 
		COUNT(DISTINCT AV_Steifigkeit.ID_AV_steifigkeit) AS AnzahlSteifigkeit, 
		COUNT(DISTINCT AV_ErmuedungDTCPR.ID_AV_ermuedung_DTCPR) AS AnzahlFATDTCPR
FROM    Asphalt LEFT OUTER JOIN
                      AV_ErmuedungDTCPR ON Asphalt.ID_asphalt = AV_ErmuedungDTCPR.FID_asphalt LEFT OUTER JOIN
                      AV_Steifigkeit ON Asphalt.ID_asphalt = AV_Steifigkeit.FID_asphalt LEFT OUTER JOIN
                      AV_Ermuedung4PBB ON Asphalt.ID_asphalt = AV_Ermuedung4PBB.FID_asphalt LEFT OUTER JOIN
                      AV_UTST ON Asphalt.ID_asphalt = AV_UTST.FID_asphalt LEFT OUTER JOIN
                      AV_TSRST ON Asphalt.ID_asphalt = AV_TSRST.FID_asphalt LEFT OUTER JOIN
                      AV_Triax ON Asphalt.ID_asphalt = AV_Triax.FID_asphalt LEFT OUTER JOIN
                      AV_Raumdichte ON Asphalt.ID_asphalt = AV_Raumdichte.FID_asphalt LEFT OUTER JOIN
                      AV_Hohlraumgehalt ON Asphalt.ID_asphalt = AV_Hohlraumgehalt.FID_asphalt
GROUP BY Asphalt.ID_asphalt
ORDER BY Asphalt.ID_asphalt

Open in new window

LVL 1
Xandolph75Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Do you have indexes on all ID_asphalt and FID_asphalt columns?
0
Xandolph75Author Commented:
ID_asphalt is a primary key and therefore a clustered index.
The FID_asphalt columns have no index, only the IDs of the test tables are primary keys and clustered indeces.
Shall I create a new index for every FID_asphalt? Clustered or NonClustered?
0
pcelbaCommented:
So, create index on all FID_asphalt columns, nonclustered should be enough. If this does not help then we have to redesign the query to avoid DISTINCT in counts.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Enroll Today
Xandolph75Author Commented:
I created an index for every FID_asphalt column (non-unique, non-clustered) but now the query took 1:00 minute instead of 45 seconds! I`m not too sure on the subject of indeces though and don`t know if I did it right...
0
pcelbaCommented:
So, it is time to remove DISTINCT...
SELECT 
   Asphalt.ID_asphalt, 
   a.RecCnt AS AnzahlHohlraum, 
   b.RecCnt AS AnzahlRaumdichte, 
   c.RecCnt AS AnzahlTriax, 
   d.RecCnt AS AnzahlUTST, 
   e.RecCnt AS AnzahlTSRST, 
   f.RecCnt AS AnzahlFAT4PBB, 
   g.RecCnt AS AnzahlSteifigkeit, 
   h.RecCnt AS AnzahlFATDTCPR 
 FROM Asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_Hohlraumgehalt GROUP BY FID_asphalt) a 
   ON Asphalt.ID_asphalt = a.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_Raumdichte GROUP BY FID_asphalt) b 
   ON Asphalt.ID_asphalt = b.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_Triax GROUP BY FID_asphalt) c 
   ON Asphalt.ID_asphalt = c.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_UTST GROUP BY FID_asphalt) d 
   ON Asphalt.ID_asphalt = d.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_Ermuedung4PBB GROUP BY FID_asphalt) e 
   ON Asphalt.ID_asphalt = e.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_Steifigkeit GROUP BY FID_asphalt) f 
   ON Asphalt.ID_asphalt = f.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_Steifigkeit GROUP BY FID_asphalt) g 
   ON Asphalt.ID_asphalt = g.FID_asphalt
 LEFT OUTER JOIN (SELECT FID_asphalt, COUNT(*) RecCnt FROM AV_ErmuedungDTCPR GROUP BY FID_asphalt) h 
   ON Asphalt.ID_asphalt = h.FID_asphalt
ORDER BY Asphalt.ID_asphalt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Xandolph75Author Commented:
Thanks a lot thats very nearly perfect. There is one more minor problem: Can I somehow set the NULL values in the results (where there are no tests at all for the asphalt) to zero (0)?
0
Xandolph75Author Commented:
Found the solution about two seconds after I postet my last message :-)
For those who are interested:
SELECT
   Asphalt.ID_asphalt,
   ISNULL(a.RecCnt,0) AS AnzahlHohlraum, ...

Thanks a lot again pcelba, now the query takes under 1 second!


0
Xandolph75Author Commented:
Brilliant and very fast!!!!
0
pcelbaCommented:
Yes, ISNULL() is the right solution.

You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.