how do I fine tune this query

Question

how do I fine tune this query

Viewing 4 posts - 46 through 48 (of 48 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

mw112009 (10/1/2015)
I pressed the "Display estimated execution plan " but it did not tell me to create any new indexes.

Please be very careful with this. Just looking for missing indexes does not mean that this is the best way to tune a query.

What this tells you is that for this query, SQL thinks that this index will help you out.

What it doesn't tell you is:

1. That there is already an index that almost matches this definition that would be better to modify than to add a new index.

2. The the query would run a lot better if it was re-written. It might even be fine with the existing indexes.

3. The suggested index may not really help.

I was just tuning a query yesterday, where I implemented two suggested indexes, with negligible impact. It never identified an index that helped a join between two tables, and that index alone turned the query from 15 minutes to 3 seconds. The two suggested indexes had no impact at all on the performance of the query - when removed, the query ran just as fast.

So don't just take a missing index recommendation as gospel.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95460 More actions · Answer 2

mw112009 (10/2/2015)
Ahh I found a way to reduce the time...
1.) Remove the DISTINCT ... Why ? The UNION operator always will return the distinct rows. I ran a little script and managed to convince myself.

How much did this reduce the time?

Also check out using the DISTINCT, but change the UNION to a UNION ALL. Since there is a different FILET value, the two queries could be just concatenated together (UNION ALL) instead of weeding out the duplicates (UNION).

Will the individual queries (on either side of the UNION) return any duplicates? If not, then you could get rid of the DISTINCT. But you should still keep the UNION ALL.

~20% of the cost of this second query is being taken up with three sort operations. One of which spilled to tempdb. Getting rid of the DISTINCT and changing UNION to UNION ALL should remove all three of these. However, the ROW_NUMBER may then need one, so you may see one added to before the SEQUENCE operator where the ROW_NUMBER is performed.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

Let me start fresh here....BTW-- I commented out the first query

Lets forget the first query.. That takes only taking 90 milliseconds.. Ignore that for now.

This is what is taking 4 seconds... I even took the DISTINCT away and I am depending on the UNION to return a distinct set of rows.

Works... But the 4 seconds has to be cut down.. I have attached the query plan

QUERY PLAN For one single query is attached.

INSERT into #tmpMHPCLMDET

(ADMDT,FILET,FORMN,SSVDT,PCDCD,PRVNO,PCDQT,BILAM,ALWAM,COPAM,WITAM,NETAM,PAYST,

AJRSN,DCTAM,NCVAM,NCRSN,EDI835RSN,

CHKNO,CHKDT,HCPCS,OICPD,REVCD,HLIID,IDAMT,APCCD,

APPAYST,BANCD,RCVDT,APCSI,ESSNPI,SSNPI,

IsReversal,PatientResponsibility,Denied,ENSVDT,Mod2,Mod3,Mod4)

Select cd.ADMDT,'H' FILET, cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,

cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN, CAST('' AS VARCHAR(50)) EDI835RSN ,

hh.CHKNO,cd.CHKDT,cd.HCPCS,cd.OICPD,cd.REVCD,cd.HLIID,cd.IDAMT,cd.APCCD,

cd.APPAYST,cd.BANCD,cd.RCVDT,cd.APCSI,'' ESSNPI, '' SSNPI,

'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility,

0 Denied , 0 ENSVDT, '' Mod2, '' Mod3, '' Mod4

from

dbo.hd835dp dd

INNER JOIN dbo.hh835DP hd

ON ((dd.PSVDT = hd.PSVDT ) AND (dd.FORMN = hd.FORMN ) AND (dd.FILET = hd.FILET) )

INNER JOIN dbo.hh835hp hh

ON ((hh.chkdts = hd.chkdt ) AND (hh.BANCD = hd.BANCD ) AND (hh.chkno = hd.chkno) )

INNER JOIN dbo.hinsdp cd ON ((dd.PSVDT = cd.admdt) AND (dd.formn = cd.formn ) AND (hh.CMPCD = cd.CMPCD))

where

(@VendorNumber is null or (hh.vndno = @VendorNumber))

AND hh.chkdts = @CheckRunDate

AND dd.DPSTF = 'N'

AND hd.FILET = 'H'

AND cd.EDI835Exclude = 0

---and (@CompanyCode IS NULL OR(cd.CMPCD = @CompanyCode))

--and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))

union

Selectcd.PSVDT as ADMDT ,'E' FILET, cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,

cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN, CAST('' AS VARCHAR(50)) EDI835RSN ,

hh.CHKNO,cd.CHKDT,'' HCPCS, cd.OICPD,'' REVCD,cd.HLIID,cd.IDAMT,'' APCCD,

cd.APPAYST,cd.BANCD,cd.RCVDT,'' APCSI,cd.ESSNPI,cd.SSNPI,

'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility,

0 Denied , 0 ENSVDT, '' Mod2, '' Mod3, '' Mod4

from

dbo.hd835dp dd

INNER JOIN dbo.hh835DP hd ON ((dd.FILET = hd.FILET ) AND (dd.FORMN = hd.FORMN) AND (dd.PSVDT = hd.PSVDT ) )

INNER JOIN dbo.hh835hp hh ON ((hh.chkdts = hd.chkdt ) AND (hh.BANCD = hd.BANCD ) AND (hh.chkno = hd.chkno ) )

INNER JOIN dbo.hencdp cd ON ((dd.PSVDT = cd.psvdt ) AND (dd.formn = cd.formn ) AND (hh.CMPCD = cd.CMPCD))

where

--(@VendorNumber is null or (hh.vndno = @VendorNumber))

--and

hh.chkdts = @CheckRunDate

and dd.DPSTF = 'N'

and hd.FILET = 'E'

and cd.EDI835Exclude = 0

--and (@CompanyCode IS NULL OR(cd.CMPCD = @CompanyCode))

--and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

mw112009 (10/2/2015)
Let me start fresh here....BTW-- I commented out the first query
Lets forget the first query.. That takes only taking 90 milliseconds.. Ignore that for now.
This is what is taking 4 seconds... I even took the DISTINCT away and I am depending on the UNION to return a distinct set of rows.
Works... But the 4 seconds has to be cut down.. I have attached the query plan
QUERY PLAN For one single query is attached.
INSERT into #tmpMHPCLMDET
(ADMDT,FILET,FORMN,SSVDT,PCDCD,PRVNO,PCDQT,BILAM,ALWAM,COPAM,WITAM,NETAM,PAYST,
AJRSN,DCTAM,NCVAM,NCRSN,EDI835RSN,
CHKNO,CHKDT,HCPCS,OICPD,REVCD,HLIID,IDAMT,APCCD,
APPAYST,BANCD,RCVDT,APCSI,ESSNPI,SSNPI,
IsReversal,PatientResponsibility,Denied,ENSVDT,Mod2,Mod3,Mod4)
Select cd.ADMDT,'H' FILET, cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,
cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN, CAST('' AS VARCHAR(50)) EDI835RSN ,
hh.CHKNO,cd.CHKDT,cd.HCPCS,cd.OICPD,cd.REVCD,cd.HLIID,cd.IDAMT,cd.APCCD,
cd.APPAYST,cd.BANCD,cd.RCVDT,cd.APCSI,'' ESSNPI, '' SSNPI,
'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility,
0 Denied , 0 ENSVDT, '' Mod2, '' Mod3, '' Mod4
from
dbo.hd835dp dd
INNER JOIN dbo.hh835DP hd
ON ((dd.PSVDT = hd.PSVDT ) AND (dd.FORMN = hd.FORMN ) AND (dd.FILET = hd.FILET) )
INNER JOIN dbo.hh835hp hh
ON ((hh.chkdts = hd.chkdt ) AND (hh.BANCD = hd.BANCD ) AND (hh.chkno = hd.chkno) )
INNER JOIN dbo.hinsdp cd ON ((dd.PSVDT = cd.admdt) AND (dd.formn = cd.formn ) AND (hh.CMPCD = cd.CMPCD))
where
(@VendorNumber is null or (hh.vndno = @VendorNumber))
AND hh.chkdts = @CheckRunDate
AND dd.DPSTF = 'N'
AND hd.FILET = 'H'
AND cd.EDI835Exclude = 0
---and (@CompanyCode IS NULL OR(cd.CMPCD = @CompanyCode))
--and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))
union
Selectcd.PSVDT as ADMDT ,'E' FILET, cd.FORMN,cd.SSVDT,cd.PCDCD,cd.PRVNO,cd.PCDQT,cd.BILAM,cd.ALWAM,cd.COPAM,cd.WITAM,cd.NETAM,cd.PAYST,
cd.AJRSN,cd.DCTAM,cd.NCVAM,cd.NCRSN, CAST('' AS VARCHAR(50)) EDI835RSN ,
hh.CHKNO,cd.CHKDT,'' HCPCS, cd.OICPD,'' REVCD,cd.HLIID,cd.IDAMT,'' APCCD,
cd.APPAYST,cd.BANCD,cd.RCVDT,'' APCSI,cd.ESSNPI,cd.SSNPI,
'0' IsReversal , CAST('' AS VARCHAR(50)) PatientResponsibility,
0 Denied , 0 ENSVDT, '' Mod2, '' Mod3, '' Mod4
from
dbo.hd835dp dd
INNER JOIN dbo.hh835DP hd ON ((dd.FILET = hd.FILET ) AND (dd.FORMN = hd.FORMN) AND (dd.PSVDT = hd.PSVDT ) )
INNER JOIN dbo.hh835hp hh ON ((hh.chkdts = hd.chkdt ) AND (hh.BANCD = hd.BANCD ) AND (hh.chkno = hd.chkno ) )
INNER JOIN dbo.hencdp cd ON ((dd.PSVDT = cd.psvdt ) AND (dd.formn = cd.formn ) AND (hh.CMPCD = cd.CMPCD))
where
--(@VendorNumber is null or (hh.vndno = @VendorNumber))
--and
hh.chkdts = @CheckRunDate
and dd.DPSTF = 'N'
and hd.FILET = 'E'
and cd.EDI835Exclude = 0
--and (@CompanyCode IS NULL OR(cd.CMPCD = @CompanyCode))
--and (@CheckNumber IS NULL OR(hh.CHKNO like @CheckNumber))

Comment out the INSERT part leaving the two UNIONed SELECTS - the insert is taking a whopping 72% of the total cost which will overwhelm the costs of the SELECT. Ensure that the filters (the WHERE clause) of the two selects is exactly what you want the to be (they are currently different, the top SELECT filters on hh.vndno, the bottom one doesn't), then grab the actual execution plan again.

EDIT: also, calculate the number of rows returned by the upper and the lower query and determine if deduplication (by UNION) is performing expensive work for nothing. You, as a developer, should know if deduplication is required by this query.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden