April 30, 2004 at 5:14 am
Hi,
I use a stored procedure to calculate the ageing analysis of debtors in a debtor system. Now I am sitting with a situation where the sp takes forever to return a result.
I have done an experiment with Profiler and Query Analyser where I take the Stored procedure, Run it in Query Analyser, then take the contents of the Stored procedure copy it, and run that in query analyser.
The results are very different and very weird. The SP takes 1:16 minutes to execute, and the text takes 3 seconds to execute. In profiler, the SP shows READS of 2,365,383 as opposed to 13425 for the text, and CPU 10734 for the SP as opposed to 328 for the text.
Does anyone know why the SP could be slower than the normal text?
One point I might add is that I am doing join from a table on itself (from Tbl1 left outer join Tbl1 as Ttbl2 on ....).
Thanks
Robert
April 30, 2004 at 5:40 am
Is it possible for you to post the code so that we can review it and see what is going on? Have you looked at the execution plan to see that you are using indexes??
When you run it in QA are you running it 1 time or several?? If you are running it more than once you will need to flush your cache to see actual same same results. Easiest way is to review either sp_proccache (I think) or keep using different QS panes each run of the query.
Good Luck
Good Hunting!
AJ Ahrens
webmaster@kritter.net
April 30, 2004 at 5:51 am
Hi,
The query is takes payments and invoices, and then check what allocations has been done against them. The allocations is [transaction type] 35, and exists in the same table ([Debtors Account]). Sorry about the size of the query. It's difficult to read here, so paste it in QA.
Something important I picked up just now in another experiment, is that when I import the table to another server, the execution time in both instances are more or less the same, and very quick. Which makes me think that this question belongs in the Administration section, rather than the T-SQL section.
CREATE PROCEDURE [QuickAgeDebtor]
@AccountNumber as char(16),
@EndDate datetime
AS
declare @AgeDate int
select @AgeDate=max(isnull([settings].[debtor age date],1)) from [settings]
declare @InitDate datetime
select @InitDate=convert(char(4),datepart(YYYY,dateadd(DD,-1*@AgeDate+1,@EndDate)))+ '-' + convert(char(2),datepart(MM,dateadd(DD,-1*@AgeDate+1,@EndDate))) + '-' + convert(char(2),@AgeDate) + ' 00:00:00'
select
[Debtors].[Number],
max([Account Name] + ' ' + [Initials] + ' ' + [Title]) as [Description],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>@InitDate then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>@InitDate then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [Current],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>dateadd(MM,-1,@InitDate) AND [AllocDocument].[date & time]<=@InitDate then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>dateadd(MM,-1,@InitDate) AND [Debtors Account].[date & time]<=@InitDate then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [30 Days],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>dateadd(MM,-2,@InitDate) AND [AllocDocument].[date & time]<=dateadd(MM,-1,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>dateadd(MM,-2,@InitDate) AND [Debtors Account].[date & time]<=dateadd(MM,-1,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [60 Days],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>dateadd(MM,-3,@InitDate) AND [AllocDocument].[date & time]<=dateadd(MM,-2,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>dateadd(MM,-3,@InitDate) AND [Debtors Account].[date & time]<=dateadd(MM,-2,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [90 Days],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>dateadd(MM,-4,@InitDate) AND [AllocDocument].[date & time]<=dateadd(MM,-3,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>dateadd(MM,-4,@InitDate) AND [Debtors Account].[date & time]<=dateadd(MM,-3,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [120 Days],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>dateadd(MM,-5,@InitDate) AND [AllocDocument].[date & time]<=dateadd(MM,-4,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>dateadd(MM,-5,@InitDate) AND [Debtors Account].[date & time]<=dateadd(MM,-4,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [150 Days],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]>dateadd(MM,-6,@InitDate) AND [AllocDocument].[date & time]<=dateadd(MM,-5,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]>dateadd(MM,-6,@InitDate) AND [Debtors Account].[date & time]<=dateadd(MM,-5,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [180 Days],
sum(case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-6,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-6,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end) as [180 Days+],
isnull(sum(case when [Debtors Account].[Transaction Type]=35 then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else [Debtors Account].[Debits]-[Debtors Account].[Credits] end),0) as [Total Balance],
sum(case upper([Debtors].[Terms])
when '30 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-1,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-1,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '60 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-2,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-2,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '90 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-3,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-3,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '120 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-4,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-4,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '150 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-5,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-5,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '180 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-6,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-6,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
else (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=@InitDate then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=@InitDate then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
end) as [In Arrears],
sum(case upper([Debtors].[Terms])
when '30 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=@InitDate then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=@InitDate then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '60 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-1,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-1,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '90 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-2,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-2,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '120 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-3,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-3,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '150 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-4,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-4,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
when '180 DAYS' then (case when [Debtors Account].[Transaction Type]=35 AND [AllocDocument].[date & time]<=dateadd(MM,-5,@InitDate) then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else (case when [Debtors Account].[date & time]<=dateadd(MM,-5,@InitDate) then [Debtors Account].[Debits]-[Debtors Account].[Credits] else 0 end) end)
else (case when [Debtors Account].[Transaction Type]=35 then (case when [AllocDocument].[transaction type]=11 OR [AllocDocument].[transaction type]=45 OR [AllocDocument].[transaction type]=50 then -1*[Debtors Account].[Allocated Amount] when [AllocDocument].[transaction type]=16 OR [AllocDocument].[transaction type]=22 OR [AllocDocument].[transaction type]=46 OR [AllocDocument].[transaction type]=49 then [Debtors Account].[Allocated Amount] else 0 end) else [Debtors Account].[Debits]-[Debtors Account].[Credits] end)
end) as [Now Due],
max([Group]) as [Group],
max(upper([Debtors].[Terms])) as [Terms],
convert(char(16),replicate('0',16-len(ltrim([number]))) +ltrim([number])) as [PCNSort]
from
[Debtors]
left outer join [Debtors Account] on ([Debtors].[Number]=[Debtors Account].[Account Number] AND (([Debtors Account].[Date & Time]<=@EndDate) AND ([Debtors Account].[Transaction Type]=35 OR [Debtors Account].[Transaction Type]=11 OR [Debtors Account].[Transaction Type]=22 OR [Debtors Account].[Transaction Type]=16 OR [Debtors Account].[Transaction Type]=45 OR [Debtors Account].[Transaction Type]=46 OR [Debtors Account].[Transaction Type]=49 OR [Debtors Account].[Transaction Type]=50) OR [Debtors Account].[Date & Time] is null))
left outer join [Debtors Account] as [AllocDocument] on ([AllocDocument].[Transaction Type]=11 OR [AllocDocument].[Transaction Type]=22 OR [AllocDocument].[Transaction Type]=16 OR [AllocDocument].[Transaction Type]=45 OR [AllocDocument].[Transaction Type]=46 OR [AllocDocument].[Transaction Type]=49 OR [AllocDocument].[Transaction Type]=50) AND [AllocDocument].[Account Number]=[Debtors Account].[Account Number] AND ([AllocDocument].[Date & Time]<=@EndDate) AND ([AllocDocument].[Document Indexer]=[Debtors Account].[Target Document Number] OR [AllocDocument].[Document Indexer]=[Debtors Account].[Source Document Number])
WHERE [Debtors].[Number]=@AccountNumber
group by [Debtors].[Number]
order by [Debtors].[Number]
RETURN
GO
Robert
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply