Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to run the same stored procedure for a lot of records (hundreds or thousands, depending on user selection). The procedure is fairly basic: insert a record in two separate tables with some calculated values, and optionally update two columns on a 3rd table.

My application has a List<T> of records, and I'm trying to decide if I should loop through the list and run the procedure on each record, or combine the id values into a single string and make a single database call. If I make a single database call, then SQL would be responsible for converting the comma-delimited string to a temp table and running the scripts on all records at once.

I'm leaning towards passing a comma-delimited string of Ids to the procedure, but I am not sure if this is an ideal way of doing this kind of mass-update, or if I am missing some key factor that would affect this decision.

What should I consider when deciding if I should pass a comma-delimited string of ids to a stored procedure instead of calling it individually per record? Or is there a more efficient way to do this kind of update from an application?

3 Answers
3

Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

I thought table parameters for stored procedures wasn't supported in SQL Server 2005? From the article you linked, "Table Value Parameters is a new feature for developers in SQL Server 2008"
–
RachelAug 22 '12 at 14:02

I had a similar problem in the past. I needed to send up to 100K numbers to SQL Server 2005 as fast as possible. After lots of benchmarks I think the fastest approach in my environment was to pack numbers in binary format. That needed less network packets and parsed very fast using very little CPU. Erland Sommarskog included the C# code I used to pack numbers into a CLOB in his article Arrays and Lists in SQL Server 2005 , in the section entitled "Passing Numbers as Binary".

I ran 2 tests for a comma-delimited list as well as a table variable type. I took the first answer above and thought that table variable types were ok and have of course read by now that they are not valid for 2005. However, I think it can still be valuable information for this post. I took the original post requirements literally and assumed updating 100,000 records. The first test the data was submitted from vb.net using a comma-delimited list. For each test, I started a timer in vb.net right before calling ExecuteNonQuery and stopped it when it returned to code (ie. I did not test the time it took to build the list, although this was trivial, less than a second, in both samples).

The comma-delimited list consistently took between 96 and 98 seconds to complete the 100,000 record update. The second test used the table variable type (SQL 2008 and greater). When submitting 100,000 records and updating a table, it consistently took between .43 and .44 seconds. This is a considerable improvement over the list.

However, seeing as you have to use the comma-delimited list, I'll post the code I used to split the comma-delimited list to see if any performance improvements can be made. I hope it helps!

After re-reading your post, I apologize for doing this in ADODB rather than EF, it was just faster to throw the test together that way. Also, I would also recommend the comma-delimited list rather than looping over the records individually as this almost always takes longer (but I'd have to test it to be sure :)
–
DeluxAug 22 '12 at 21:54