Query optimization

Execution plan doubts.
Query is running so long time can any one pl say how can i check am attaching my execution plan file and my function file also.
Execution-plan-Slow-running-quer.sqlplan
Loln_fn_allprint_disbursementdet.sql
kowsika deviAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
DISTINCT is not the only way to guarantee the uniqueness of the results from a query.
You can review your JOINs as most of the times, adding a new join clause will be enough to guarantee the uniqueness of the results.
Other option is to use GROUP BY. This is the example for your case:
SELECT d.PymntBatchNo,
	CONVERT(VARCHAR, d.PymntBatchDT, 103) AS PymntBatchDT,
    'Other'                               AS PdtShrtDescr
FROM   Loln_LnDisbmntPaymntDet d (nolock)
    JOIN loln_lnpayablelocation LnPL WITH (nolock) ON d.PymtLocShrtDescr = LnPL.LocShrtDescr
    JOIN LOln_ProposalEntry_h h (nolock) ON d.LnNo = h.PrpslNo
    JOIN LGen_CntrlngUnit c (nolock) ON h.UnitShrtDescr = c.UnitShrtDescr AND h.PdtShrtDescr = c.PdtShrtDescr
WHERE d.PymntBatchNo NOT IN('-1', '9', '')
    AND d.REFPymntBatchNo IS NULL 
	AND	LnPL.BankAccCode = '135570' and c.DisbmntCntrlUnit = 'LUDHO' 
    AND PrpslStat NOT IN ( 'Cancelled', 'Closed' )      
    AND PymntBatchNo LIKE Ltrim(Rtrim(397)) + '%' 
GROUP BY d.PymntBatchNo, CONVERT(VARCHAR, d.PymntBatchDT, 103)
UNION ALL
SELECT d.PymntBatchNo,
	CONVERT(VARCHAR, d.PymntBatchDT, 103) AS PymntBatchDT,
    d.PdtShrtDescr
FROM Loln_LnTaDetails_H d (nolock)
	JOIN LGen_CntrlngUnit c (nolock) ON c.UnitShrtDescr = d.UnitShrtDescr
				AND c.PdtShrtDescr = d.PdtShrtDescr
				AND c.DisbmntCntrlUnit = 'JAIPR'
WHERE d.CompShrtDescr = 'SCUF' AND d.AccCode='135570' 
	AND d.PymntBatchNo NOT IN('-1', '9', '')
    AND Isnull(d.REFPymntBatchNo, '') = ''
    AND PymntBatchNo LIKE Ltrim(Rtrim(699)) + '%'
GROUP BY d.PymntBatchNo, CONVERT(VARCHAR, d.PymntBatchDT, 103), d.PdtShrtDescr

Open in new window

1
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If you can remove the DISTINCT from the queries then it will help to improve the performance.
0
 
kowsika deviAuthor Commented:
but i need with out duplicates victor jj.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Solution provided.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.