Bulk insert of records into SQL

( ) writes:
> I am writing a program which essentially transfers data from an Access
> database into an SQL server 2008 database. Its simply a loop where
> each iteration reads records out of an Access database using an
> OleDbDataReader, does some manipulations, and inserts the record into
> SQL table.
>
> Running the program, I get excellent transfer rates (circa 1500 recs
> per sec), until I get to one of the tables that has 4.5 million
> records in it. It starts off going really well, but after about 2
> million record it goes ridiculously slow and the transfer rate will
> stall regularly. The machine becomes unusable as memory is
> exhausted. I'm guessing some sort of internal cache is filling up and
> cannot be emptied fast enough. Is there any way of improving this
> situation? By improve, I mean I would like to see a consistent
> transfer rate over the entire execution of the program, and not use so
> much memory as to make the machine impossible to use

There are lots of information missing. It could help if you posted the
code. If that is not realistic, you could at least answer these questions:

1) How many rows at a time do you insert?
2) Do you use any transactions, and in such case how long are they?
3) Are there any triggers on the target table?
4) Have you made any attempts to narrow down where the bottleneck is? For
instance, by using Profiler you could see whether the INSERTs are
taking longer and longer time.
5) Is SQL Server running on the same server as your program=

Also, it's a good idea to grow the database to the right size, both
data files and log, before you commence the operation.

--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Bulk insert of records into SQL 

On Mar 24, 4:28 am, Erland Sommarskog wrote:
> Also, it's a good idea to grow the database to the right size, both
> data files and log, before you commence the operation.

Is there any easy way to compute what the right size would be, before
doing the operation?
 >> Stay informed about: Bulk insert of records into SQL 

Ross Presser ( ) writes:
> On Mar 24, 4:28 am, Erland Sommarskog wrote:
>> Also, it's a good idea to grow the database to the right size, both
>> data files and log, before you commence the operation.
>
> Is there any easy way to compute what the right size would be, before
> doing the operation?

I guess that in this case, the size of the Access database is a good start.
While the overhead of Access and SQL Server may be different, it's
still a hint.


--
Erland Sommarskog, SQL Server MVP, esquel.RemoveThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Bulk insert of records into SQL 

On Mar 24, 5:12 pm, Rahul wrote:

> Change the recovery model as Bulk-logged and then try it.

Hi Rahul, I'm fairly new with SQL server so I'm not sure what you
mean. Is this something I configure on the database itself, and if so
how?
 >> Stay informed about: Bulk insert of records into SQL 

On Mar 24, 6:28 pm, Erland Sommarskog wrote:

>
> There are lots of information missing. It could help if you posted the
> code. If that is not realistic, you could at least answer these questions:
>
> 1) How many rows at a time do you insert?

one at a time

> 2) Do you use any transactions, and in such case how long are they?

no

> 3) Are there any triggers on the target table?

no, but there are some foreign key constraints

> 4) Have you made any attempts to narrow down where the bottleneck is? For
>    instance, by using Profiler you could see whether the INSERTs are
>    taking longer and longer time.

That's the problem, the INSERTs are taking longer, but I think it is
due to running out of memory. The system starts running poorly, so
I'm not sure if INSERTS are really slowing down, or if it is just the
product of a memory leak making everything slow down. I should note
here that after terminating the program, the memory wasn't realeased.
I had to reboot the computer. But this is a .NET program, which I
thought was all managed, and should be released when the program is
terminated? Maybe it is the SQL server database engine (non-managed)
that is leaking memory?

> 5) Is SQL Server running on the same server as your program=
>

yes

> Also, it's a good idea to grow the database to the right size, both
> data files and log, before you commence the operation.
>

I will look into doing this, thanks for your help

Regards, B.
 >> Stay informed about: Bulk insert of records into SQL 

( ) writes:
> On Mar 24, 6:28 pm, Erland Sommarskog wrote:
>> 1) How many rows at a time do you insert?
>
> one at a time

And how do these INSERT statements look like? Do you use parameterised
statements, or do you build a single SQL string with the INSERT statement?

My guess is that you do the latter, which is not good for performance.
You will get better performance if you use the same command string
over and over again, and provide the values to insert as parameters.

This could also explain the "memory leak" you are seeing. You will
fill the plan cache in SQL Server with lots of meaningless plans.

> That's the problem, the INSERTs are taking longer, but I think it is
> due to running out of memory. The system starts running poorly, so
> I'm not sure if INSERTS are really slowing down, or if it is just the
> product of a memory leak making everything slow down. I should note
> here that after terminating the program, the memory wasn't realeased.

Not released by whom? If your program is no longer running, it should
no longer hold any memory.

There is also a possibility that you are not closing your command and
connection objects properly, and you are introducing a leak that way.
Since I don't see your code, this is something I can only provide
speculations about.


--
Erland Sommarskog, SQL Server MVP, esquel.DeleteThis@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Bulk insert of records into SQL 

( ) writes:
> On Mar 24, 5:12 pm, Rahul wrote:
>
>> Change the recovery model as Bulk-logged and then try it.
>
> Hi Rahul, I'm fairly new with SQL server so I'm not sure what you
> mean. Is this something I configure on the database itself, and if so
> how?


This is indeed a database setting, but it has no relevance to your
particular issue. Rahul was mislead by your subject line; "BULK INSERT"
is a command in SQL Server that permits you to load many rows from a
disk file in one swoop. This operation can under some circumstances benefit
from bulk-logged recovery.


--
Erland Sommarskog, SQL Server MVP, esquel.TakeThisOut@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Bulk insert of records into SQL 

On Mar 25, 8:24 pm, Erland Sommarskog wrote:
>  ( ) writes:
> > On Mar 24, 6:28 pm, Erland Sommarskog wrote:
> >> 1) How many rows at a time do you insert?
>
> > one at a time
>
> And how do these INSERT statements look like? Do you use parameterised
> statements, or do you build a single SQL string with the INSERT statement?
>
> My guess is that you do the latter, which is not good for performance.
> You will get better performance if you use the same command string
> over and over again, and provide the values to insert as parameters.
>
> This could also explain the "memory leak" you are seeing. You will
> fill the plan cache in SQL Server with lots of meaningless plans.

Yes, I am using parameterized statements, so this is not the issue.
However, I think I have found the source of the problem. It seems the
primary key constraint on the table is causing it to run poorly. It
is a single-column integer key, nothing fancy. I noticed that while
the constraint is on, the program actually sleeps about 50% of its
execution time when it gets past 1 million records inserted.

When I remove this, the program finishes in a timely manner with no
stalls. Perhaps I should create the table with primary key constraint
off, then turn it on after updating? Is that possible? I know that
sounds a bit stupid, but if it throws an error when turning it on, it
indicates a corrupt database has been sent to us.
 >> Stay informed about: Bulk insert of records into SQL 

( ) writes:
> Yes, I am using parameterized statements, so this is not the issue.
> However, I think I have found the source of the problem. It seems the
> primary key constraint on the table is causing it to run poorly. It
> is a single-column integer key, nothing fancy. I noticed that while
> the constraint is on, the program actually sleeps about 50% of its
> execution time when it gets past 1 million records inserted.

Is the PK clustered? Do you insert the rows in the same order as the PK?
If you do not, you may get excess page-splitting.

Inserting rows without any indexes is certainly faster, since there is
less to update, but of course you take some toll when adding the index
afterwards.

Then there is a whole number of things you could do to make this operation
perform even faster. For instance, you could build an XML document and
pass that, and then insert all rows at once, or at least even 1000 at a
time. Such a simple thing like saying BEGIN TRANSACTION and then commit
and start a new transaction after each 1000 rows should pay off.

--
Erland Sommarskog, SQL Server MVP, esquel DeleteThis @sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 >> Stay informed about: Bulk insert of records into SQL 

On Mar 26, 7:02 pm, Erland Sommarskog wrote:
>  ( ) writes:
> > Yes, I am using parameterized statements, so this is not the issue.
> > However, I think I have found the source of the problem.  It seems the
> > primary key constraint on the table is causing it to run poorly.  It
> > is a single-column integer key, nothing fancy.  I noticed that while
> > the constraint is on, the program actually sleeps about 50% of its
> > execution time when it gets past 1 million records inserted.
>
> Is the PK clustered?

no

> Do you insert the rows in the same order as the PK?

no, the Access database is unordered. I tried to use an ORDER BY <pk>
clause to the select statement to see if it would make a difference,
but it caused the program to hang. I'm guessing because it is trying
to order 4.5 million records, it will take a while, but it has the
side effect of using up all the system memory

> If you do not, you may get excess page-splitting.
>
> Inserting rows without any indexes is certainly faster, since there is
> less to update, but of course you take some toll when adding the index
> afterwards.
>
> Then there is a whole number of things you could do to make this operation
> perform even faster. For instance, you could build an XML document and
> pass that, and then insert all rows at once, or at least even 1000 at a
> time. Such a simple thing like saying BEGIN TRANSACTION and then commit
> and start a new transaction after each 1000 rows should pay off.

I would like to try the "begin trans, insert 1000 recs, commit"
approach, how do I do this in code? At the moment I am using the
following design

mySqlCmd.CommandText = "INSERT INTO sqlTable VALUES (@Field1, @Field2,
etc...)";
while(myDatareader.Read())
{
mySqlCmd.Parameters.Clear()
mySqlCmd.Parameters.AddWithValue("Field1", datareader["val1"]);
etc...

mySqlCmd.ExecuteNonQuery();
}



Thanks for all your help,

Regards, B.
 >> Stay informed about: Bulk insert of records into SQL 

On Mar 27, 10:54 am, wrote:
> On Mar 26, 7:02 pm, Erland Sommarskog wrote:
>
> >  ( ) writes:
> > > Yes, I am using parameterized statements, so this is not the issue.
> > > However, I think I have found the source of the problem.  It seems the
> > > primary key constraint on the table is causing it to run poorly.  It
> > > is a single-column integer key, nothing fancy.  I noticed that while
> > > the constraint is on, the program actually sleeps about 50% of its
> > > execution time when it gets past 1 million records inserted.
>
> > Is the PK clustered?

woops, it *is* actually clustered, and this seems to be the cause of
the problem! I have set it to non-clustered and it runs like a
dream! Thanks for your help

Regards, B.
 >> Stay informed about: Bulk insert of records into SQL