May 30, 2005 at 3:14 pm
Since you just stated that you were a beginner, I'll say this part again. UNLESS for much improved performance, this is the type of operation that is better handled by a single smaller stored proc than is called many times by the application (makes the debugging easier because there's much less code and variables involved).
This type of optimisation is used so that you don't have an application call the same stored proc 1000s of times in a row (each time you call the proc it takes 3 round trips on the network to accomplish the task, so you can imagine the time saved by such an optimisation). The downside of such a proc is that it's much harder to debug since there's a lot of code in it instead of a single simple statement.
The same proc could be rewritten like so :
create proc dbo.DeleteOldData @id as int
as
delete from..
go
create proc dbo.InsertNewData --pass the 15+ params here
as
insert into dbo.YourTable (field1... field15) values (param1... param15)
go
Then on the application side you can simply call the stored procs in the correct order. Could be done in a batch sql statement or multiple calls of a command object. The batch sql has the advantage that it can be contained in a single transaction.. assuring that the whole statement executed and not only a single part.
I think you should really consider this option since it's much easier to understand by any programmer than that monster of a stored proc you got running now. You should also test both to see if there's a big speed difference between the 2 options and then decide which one to use.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply