Hi All
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
