February 27, 2008 at 4:30 am
Hi ,
What is the best way (in terms of performance) to Insert Master And its Child details in one go from Client Application (.Net).
e.g. I want to create insertion procedure for Order and Order Item(details) in one transaction in stored procedure.
How should we pass on all data to stored procedure to have better performance.
Thanks
Avnish
Avnish
March 17, 2008 at 5:22 am
You can do this in one pass, however the legwork to get the data into a format for one pass and then decompose it inside the stored procedure is probably going to be more overhead than you will want to deal with. I.e. build order items as an XML or other delimited format to pass in via parameter, then break out order lines from the XML inside the stored proc. Chances are, the overhead doing this is likely going to be less performant than the problem you are concerned about.
What challenges are you trying to overcome by doing this all at once? If you are concerned about .net performance you can use the same connection to create the order and order items, including setting this up as a Transaction if you need every step to complete successful before commiting.
March 17, 2008 at 7:47 am
You write one stored procedure that does everything that you want to do in this transaction, including the transaction itself. Then you run the stored procedure from your client in ADO.net using the [font="Courier New"]SqlCommand[/font] object with the [font="Courier New"]CommandType [/font]set to [font="Courier New"]StoredProcedure[/font], passing in the parameters as parameter items before you execute it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply