[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1703
  • Last Modified:

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

0
Xandolph75
Asked:
Xandolph75
  • 5
  • 4
1 Solution
 
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now