Why does my query run too long

Question

I need feedback on why query is taking too long to run

With DtlProperty (PolicyNumber)

As 
(SELECT
          Archive.PolicyNumber
        FROM CLUEReporting.dbo.CLUE_Archive Archive
        Left Join CLUEReporting.dbo.PropertyAddress On (PropertyAddress.PolicyNumber = Archive.PolicyNumber)
        WHERE Archive.PolicyNumber = PropertyAddress.PolicyNumber
        AND Archive.Street       = PropertyAddress.LocAddress1
        AND Archive.Street2      = PropertyAddress.LocAddress2
        AND Archive.City         = PropertyAddress.LocCity
        AND Archive.State        = PropertyAddress.LocState
        AND Archive.Zip          = PropertyAddress.LocZipCode
),

     DtlMailing (PolicyNumber)

As 
(SELECT
          Archive.PolicyNumber
        FROM CLUEReporting.dbo.CLUE_Archive Archive
        Left Join CLUEReporting.dbo.PropertyAddress On (PropertyAddress.PolicyNumber = Archive.PolicyNumber)
        WHERE Archive.PolicyNumber = PropertyAddress.PolicyNumber
        AND Archive.Street       = PropertyAddress.bioAddress1
        AND Archive.Street2      = PropertyAddress.bioAddress2
        AND Archive.City         = PropertyAddress.bioCity
        AND Archive.State        = PropertyAddress.bioState
        AND Archive.Zip          = PropertyAddress.bioZipCode
)


--Property Address and Mailing Address
SELECT DISTINCT
  PropertyAddress.PolicyNumber                  AS PolicyNumber,
  PropertyAddress.PreviousPolicyID              AS PreviousPolicyID,
  ''                                            AS ClaimID,
  '"' + PropertyAddress.LocAddress1 + '"'       AS Street,
  '"' + PropertyAddress.LocAddress2 + '"'       AS Street2,
  '"' + PropertyAddress.LocCity + '"'           AS City,
  PropertyAddress.LocState          AS State,
  PropertyAddress.LocZipCode        AS Zip,
  'B'                               AS AddressType,
  PropertyAddress.DATABASEPOLICY    AS DATABASEPOLICY,
  PropertyAddress.ENTRYDATE         AS ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress
Left  Join DtlProperty  DtlProperty On (DtlProperty.PolicyNumber = PropertyAddress.PolicyNumber)

WHERE DtlProperty.PolicyNumber is  null 


AND PropertyAddress.PolicyNumber  IN 
        (SELECT
          PolicyNumber
        FROM CLUEReporting.dbo.PropertyAddress
        WHERE PropertyAddress.LocAddress1 = PropertyAddress.bioAddress1
        AND PropertyAddress.LocAddress2   = PropertyAddress.bioAddress2
        AND PropertyAddress.LocCity       = PropertyAddress.bioCity
        AND PropertyAddress.LocState      = PropertyAddress.bioState
        AND PropertyAddress.LocZipCode    = PropertyAddress.bioZipCode)

UNION ALL

--Property Address
SELECT DISTINCT
  PropertyAddress.PolicyNumber                  AS PolicyNumber,
  PropertyAddress.PreviousPolicyID              AS PreviousPolicyID,
  ''                                            AS ClaimID,
  '"' + PropertyAddress.LocAddress1 + '"'       AS Street,
  '"' + PropertyAddress.LocAddress2 + '"'       AS Street2,
  '"' + PropertyAddress.LocCity + '"'           AS City,
  PropertyAddress.LocState                      AS State,
  PropertyAddress.LocZipCode                    AS Zip,
  'P'                                           AS AddressType,
  PropertyAddress.DATABASEPOLICY                AS DATABASEPOLICY,
  PropertyAddress.ENTRYDATE                     AS ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress
Left  Join DtlProperty  DtlProperty On (DtlProperty.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE DtlProperty.PolicyNumber is  null 


AND PropertyAddress.PolicyNumber  IN 
        (SELECT
          PolicyNumber
        FROM CLUEReporting.dbo.PropertyAddress
        WHERE (PropertyAddress.LocAddress1 != PropertyAddress.bioAddress1
        OR PropertyAddress.LocAddress2     != PropertyAddress.bioAddress2
        OR PropertyAddress.LocCity         != PropertyAddress.bioCity
        OR PropertyAddress.LocState        != PropertyAddress.bioState
        OR PropertyAddress.LocZipCode      != PropertyAddress.bioZipCode))

UNION ALL

--Mailing Address
SELECT DISTINCT
  PropertyAddress.PolicyNumber                  AS PolicyNumber,
  PropertyAddress.PreviousPolicyID              AS PreviousPolicyID,
  ''                                            AS ClaimID,
  '"' + PropertyAddress.bioAddress1 + '"'       AS Street,
  '"' + PropertyAddress.bioAddress2 + '"'       AS Street2,
  '"' + PropertyAddress.bioCity + '"'           AS City,

  PropertyAddress.bioState                      AS State,
  PropertyAddress.bioZipCode                    AS Zip,
  'M'                                           AS AddressType,
  PropertyAddress.DATABASEPOLICY                AS DATABASEPOLICY,
  PropertyAddress.ENTRYDATE                     AS ENTRYDATE
FROM CLUEReporting.dbo.PropertyAddress
Left  Join DtlMailing  DtlMailing On (DtlMailing.PolicyNumber = PropertyAddress.PolicyNumber)
WHERE DtlMailing.PolicyNumber is  null  

AND PropertyAddress.PolicyNumber  IN 
        (SELECT
          PolicyNumber
        FROM CLUEReporting.dbo.PropertyAddress
        WHERE (PropertyAddress.LocAddress1 != PropertyAddress.bioAddress1
        OR PropertyAddress.LocAddress2     != PropertyAddress.bioAddress2
        OR PropertyAddress.LocCity         != PropertyAddress.bioCity
        OR PropertyAddress.LocState        != PropertyAddress.bioState
        OR PropertyAddress.LocZipCode      != PropertyAddress.bioZipCode))

The query runs too long. Basically the query checks if the property address is on the archive table and then determines if the property address and the mailing address is not the same. The query is running over 35 minutes.

SSMS has a good feature called Query Execution Plan; Use that to see where the issue lies. It could be the index, the join or anything, no one tell you with just the query without knowing all the indexes/keys etc. [Further Reading](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan) — Polynomial Proton, Mar 21 '17 at 20:06

score 0 · Answer 1 · answered Mar 21 '17 at 20:16

This doesn't look good, especially not twice:

AND PropertyAddress.PolicyNumber  IN 
        (SELECT
          PolicyNumber
        FROM CLUEReporting.dbo.PropertyAddress
        WHERE (PropertyAddress.LocAddress1 != PropertyAddress.bioAddress1
        OR PropertyAddress.LocAddress2     != PropertyAddress.bioAddress2
        OR PropertyAddress.LocCity         != PropertyAddress.bioCity
        OR PropertyAddress.LocState        != PropertyAddress.bioState
        OR PropertyAddress.LocZipCode      != PropertyAddress.bioZipCode))

Why does my query run too long

1 Answers1