How to do efficient database insertion from c# 2.0?

[Mats-Lennart Hansson]

Hi,
I'm creating a program that reads data from a number of textfiles,
manipulates the data and then inserts it into a database. There are >200.000
rows and I use a SqlCommand object for each row . The SqlCommand object
calls a stored procedure that I have created.

There are approx. 10-15 different stored procedures that I use.

My problem is that the program is really slow, so what do I do wrong? Is
there a better way to do this?

Thanks,
Mats-Lennart

The code I use look something like this:

SqlConnection con = Utils.GetSqlConnection(); // Reuse the same
connection for all sql commands

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "procname";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("P1", V1));
cmd.Parameters.Add(new SqlParameter("P2", V2));
cmd.Parameters.Add(new SqlParameter("P3", V3));

cmd.Connection = con;

cmd.ExecuteNonQuery();

[Roman Kuzmin]

It is rather SQL issue; try to post your question there as well. Anyway, how
long does it take? Taking into account >200.000 operations, say, half an hour
may be quite reasonable time (depending on your database and stored
procedure, of course). Consider also using BULK copy or insert, see SQL
documentation; quotation: “Bulk copy is the fastest way to add large numbers
of rows in SQL Server�.

--
Thanks,
Roman

[Marc Gravell]

The first thing to do is to understand what time is being used in the db,
and what in the C#.

I would (literally) comment out all of the ExecuteNonQuery lines, and fake
any return data required (e.g. using a simple counter to simulate IDENTITY
values). It could well be that the time is due to inefficient collection
management or suchlike.

There are approx. 10-15 different stored procedures that I use.

Well, if they are on the same connection, there is no reason that you can't
re-use the existing command objects: just update the parameters before each
call. Could also call .Prepare() after the initial creation, which should
get everything nicely ready...

***However***: I'd be suprised if this made a /massive/ difference - but it
should help; downside is that (depending on the implementation) can make
things less encapsulated.

If the database-server is a long way (ping-wise) from the client, you could
also look at preparing larger batched commands to reduce the round-trip
count. Could also look at the SP / trigger performance; any foobars?

Marc

[Phil]

On Thu, 10 Aug 2006 11:02:06 +0200, "Mats-Lennart Hansson"
<ap********@hotmail.comwrote:

>Hi,
I'm creating a program that reads data from a number of textfiles,
manipulates the data and then inserts it into a database. There are >200.000
rows and I use a SqlCommand object for each row . The SqlCommand object
calls a stored procedure that I have created.

There are approx. 10-15 different stored procedures that I use.

My problem is that the program is really slow, so what do I do wrong? Is
there a better way to do this?

Thanks,
Mats-Lennart

The code I use look something like this:

SqlConnection con = Utils.GetSqlConnection(); // Reuse the same
connection for all sql commands

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "procname";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("P1", V1));
cmd.Parameters.Add(new SqlParameter("P2", V2));
cmd.Parameters.Add(new SqlParameter("P3", V3));

cmd.Connection = con;

cmd.ExecuteNonQuery();

Fundamental problem is that you are doing everything in a row-oriented
fashion, calling the proc once for every row of your data. That's a
lot of to-and-fro, for a start. For large amounts of data you need to
work in terms of sets rather than rows.

Change your program so that you:

1. Load all the data in one hit (look into bulk insert or DTS).

2. Rewrite your proc so that it can process all 200,000 rows in one
go.
This is really more of an SQL question than a C# one.

--
Phil