SQL Server Performance

Slow Performance Under SQL Agent

Discussion in 'T-SQL Performance Tuning for Developers' started by bcm, Nov 11, 2005.

  1. bcm New Member

    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?

  2. mmarovic Active Member

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

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

    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.
  5. bcm New Member

    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.
  6. mmarovic Active Member

    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.
  7. bcm New Member

    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.

  8. mmarovic Active Member

    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.
  9. mmarovic Active Member

    Cursor on 1.5 million rows!!! Let's see if we can avoid it. Here is what I have so far:<pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE spBuildAccruedIncome<br />AS<br />declare @CutoffDate smalldatetime<br /><br />--to avoid sp recompilations and tempdb locking during select ... insert into. Not so important in this case but still<br />create table #tbl_Deal_Trans(<br />ContractNo varchar(12) not null,<br />EffectiveDate smallDateTime not null,<br />Principal numeric(12,2) not null,<br />Interest numeric(12,2) not null<br />)<br /><br />create table #tbl_Deal_RateChg(<br />ContractNo varchar(12) not null,<br />EffectiveDate smallDatetime not null,<br />DateTo smallDateTime null,<br />Rate numeric(12,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null<br />)<br /><br />create table #tbl_Deal_DailyRates(<br />ContractNo varchar(12) not null,<br />[Date] smallDatetime not null,<br />Rate numeric(12,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null<br />)<br /><br />create table #tbl_Deal_Daily(<br />ContractNo varchar(12) not null,<br />[Date] smallDatetime not null,<br />Rate numeric(12,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null,<br />Principal numeric(12,2) not null,<br />Interest numeric(12,2) not null,<br />PrinBalance numeric(12,2) not null,<br />PerDiem numeric(12,2) not null,<br />AccruedIncome numeric(12, 2) not null<br />)<br /><br />set @CutoffDate = ABC.dbo.fnSTRIPTIME(ABC.dbo.fnGET_LAST_DAY_OF_MONTH(getdate()))<br /><br />insert into #tbl_Deal_Trans(ContractNo, EffectiveDate, Principal, Interest)<br />select<br /> p.ContractNo<br />,p.EffectiveDate)<br />,sum(coalesce(p.Principal,0))<br />,sum(coalesce(p.Interest,0))<br />from Reporting..CashFlow p<br />join ABC..ContractList c on c.ContractNo = p.ContractNo<br />where p.reversed = 0<br /> and patindex('%[^0-9,-]%', p.ContractNo) = 0<br /> and not (p.Event in ('Bill','Current Due','Rate Change'))--here better use in (&lt;other events&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /> and p.EffectiveDate &lt;= @CutoffDate<br />group by<br /> p.ContractNo<br /> p.EffectiveDate<br /><br />insert into #tbl_Deal_RateChg(ContractNo, DateFrom, Rate) <br />select <br /> p.ContractNo<br />,p.EffectiveDate<br />,ABC.dbo.fnGET_PRECISE_QUOTIENT(Rate/100, d.AnnualDays)<br />from Reporting..CashFlow p<br />inner join ABC..ContractList c on c.ContractNo = p.ContractNo<br />inner join Reporting..Contract d on d.ContractNo = c.ContractNo<br />where p.reversed = 0<br /> and patindex('%[^0-9,-]%', c.ContractNo) = 0<br /> and p.Event = 'Rate Change'<br /> and p.EffectiveDate &lt;= @CutoffDate<br /><br />--Should be faster then original update<br />----------------------<br />create index tbl_Deal_RateChg_IX on #tbl_Deal_RateChg (contractNo, DateFrom desc)<br /><br />declare @DateTo smallDateTime<br />set @dateTo = @CutOffDate<br /><br />update t<br /> set DateTo = DateAdd(d, -1,@DateTo), @DateTo = DateFrom<br />from #tbl_Deal_RateChg t (index=tbl_Deal_RateChg_IX)<br />-----------------------<br /><br />insert into #tbl_Deal_DailyRates(ContractNo, [Date], Rate)<br />select p.ContractNo, d.[Date], p.Rate<br />from #tbl_Deal_RateChg p<br />join FFCITempData..DailyTemplate d on d.[Date] between p.DateFrom and p.DateTo<br />where d.[Date] &lt;= @CutoffDate<br />option (force order) --hopefully there are no many rows in DailyTemplate table, or there is an index on [date] column<br /><br />create unique clustered index #tbl_Deal_Trans_IX on #tbl_Deal_Trans (EffectiveDate, ContractNo)<br /><br />insert into #tbl_Deal_Daily(<br />select <br /> r.ContractNo<br />,r.[Date]<br />,r.Rate<br />,coalesce(-t.Principal,0)<br />,coalesce(-t.Interest,0)<br />,0<br />,0<br />,0<br />from #tbl_Deal_DailyRates r<br />left outer join #tbl_Deal_Trans t on t.EffectiveDate = r.[Date] and t.ContractNo = r.ContractNo<br /><br />create unique clustered index tbl_Deal_Daily_IX2 on #tbl_Deal_Daily (ContractNo, [Date])<br /><br />declare @contract varchar(12)<br />declare @prin numeric(12,2)<br />declare @AccruedIncome numeric(20,<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @PerDiem numeric(18,<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @PrinBalance numeric(12,2)<br /><br />--ovde nastavi<br />update t<br /> set <br />@contract = contractNo,<br />@prinBalance = PrinBalance = case<br />when @contract = contractNo then PrinBalance = @PrinBalance + prin<br />else Prin<br /> end,<br />PerDiem = rate * case<br />when @contract = contractNo then @PrinBalance + prin<br />else Prin<br /> end,<br />@i = case <br />when @contract = ContractNo then @i + interest<br />else interest<br /> end,<br />@AccruedIncome = AccruedIncome = @AccruedIncome <br />+ @rate * case<br />when @contract = contractNo then @PrinBalance + prin<br />else Prin<br /> end<br />+ interest<br />from #tbl_Deal_Daily t (index=tbl_Deal_Daily_IX2)<br /><br />insert into ABC..DealDaily (ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate)<br />select ConstractNo, [date], Rate, Principal, Interest, PrinBalance, PerDiem, AccruedIncome, @cutoffdate<br /> from #tbl_Deal_Daily<br /><br /><br />drop table #tbl_Deal_Daily<br />drop table #tbl_Deal_DailyRates<br />drop table #tbl_Deal_RateChg<br />drop table #tbl_Deal_Trans<br /><br />GO</font id="code"></pre id="code">I havent handled statement bellow yet:<pre id="code"><font face="courier" size="2" id="code">if (coalesce(@Prin, 0) &lt;&gt; 0) set @a = round(@a, 2)</font id="code"></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. <br />See if code returns results close enough to what you need. If so we can finish it tomorrow.
  10. mmarovic Active Member

    Click on the reply with quote icon on the previous post to have query properly formated.
  11. bcm New Member

    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.
  12. mmarovic Active Member

    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.
  13. bcm New Member

    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.
  14. mmarovic Active Member

    Yes, that's what the code I posted is supposed to do. Have you tried it?
  15. mmarovic Active Member

    Here I removed @i assingment from update, because it is not needed::<pre id="code"><font face="courier" size="2" id="code">CREATE PROCEDURE spBuildAccruedIncome<br />AS<br />declare @CutoffDate smalldatetime<br /><br />--to avoid sp recompilations and tempdb locking during select ... insert into. Not so important in this case but still<br />create table #tbl_Deal_Trans(<br />ContractNo varchar(12) not null,<br />EffectiveDate smallDateTime not null,<br />Principal numeric(12,2) not null,<br />Interest numeric(12,2) not null<br />)<br /><br />create table #tbl_Deal_RateChg(<br />ContractNo varchar(12) not null,<br />EffectiveDate smallDatetime not null,<br />DateTo smallDateTime null,<br />Rate numeric(12,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null<br />)<br /><br />create table #tbl_Deal_DailyRates(<br />ContractNo varchar(12) not null,<br />[Date] smallDatetime not null,<br />Rate numeric(12,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null<br />)<br /><br />create table #tbl_Deal_Daily(<br />ContractNo varchar(12) not null,<br />[Date] smallDatetime not null,<br />Rate numeric(12,<img src='/community/emoticons/emotion-11.gif' alt='8)' /> not null,<br />Principal numeric(12,2) not null,<br />Interest numeric(12,2) not null,<br />PrinBalance numeric(12,2) not null,<br />PerDiem numeric(12,2) not null,<br />AccruedIncome numeric(12, 2) not null<br />)<br /><br />set @CutoffDate = ABC.dbo.fnSTRIPTIME(ABC.dbo.fnGET_LAST_DAY_OF_MONTH(getdate()))<br /><br />insert into #tbl_Deal_Trans(ContractNo, EffectiveDate, Principal, Interest)<br />select<br /> p.ContractNo<br />,p.EffectiveDate)<br />,sum(coalesce(p.Principal,0))<br />,sum(coalesce(p.Interest,0))<br />from Reporting..CashFlow p<br />join ABC..ContractList c on c.ContractNo = p.ContractNo<br />where p.reversed = 0<br /> and patindex('%[^0-9,-]%', p.ContractNo) = 0<br /> and not (p.Event in ('Bill','Current Due','Rate Change'))--here better use in (&lt;other events&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br /> and p.EffectiveDate &lt;= @CutoffDate<br />group by<br /> p.ContractNo<br /> p.EffectiveDate<br /><br />insert into #tbl_Deal_RateChg(ContractNo, DateFrom, Rate) <br />select <br /> p.ContractNo<br />,p.EffectiveDate<br />,ABC.dbo.fnGET_PRECISE_QUOTIENT(Rate/100, d.AnnualDays)<br />from Reporting..CashFlow p<br />inner join ABC..ContractList c on c.ContractNo = p.ContractNo<br />inner join Reporting..Contract d on d.ContractNo = c.ContractNo<br />where p.reversed = 0<br /> and patindex('%[^0-9,-]%', c.ContractNo) = 0<br /> and p.Event = 'Rate Change'<br /> and p.EffectiveDate &lt;= @CutoffDate<br /><br />--Should be faster then original update<br />----------------------<br />create index tbl_Deal_RateChg_IX on #tbl_Deal_RateChg (contractNo, DateFrom desc)<br /><br />declare @DateTo smallDateTime<br />set @dateTo = @CutOffDate<br /><br />exec('<br />update t<br /> set DateTo = DateAdd(d, -1,@DateTo), @DateTo = DateFrom<br />from #tbl_Deal_RateChg t (index=tbl_Deal_RateChg_IX)'<br />)<br />-----------------------<br /><br />insert into #tbl_Deal_DailyRates(ContractNo, [Date], Rate)<br />select p.ContractNo, d.[Date], p.Rate<br />from #tbl_Deal_RateChg p<br />join FFCITempData..DailyTemplate d on d.[Date] between p.DateFrom and p.DateTo<br />where d.[Date] &lt;= @CutoffDate<br />option (force order) --hopefully there are no many rows in DailyTemplate table, or there is an index on [date] column<br /><br />create unique clustered index #tbl_Deal_Trans_IX on #tbl_Deal_Trans (EffectiveDate, ContractNo)<br /><br />insert into #tbl_Deal_Daily(<br />select <br /> r.ContractNo<br />,r.[Date]<br />,r.Rate<br />,coalesce(-t.Principal,0)<br />,coalesce(-t.Interest,0)<br />,0<br />,0<br />,0<br />from #tbl_Deal_DailyRates r<br />left outer join #tbl_Deal_Trans t on t.EffectiveDate = r.[Date] and t.ContractNo = r.ContractNo<br /><br />create unique clustered index tbl_Deal_Daily_IX2 on #tbl_Deal_Daily (ContractNo, [Date])<br /><br />declare @contract varchar(12)<br />declare @prin numeric(12,2)<br />declare @AccruedIncome numeric(20,<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />declare @PerDiem numeric(18,<img src='/community/emoticons/emotion-11.gif' alt='8)' /><br />DECLARE @PrinBalance numeric(12,2)<br /><br />exec('<br />update t<br /> set <br />@contract = contractNo,<br />@prinBalance = PrinBalance = case<br />when @contract = contractNo then @PrinBalance + prin<br />else Prin<br /> end,<br />PerDiem = rate * case<br />when @contract = contractNo then @PrinBalance + prin<br />else Prin<br /> end,<br />@AccruedIncome = AccruedIncome = @AccruedIncome <br />+ @rate * case<br />when @contract = contractNo then @PrinBalance + prin<br />else Prin<br /> end<br />+ interest<br />from #tbl_Deal_Daily t (index=tbl_Deal_Daily_IX2)'<br />)<br /><br />insert into ABC..DealDaily (ContractNo,[Date],Rate,Principal,Interest,PrinBalance,PerDiem,AccruedIncome,AccruedThruDate)<br />select ConstractNo, [date], Rate, Principal, Interest, PrinBalance, PerDiem, AccruedIncome, @cutoffdate<br /> from #tbl_Deal_Daily<br /><br /><br />drop table #tbl_Deal_Daily<br />drop table #tbl_Deal_DailyRates<br />drop table #tbl_Deal_RateChg<br />drop table #tbl_Deal_Trans<br /><br />GO</font id="code"></pre id="code"><br />[Edited] I fixed one error in the code.
  16. mmarovic Active Member

    I fixed an error in the code posted above.
  17. mmarovic Active Member

    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.
  18. Adriaan New Member

    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

  19. bcm New Member

    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.
  20. bcm New Member

    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.
  21. mmarovic Active Member

    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).
  22. mmarovic Active Member

    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.

Share This Page