SQL Server 2005 Performance Issue with insertion of 2000 Recs per Second ( 300 KB of Data)

Question

SQL Server 2005 Performance Issue with insertion of 2000 Recs per Second ( 300 KB of Data)

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 1

cjones-956169 (2/26/2010)
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?

Paul Randall has some recent blog posts about how batching can DRAMATICALLY improve throughput (both of tlog stuff and resultingly the inserts themselves). I think Linchi Shea may have done this type of analysis long ago as well. This was what I was speaking about when I mentioned logging as one of the hindrances to insert performance.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Matt Miller already covered batching with the OP on the first page. The OP is looking into 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]

Paul White SSC Guru Points: 150468 More actions · Answer 3

Two small observations:

1. You're passing a unique identifier to the procedure that isn't used

2. The clustered index you have defined is likely causing many page splits

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

mansshah SSC Enthusiast Points: 138 More actions · Answer 4

sqlbulkcopy throws Primery key violation exception, and there is no priemery key issue, I mean all are uniqure record.

All ideas are welcomed.

Thanks.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Have you tried importing the data into a table that does not have the primary key defined and then adding the PK after word? I have found that this actually helps find the duplicate records as SQL will tell you where the duplicate record is in this case.

If you are getting PK violations, there appears to be duplicate data.

mansshah SSC Enthusiast Points: 138 More actions · Answer 6

I will surely try that but what suprises me is, when i tried to use a differnt machine with same database and same setup of records insertion works perfect.

mansshah SSC Enthusiast Points: 138 More actions · Answer 7

My table has combined Primary key of (Composite key of ID and date Time). It seems SQLBULKCOPY throws exception bcs it is not checking for millisecond interval precision, it throws exception if i will try to insert record with same id in a second.

Any idea how can i make command to check for Datetime primary key till millisecond interval and just not throw exception. has any one have tried SQLBULKCOPY with Composite key ?

Thanks.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 8

mansshah (3/3/2010)
My table has combined Primary key of (Composite key of ID and date Time). It seems SQLBULKCOPY throws exception bcs it is not checking for millisecond interval precision, it throws exception if i will try to insert record with same id in a second.
Any idea how can i make command to check for Datetime primary key till millisecond interval and just not throw exception. has any one have tried SQLBULKCOPY with Composite key ?
Thanks.

1) if ID is an identity, do you need datetime as well for PK? Does this allow duplicates potentially?

2) are you explicitly stating the time down to the millisecond for every inserted row or using getdate()? If the latter that is possibly your problem? If so, maybe you can send in each row in it's own insert statement, at a huge cost in performance. Also note it is easy to get sub-millisecond activity that will STILL give PK violation since datetime precision is 3.33ms IIRC. Not a very good design...??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mansshah SSC Enthusiast Points: 138 More actions · Answer 9

TheSQLGuru (3/3/2010)
mansshah (3/3/2010)
My table has combined Primary key of (Composite key of ID and date Time). It seems SQLBULKCOPY throws exception bcs it is not checking for millisecond interval precision, it throws exception if i will try to insert record with same id in a second.
Any idea how can i make command to check for Datetime primary key till millisecond interval and just not throw exception. has any one have tried SQLBULKCOPY with Composite key ?
Thanks.
1) if ID is an identity, do you need datetime as well for PK? Does this allow duplicates potentially?
2) are you explicitly stating the time down to the millisecond for every inserted row or using getdate()? If the latter that is possibly your problem? If so, maybe you can send in each row in it's own insert statement, at a huge cost in performance. Also note it is easy to get sub-millisecond activity that will STILL give PK violation since datetime precision is 3.33ms IIRC. Not a very good design...??

1) yes i need datetime also with Id.

2) I am getting datetime from Datetime.Now and just inserting in datasource. My sensor data is changing may be twice in second and my app is designed accordingly.

Performance is what i was looking for so no way of going back to each row insert as there will be big queue on my thread bcs of slow insertion.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

What is the precision of Datetime.Now??

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mansshah SSC Enthusiast Points: 138 More actions · Answer 11

TheSQLGuru (3/3/2010)
What is the precision of Datetime.Now??

In Milliseconds.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 12

simple answer: having time with your PK will ALWAYS leave you at risk for PK violations.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Stefan_G SSCertifiable Points: 6609 More actions · Answer 13

I would use SqlBulkCopy() to get a buffer full of data into a temporary table, then I would call a stored procedure that performs the necessary work to move the data from the temp table to the real table using a set-based insert.

/sg