insert slows

  • Thread starter Thread starter Rogas69
  • Start date Start date
R

Rogas69

Hi,
I am struggling against problem that is strange to me.
I have to insert large amounts of data into two tables. Data are generated
on the fly so there is no possibility of using bulk operations. I am using
stored procedure that performs several checks and inserts or updates data in
appropriate way.
Stored procedure is enclosed in transaction as it returns some data needed
for the second stage of processing. This is not the problem of commiting
every statement then.
I am using SqlCommand with CommandType.StoredProcedure type, so it is not a
problem of parsing SQL every time. I call ExecuteScalar method as procedure
returns string.
When I created empty database, 1000 records took about 7 seconds to be
stored. I inserted 1 000 000 records and now time of 1000 records is up to
50 seconds!, 25 on average .
I noticed that there are strange bottlenecks despite that there is only one
client for now that pushes data to database - sometimes profiler shows that
execution time of the procedure can take even more than 2 seconds, and
sometimes it takes only 15ms.
Database is very simple and has clustered indexes created on primary keys.
I created simple test in query analyzer to check if this is database issue.
5000 calls to stored procedure take only 7 seconds - in my opinion this
shows that the problem is somewhere in ADO.NET.
What do you think? I am stuck with this problem for three days.. I will
appreciate your opinions.

Peter
 
Peter,

If you would, please post your code that you use to call the SP. (I'm
willing to bet it's how either the connection object or ExecuteScalar method
are used.)

Eric
 
Hi,

First of all you could do a bulk insert. What you need to do is to pass XML
representation of the dataset as a text parameter into your SP and use
OPENXML to work with the passed XML. Next examples show you how to work with
XML in this case What could be in your case is that if your database grows
automatically with the small increases, then SQL Server could spend most of
the time to expand database. But I would expect same issue from the QA
environment as well. Try to check how your setting for the database specify
growing.

http://support.microsoft.com/default.aspx?scid=kb;en-us;316244

http://support.microsoft.com/default.aspx?scid=kb;en-us;555266
 
Hi,
I found one probable reason of the problem - average disk queue length in
performance monitor hits the roof while operations are slowing. This is the
bottleneck I was talking about. SQL server has to wait till HD is ready to
serve its requests.
I checked sizing of the database and changed it to grow by 20 MB each time -
yet it does not have impact on performance in my case. Database is not very
big in fact.

I thought about the problem during weekend :) and I wanted to try to divide
updates and inserts that are made in the stored procedure and make inserts
every 100 (configurable) records or after a specified timeout in one
transaction. After a consideration it seems that your idea with XML might be
even better Val.
I believe that it will help. I tried to run stored procedure with update
part only (without inserts to big tables) and it performs well though after
4000 hits I encountered bottleneck as well. Maybe it has something to do
with my machine configuration so I will perform more tests on our
development server.

Regards

Peter
 
Hi Eric,

the code is pretty simple - I use in this case data components generated by
VS 2005 but I examined the code and it seems that I would type it similar
way :)
I will try the bulk insert as Val advices.

connection is created as usual
this.m_connection = new System.Data.SqlClient.SqlConnection();

and its connection string as usual of course. then the connection is
assigned to command



the call of stored procedure looks as in the following

System.Data.SqlClient.SqlCommand command =
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[2]));
command.Parameters[1].Value = ((string)(@id));

command.Parameters[2].Value = ((int)(@FK_OtherID));

command.Parameters[3].Value = ((string)(@strMetadata));

command.Parameters[4].Value = ((int)(@length));

string returnValue;

try {

command.Connection.Open();

returnValue = ((string)(command.ExecuteScalar()));

}

finally {

command.Connection.Close();

}


where command is

this.m_commandCollection[2] = new System.Data.SqlClient.SqlCommand();

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).Connection
= this.Connection;

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).CommandText
= "dbo.sp_InsertDataStream";

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).CommandType
= System.Data.CommandType.StoredProcedure;

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).Parameters.Add(new
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
10, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).Parameters.Add(new
System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.NChar, 65,
System.Data.ParameterDirection.Input, 0, 0, null,
System.Data.DataRowVersion.Current, false, null, "", "", ""));

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).Parameters.Add(new
System.Data.SqlClient.SqlParameter("@FK_OtherID", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.Input, 10, 0, null,
System.Data.DataRowVersion.Current, false, null, "", "", ""));

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).Parameters.Add(new
System.Data.SqlClient.SqlParameter("@strMetadata",
System.Data.SqlDbType.NVarChar, 1000, System.Data.ParameterDirection.Input,
0, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));

((System.Data.SqlClient.SqlCommand)(this.m_commandCollection[2])).Parameters.Add(new
System.Data.SqlClient.SqlParameter("@length", System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.Input, 10, 0, null,
System.Data.DataRowVersion.Current, false, null, "", "", ""));

Regards

Peter
 
Now it works as it should thanks Val [up]
I cache records in dataset and flush them as XML to database every second
from another thread.

What I noticed when generating command for stored procedure that accepts a
NText parameter
- VS 2005 does not reckognize it properly or something - it generates
NVarchar(16) type so
xml ends rather sooner than expected :).

Peter
 
Back
Top