Insert data into SQL server with best performance

Question

I have an application which intensively uses DB (SQL Server).

As it must have high performance I would like to know the fastest way to insert record into DB.Fastest from the standpoint of execution time.

What should I use ?

As I know the fastest way is to create stored procedure and to call it from code (ADO.NET). Please let me know is there any better way or may be there are is some other practices to increase performance.

Answer 1

a bulk insert would be the fastest since it is minimally logged, perhaps you can use the SqlBulkCopy class

Answer 2

"It depends".

• How many rows are you talking about inserting?
• How frequently will they be inserted?
• What other database operations will be taking place at the same time?
• Will the rows be inserted because of user action (clicking a button), or because of some external stimulus?

---

Based on your update, I think you should consider mechanisms other than simple code. Look into SQL Server Integration Services, which are optimized for bulk database operations. It's possible that what you need is a simple SSIS job that runs periodically to do a bulk insert on all "new" data meeting particular criteria. It would allow modification over time to use things like staging tables or intermediate servers if that should prove necessary.

Answer 3

Please let me know is there any better way or may be there are is some other practices to increase performance.

• Do not open one connection per record. Do learn how connection pooling generally stops you from inadvertently opening one connection per record.
• If possible, do not open one transaction per record. Also do not leave the transaction open for undue periods of time.
• Consider table design: narrow tables with few indexes/constraints and no triggers.
• If you need a fast insert because you're a web application and need to return a page to the user NOW or you're a winform app and are blocking on the UI thread, consider performing the insert async or on another thread.
• If you need a fast insert to import a million line file, consider doing a bulk insert.
• If all you want to do is store the data, and not to query it... consider using a file-based solution instead.

Answer 4

Have you done the math? 2M/day = 83k/hour = 1388/min = 23/second.

At 23 inserts per second SQL Server won't break a sweat.