Posted by xAvailx on 10/17/06 17:52

>> which is most efficient. <<

I would start by first focusing on correctness before worring about
efficiency. Does the system you are working on have such high traffic
requirements that a few insert statements would make such a big
difference? I don't know your requirements, but I find that too many
developers worry about these types of issues too much and often come up
with unnecessary hacks for no good reason. Having said that...

>> Surely this is a common problem. Are there any more elegant/efficient methods that anyone can suggest? Can one pass an array to a proc? Is this a place for a user defined data type? <<

You may want to look into passing the data as XML. See this link for an
example.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro01/html/sql01c5.asp

HTH




JRoughgarden wrote:
> After a customer decides to buy a shopping list, there is generally a
> need to store/insert one master record and a variable number of child
> detail records, preferably all wrapped in a transaction. There are lots
> of ways to do this and I am wondering if anyone knows which is most
> efficient.
>
> One approach is to use ADO.NET's transaction capabilities, define
> single-record insert procs for the master and detail tables, and call
> the detail insert in a loop from the web page. This has N+1 trips to
> the server, which is not too attractive.
>
> Another approach is to concatenate all the data into a big
> string/varchar variable and pass it to a decoder proc that would then
> call the single record insert procs via a loop inside the decoder proc.
> This second approach would use T-SQL's transaction capability and have
> only one trip to the server, but it is more effort to code on the web
> page and in the decoder proc.
>
> Surely this is a common problem. Are there any more elegant/efficient
> methods that anyone can suggest? Can one pass an array to a proc? Is
> this a place for a user defined data type?
>
> Any advice is much appreciated.

• Next in forum: Re: How can I create a cool data model table map?
• Prev in forum: Re: Is there a way to view Stored Procedure, Trigger andFunction Usage in SQL Server?
• Thread view: Re: What is the best way to store a shopping list or order?

[Reply to this message]