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

.netoptimizationsqlstored-procedures

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.

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 Solutions

Sql – How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server

The answer you're looking for is found in a similar SO question by Josh Burke:

-- Assume this table matches the output of your procedure
DECLARE @tmpNewValue TABLE ([Id] int, [Name] varchar(50))

INSERT INTO @tmpNewValue 
  EXEC [ProcedureB]

-- SELECT [Id], [Name] FROM @tmpNewValue

Sqlite – Improve INSERT-per-second performance of SQLite

Several tips:

Put inserts/updates in a transaction.
For older versions of SQLite - Consider a less paranoid journal mode (pragma journal_mode). There is NORMAL, and then there is OFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. If your application crashes the data should be fine. Note that in newer versions, the OFF/MEMORY settings are not safe for application level crashes.
Playing with page sizes makes a difference as well (PRAGMA page_size). Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Note that more memory will be used for your database.
If you have indices, consider calling CREATE INDEX after doing all your inserts. This is significantly faster than creating the index and then doing your inserts.
You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out. This has been improved somewhat with the addition of a WAL in newer SQLite versions.
Take advantage of saving space...smaller databases go faster. For instance, if you have key value pairs, try making the key an INTEGER PRIMARY KEY if possible, which will replace the implied unique row number column in the table.
If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.
Don't use !feof(file)!

I've also asked similar questions here and here.

Best Solution

Related Solutions

Sql – How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server

Sqlite – Improve INSERT-per-second performance of SQLite

Related Question