February 22, 2010 at 3:16 pm
Hi, My application ( written in c#) was inserting lots of records in sql server 2005 sp3 and a result i saw a big queue of records in my app which had eventually killed my app. I was trying around 2000 records in sec, But i was surprised when i saw the number is not consistent. Sometimes it allows 2000 records in sec sometimes not.
This issue drives in exploration of SQL server Performance and max throughput in details. I have created a test database with stored procedure which is having a single insert statement. Now i am inserting records as fast as i can, and the result is almost same 2000 recs per sec.
Let me mention my table structure also here: 4 fields, max bytes used around 150 per record. so through put that i am getting is 300 kb per sec . ( This is nothing and even not consistent !!!)
Then, i have added one more thread in same app with separate connection to database and my through put is drastically improved. now i dont see any queue for 1000 recs on each thread but as i increased record count to 2000 recs per sec. on both thread , queue had started building up on both threads.
My questions is : Why sql server performance improved with 2 connections ?? (2000 recs with single Connection is not possible but with 1000 on two connection having no issue.)
but as my goal ( app Demand) was to improve performance with single Connection, i have started look in to root cause of this issue and have recorded performance counters
Few performance counters for your reference while inserting 2000 recs
% Processor Time: 31.00
Avg. Disk Queue Length: 0.470
Avg. Disk sec/Read: 0.023
Avg. Disk sec/Write: 0.010
%Disk Time : 40.5335
Transactions / Sec : 1921 ( For SQL Database)
Avg Disk Bytes/ Read: 4096
Avg. Disk Bytes / Write: 522
I have also notices that AVG. Disk Queue Length is going up (100%) while processer time is going down towards (0%) and that the point my queue is building up.
All Comments and suggestions are invited to improve performance. Thanking you in advance.
February 22, 2010 at 7:22 pm
Is it really necessary to do row-by-row inserts? You're killing performance that way. If it were possible to do this set-based you'd be inserting multiple times what you're seeing now, without breaking a sweat.
Without any more detail as to what you're up against, and why we're inserting one row at a time - it's going to be hard to tell how to improve.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2010 at 7:56 pm
Please post your c# code that is doing the Insert.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 9:12 am
Thanks for such a quick reply.
Yha I see your point there but I have no choice other then inserting a single row at this time, but let me see if i can implement a buffer mechanism in my front end App, so i can some how send multiple records in database. I am relieving Changes from different threads from Pool and serializing all of them to insert the data in SQL server.
Considering the same Example above ( 2000 recs in sec), i will insert them in one shot in a group. Can you suggest me, which way i should pass the data more efficiently and quickly?
February 23, 2010 at 9:15 am
Thanks for quick reply. This function code is serializing all data insert from different threads.
Here is my code:
_sqlcommandInsertSP.Parameters["@fp"].Value = new Guid();
_sqlcommandInsertSP.Parameters["@tag"].Value = Convert.ToInt32(engineeringvalue.Unit);
_sqlcommandInsertSP.Parameters["@time"].Value = engineeringvalue.DateTime;
if (engineeringvalue.Value is float)
{
float fValue = (float)engineeringvalue.Value;
if (Math.Abs(fValue) < 1.18E-38F)
fValue = ((float)engineeringvalue.Value) > 0 ? 1.18E-38F : -1.18E-38F;
_sqlcommandInsertSP.Parameters["@float_value"].Value = fValue;
_sqlcommandInsertSP.Parameters["@string_value"].Value = DBNull.Value;
}
else
{
_sqlcommandInsertSP.Parameters["@float_value"].Value = DBNull.Value;
_sqlcommandInsertSP.Parameters["@string_value"].Value = engineeringvalue.Value.ToString();
}
_sqlcommandInsertSP.Parameters["@unit"].Value = Convert.ToInt32(engineeringvalue.Unit);
_sqlcommandInsertSP.Parameters["@quality"].Value = engineeringvalue.Quality.RawValue;
// attempt to execute the stored procedure
try
{
_sqlcommandInsertSP.ExecuteNonQuery();
}
// catch any SP errors that occurred
catch (SqlException ex)
{}
February 23, 2010 at 12:08 pm
OK, thanks for the c code. It looks OK, other than the whole "one at a time" issue that you and Matt are are already talking about, because its having the SP do everything (which is good, generally).
Now of course, we'll need to see the SP code on the other side of this, ans the taret table definition, including primary key, and any FKs and indexes on it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 23, 2010 at 3:48 pm
- You could batch them into a single XML document and process that inside SQL using SQLXML.
- you could look at using SQL Service Broker. One of the use cases built around it is the bulk retrieval and processing of data requests from multiples sources.
- Even if you just created simple delimited lists with the individual requests that got processed together would likely be less work on the server.
Barry knows quite a bit more about Service broker than I, so he may be able to comment there.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 23, 2010 at 4:08 pm
Matt Miller (#4) (2/23/2010)
- You could batch them into a single XML document and process that inside SQL using SQLXML.- you could look at using SQL Service Broker. One of the use cases built around it is the bulk retrieval and processing of data requests from multiples sources.
- Even if you just created simple delimited lists with the individual requests that got processed together would likely be less work on the server.
Another idea would be to use SQLBulkCopy() for this.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 24, 2010 at 8:45 am
RBarryYoung (2/23/2010)
OK, thanks for the c code. It looks OK, other than the whole "one at a time" issue that you and Matt are are already talking about, because its having the SP do everything (which is good, generally).Now of course, we'll need to see the SP code on the other side of this, ans the taret table definition, including primary key, and any FKs and indexes on it.
Table Definition:
=====
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FLOAT_DATA](
[FD_TAG_FK] [int] NOT NULL,
[FD_DATETIME] [datetime] NOT NULL,
[FD_VALUE] [real] NULL,
[FD_UNIT_FK] [smallint] NULL,
[FD_QUALITY] [tinyint] NULL,
CONSTRAINT [PK_FLOAT_DATA] PRIMARY KEY CLUSTERED
(
[FD_TAG_FK] ASC,
[FD_DATETIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Procedure
========
ALTER PROCEDURE [dbo].[SP_INSERT_DATA]
@fp uniqueidentifier, -- the guid of the tag's function provider
@tag int, -- the string name of the tag
@time datetime, -- the sample time of the engineering value
@float_value real, -- the data value, only used for float types
@string_value nvarchar(50), -- the data value, only used for non-float types
@unit int, -- string name of the unit of measurement
@quality int -- numeric representation of the EV quality
AS
BEGIN
INSERT INTO FLOAT_DATA (FD_TAG_FK,FD_DATETIME,FD_VALUE,FD_UNIT_FK,FD_QUALITY)
VALUES (@tag,@time,@float_value,@unit,@quality)
END
February 24, 2010 at 2:27 pm
Thanks for all your Suggestions.
I have tried buffering the data and updating them in a second duration with XML datatype. It has improved throughput 3 times with single connection. I have also implemented solution with SqlBulkCopy. I thin this object is a exceptional performance improver. (15 times) , the Issue is my actual stored procedure having lookups and joins of query to get data from different table, SO i have to work on how i can get a direct table mapping to run "SQLBulkCopy" in my front end App.
also, I dont know i can buffer the data or not but i atleast know the workaround if i want to improve through put.
Thanks.
February 24, 2010 at 2:50 pm
mansshah (2/24/2010)
Table Definition:=====
CREATE TABLE [dbo].[FLOAT_DATA](
[FD_TAG_FK] [int] NOT NULL,
[FD_DATETIME] [datetime] NOT NULL,
[FD_VALUE] [real] NULL,
[FD_UNIT_FK] [smallint] NULL,
[FD_QUALITY] [tinyint] NULL,
CONSTRAINT [PK_FLOAT_DATA] PRIMARY KEY CLUSTERED
(
[FD_TAG_FK] ASC,
[FD_DATETIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Procedure
========
ALTER PROCEDURE [dbo].[SP_INSERT_DATA]
@fp uniqueidentifier, -- the guid of the tag's function provider
@tag int, -- the string name of the tag
@time datetime, -- the sample time of the engineering value
@float_value real, -- the data value, only used for float types
@string_value nvarchar(50), -- the data value, only used for non-float types
@unit int, -- string name of the unit of measurement
@quality int -- numeric representation of the EV quality
AS
BEGIN
INSERT INTO FLOAT_DATA (FD_TAG_FK,FD_DATETIME,FD_VALUE,FD_UNIT_FK,FD_QUALITY)
VALUES (@tag,@time,@float_value,@unit,@quality)
END
I don't see any FK definitions or Index definitions for this table, do you really not have any?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 24, 2010 at 2:54 pm
mansshah (2/24/2010)
I have tried buffering the data and updating them in a second duration with XML datatype. It has improved throughput 3 times with single connection. I have also implemented solution with SqlBulkCopy. I thin this object is a exceptional performance improver. (15 times) , the Issue is my actual stored procedure having lookups and joins of query to get data from different table, SO i have to work on how i can get a direct table mapping to run "SQLBulkCopy" in my front end App....
Huh? But neither the app code that you showed us, nor your stored procedure (above) had anything like lookups or joins? Why didn't we see that? Are they in a Trigger on the table? If, so, we would really need to see that too.
Thanks, ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 24, 2010 at 3:03 pm
This all test that i did is to see how much performance i will get with just insert command, bcs while i ran in to this issue, I saw in sql server profiler that all time is taken by just insert command and with lots of disk queues, and then onwards i have decided to check maximum capability of insert commands., and i am ending up creating a test app with just insert command inside.
Anyway, those lookups doesnt take much time but as i saw lots of improvement with sqlBulkcopy, I will create lookups in front end app.
Thanks.
February 25, 2010 at 6:51 am
2000 record per second steady state capabilities is reasonably high, even in today's world. There are MANY things that can cause your throughput to fluctuate, chiefly among them are log/checkpoint issues. There are MANY more metrics you would need to evaulate to find out where the real bottleneck(s) are. There are MANY settings and configurations that might need to be modified to get optimal performance. As other's have mentioned some form of bulking could indeed help, but if you need maximum performance you REALLY need to hire a performance consultant to get things done right for you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 26, 2010 at 11:18 am
From the perfmon counters, it looks like you are doing 1 insert per transaction.
Transactions / Sec : 1921 ( For SQL Database)
Would it be possible for you to batch several inserts in a transaction?
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply