Reputation: 167
I have a registration page and at first I want to insert some mandatory fields into the DB, after that in the second page I want to update some more details according to the userid
.
So I want to do this with stored procedure which is the best to way to do it,creating an insert and update stored procedure separately or creating a single procedure which have insert and update?
Which is best in terms of performance?
Upvotes: 0
Views: 243
Reputation: 29000
You can create a procedure for insert and another for update
Upvotes: 0
Reputation: 113382
Any performance difference will be pretty negligible. There's bound to be a difference of a couple of microseconds in there somewhere, but it really will just be that small, and a bunch of other little decisions are going to wipe out the difference.
The only real difference then, is how clear the code would be. It seems to me from your description that "create a new user" and "update a user" are being treated as separate concerns by the rest of your code, and so should be separate.
If there was some reason why your code would find it easier to deal with a "create or update as appropriate" operation, then that would be a different matter, but there's nothing to suggest it's the case here.
Upvotes: 0
Reputation: 2568
writing a separate insert and update procedure would be more efficient as the procedure does not have to consider whether to insert or update though the efficiency saving is going to be minimal so I wouldn't make a decision based on this and it should be more based around readability and understandability.
Upvotes: 0
Reputation: 5889
You could create two separate stored procedures, one for INSERT
and one for UPDATE
. That would be simple enough for anyone to understand.
However if you are using MSSQL 2008 or newer then you could make a single stored procedure and utilise MERGE
:
Upvotes: 0
Reputation: 6020
There's no performance gain from having these in the same query, as two separate stored procedures are still pre-compiled by the database server. This is more of a maintenance issue than performance - I'd suggest having separate stored procedures for insert and update which is far more readable and obvious than mashing them together in a single script.
Upvotes: 1