Inefficient query

Transact-SQL
1

Hi,

I have the following query:

SELECT
lc.DownLoad
, lc.AccountNum
, lc.PolicyNum
--, splitLC.SplitPolicyNumLmt
, splitLC.SplitPolicyNumLmt
, splitLC.SplitPolicyNumCA
, splitLC.SplitPolicyNumReins
, lc.LCDetailGK
, splitLC.PolicyInsuredLocGK
, lc.CbiId
, lc.LCAmtUSD
, lc.LCAplbtyCode
, lc.LCTypeCode
, lc.PerLocInd
, lc.PolicyLevelInd
, lc.LCQualifierTypeCode
, lc.LCFrequencyCode
, 0 AS ProcessInd
FROM
AIRGBS.vLCExtract AS lc
INNER JOIN AIRGBS.SplitLCLimits AS splitLC
ON splitLC.AccountNum = lc.AccountNum
AND splitLC.PolicyNum = lc.PolicyNum
AND splitLC.DownLoad = lc.DownLoad
AND splitLC.CbiID = lc.CbiId
AND splitLC.PolicyInsuredLocGK = lc.PolicyInsuredLocGK
WHERE
lc.PerLocInd = 'N'
AND lc.LCTypeCode = 'LIM'
AND
(
lc.LCDetailGK <> splitLC.PolicyLCDetailGK
AND lc.LCDetailGK <> splitLC.PolicyTEDetailGK
AND lc.LCDetailGK <> splitLC.PolicyPDDetailGK
AND lc.LCDetailGK <> splitLC.LocLCDetailGK
AND lc.LCDetailGK <> splitLC.LocPDDetailGK
AND lc.LCDetailGK <> splitLC.LocTEDetailGK
)
AND
1 =
(
SELECT
COUNT(DISTINCT lc2.PolicyInsuredLocGK)
FROM
AIRGBS.vLCExtract AS lc2
WHERE
lc.AccountNum = lc2.AccountNum
AND lc.PolicyNum = lc2.PolicyNum
AND lc.DownLoad = lc2.DownLoad
AND lc.LCDetailGK = lc2.LCDetailGK
)

Basically, this query chokes with the last piece where I have the 1 = ....

I need to only bring back rows from AIRGBS.vLCExtract where this is only 1 distinct PolicyInsuredLocGK for each combination of AccountNum, PolicyNum, Download, and LCDetailGK.

That view has about 2 million rows. The rest of the query runs in about 6 minutes, but takes 4 hours when I add the last piece of code with the 1 = ...

2

Use the original tables not the view if at all possible. But I can't do that code for you without the DDL for the view and for the underlying tables.

Using the existing view, this should help keep from having to materialize the view multiple times:

SELECT
lc.DownLoad
, lc.AccountNum
, lc.PolicyNum
--, splitLC.SplitPolicyNumLmt
, splitLC.SplitPolicyNumLmt
, splitLC.SplitPolicyNumCA
, splitLC.SplitPolicyNumReins
, lc.LCDetailGK
, splitLC.PolicyInsuredLocGK
, lc.CbiId
, lc.LCAmtUSD
, lc.LCAplbtyCode
, lc.LCTypeCode
, lc.PerLocInd
, lc.PolicyLevelInd
, lc.LCQualifierTypeCode
, lc.LCFrequencyCode
, 0 AS ProcessInd
FROM
AIRGBS.vLCExtract AS lc
INNER JOIN AIRGBS.SplitLCLimits AS splitLC
ON splitLC.AccountNum = lc.AccountNum
AND splitLC.PolicyNum = lc.PolicyNum
AND splitLC.DownLoad = lc.DownLoad
AND splitLC.CbiID = lc.CbiId
AND splitLC.PolicyInsuredLocGK = lc.PolicyInsuredLocGK
INNER JOIN (
    SELECT
    AccountNum,
    PolicyNum,
    DownLoad,
    LCDetailGK,
    COUNT(DISTINCT PolicyInsuredLocGK)
    FROM AIRGBS.vLCExtract
    GROUP BY 
    AccountNum,
    PolicyNum,
    DownLoad,
    LCDetailGK
) AS lc2 ON 
lc.AccountNum = lc2.AccountNum
AND lc.PolicyNum = lc2.PolicyNum
AND lc.DownLoad = lc2.DownLoad
AND lc.LCDetailGK = lc2.LCDetailGK
WHERE
lc.PerLocInd = 'N'
AND lc.LCTypeCode = 'LIM'
AND 
(
lc.LCDetailGK <> splitLC.PolicyLCDetailGK
AND lc.LCDetailGK <> splitLC.PolicyTEDetailGK
AND lc.LCDetailGK <> splitLC.PolicyPDDetailGK
AND lc.LCDetailGK <> splitLC.LocLCDetailGK
AND lc.LCDetailGK <> splitLC.LocPDDetailGK
AND lc.LCDetailGK <> splitLC.LocTEDetailGK
)