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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply