CODEWITHSUNDEEP
sql-servert-sql
TonyP
TonyP

Reputation: 5873

Strange execution times in t-sql

I have two stored procedures, the first one calls the second .. If I execute the second one alone it takes over 5 minutes to complete.. But when executed within the first one it takes little over 1 minute.. What is the reason !

Here is the first one

ALTER procedure [dbo].[schRefreshPriceListItemGroups] as

begin tran
delete from PriceListItemGroups
if @@error !=0 goto rolback
Insert PriceListItemGroups(comno,t$cuno,t$cpls,t$cpgs,t$dsca,t$cpru)
SELECT distinct c.comno,c.t$cuno, c.t$cpls,I.t$cpgs,g.t$dsca,g.t$cpru
    FROM    TTCCOM010nnn    C
    JOIN    TTDSLS032nnn    PL  ON  PL.comno    =   c.Comno     and PL.t$cpls   =   c.t$cpls
    JOIN    TTIITM001nnn    I   ON  I.t$item    =   pl.t$item   AND I.comno     =   pl.comNo
    JOIN    TTCMCS024nnn    G   ON  g.T$cprg    =   I.t$cpgs    AND g.comno     =   I.Comno
 WHERE  c.t$cpls !='' 
order by comno desc, t$cuno, t$cpgs
if @@error !=0 goto rolback
-----------------------------------------------------
Exec scrRefreshCustomersCatalogs
-----------------------------------------------------
commit tran


return
rolback:
Rollback tran

And the second one 

Alter proc scrRefreshCustomersCatalogs as
declare @baanIds table(id int identity(1,1),baanId varchar(12))
declare @baanId varchar(12),@i int, @n int
Insert @baanIds(BaanId)
select baanId from ftElBaanIds()
SELECT @I=1,@n=max(id) from @baanIds
select @i,@n
Begin tran
if @@error !=0 goto xRollBack
WHILE @I <=@n
    Begin
        select @baanId=baanId from @baanIds where id=@i
        if @@error !=0 goto xRollBack
        Delete from customersCatalogs where comno+'-'+t$cuno=@baanId
        print Convert(varchar,@i)+' baanId='+@baanId
        Insert customersCatalogs exec customersCatalog @baanId
        if @@error !=0 goto xRollBack

        set @i=@i+1;
    end

Commit Tran
Update statistics customersCatalogs with fullscan

Return
xRollBack:
Print '*****Rolling back*************'
Rollback tran

Upvotes: 1

Views: 210

Answers (1)

Cade Roux
Cade Roux

Reputation: 89661

It's hard to say. When run within the first SP, you will be inside two transactions.

I assume the second SP does something indirectly with the data insert in the first SP (PriceListItemGroups), so when you are running the second SP by itself, is the same data also insert into that table?

In the first SP, it also seems odd that you are inserting with an ORDER BY. While I know that's technically allowed, in almost every case, it's unnecessary.

Upvotes: 1

Related Questions