January 3, 2011 at 9:52 am
Are there issues with having a insert, update, and delete of one possible table in one proc? Should they be separated? If so why? I have a case where I need all three because the code does not recognize this table and it is used to optimize data design
January 3, 2011 at 10:08 am
You can do as many DML operations per stored procedure as you need. They can target one or multiple tables, in one or more databases, on one or more servers. You can control transactionality on this so that all-or-none is set up, or some operations can commit even if others are rolled back, and so on.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2011 at 10:15 am
Thanks - In a SQL weekend class they said multiple DML statements ruins the procedure cache and slows it down. That separating them was needed for SQL to know how to handle them correctly. ie
option a
Update table1 set t1 = 't'
Delete table1 where t1 = 't'
vs
option b
exec proc_upd_table1 @t1 = 't'
exec proc_del_table1 @t1 = 't'
without testing do you know if that is true or can I use option a for code clarity?
January 3, 2011 at 10:36 am
Where that has an impact on the execution plan cache is when there's procedural flow in it.
For example:
if @Parameter1 = 1
update dbo.MyTable
set AColumn = SomeValue
where AnotherColumn = @Parameter2
else
insert into dbo.MyTable
values (@Parameter2, @Parameter3);
When you have that kind of procedural flow, using IF statements, then the cache can be messed up. In that case, you'll want to separate out each piece of the IF statement and create a proc for each one, and then call those in the IF block.
On the other hand, if all the statements will be executed each time, then it's not a problem.
There are more details to be had if you want them. I believe Gail Shaw has more data on it in her blog, http://sqlinthewild.co.za/.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 3, 2011 at 10:46 am
JKSQL (1/3/2011)
Thanks - In a SQL weekend class they said multiple DML statements ruins the procedure cache and slows it down. That separating them was needed for SQL to know how to handle them correctly
Nope. Whoever told you that was incorrect.
The blog post Gus is referring to is probably one of these two:
http://sqlinthewild.co.za/index.php/2010/12/14/do-if-statements-cause-recompiles/
http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply