April 22, 2016 at 5:28 am
I Need help in optimizing the query ,
Could you please throw some techniques or any valid query for replacement of this query . Help is highly appreciated as i am in dead lock to finish this .
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
LEFT JOIN Systems ON Systems.ID = PolicyAudit.SourceSystemID
OUTER APPLY (SELECT Top(1) pa.HasERN,
st.SiteNumber,
pa.PolicyID,
CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) as 'DateSent1' ,
ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
WHERE rtrim(ltrim(pa.PolicyID)) =PolicyAudit.PolicyID
And pa.Status=1
Order By pa.ID desc ) Derived_Policy_Audit_Latest
WHERE PolicyAudit.Status =1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator in ('C','V')
and PolicyAudit.policyid=Derived_Policy_Audit_Latest.policyid
and CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112)=Derived_Policy_Audit_Latest.DateSent1
and Systems.SiteNumber=Derived_Policy_Audit_Latest.SiteNumber
and Derived_Policy_Audit_Latest.HasERN=0
April 22, 2016 at 6:03 am
mbavabohrude (4/22/2016)
I Need help in optimizing the query ,Could you please throw some techniques or any valid query for replacement of this query . Help is highly appreciated as i am in dead lock to finish this .
SELECT Distinct Systems.SiteNumber AS 'SiteNumber',
PolicyAudit.PolicyID AS 'PolicyID',
PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag',
CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
LEFT JOIN Systems ON Systems.ID = PolicyAudit.SourceSystemID
OUTER APPLY (SELECT Top(1) pa.HasERN,
st.SiteNumber,
pa.PolicyID,
CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) as 'DateSent1' ,
ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
WHERE rtrim(ltrim(pa.PolicyID)) =PolicyAudit.PolicyID
And pa.Status=1
Order By pa.ID desc ) Derived_Policy_Audit_Latest
WHERE PolicyAudit.Status =1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator in ('C','V')
and PolicyAudit.policyid=Derived_Policy_Audit_Latest.policyid
and CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112)=Derived_Policy_Audit_Latest.DateSent1
and Systems.SiteNumber=Derived_Policy_Audit_Latest.SiteNumber
and Derived_Policy_Audit_Latest.HasERN=0
Technique #1: please include the actual execution plan and DDL for the tables from are selecting from in your performance-related posts 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 22, 2016 at 6:05 am
Technique #2: Make your query readable by using indentation and code="sql" tags:
SELECT DISTINCT
Systems.SiteNumber AS 'SiteNumber'
, PolicyAudit.PolicyID AS 'PolicyID'
, PolicyAudit.ERNExemptFlag AS 'ERNExemptFlag'
, CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) AS 'DateSent'
FROM PolicyAudit
LEFT JOIN Systems ON Systems.ID = PolicyAudit.SourceSystemID
OUTER APPLY (SELECT TOP (1)
pa.HasERN
, st.SiteNumber
, pa.PolicyID
, CONVERT(VARCHAR(8), pa.LastModifiedDate, 112) AS 'DateSent1'
, ERNExemptFlag
FROM PolicyAudit pa
LEFT JOIN Systems st ON st.ID = pa.SourceSystemID
WHERE RTRIM(LTRIM(pa.PolicyID)) = PolicyAudit.PolicyID
AND pa.Status = 1
ORDER BY pa.ID DESC
) Derived_Policy_Audit_Latest
WHERE PolicyAudit.Status = 1
AND PolicyAudit.HasERN = 0
AND Systems.FileTypeIndicator IN ('C', 'V')
AND PolicyAudit.policyid = Derived_Policy_Audit_Latest.policyid
AND CONVERT(VARCHAR(8), PolicyAudit.LastModifiedDate, 112) = Derived_Policy_Audit_Latest.DateSent1
AND Systems.SiteNumber = Derived_Policy_Audit_Latest.SiteNumber
AND Derived_Policy_Audit_Latest.HasERN = 0;
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 22, 2016 at 8:28 am
1) Have you done a file IO stall analysis or wait stats analysis while the code is running to see why it is slow? sp_whoisactive has a differential parameter
2) You have two functions around columns in WHERE clauses. Those have to go.
3) Have you checked for mismatched column data types you are joining?
4) Check the query plan for huge mismatches between estimated and actual rows.
5) How up to date are the statistics in the tables involved?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply