May 1, 2008 at 11:28 pm
I need to increase the performance of some sql code which imports data from a flat file into an sql 2005 db.
The code already exists but who ever wrote it broke it down into multiple sp's instead of 1 larger one.
Is there any performance impact in doing it this way?
May 2, 2008 at 6:58 am
Short answer: It depends.
Longer answer: It depends. Let's take an example. You're doing a load that's going to direct data to one of two tables, so there's an if statement and then one of two insert statements that have to be run. If you put this all in one procedure and then run the code, the first time through, it generates a nice execution plan for inserting into the first table, but no plan for the second table. The next time it runs, it wants to load into the second table, but there's no plan for it, so it recompiles, slowing down your processes (simple queries run once a day, this is no big deal, complex queries or queries run frequently, this can be HUGE). Instead of this, you leave the if statement in the main procedure and then create two smaller procedures, one for each insert statement. Then, same scenario, you get two reusable exection plans and no recompiles.
So, it really depends on why they broke it into multiple procedures. If they did it for the right reasons you will get a performance impact, a positive one. If they did it for the wrong reasons, it could cause negative performance impact, but it really depends on what they're doing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 2, 2008 at 7:31 am
I agree with Grant. There's no way to know what works better. It really depends on how things are written and what the breakdown is of tasks in each SP.
I tend to separate things out if they might get reused.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply