I have select, insert, update and delete query.
If I have to write all queries in the same stored procedure that is good for performance or should I write all queries in separate stored procedures?
+3
A:
For ease of maintenance, I would go for separate procedures.
Speed is not going to be an issue if the same code is in one proc or multiple procs - as long as the code is the same, it will behave in the same way in one proc or many.
The best method to get good speed is to write an efficient query. Run it and review the execution plan; then tune the query where required.
You will find a lot of good information on query tuning and index tuning on this site (just search for it).
Raj More
2010-03-19 14:59:21
+3
A:
If it is something like, and all the parameters are manageable:
BEGIN TRANSACTION
INSERT
....
UPDATE
...
DELETE
COMMIT
yes, all in one will eliminate the little overhead of multiple calls and keep the logic together as a unit.
however if it is:
@ParamType char(1) --given parameter "I"nsert, "U"pdate, "D"elete
IF @ParamType='I'
INSERT
ELSE IF @ParamType='U'
UPDATE
ELSE
DELETE
split them up into separate procedures, they make no sense to be combined together.
KM
2010-03-19 15:02:21
@AjmeraInfo, my answer tries to address WHEN to combine them and when not to. Sometimes it is good to combine them, sometimes not. I say that if they are related to a transaction, combine them, if they are not related do not combine them. Your question doesn't give any hints as to if they are related together or not, and is very vague. If you edit your question and provide more details, Id be glad to revise my answer based on any new information you provide.
KM
2010-03-19 15:12:17