Sql – How should I optimize multiple calls in the .net code to a trivial stored procedure


I've got a very simple stored procedure :

create procedure spFoo(v varchar(50))
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.

Best Solution

If your problem is multiple rows trying to be passed in then as of SQL Server 2008 you have a new parameter 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 in both C# and VB.Net. Hope this helps.

Related Question