June 9, 2009 at 5:14 am
Hi All
I have a task from my manager that i have to optimize the query, when i run that query in my qury analyzer it is taking 58 min to retrive the 998records. can any one pleas e tell any tips in optimizing the query + any syntax i can apply
regads
sateesh
June 9, 2009 at 5:28 am
Please post the query, table definitions, index definitions, function definitions and the execution plan (saved as a .sqlplan file, zipped and attached)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2009 at 6:29 am
Without the things that Gail asked for... No. No one can tell you what's wrong because we're not there and don't have a clue what's the problem on your end. You might go to Simple-Talk and look up Gail's performance tuning articles, just a start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 9, 2009 at 11:33 pm
HI
this was my query,
by including the schema name there will be any diffrence in the performance
--Invoice
select distinct Comp_Abbreviation
,t13.Cust_Name
,t3.OPF_Cust_Ord_No
,Convert(varchar(20)
,t3.OPF_Cust_Ord_Date,106) as OPF_Cust_Ord_Date
,dbo.concatvals(t2.InvoiceNo,'tblOPF') as OPF_No
,t3.Opf_Type
,t2.InvoiceNo2 as InvoiceNo2
,t2.InvoiceNo as InvNo
,''+t2.InvoiceNo2+''+space(1)+dbo.FindSaleReturn(t2.InvoiceNo) as InvoiceNo
,t2.Invoicedate as Invoicedate
,dbo.concatvals(t2.InvoiceNo,'tblItem') Items
,convert(decimal(18,2),round(isnull(t2.GrTotal,0),2)) as InvAmount
,isnull((SELECT sum(convert(decimal(18,2),round(isnull(t.Amount,0),2))) FROM tblPayInTags t INNER JOIN tblPayIn p ON t.PayInId = p.PayInId WHERE (t.AgainstNo = t2.invoiceno2)),0) PaymentRcvd
, isnull((SELECT sum(convert(decimal(18,2),round(isnull(amount,0),2))) FROM tblOutstanding o where (RelatedNo = t2.invoiceno2)),0) WrittenAmount
,(convert(decimal(18,2),round(isnull(t2.GrTotal,0),2))-(isnull((SELECT sum(convert(decimal(18,2),round(isnull(t.Amount,0),2))) FROM tblPayInTags t INNER JOIN tblPayIn p ON t.PayInId = p.PayInId WHERE (t.AgainstNo = t2.invoiceno2)),0) +isnull((SELECT sum(convert(decimal(18,2),round(isnull(amount,0),2))) FROM tblOutstanding o where (RelatedNo = t2.invoiceno2)),0)) ) as balance
,t10.Emp_Name as Emp_Name
,t8.ActualME
,[Aging of O/S Invoice]=case when (convert(decimal(18,2),round(isnull(t2.GrTotal,0),2))-(isnull((SELECT sum(convert(decimal(18,2),round(isnull(t.Amount,0),2))) FROM tblPayInTags t INNER JOIN tblPayIn p ON t.PayInId = p.PayInId WHERE (t.AgainstNo = t2.invoiceno2)),0) +isnull ((SELECT sum(convert(decimal(18,2),round(isnull(amount,0),2))) FROM tblOutstanding o where (RelatedNo = t2.invoiceno2)),0)) ) =0 then Dbo.GetAging(t2.InvoiceNo2,'tblInvoice') else datediff(d,InvoiceDate,getDate()) end
,t11.Emp_Name as Manager
, dbo.getinterest(t3.opf_no,convert(varchar(100),getdate()),t2.InvoiceNo2) as InterestAmt
,'NoAccountName' as Accontname
,CareTakerName=case when t2.Caretaker!=opf_submitted_by then t15.Emp_Name else ''end
,Remarks = (Select [dbo].[FnConcatOutStndRemarks](t1.invoiceNo,'Remarks'))
,CMRemarks = (Select [dbo].[FnConcatOutStndRemarks](t1.invoiceNo,'CMRemarks')) ,'' as Attachment
from tblinvoicedetails t1
left outer join tblInvoice t2 on t1.invoiceNo=t2.InvoiceNo
left outer join tblOPF t3 on t2.opfno=t3.opf_no
left outer join tblOPF_detail t4 on t3.opf_no=t4.opf_no
left outer join tblControlChartInputs t5 on t4.transsno=t5.transsno
left outer join tblPayInTags t6 on t2.InvoiceNo2=AgainstNo and Against='Invoice'
left outer join tblPayIn t7 on t6.PayInId=t7.PayInId
left outer join tblOutstanding t8 on t8.RelatedNo=t2.InvoiceNo2 and WrittenOFFAgainst='I'
left outer join tblCompany t9 on t2.compcode=t9.comp_code
left outer join tblCustomerlocation t12 on t2.custloccode=t12.cust_loc_code
left outer join tblCustomer t13 on t13.Cust_Code=t12.Cust_Code
left outer join tblSalesReturnDetails t14 on t14.InvoiceNo=t2.InvoiceNo
left outer join tblEmployee t10 on t10.emp_code=opf_submitted_by
left outer join tblEmployee t15 on t15.emp_code=t2.Caretaker
left outer join tblEmployee t11 on t11.emp_code=t10.Emp_Mgr where 2>1
AND t3.Status='A' AND Cancelled='NO'
AND t2.Status='ACTIVE'
and (InvoiceDate>='01/01/2009' and InvoiceDate<='03/31/2009')
and t3.manager in( 'EM00000168','EM00000048','EM00000009','EM00000085','EM00000059','EM00000212','EM00000064','EM00000330','EM00000004','EM00000152','EM00000003','EM00000033','EM00000077','EM00000131','EM00000016','EM00000044','EM00000018','EM00000005','EM00000144','EM00000076','EM00000001')
and t3.opf_submitted_by in( 'EM00000006','EM00000190','EM00000338','EM00000088','EM00000340','EM00000089','EM00000211','EM00000192','EM00000332','EM00000333','EM00000213','EM00000073','EM00000119','EM00000171','EM00000168','EM00000107','EM00000161','EM00000145','EM00000098','EM00000335','EM00000101','EM00000218','EM00000048','EM00000172','EM00000113','EM00000049','EM00000182','EM00000198','EM00000007','EM00000205','EM00000334','EM00000137','EM00000217','EM00000185','EM00000008','EM00000221','EM00000009','EM00000060','EM00000122','EM00000170','EM00000123','EM00000066','EM00000072','EM00000082','EM00000083','EM00000125','EM00000331','EM00000010','EM00000030','EM00000114','EM00000085','EM00000203','EM00000118','EM00000160','EM00000062','EM00000174','EM00000162','EM00000337','EM00000138','EM00000207','EM00000059','EM00000212','EM00000011','EM00000115','EM00000025','EM00000173','EM00000120','EM00000022','EM00000179','EM00000064','EM00000058','EM00000108','EM00000208','EM00000026','EM00000184','EM00000032','EM00000029','EM00000219','EM00000330','EM00000150','EM00000164','EM00000051','EM00000063','EM00000133','EM00000012','EM00000034','EM00000013','EM00000004','EM00000080','EM00000054','EM00000147','EM00000014','EM00000081','EM00000189','EM00000195','EM00000096','EM00000136','EM00000191','EM00000039','EM00000040','EM00000111','EM00000139','EM00000166','EM00000199','EM00000035','EM00000117','EM00000061','EM00000152','EM00000341','EM00000042','EM00000078','EM00000091','EM00000201','EM00000041','EM00000067','EM00000154','EM00000003','EM00000102','EM00000070','EM00000204','EM00000200','EM00000097','EM00000194','EM00000047','EM00000106','EM00000163','EM00000181','EM00000155','EM00000128','EM00000033','EM00000129','EM00000157','EM00000046','EM00000197','EM00000069','EM00000216','EM00000176','EM00000052','EM00000093','EM00000196','EM00000027','EM00000015','EM00000077','EM00000149','EM00000024','EM00000131','EM00000220','EM00000215','EM00000103','EM00000143','EM00000209','EM00000053','EM00000074','EM00000167','EM00000210','EM00000050','EM00000343','EM00000095','EM00000104','EM00000037','EM00000158','EM00000188','EM00000153','EM00000036','EM00000156','EM00000134','EM00000016','EM00000017','EM00000142','EM00000044','EM00000023','EM00000090','EM00000130','EM00000087','EM00000132','EM00000075','EM00000339','EM00000187','EM00000071','EM00000028','EM00000094','EM00000222','EM00000105','EM00000135','EM00000159','EM00000148','EM00000018','EM00000031','EM00000124','EM00000127','EM00000186','EM00000140','EM00000151','EM00000121','EM00000092','EM00000043','EM00000214','EM00000005','EM00000144','EM00000165','EM00000193','EM00000109','EM00000038','EM00000116','EM00000112','EM00000206','EM00000126','EM00000068','EM00000180','EM00000336','EM00000178','EM00000177','EM00000086','EM00000169','EM00000019','EM00000202','EM00000020','EM00000110','EM00000141','EM00000021','EM00000076','EM00000099') order by InvoiceNo2
Thanks & regards
sateesh
June 10, 2009 at 1:09 am
Still need to see the table definitions, index definitions, function definitions and the execution plan.
Also, can you please edit your post and wrap your query in the code tags to stop the scrolling?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2009 at 1:23 am
Additionally you have quite a few UDF's being used in your return columns.
Change your statement to 'select 1 from ...' to eliminate the possibility that it is one of them is causing the problem.
June 10, 2009 at 1:43 am
Without knowing the tb / ix definitions and stats, i would say to modify all in-lists to @temp objects and rewrite to join with those ! (of use a split-function to accomplish this)
You will also suffer RBAR because of the nested joins using in row functions !!
e.g. Select [dbo].[FnConcatOutStndRemarks](t1.invoiceNo, 'Remarks')
Maybe you could even convert your summs to use the OVER clause
or convert them to a extra left join (adding AgainstNo and grouping by it) !
e.g. isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(t.Amount, 0),
2)))
FROM tblPayInTags t
INNER JOIN tblPayIn p
ON t.PayInId = p.PayInId
WHERE ( t.AgainstNo = t2.invoiceno2 )
), 0) PaymentRcvd
the original select statement in a readable format... :hehe:
select distinct
Comp_Abbreviation
, t13.Cust_Name
, t3.OPF_Cust_Ord_No
, Convert(varchar(20), t3.OPF_Cust_Ord_Date, 106) as OPF_Cust_Ord_Date
, dbo.concatvals(t2.InvoiceNo, 'tblOPF') as OPF_No
, t3.Opf_Type
, t2.InvoiceNo2 as InvoiceNo2
, t2.InvoiceNo as InvNo
, '' + t2.InvoiceNo2 + '' + space(1) + dbo.FindSaleReturn(t2.InvoiceNo) as InvoiceNo
, t2.Invoicedate as Invoicedate
, dbo.concatvals(t2.InvoiceNo, 'tblItem') Items
, convert(decimal(18, 2), round(isnull(t2.GrTotal, 0), 2)) as InvAmount
, isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(t.Amount, 0),
2)))
FROM tblPayInTags t
INNER JOIN tblPayIn p
ON t.PayInId = p.PayInId
WHERE ( t.AgainstNo = t2.invoiceno2 )
), 0) PaymentRcvd
, isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(amount, 0), 2)))
FROM tblOutstanding o
where ( RelatedNo = t2.invoiceno2 )
), 0) WrittenAmount
, ( convert(decimal(18, 2), round(isnull(t2.GrTotal, 0), 2))
- ( isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(t.Amount, 0), 2)))
FROM tblPayInTags t
INNER JOIN tblPayIn p
ON t.PayInId = p.PayInId
WHERE ( t.AgainstNo = t2.invoiceno2 )
), 0)
+ isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(amount, 0), 2)))
FROM tblOutstanding o
where ( RelatedNo = t2.invoiceno2 )
), 0) ) ) as balance
, t10.Emp_Name as Emp_Name
, t8.ActualME
, [Aging of O/S Invoice] = case when ( convert(decimal(18, 2), round(isnull(t2.GrTotal, 0), 2))
- ( isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(t.Amount, 0), 2)))
FROM tblPayInTags t
INNER JOIN tblPayIn p
ON t.PayInId = p.PayInId
WHERE ( t.AgainstNo = t2.invoiceno2 )
), 0)
+ isnull((
SELECT sum(convert(decimal(18, 2), round(isnull(amount, 0), 2)))
FROM tblOutstanding o
where ( RelatedNo = t2.invoiceno2 )
), 0) ) ) = 0
then Dbo.GetAging(t2.InvoiceNo2,
'tblInvoice')
else datediff(d, InvoiceDate, getDate())
end
, t11.Emp_Name as Manager
, dbo.getinterest(t3.opf_no, convert(varchar(100), getdate()),
t2.InvoiceNo2) as InterestAmt
, 'NoAccountName' as Accontname
, CareTakerName = case when t2.Caretaker != opf_submitted_by
then t15.Emp_Name
else ''
end
, Remarks = (
Select [dbo].[FnConcatOutStndRemarks](t1.invoiceNo,
'Remarks')
)
, CMRemarks = (
Select [dbo].[FnConcatOutStndRemarks](t1.invoiceNo, 'CMRemarks')
)
, '' as Attachment
from tblinvoicedetails t1
left outer join tblInvoice t2
on t1.invoiceNo = t2.InvoiceNo
left outer join tblOPF t3
on t2.opfno = t3.opf_no
left outer join tblOPF_detail t4
on t3.opf_no = t4.opf_no
left outer join tblControlChartInputs t5
on t4.transsno = t5.transsno
left outer join tblPayInTags t6
on t2.InvoiceNo2 = AgainstNo
and Against = 'Invoice'
left outer join tblPayIn t7
on t6.PayInId = t7.PayInId
left outer join tblOutstanding t8
on t8.RelatedNo = t2.InvoiceNo2
and WrittenOFFAgainst = 'I'
left outer join tblCompany t9
on t2.compcode = t9.comp_code
left outer join tblCustomerlocation t12
on t2.custloccode = t12.cust_loc_code
left outer join tblCustomer t13
on t13.Cust_Code = t12.Cust_Code
left outer join tblSalesReturnDetails t14
on t14.InvoiceNo = t2.InvoiceNo
left outer join tblEmployee t10
on t10.emp_code = opf_submitted_by
left outer join tblEmployee t15
on t15.emp_code = t2.Caretaker
left outer join tblEmployee t11
on t11.emp_code = t10.Emp_Mgr
where 2 > 1
AND t3.Status = 'A'
AND Cancelled = 'NO'
AND t2.Status = 'ACTIVE'
and (
InvoiceDate >= '01/01/2009'
and InvoiceDate <= '03/31/2009'
)
and t3.manager in ( 'EM00000168', 'EM00000048', 'EM00000009',
'EM00000085', 'EM00000059', 'EM00000212',
'EM00000064', 'EM00000330', 'EM00000004',
'EM00000152', 'EM00000003', 'EM00000033',
'EM00000077', 'EM00000131', 'EM00000016',
'EM00000044', 'EM00000018', 'EM00000005',
'EM00000144', 'EM00000076', 'EM00000001' )
and t3.opf_submitted_by in ( 'EM00000006', 'EM00000190', 'EM00000338',
'EM00000088', 'EM00000340', 'EM00000089',
'EM00000211', 'EM00000192', 'EM00000332',
'EM00000333', 'EM00000213', 'EM00000073',
'EM00000119', 'EM00000171', 'EM00000168',
'EM00000107', 'EM00000161', 'EM00000145',
'EM00000098', 'EM00000335', 'EM00000101',
'EM00000218', 'EM00000048', 'EM00000172',
'EM00000113', 'EM00000049', 'EM00000182',
'EM00000198', 'EM00000007', 'EM00000205',
'EM00000334', 'EM00000137', 'EM00000217',
'EM00000185', 'EM00000008', 'EM00000221',
'EM00000009', 'EM00000060', 'EM00000122',
'EM00000170', 'EM00000123', 'EM00000066',
'EM00000072', 'EM00000082', 'EM00000083',
'EM00000125', 'EM00000331', 'EM00000010',
'EM00000030', 'EM00000114', 'EM00000085',
'EM00000203', 'EM00000118', 'EM00000160',
'EM00000062', 'EM00000174', 'EM00000162',
'EM00000337', 'EM00000138', 'EM00000207',
'EM00000059', 'EM00000212', 'EM00000011',
'EM00000115', 'EM00000025', 'EM00000173',
'EM00000120', 'EM00000022', 'EM00000179',
'EM00000064', 'EM00000058', 'EM00000108',
'EM00000208', 'EM00000026', 'EM00000184',
'EM00000032', 'EM00000029', 'EM00000219',
'EM00000330', 'EM00000150', 'EM00000164',
'EM00000051', 'EM00000063', 'EM00000133',
'EM00000012', 'EM00000034', 'EM00000013',
'EM00000004', 'EM00000080', 'EM00000054',
'EM00000147', 'EM00000014', 'EM00000081',
'EM00000189', 'EM00000195', 'EM00000096',
'EM00000136', 'EM00000191', 'EM00000039',
'EM00000040', 'EM00000111', 'EM00000139',
'EM00000166', 'EM00000199', 'EM00000035',
'EM00000117', 'EM00000061', 'EM00000152',
'EM00000341', 'EM00000042', 'EM00000078',
'EM00000091', 'EM00000201', 'EM00000041',
'EM00000067', 'EM00000154', 'EM00000003',
'EM00000102', 'EM00000070', 'EM00000204',
'EM00000200', 'EM00000097', 'EM00000194',
'EM00000047', 'EM00000106', 'EM00000163',
'EM00000181', 'EM00000155', 'EM00000128',
'EM00000033', 'EM00000129', 'EM00000157',
'EM00000046', 'EM00000197', 'EM00000069',
'EM00000216', 'EM00000176', 'EM00000052',
'EM00000093', 'EM00000196', 'EM00000027',
'EM00000015', 'EM00000077', 'EM00000149',
'EM00000024', 'EM00000131', 'EM00000220',
'EM00000215', 'EM00000103', 'EM00000143',
'EM00000209', 'EM00000053', 'EM00000074',
'EM00000167', 'EM00000210', 'EM00000050',
'EM00000343', 'EM00000095', 'EM00000104',
'EM00000037', 'EM00000158', 'EM00000188',
'EM00000153', 'EM00000036', 'EM00000156',
'EM00000134', 'EM00000016', 'EM00000017',
'EM00000142', 'EM00000044', 'EM00000023',
'EM00000090', 'EM00000130', 'EM00000087',
'EM00000132', 'EM00000075', 'EM00000339',
'EM00000187', 'EM00000071', 'EM00000028',
'EM00000094', 'EM00000222', 'EM00000105',
'EM00000135', 'EM00000159', 'EM00000148',
'EM00000018', 'EM00000031', 'EM00000124',
'EM00000127', 'EM00000186', 'EM00000140',
'EM00000151', 'EM00000121', 'EM00000092',
'EM00000043', 'EM00000214', 'EM00000005',
'EM00000144', 'EM00000165', 'EM00000193',
'EM00000109', 'EM00000038', 'EM00000116',
'EM00000112', 'EM00000206', 'EM00000126',
'EM00000068', 'EM00000180', 'EM00000336',
'EM00000178', 'EM00000177', 'EM00000086',
'EM00000169', 'EM00000019', 'EM00000202',
'EM00000020', 'EM00000110', 'EM00000141',
'EM00000021', 'EM00000076', 'EM00000099' )
order by InvoiceNo2
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 10, 2009 at 6:52 am
HI All
1)I run the same Query in the DTA(Databasetuning advisor) it show some recomended indexes to implemented , and also it is recomended to apply for statictis on, after implementing it is retrving the records into 14 mins.. from 59 mins.., is this good using for me it will effect any thing in my server .
2)One thing i observe in it is as i am new to database optimization , if i run the query in DTA it was showing the recomended indexes to implement in defenitions only with the scripts, but i need to do this scripts with my own thoughts.
3) please answer ur suggestion regarding this
regards
sateesh
June 10, 2009 at 7:18 am
Hi,
Adding indexes will obviously have a negative impact when inserting data. Its a balancing act.
But you still havent identified which portion of your query is causing your issues.
I very much suspect that it is due to the multiple functions calls made.
Have you tried removing those and doing count(*) ?
June 10, 2009 at 7:23 am
any DTA advise must be interpreted and tested before you deploy it.
You may even see quit duplicate indexes advised;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply