I have SP1 that runs on its own in 4 to 5 seconds and returns 200 or 300 records. I now have SP2 that executes SP1 so I can perform other processing on the record set. I've let this run at night for over an hour and it will finally complete. I don't understand why it takes so long or how to fix it. What is wrong?
SP 2 looks like this:
----------------------------------------------------------
Thanks - Dave
SP 2 looks like this:
----------------------------------------------------------
Code:
Declare @Today Varchar (12)
Declare @Entity Varchar (5)
Declare @DBName Varchar (5)
Set @Today = GetDate()
Set @Entity = 'EM1'
Set @DBName = 'PA'
create table #DB_AR_Aging (
Custid varchar (15),
custname varchar (50),
territory varchar (15),
aropen decimal (18,2),
arcurr decimal (18,2),
ar31 decimal (18,2),
ar61 decimal (18,2),
ar91 decimal (18,2)
)
insert into #DB_AR_Aging execute SP1 @Entity, @DBName, @Today, '', 'Customer','AR91', 'smry'
update eswdata.dbo.crm_DB_Summary
set
crm_AROpen = (select Sum(aropen) from #DB_AR_Aging DBAR Where DBAR.Custid = eswdata.dbo.crm_DB_Summary.crm_Customer_ID Group by DBAR.Custid),
crm_ARCurrent = (select Sum(arcurr) from #DB_AR_Aging DBAR Where DBAR.Custid = eswdata.dbo.crm_DB_Summary.crm_Customer_ID Group by DBAR.Custid),
crm_AR31 = (select Sum(ar31) from #DB_AR_Aging DBAR Where DBAR.Custid = eswdata.dbo.crm_DB_Summary.crm_Customer_ID Group by DBAR.Custid),
crm_AR61 = (select Sum(ar61) from #DB_AR_Aging DBAR Where DBAR.Custid = eswdata.dbo.crm_DB_Summary.crm_Customer_ID group by DBAR.Custid),
crm_AR91 = (select Sum(ar91) from #DB_AR_Aging DBAR Where DBAR.Custid = eswdata.dbo.crm_DB_Summary.crm_Customer_ID group by DBAR.Custid)
Where crm_Entity_ID = @EntityID
Thanks - Dave