Exec SP within another SP Performs Poorly

[dfrazell]

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:
----------------------------------------------------------

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

 

[gmmastros]

What does the code for SP1 look like?

I suspect that if you speed this up, this code will run faster too.

Truth is... I'm not real impressed with the code I already see. You have 5 subqueries here, which is going to slow things down considerably. Instead, you could write 1 query that gets all of the sums and another to update the table. Like this...

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'

Declare @aropen decimal (18,2)
Declare @arcurr decimal (18,2)
Declare @ar31   decimal (18,2)
Declare @ar61   decimal (18,2)
Declare @ar91   decimal (18,2)

Select @aropen = Sum(aropen),
       @arcurr = Sum(arcurr),
       @ar31   = Sum(ar31),
       @ar61   = Sum(ar61),
       @ar91   = Sum(ar91)
from   #DB_AR_Aging DBAR 

update eswdata.dbo.crm_DB_Summary
set    crm_AROpen = @aropen,
       crm_ARCurrent = @arcurr,
       crm_AR31      = @ar31,
       crm_AR61      = @ar61,
       crm_AR91      = @ar91
Where  crm_Entity_ID = [!]@EntityID[/!]

This code assumes that all values in the temp table are summed and that only 1 row in the real table is updated. This assumption can be completely wrong. If it is, do not use this code.

Also, it appears as though you are using @Entity and @EntityId in different places. Double check that this is correct.

I still think you'll see more improvement by optimizing SP1.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dfrazell]

The problem isn't with the Update in SP2 because in testing I've removed that code to eliminate it as a problem. I'd agree that optimizing SP1 could improve the performance but I don't understand why SP1 will run on it's own in 4 or 5 seconds but takes more than an hour to run when executed from SP2?
(@Entity and @EntityId are the same. I was cleaning up my example code and missed one.)

 

[dfrazell]

Any other ideas on what my performance issues are? Thanks!

 

[haroldholt]

What do your execution plans look like when you run SP1 by itself and then via a call from SP2 - are they different ? Are you using defaults for SP1's parameters, if so are they typical values and are these values the same as those passed in when you call it from SP2 ?

 

[RyanEK]

Adding the update code within the second stored procedure may indeed affect the performance of the first. To truly discount this as the issue, I suggest you display a print getdate() before and after the first stored proc call to get its real execution time.

 

[dfrazell]

It's hard to tell if the execution plans are different for SP1 when ran by itself vs within SP2. When ran within SP2 I don't see the same detailed information for SP1. Maybe I'm not getting the execution plan information setup correctly. The parameters are typical values and are the same.

As RyanEK suggested I put print getdate() before and after the exec stored proc to eliminate the update issue. The time difference between the before and after was 10 minutes and the final update was 1 second.

So in this example where it took 10 minutes to execute SP1 from SP2, it only took 8 seconds to run SP1 independently.

I'm still puzzled and don't know why it's taking so long to run or how to fix it. What else can I look at?

 

[SQLSister]

Well one reason why it is slower is you are inserting the data into a temp table rather than just executing the sp. Inserting usually takes more time than just selecting. Maybe you are having performance issues with temp db?

"NOTHING is more important in a database than integrity." ESquared

 

[dfrazell]

What can I do to improve this performance? Is there a better way to update the crm_DB_Summary table? That is the only reason I have a temp table.

 

[SQLSister]

what does the sp do? Hard to say without seeing the code.
You could try growing tempdb at a higher percentage but I think the key to fixing the problem is inthe sp.

"NOTHING is more important in a database than integrity." ESquared

 

[MDXer]

About how many rows are added to your temp table?

My Guess is you are using the temp table since you can't use an aggregate in the update statement. One work around is to create a view that preforms your Select statement with your aggregates and then use this view as the source of your update.

Either way look at your execution plan and see where most of the effort is in the query then you can address a more specific point.

Things to look at are:

1) Do you have the needed indexes to support your Select query. Table Scan, Index scan vs. Index seek.

2) when you join your table to update with your temp table what does the execution plan look like?

Paul
---------------------------------------
Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[gmmastros]

What version of SQL Server are you using? If you are using SQL2005, I encourage you to try this...

Declare @Today Varchar (12)
Declare @Entity Varchar (5)
Declare @DBName Varchar (5)

Set @Today = GetDate()
Set @Entity = 'EM1'
Set @DBName = 'PA'

Declare @DB_AR_Aging Table (
    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

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[haroldholt]

This is merely restating what others have described above (so all of you please forgive me for this!) but I guess your next step would be to run SP2 with just

Declare @Today Varchar (12)
Declare @Entity Varchar (5)
Declare @DBName Varchar (5)

Set @Today = GetDate()
Set @Entity = 'EM1'
Set @DBName = 'PA'

execute SP1 @Entity, @DBName, @Today, '', 'Customer','AR91', 'smry'

instead of

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'

If the first option runs faster then its something to do with tempdb, otherwise its in the sp.

I initially thought that if it isn't a temp table issue then perhaps it was a parameter sniffing one - hence the execution plan request - but this has been discounted by the fact that you are using the same parameters in both your sp calls and you aren't using atypical default values.

George, can you explain why using a table var would be better than a temp table in this case ? If SP1 returns a large result set I would have thought a table var would not be such a good idea - I'm a bit fuzzy on this point.

If its possible the code for SP1 would be good to look at - interesting problem.

harry

 

[gmmastros]

Harry,

According to the original question....

I have SP1 that runs on its own in 4 to 5 seconds and returns 200 or 300 records.

A couple hundred records shouldn't matter, but nothing else seems to make any difference, ya know?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[haroldholt]

Yes, my apologies, you are quite correct, I completely missed that bit - still like to see what happens when SP1 is called from SP2 without any insert...

 

[dfrazell]

Okay as we might expect SP1 runs in 3 seconds by itself and Executing SP1 within SP2 without any Insert or Update it also runs in 3 seconds. When I put the Create Table and Insert back in (no update yet) it runs a long time. I waited 30 minutes before I canceled it. So now it looks like the poor performance has something to do with the temp table and the insert. Is it some kind of resource issue? FYI - I'm running SQL 2000.