Question about sproc performance?

whats the difference in performance between these two scenarioes...

Suppose if i build a sproc to insert or update into one table and then take its primary key and insert and update subsequent tables may one or 2 or more take more time in exceuting...

or

writing indivdual sproc for insert and update for particular table in different sproc and then calling all the sprocs in a master proc using exec sproc command...

Which scenario would better if they are around 230 or 300 or more than records.

Hope some would shed some light into this..

Any help is appreciated...

Regards

Karen

The best advise I can give is to build both of them. You can then look at the individual execution plans of them both and determine which is the better approach for your database. It should also help to highlight if any indexes need to be added to your tables.

|||

I dont think it will be too much of a noticeable difference.. unless you have some very complex logic involving several IF loops which radically changes the WHERE conditions in the query.

|||

Thanks Dinakar...

I just plain inserts and updates...to different tables... and in the above scenario which is best approach to take?

Regards

Karen

|||

If its plain INSERTs/UPDATEs its more of a convenience and easier maintenance that you should decide based on rather than performance. I would create one proc for each table that takes care of INSERT and UPDATE (Depending on whether record exists or not) so you can re-use the procs. If you put multiple tables related INSERTs into one proc and later if you had to do an INSERTt/UPDATE into one of those 2 tables, you'd be re-writing the same logic into another proc again. If you created a separate proc for the table now, you could just call the proc later.