Inserts gradually slow down

[andrewbb]

I'm inserting 2 million+ records from a C# routine which starts out
very fast and gradually slows down. Each insert is through a stored
procedure with no transactions involved.

If I stop and restart the process it immediately speeds up and then
gradually slows down again. But closing and re-opening the connection
every 10000 records didn't help.

Stopping and restarting the process is obviously clearing up some
resource on SQL Server (or DTC??), but what? How can I clean up that
resource manually?

[andrewbb]

BTW, the tables have no indexes or constraints. Just simple tables
that are dropped and recreated each time the process is run.

[Erland Sommarskog]

(an******@gmail.com) writes:

I'm inserting 2 million+ records from a C# routine which starts out
very fast and gradually slows down. Each insert is through a stored
procedure with no transactions involved.

If I stop and restart the process it immediately speeds up and then
gradually slows down again. But closing and re-opening the connection
every 10000 records didn't help.

Stopping and restarting the process is obviously clearing up some
resource on SQL Server (or DTC??), but what? How can I clean up that
resource manually?

If I understand thius correctly, every time you restart the process
you also drop the tables and recreate them. So that is the "resource"
you clear up.

One reason could be autogrow. It might be an idea to extend the database
to reasonable size before you start loading. If you are running with
full recovery, this also includes the transaction log.

You could also consider adding a clustered index that is aligned with
the data that you insert. That is, you insert the data in foo-order,
you should have a clustered index on foo.

But since 2 million rows is quite a lot, you should probably examine
more efficient methods to load them. The fastest method is bulk-load,
but ADO .Net 1.1 does not have a bulk-load interface. But you could
run command-line BCP.

You could also build XML strings with your data and unpack these
with OPENXML in the stored procedure.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Tzvika Barenholz]

In my experience 9 times out of 10 the reason for the problem is a file
that keeps growing by 10%.
either pre-allocate a big file as Erland suggests or change the
filegrowth from 10% (the default, which by the way is a bad default) to
something in the region of 20 mb or so.

[andrewbb]

You're right, I removed the drop and re-create and it's definitely
slower when data already exists. So how would you suggest loading this
data?

The text file contains 11 different types of Rows. Each type of row
goes to a separate table, so I need to read each line, determine its
type, parse it and insert into the appropriate table.

Can BCP handle this? DTS? Or your XML idea?

Thanks

Erland Sommarskog wrote:

(an******@gmail.com) writes:

I'm inserting 2 million+ records from a C# routine which starts out
very fast and gradually slows down. Each insert is through a stored procedure with no transactions involved.

If I stop and restart the process it immediately speeds up and then
gradually slows down again. But closing and re-opening the connection every 10000 records didn't help.

Stopping and restarting the process is obviously clearing up some
resource on SQL Server (or DTC??), but what? How can I clean up that resource manually?
If I understand thius correctly, every time you restart the process
you also drop the tables and recreate them. So that is the "resource"
you clear up.

One reason could be autogrow. It might be an idea to extend the

database to reasonable size before you start loading. If you are running with
full recovery, this also includes the transaction log.

You could also consider adding a clustered index that is aligned with
the data that you insert. That is, you insert the data in foo-order,
you should have a clustered index on foo.

But since 2 million rows is quite a lot, you should probably examine
more efficient methods to load them. The fastest method is bulk-load,
but ADO .Net 1.1 does not have a bulk-load interface. But you could
run command-line BCP.

You could also build XML strings with your data and unpack these
with OPENXML in the stored procedure.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[andrewbb]

I tried adjusting the settings... both different %'s and specific MBs,
but the slow down is the same in all cases.

It's dramatically slower to insert records into a 100,000 row table
than an empty one I guess.

[Joe Weinstein]

an******@gmail.com wrote:

I tried adjusting the settings... both different %'s and specific MBs,
but the slow down is the same in all cases.

It's dramatically slower to insert records into a 100,000 row table
than an empty one I guess.

So these are existing tables? Do these tables have indexes already? If you
had a clustered index, for instance, and were inserting data in a random
order, there would be a lot of inefficiency and index maintenance. The
fastest way to get that much data in, would be to sort it by table,
and in the order you'd want the index, and BCP it in, to tables with no
indices, and then create your indexes.

[Erland Sommarskog]

(an******@gmail.com) writes:

You're right, I removed the drop and re-create and it's definitely
slower when data already exists. So how would you suggest loading this
data?
I can't really give good suggestions about data that I don't anything
about.
The text file contains 11 different types of Rows. Each type of row
goes to a separate table, so I need to read each line, determine its
type, parse it and insert into the appropriate table.

Can BCP handle this? DTS? Or your XML idea?

If the data has a conformant appearance, you could load the lot in a staging
table and then distribute the data from there.

You could also just write new files for each table and then bulk-load
these tables.

It's possible that a Data Pump task in DTS could do all this out
of the box, but I don't know DTS.

The XML idea would require you parse the file, and build an XML document
of it. You wouldn't have to build 11 XML documents, though. (Although
that might be easier than building one big one.)

It's also possible to bulk-load from variables, but not in C# with
ADO .Net 1.1.

So how big did you make the database before you started loading? With
two million records, you should have at least 100 MB for both data and
log.

By the way, how do call the stored procedure? You are using
CommandType.StoredProcedure, aren't you?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[andrewbb]

Thanks for the help. I found the fastest way to do it in a single SP:

- BULK INSERT all data into a 2 column staging table using a BCP Format
file. (One column is the RowType, the rest is the data to be parsed)
- create an index on RowType
- call 11 different SELECT INTO statements based on RowType

2,000,000 rows loaded in 1.5 minutes.

Thanks a lot, BCP works very well.

[Erland Sommarskog]

(an******@gmail.com) writes:

Thanks for the help. I found the fastest way to do it in a single SP:

- BULK INSERT all data into a 2 column staging table using a BCP Format
file. (One column is the RowType, the rest is the data to be parsed)
- create an index on RowType
- call 11 different SELECT INTO statements based on RowType

2,000,000 rows loaded in 1.5 minutes.

Thanks a lot, BCP works very well.

Hmmm! It's always great to hear when things work out well!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp