How to write(insert) into database faster

Question

How to write(insert) into database faster

cmrhema

SSCommitted

Points: 1558
More actions
August 31, 2008 at 10:39 pm

#211924

Hi,
We have socket connection program, which is in ASP.NET. We have around 1000 connections a minute, which is likely to be increase.
We have to insert all these values into SQL SERVER2005. We do it using bulkcopy and we have used stored procedures for the same.
But all the same we face problem in insertion. Thats the time take to insert is longer. So can you please let me know the way of inserting into database faster.
Of course we do two or three insertions into the database in different tables.
How do we reduce the time taken for writing into database.
We have not used any adhoc queries. All are stored procedures.
Kindly let me know a way of interacting with the database faster.
Regards
Hemalatha.R

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 1

You're using "bulkcopy" to do the inserts for "connections"? That's a new one on me. You're gonna have to show me that one. 🙂

The key is likely going to be the indexes. If the clustered index is keyed so that inserts don't always go to the logical "end" of the index, then page splits are gonna kill ya. Same thing goes for non-clustered indexes... when they split a page, they make a whole new extent.

When's the last time there was any maintenance done on the indexes?

Also, any triggers involved? I've found that those can be a huge performance drain if not correctly written.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

cmrhema SSCommitted Points: 1558 More actions · Answer 2

This is the way we make a bulkcopy

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(MyCon))

{

bulkCopy.BatchSize = 100;

bulkCopy.DestinationTableName = "gpsdata_history_storegeofence";

bulkCopy.WriteToServer(dt);

}

We dont have any indexes

regards

cmrhema

Wyatt Eurich SSC Enthusiast Points: 128 More actions · Answer 3

I wonder if you wrapped all your SqlBulkCopys in a transaction if that would help.

Also, have you profiled your code to make sure that it is the database slowing you down? I would guess the issue to be more likely how your building your datatable that is being inserted to be where slowness is coming from.

cmrhema SSCommitted Points: 1558 More actions · Answer 4

Thank u All,

The delay was becuase we had to call another stored procedure in the way to insert.

Bulkcopy to do insertion was not the culprit.

Many Thanks

Regards

Hema