SQL Efficiency

[cschristian78]

I am converting a Access database into VB.Net and when writing the SQL, I have queries based on queries based on more queries. (Below is an example). What is the best way to increase efficiency when using nested queries? Esp. ones that I use often.

Query 1 (__Accounts)
SELECT XPRO_LOC.ACCGRPID
FROM XPRO_LOC
GROUP BY XPRO_LOC.ACCGRPID

Query 2
SELECT ACCGRPID, Sum(BLANLIMAMT) AS SumOfBLANLIMAMT, Min(UNDCOVAMT) AS MinOfUNDCOVAMT

FROM
(SELECT XPRO_POLICY.POLICYID, XPRO_POLICY.ACCGRPID,XPRO_POLICY.BLANLIMAMT,XPRO_POLICY.UNDCOVAMT
FROM XPRO_POLICY INNER JOIN (XPRO_LOC INNER JOIN
(SELECT XPRO_LOC.ACCGRPID
FROM XPRO_LOC
GROUP BY XPRO_LOC.ACCGRPID) AS __Accounts ON XPRO_LOC.ACCGRPID = [__Accounts].ACCGRPID) ON XPRO_POLICY.ACCGRPID = [__Accounts].ACCGRPID
GROUP BY XPRO_POLICY.POLICYID, XPRO_POLICY.ACCGRPID, XPRO_POLICY.BLANLIMAMT, XPRO_POLICY.UNDCOVAMT) as zzzzz_NewCountAllPolicies
GROUP BY ACCGRPID

I have several other queries that need this query and so on. I repeat the __Accounts query every time. Is there somthing I can do to prevent having to run this query over and over?

Thanks

 

[mrdenny]

There isn't much you can do to avoid running the code over and over.

The good news is that SQL Server will buffer that data into cache so it won't have to go back to the disk each time.

Why are you running the code selecting a single column with a group by. Why not use a select distinct, it should run faster as a select distinct.

SELECT [COLOR=red]distinct[/color] XPRO_LOC.ACCGRPID
FROM XPRO_LOC

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[cschristian78]

Denny,
Thanks for the reply. I'll make that change to the query. The query takes about 30 minutes to run...I wanted to reduce the time it took if possible. The problem I have is that the query listed above is one of about 7 queries that do the same thing. For example, I count the number of policies that have an attachment of 0 vs >0 as well as all. Each of the three queries use the same query only thing being different is the attachment: 0, >, or all. this repetition seems costly. Any thoughts?

Thanks,
Scott