SQL Server Performance Forum – Threads Archive

Slow Performance Under SQL Agent

I try to avoid using cursors, but I have a situation that require a cursor. The cursor is declared FAST_FORWARD. When I run the process as a script under the SQL Query Analyzer client on my machine it runs in 5 minutes. When the same script is saved as a stored procedure and run as a scheduled job under SQL Agent, the run time is 1-3 hours. The server is multi-processor with 4 gigs of memory. My desktop client is single process with 1 gig of memory. Why does the job run so much slower on the server than on the client?

We need to see the procedure definition. Parameter sniffing can be the reason for such problem.

mmarovic, No parameters are passed to the procedure. CREATE PROCEDURE spBuildAccruedIncome AS declare @CutoffDate smalldatetime set @CutoffDate = ABC.dbo.fnSTRIPTIME(ABC.dbo.fnGET_LAST_DAY_OF_MONTH(getdate())) select convert(varchar(12), p.ContractNo) as ContractNo ,convert(smalldatetime, p.EffectiveDate) as EffectiveDate ,convert(numeric(12,2), sum(coalesce(p.Principal,0))) as Principal ,convert(numeric(12,2), sum(coalesce(p.Interest,0))) as Interest into #tbl_Deal_Trans from Reporting..CashFlow p inner join ABC..ContractList c on c.ContractNo = p.ContractNo where p.reversed = 0 and patindex(‘%[^0-9,-]%’, p.ContractNo) = 0 and not (p.Event in (‘Bill’,’Current Due’,’Rate Change’)) and p.EffectiveDate <= @CutoffDate group by convert(varchar(12), p.ContractNo) ,convert(smalldatetime, p.EffectiveDate) select convert(varchar(12), p.ContractNo) as ContractNo ,convert(smalldatetime, p.EffectiveDate) as DateFrom ,convert(smalldatetime, NULL) as DateTo ,convert(numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, ABC.dbo.fnGET_PRECISE_QUOTIENT(Rate/100, d.AnnualDays)) as Rate into #tbl_Deal_RateChg from Reporting..CashFlow p inner join ABC..ContractList c on c.ContractNo = p.ContractNo inner join Reporting..Contract d on d.ContractNo = c.ContractNo where p.reversed = 0 and patindex(‘%[^0-9,-]%’, c.ContractNo) = 0 and p.Event = ‘Rate Change' and p.EffectiveDate <= @CutoffDate create unique index #tbl_Deal_RateChg_IX on #tbl_Deal_RateChg (DateFrom, DateTo, ContractNo) update p set p.DateTo = coalesce(DateAdd(d, -1, (select min(DateFrom) from #tbl_Deal_RateChg x where x.ContractNo=p.ContractNo and x.DateFrom&gt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />.DateFrom)),@CutoffDate) from #tbl_Deal_RateChg p select p.ContractNo, d.[Date], p.Rate into #tbl_Deal_DailyRates from #tbl_Deal_RateChg p inner join ABCTempData..DailyTemplate d on d.[Date] between p.DateFrom and p.DateTo where d.[Date] <= @CutoffDate create unique index #tbl_Deal_Trans_IX on #tbl_Deal_Trans (EffectiveDate, ContractNo) select r.ContractNo ,r.[Date] ,r.Rate ,convert(numeric(12,2), coalesce(-t.Principal,0)) as Principal ,convert(numeric(12,2), coalesce(-t.Interest,0)) as Interest into #tbl_Deal_Daily from #tbl_Deal_DailyRates r left outer join #tbl_Deal_Trans t on t.EffectiveDate = r.[Date] and t.ContractNo = r.ContractNo create unique index #tbl_Deal_Daily_IX2 on #tbl_Deal_Daily (ContractNo, [Date]) declare @contract varchar(12) declare @date smalldatetime declare @rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> declare @prin numeric(12,2) declare @int numeric(12,2) declare @p numeric(12,2) declare @i numeric(12,2) declare @a numeric(20,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> declare @PerDiem numeric(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> declare @LastContract varchar(12) – !!! SLOW DOWN OCCURS AFTER THIS POINT declare csr CURSOR FAST_FORWARD for select ContractNo, [Date], Principal, Interest, Rate from #tbl_Deal_Daily order by ContractNo, [Date] open csr set @LastContract = ‘XXX' fetch next from csr into @contract,@date, @prin, @int, @rate while @@fetch_status=0 begin if (@LastContract <> @Contract) begin set @LastContract = @Contract set @p = 0 set @i = 0 set @a = 0 end set @p = @p + @Prin set @i = @i + @Int set @[email protected] * @p set @a = @a + @PerDiem + @Int if (coalesce(@Prin, 0) <> 0) set @a = round(@a, 2) insert into ABC..DealDaily (ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate) values (@contract, @date, @prin, @int, @p, @perdiem, @a, @cutoffdate) fetch next from csr into @contract,@date, @prin, @int, @rate end close csr deallocate csr drop table #tbl_Deal_Daily drop table #tbl_Deal_DailyRates drop table #tbl_Deal_RateChg drop table #tbl_Deal_Trans GO

This procedure shouldn’t even work because next statement is not correct:
insert into ABC..DealDaily
(ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate)
values (@contract, @date, @prin, @int, @p, @perdiem, @a, @cutoffdate)
There is one less value then column. Please post correct proc.

mmarovic, I made minor edits to my code before posting it. I left out @rate from the values clause. So shoot me. You are not focusing on the issue. You are examining my code to evaluate my ability in your eyes. Just forget it. I’ll find the answer eventually. I always do.

Actually I’m working on your query for an hour (because it’s really interesting problem). I want to be sure I am working on the real proc that causes problem. If it is not syntaxly correct how can I be sure that I am working on the real issue. So my question is: Is the code posted (+ @rate in values list) the one that causes the problem? If so, I’ll come back in a few minutes with another version I think can work better.

Yes.
insert into ABC..DealDaily
(ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate)
values (@contract, @date, @rate, @prin, @int, @p, @perdiem, @a, @cutoffdate) More info – all numbers given as approximates:
The set processed with the cursor has 1.5 million rows.
The set consists of 2500 distinct ContractNo values.
The date range varies for each contract.
The average number of dates per ContractNo is 500. For each Date, the Principal is the sum of Principal
for all rows having the same ContractNo and for Date <= the Date of the current row. Basically, this is an accumulator. The Interest is charged on the accumulated Principal
at that point in time.

Additional question, what is
set @i = @i + @Int
used for? If it is another mistake during the edit I need to know correct code to offer right solution.

Cursor on 1.5 million rows!!! Let’s see if we can avoid it. Here is what I have so far:<pre id="code">CREATE PROCEDURE spBuildAccruedIncome AS declare @CutoffDate smalldatetime –to avoid sp recompilations and tempdb locking during select … insert into. Not so important in this case but still create table #tbl_Deal_Trans( ContractNo varchar(12) not null, EffectiveDate smallDateTime not null, Principal numeric(12,2) not null, Interest numeric(12,2) not null ) create table #tbl_Deal_RateChg( ContractNo varchar(12) not null, EffectiveDate smallDatetime not null, DateTo smallDateTime null, Rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null ) create table #tbl_Deal_DailyRates( ContractNo varchar(12) not null, [Date] smallDatetime not null, Rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null ) create table #tbl_Deal_Daily( ContractNo varchar(12) not null, [Date] smallDatetime not null, Rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null, Principal numeric(12,2) not null, Interest numeric(12,2) not null, PrinBalance numeric(12,2) not null, PerDiem numeric(12,2) not null, AccruedIncome numeric(12, 2) not null ) set @CutoffDate = ABC.dbo.fnSTRIPTIME(ABC.dbo.fnGET_LAST_DAY_OF_MONTH(getdate())) insert into #tbl_Deal_Trans(ContractNo, EffectiveDate, Principal, Interest) select p.ContractNo ,p.EffectiveDate) ,sum(coalesce(p.Principal,0)) ,sum(coalesce(p.Interest,0)) from Reporting..CashFlow p join ABC..ContractList c on c.ContractNo = p.ContractNo where p.reversed = 0 and patindex(‘%[^0-9,-]%’, p.ContractNo) = 0 and not (p.Event in (‘Bill’,’Current Due’,’Rate Change’))–here better use in (<other events&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> and p.EffectiveDate <= @CutoffDate group by p.ContractNo p.EffectiveDate insert into #tbl_Deal_RateChg(ContractNo, DateFrom, Rate) select p.ContractNo ,p.EffectiveDate ,ABC.dbo.fnGET_PRECISE_QUOTIENT(Rate/100, d.AnnualDays) from Reporting..CashFlow p inner join ABC..ContractList c on c.ContractNo = p.ContractNo inner join Reporting..Contract d on d.ContractNo = c.ContractNo where p.reversed = 0 and patindex(‘%[^0-9,-]%’, c.ContractNo) = 0 and p.Event = ‘Rate Change' and p.EffectiveDate <= @CutoffDate –Should be faster then original update ———————- create index tbl_Deal_RateChg_IX on #tbl_Deal_RateChg (contractNo, DateFrom desc) declare @DateTo smallDateTime set @dateTo = @CutOffDate update t set DateTo = DateAdd(d, -1,@DateTo), @DateTo = DateFrom from #tbl_Deal_RateChg t (index=tbl_Deal_RateChg_IX) ———————– insert into #tbl_Deal_DailyRates(ContractNo, [Date], Rate) select p.ContractNo, d.[Date], p.Rate from #tbl_Deal_RateChg p join FFCITempData..DailyTemplate d on d.[Date] between p.DateFrom and p.DateTo where d.[Date] <= @CutoffDate option (force order) –hopefully there are no many rows in DailyTemplate table, or there is an index on [date] column create unique clustered index #tbl_Deal_Trans_IX on #tbl_Deal_Trans (EffectiveDate, ContractNo) insert into #tbl_Deal_Daily( select r.ContractNo ,r.[Date] ,r.Rate ,coalesce(-t.Principal,0) ,coalesce(-t.Interest,0) ,0 ,0 ,0 from #tbl_Deal_DailyRates r left outer join #tbl_Deal_Trans t on t.EffectiveDate = r.[Date] and t.ContractNo = r.ContractNo create unique clustered index tbl_Deal_Daily_IX2 on #tbl_Deal_Daily (ContractNo, [Date]) declare @contract varchar(12) declare @prin numeric(12,2) declare @AccruedIncome numeric(20,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> declare @PerDiem numeric(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @PrinBalance numeric(12,2) –ovde nastavi update t set @contract = contractNo, @prinBalance = PrinBalance = case when @contract = contractNo then PrinBalance = @PrinBalance + prin else Prin end, PerDiem = rate * case when @contract = contractNo then @PrinBalance + prin else Prin end, @i = case when @contract = ContractNo then @i + interest else interest end, @AccruedIncome = AccruedIncome = @AccruedIncome + @rate * case when @contract = contractNo then @PrinBalance + prin else Prin end + interest from #tbl_Deal_Daily t (index=tbl_Deal_Daily_IX2) insert into ABC..DealDaily (ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate) select ConstractNo, [date], Rate, Principal, Interest, PrinBalance, PerDiem, AccruedIncome, @cutoffdate from #tbl_Deal_Daily drop table #tbl_Deal_Daily drop table #tbl_Deal_DailyRates drop table #tbl_Deal_RateChg drop table #tbl_Deal_Trans GO</pre id="code">I havent handled statement bellow yet:<pre id="code">if (coalesce(@Prin, 0) <> 0) set @a = round(@a, 2)</pre id="code">but I have to go now. Please review the code, fix possible typos and maybe forgotten local variables and test it. I hope it is equivalent to your code but faster. My guess is that while working with qa you read data from the cache, but job dosen’t have the benefit of cached data at the moment it is running. If it is not the reason then I don’t know, but the code I posted should be faster anyway. See if code returns results close enough to what you need. If so we can finish it tomorrow.

Click on the reply with quote icon on the previous post to have query properly formated.

mmarovic,
I appreciate the time you have spent on this. I do not think I have properly communicated the situation.
ROW PRINCIPAL @P (balance) @rate @a
—- ——— ———— ———- ————
Row1 1000 1000 .000208033 @P * @rate
Row2 0 1000 .000208033
Row3 400 1400 .000211520
Row4 0 1400 .000211520
Row5 0 1400 .000211520
Row6 -100 1300 .000224000 See that @P is the sum of Principal on all rows from Row1 through the current row.

quote:Originally posted by bcm mmarovic,
I appreciate the time you have spent on this. I do not think I have properly communicated the situation.
ROW PRINCIPAL @P (balance) @rate @a
—- ——— ———— ———- ————
Row1 1000 1000 .000208033 @P * @rate
Row2 0 1000 .000208033
Row3 400 1400 .000211520
Row4 0 1400 .000211520
Row5 0 1400 .000211520
Row6 -100 1300 .000224000 See that @P is the sum of Principal on all rows from Row1 through the current row.

I am not sure I understand. Based on your code balance is related to specific contractNo and it is reset when new contractNo is accessed.

Let us focus on the root of the problem. ROW PRINCIPAL @P (balance) Formula
—- ———- ———— —————————————
Row1 1000 1000 Row1
Row2 0 1000 Row1 + Row2
Row3 400 1400 Row1 + Row2 + Row3
Row4 0 1400 Row1 + Row2 + Row3 + Row4
Row5 0 1400 Row1 + Row2 + Row3 + Row4 + Row5
Row6 -100 1300 Row1 + Row2 + Row3 + Row4 + Row5 + Row6
The root of the problem is the need to accumulate rather than sum. The resulting recordset will have the same number of rows as the original. The Balance will be the intermediate sum() as of each row.

Yes, that’s what the code I posted is supposed to do. Have you tried it?

Here I removed @i assingment from update, because it is not needed::<pre id="code">CREATE PROCEDURE spBuildAccruedIncome AS declare @CutoffDate smalldatetime –to avoid sp recompilations and tempdb locking during select … insert into. Not so important in this case but still create table #tbl_Deal_Trans( ContractNo varchar(12) not null, EffectiveDate smallDateTime not null, Principal numeric(12,2) not null, Interest numeric(12,2) not null ) create table #tbl_Deal_RateChg( ContractNo varchar(12) not null, EffectiveDate smallDatetime not null, DateTo smallDateTime null, Rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null ) create table #tbl_Deal_DailyRates( ContractNo varchar(12) not null, [Date] smallDatetime not null, Rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null ) create table #tbl_Deal_Daily( ContractNo varchar(12) not null, [Date] smallDatetime not null, Rate numeric(12,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> not null, Principal numeric(12,2) not null, Interest numeric(12,2) not null, PrinBalance numeric(12,2) not null, PerDiem numeric(12,2) not null, AccruedIncome numeric(12, 2) not null ) set @CutoffDate = ABC.dbo.fnSTRIPTIME(ABC.dbo.fnGET_LAST_DAY_OF_MONTH(getdate())) insert into #tbl_Deal_Trans(ContractNo, EffectiveDate, Principal, Interest) select p.ContractNo ,p.EffectiveDate) ,sum(coalesce(p.Principal,0)) ,sum(coalesce(p.Interest,0)) from Reporting..CashFlow p join ABC..ContractList c on c.ContractNo = p.ContractNo where p.reversed = 0 and patindex(‘%[^0-9,-]%’, p.ContractNo) = 0 and not (p.Event in (‘Bill’,’Current Due’,’Rate Change’))–here better use in (<other events&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> and p.EffectiveDate <= @CutoffDate group by p.ContractNo p.EffectiveDate insert into #tbl_Deal_RateChg(ContractNo, DateFrom, Rate) select p.ContractNo ,p.EffectiveDate ,ABC.dbo.fnGET_PRECISE_QUOTIENT(Rate/100, d.AnnualDays) from Reporting..CashFlow p inner join ABC..ContractList c on c.ContractNo = p.ContractNo inner join Reporting..Contract d on d.ContractNo = c.ContractNo where p.reversed = 0 and patindex(‘%[^0-9,-]%’, c.ContractNo) = 0 and p.Event = ‘Rate Change' and p.EffectiveDate <= @CutoffDate –Should be faster then original update ———————- create index tbl_Deal_RateChg_IX on #tbl_Deal_RateChg (contractNo, DateFrom desc) declare @DateTo smallDateTime set @dateTo = @CutOffDate exec(‘ update t set DateTo = DateAdd(d, -1,@DateTo), @DateTo = DateFrom from #tbl_Deal_RateChg t (index=tbl_Deal_RateChg_IX)' ) ———————– insert into #tbl_Deal_DailyRates(ContractNo, [Date], Rate) select p.ContractNo, d.[Date], p.Rate from #tbl_Deal_RateChg p join FFCITempData..DailyTemplate d on d.[Date] between p.DateFrom and p.DateTo where d.[Date] <= @CutoffDate option (force order) –hopefully there are no many rows in DailyTemplate table, or there is an index on [date] column create unique clustered index #tbl_Deal_Trans_IX on #tbl_Deal_Trans (EffectiveDate, ContractNo) insert into #tbl_Deal_Daily( select r.ContractNo ,r.[Date] ,r.Rate ,coalesce(-t.Principal,0) ,coalesce(-t.Interest,0) ,0 ,0 ,0 from #tbl_Deal_DailyRates r left outer join #tbl_Deal_Trans t on t.EffectiveDate = r.[Date] and t.ContractNo = r.ContractNo create unique clustered index tbl_Deal_Daily_IX2 on #tbl_Deal_Daily (ContractNo, [Date]) declare @contract varchar(12) declare @prin numeric(12,2) declare @AccruedIncome numeric(20,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> declare @PerDiem numeric(18,<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> DECLARE @PrinBalance numeric(12,2) exec(‘ update t set @contract = contractNo, @prinBalance = PrinBalance = case when @contract = contractNo then @PrinBalance + prin else Prin end, PerDiem = rate * case when @contract = contractNo then @PrinBalance + prin else Prin end, @AccruedIncome = AccruedIncome = @AccruedIncome + @rate * case when @contract = contractNo then @PrinBalance + prin else Prin end + interest from #tbl_Deal_Daily t (index=tbl_Deal_Daily_IX2)' ) insert into ABC..DealDaily (ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate) select ConstractNo, [date], Rate, Principal, Interest, PrinBalance, PerDiem, AccruedIncome, @cutoffdate from #tbl_Deal_Daily drop table #tbl_Deal_Daily drop table #tbl_Deal_DailyRates drop table #tbl_Deal_RateChg drop table #tbl_Deal_Trans GO</pre id="code"> [Edited] I fixed one error in the code.

I fixed an error in the code posted above.

I also added exec(‘…’) in statements that refer to temp index that doesn’t exist at the compile time. Procedure might not compile because of that, so I added dynamic execution.

Or use a subquery where you sum all the values up to and including the current row, according to the same ORDER BY. This does require that you have a column that is ordered the same way, and where the cut-off point conincides exactly with the current record – like if you have an identity column that you are sorting on. In air code, something along these lines … SELECT t.id, t.col1, t.col2,
(SELECT SUM(t1.col2) FROM t AS t1 WHERE t1.id <= t.id)
FROM t
OPRDER BY t.id

Multiple assignments are to be avoided as they do not work consistently. I do not believe that multiple assignments are standard SQL-92 practice. I guess I should have posted this in a DBA forum. My original question did not ask how to speed up the process. Given that there are 1.5 million records processed, the 5-7 minute processing time is acceptable. The question is why does the process run in 5-7 minutes locally but it takes 1-2 hours when run as a scheduled job on the server itself.

mmarovic,
I read about multiple assignments and the rules under which they work. I created a quick test on my server at home. I have confirmed that this technique will work. Thank-you for showing me something new.

You are wellcome. I didn’t have time to explain in the meantime, I had something urgent to work on. Important detail here that makes it work is forcing update order using index hint. That’s the only way to make update process data in specific order (AFAIK).

Also, I already admited I don’t have answer to your original question, but I hope if you apply solution suggested you will solve the problem. I mean, you will not have slow down.

]]>