How should I optimize multiple calls in my .net code to a trivial stored procedure ?

Question

I've got a very simple stored procedure :

create procedure spFoo(v varchar(50))
as 
insert into tbFoo select v

I've got 50 values to insert into tbFoo, which means in my c# code I call spFoo 50 times. This is a pretty inefficient way of doing this, especially if there's some lag between my program and the database.

What do you usually do in this situation ?

I'm using SQL Server 2008 but it's probably unrelated.

Answer 1

It depends if this is a performance bottleneck or not.
If it isn't, I wouldn't worry about it.

If it is, you could pass all 50 items to one stored procedure, as one string for example.
The stored procedure could then split out the values, and then call your create procedure as required.
So you would call SQL once, and it would do 50 inserts.

Answer 2

Actually, what is here inefficient? If you don't want use any sort of BULK INSERT, you'll have to call it 50 times. Nevertheless, if you used a prepared statement, and just replace parameter value on each run, it should go really fast.

Also, 50 inserts/sp calls is actually nothing. Go straight away with that.

Answer 3

Pass the values to be inserted as an XML parameter. You can then use a XPath query to bulk insert the data.

Answer 4

Actually, a cool feature of SQL Server 2008 is table valued parameters.

setup:

create type fooTableType as table (v varchar(50));
go 

create proc spFoo(@t fooTableType readonly) as
  insert into tbFoo(v) select v from @t
go

then your C# code gens:

declare @t as fooTableType
insert @t(v) values('beyond'),('all'),('recognition')
exec spFoo(@t)

here's some rough C# code to do that:

using (SqlCommand cmd=new SqlCommand()) {
  StringBuilder sql = new StringBuilder();
  for(int i=0; i<50; i++) {
    sql.AppendFormat(",(@v{i})",i);
    cmd.Parameters.AddWithValue("@v"+i.ToString(),values[i]);
  }
  cmd.CommandText = "declare @t as fooTableType; insert @t(v) values" +
                    sql.ToString().Substr(1) + "; exec spFoo(@t);"
  cmd.CommandType = CommandType.Text;
  cmd.Connection = myConnection;
  cmd.ExecuteNonQuery();
}

I would generally agree that such an optimization is not absolutely necessary. Perhaps it is OCD but I wouldn't be able to leave it as a loop of cmd.ExecuteNonQuery().

Check out CertifiedCrazy's answer for a cleaner version of the C# code.

Also note that this technique is limited to 2100 total parameters - this a limitation of the underlying TDS protocol.

Answer 5

Batch the calls by creating parameters @v0 through @v49, so it's only 1 network round trip:

using (var cmd = new SqlCommand()) {
   for (int i = 0; i < 50; i++) {
      string paramName = "@v" + i.ToString();
      cmd.CommandText += string.Format("EXEC spFoo " + paramName + ";");
      cmd.Parameters.AddWithValue(paramName, values[i]);
   }

   cmd.ExecuteNonQuery();
}

Answer 6

If your problem is multiple rows trying to be passed in then with SQL Server 2008 you have a new paramter type Table-Valued. Which allows you to pass a .Net Datatable directly into a stored procedure through a .NET SQLParamter of Type Structured.

tvpParam.SqlDbType = SqlDbType.Structured

However if the problem is that there are 50 columns in 1 row that you are trying to populate then you would be better passing them all in as separate parameters and change the Procedure rather than trying to get slick with either code or T-SQL.

There's a good article that demonstrates how to use table valued parameters in SQL Server and through .NET, the code is in VB.Net but it is very clear what needs to be done.

Hope this helps.

Answer 7

I construct the list as an xml string and pass it to the stored proc. In SQL 2005, it has good xml functionalities to parse the xml and do a bulk insert.

check this post: Passing lists to SQL Server 2005 with XML Parameters