cschristian78
Technical User
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
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