April 4, 2011 at 6:22 am
Dear All,
below is the view we are creating to get the aging report data then executing the SP to Fetch results from this view. but the report is taking more than 10 mints and mostly server is getting hanged. is there any way to fine tune this query?
select invoiceno,invoicedt,customerid,invoiceamt totout,0 six,0 seven,0 thirty,0 sixty,0 ninty from v_aging
union
select invoiceno,invoicedt,customerid,0,invoiceamt,0,0,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=7
union select invoiceno,invoicedt,customerid,0,0,invoiceamt,0,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=7 and datediff(day,CreditPeriodDate,getdate())<30
union select invoiceno,invoicedt,customerid,0,0,0,invoiceamt,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=30 and datediff(day,CreditPeriodDate,getdate())<60
union select invoiceno,invoicedt,customerid,0,0,0,0,invoiceamt,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=60 and datediff(day,CreditPeriodDate,getdate())<90
union select invoiceno,invoicedt,customerid,0,0,0,0,0,invoiceamt from v_aging where datediff(day,CreditPeriodDate,getdate())>=90
Thanks in Advance
April 4, 2011 at 6:25 am
I don't see UNION ALL, I see UNIONs here.
UNION means distinct values and distinct means expensive sorts.
Can you post table scripts and execution plan?
-- Gianluca Sartori
April 4, 2011 at 6:28 am
--Our system doesn't use hours in the date, so I can use end date today, midnight.
DECLARE @Today DATETIME
SET @Today = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
SELECT SL.No_ AS NoArticle
, SUM(CASE WHEN SH.[Document Date] >= DATEADD(MM, -1, @Today)
THEN Quantity
ELSE 0
END) AS Qty1Mois
, SUM(CASE WHEN SH.[Document Date] >= DATEADD(MM, -3, @Today)
THEN Quantity
ELSE 0
END) AS Qty3Mois
, SUM(CASE WHEN SH.[Document Date] >= DATEADD(MM, -6, @Today)
THEN Quantity
ELSE 0
END) AS Qty6Mois
, SUM(SL.Quantity) AS Qty1An
FROM dbo.[Groupe FORDIA Inc_$Sales Header] SH
INNER JOIN dbo.[Groupe FORDIA Inc_$Sales Line] SL
ON SH.No_ = SL.[Document No_]
WHERE SH.[Document Type] = 0
AND SH.[Document Date] >= DATEADD(YYYY, -1, @Today)
AND SL.Type = 2
--AND SL.No_ > ''
AND SL.Quantity <> 0
AND SL.[No_] IN ( SELECT ItemNumber
FROM #Items )
GROUP BY SL.No_
April 4, 2011 at 6:32 am
Ninja's_RGR'us (4/4/2011)
-- ... some code here ...
Are you sure?
-- Gianluca Sartori
April 4, 2011 at 6:38 am
Sure of what? I have this is prod without issue, with great performance.
I've simply pasted working code so that he gets the idea of sum(case when period).
That requires s single range seek of the table. Much faster than N union alls.
April 4, 2011 at 6:42 am
Ninja's_RGR'us (4/4/2011)
Sure of what? I have this is prod without issue, with great performance.I've simply pasted working code so that he gets the idea of sum(case when period).
That requires s single range seek of the table. Much faster than N union alls.
Ah, I see. I'm sorry.
I couldn't see any relation between your code and the OP's and I thought you pasted in the wrong thread. :blush:
-- Gianluca Sartori
April 4, 2011 at 6:56 am
i was also confused i thought ninja added wrong code..anyways...
for table structure as you can see the data is coming from multiple tables.... execution plan of above query i can paste here but plz be specific about the table code i am bit confused.
April 4, 2011 at 7:01 am
I don't give the code because I want you to UNDERSTAND 100%.
SELECT SUM(CASE WHEN DATE BETWEEN <Start> AND <END> THEN invoiceamt ELSE 0 END) As Period1
, SUM(CASE WHEN DATE BETWEEN <Start> AND <END> THEN invoiceamt ELSE 0 END) As Period2
, SUM(CASE WHEN DATE BETWEEN <Start> AND <END> THEN invoiceamt ELSE 0 END) As Period3
, SUM(CASE WHEN DATE BETWEEN <Start> AND <END> THEN invoiceamt ELSE 0 END) As Period4
, SUM(CASE WHEN DATE BETWEEN <Start> AND <END> THEN invoiceamt ELSE 0 END) As Periodn
FROM BaseTable (Only once now)
GROUP BY ...
April 4, 2011 at 7:04 am
i got the idea out of it but i am bit confused because in our case its Date difference not the between, i cant give hardcoded date there to use the between operator. so we need based on the difference, as user can give any date while generating the report.
this query mainly used for Customer Ageing Report. That is causing a lot of issues and mostly hanging the sql server.
April 4, 2011 at 7:08 am
Look at my code... it's doing EXACTLY what you need.
You're just proving my point that you need to understand the code before implementing it.
Remember that at 3 AM you'll be the one supporting it, not me.
April 5, 2011 at 4:18 am
Hello faisalkhanbk,
when you code
select invoiceno,invoicedt,customerid,0,0,invoiceamt,0,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=7 and datediff(day,CreditPeriodDate,getdate())<30
you force SQL Server to made calculations over CreditPeriodDate for each row. That's bad because you can code it in a way to avoid these calculations,
select invoiceno,invoicedt,customerid,0,0,invoiceamt,0,0,0 from v_aging where CreditPeriodDate <= getdate()-7 and CreditPeriodDate > getdate()-30
Coding like that you allow SQL Server to calculate getdate()-7, getdate()-30 only once. Not only is a benefit reducing calculations, if your table is indexed by CreditPeriodDate then SQL Server can try to use your index because this instruction is SARGable and the first instruction is not.
For more safety you can calculate getdate()-7 and getdate()-30 over two previously defined variables and use these variables in the query.
Regards,
Francesc
April 5, 2011 at 11:58 am
faisalkhanbk (4/4/2011)
i got the idea out of it but i am bit confused because in our case its Date difference not the between, i cant give hardcoded date there to use the between operator. so we need based on the difference, as user can give any date while generating the report.this query mainly used for Customer Ageing Report. That is causing a lot of issues and mostly hanging the sql server.
Faisal,
I've been using this type of Cross Tab for years to do aging. It works perfectly and it's very, very fast. You need to understand the concept of summing based on a CASE statement - just as Ninja suggested. The idea is to make one pass at the data and get what you want in that one pass.
Also, you are using a View for the aging. You may want to actually use the base tables since the View may be a slow one.
Todd Fifield
April 5, 2011 at 9:46 pm
Hi,
In a similar sitation, I've created either a temp table or table variable and then used INSERTs rather than unions. As in:
declare @mytable table
(invoiceno numeric,invoicedt datetime,customerid numeric,totout numeric,six numeric,seven numeric, thirty numeric,sixty numeric,ninty numeric)
insert into @mytable
select invoiceno,invoicedt,customerid,invoiceamt totout,0 six,0 seven,0 thirty,0 sixty,0 ninty from v_aging
insert into @mytable
select invoiceno,invoicedt,customerid,0,invoiceamt,0,0,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=7
insert into @mytable
select invoiceno,invoicedt,customerid,0,0,invoiceamt,0,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=7 and datediff(day,CreditPeriodDate,getdate())<30
insert into @mytable
select invoiceno,invoicedt,customerid,0,0,0,invoiceamt,0,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=30 and datediff(day,CreditPeriodDate,getdate())<60
insert into @mytable
select invoiceno,invoicedt,customerid,0,0,0,0,invoiceamt,0 from v_aging where datediff(day,CreditPeriodDate,getdate())>=60 and datediff(day,CreditPeriodDate,getdate())<90
insert into @mytable
select invoiceno,invoicedt,customerid,0,0,0,0,0,invoiceamt from v_aging where datediff(day,CreditPeriodDate,getdate())>=90
The temp table will be more useful if you have a large parent dataset, but other times a table variable will be more appropriate. It depends on the circumstance - try both and see which is better.
April 6, 2011 at 2:50 am
He doesn't need to ge to the view once per perdiod, once overall is enough!
April 6, 2011 at 6:46 am
Christine, your solution still hits the view umpteen times. The SUM(CASE . . .) solution hits that same data ONE time. It can't possibly be as efficient to do anything other than one pass over the data, right? PLEASE do yourself a HUGE favor and learn how to use that method for doing "bucketized" aggregation! It can be a bit difficult to grasp (unless someone sitting next to you explains it, preferably with a whiteboard), but believe me when I tell you it is well worth the effort!!! Just think of the difference between scanning a billion row table 12 times to come up with 12 monthly aggregates or scanning that same billion row table ONCE and calculating all 12 aggregates in a single pass. This solution is even MORE effective these days since CPU power has become thousands of times more powerful than in days gone by yet IO throughput has increased just a relatively tiny amount in comparison. And this solution is all about saving on IO.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply