Inserting 25 millions records SQL server takes 3hrs.


I am using SQL Server Standard Edition .

in one of my table , there i am inserting around 25 millions records and that takes time around more than 3 hrs.

same thing is happening while fetching records from that table.

this database contains only single file group i.e primary

and that table contains .. Clustered as well as non clustered index.

it doesnot have any Triggers.

How do i increase this performance.

Paritioning of table cannot be use in SQL Server Standard Edition.

Or Dropping all non clustered index before insert operation will improve my performance.

Please suggest me.

Thanks

Msdes
Fexon
Asia

I suggest you read the forum rules and post in the appropriate section.

Wrong forum

Try Databases

\0/ rodger

This post has been edited by KYA: 15 May, 2008 - 10:06 PM

That long huh?

Move to database forum?

I will move this, but I'd also suggest you consider a few things:

1. If you are inserting 25 million records, you should be doing these in batches. If you stagger them out into smaller batches, your runtime will decrease.
2. If you are fetching 25 million records at the same time, you may need to look at a shift in process, or at least implementing a secondary table that contains summary information (which is the only reason I can think of someone needing 25 million records in a single fetch.
3. Index the data.
4. A final option is to perhaps consider switching to an Oracle DB server.

Is your DB server dedicated?

1: dont use any index while inserting. You can drop the index (all indexes, triggers, and all foreign key constraints too) before, and recreate it after the bulk insert. This will be a lot faster.
2: use a special tool to insert the data.

but any way you look at it, 25 mil records is a lot of data, it will take a while.

Other ways to increase performance have nothing to do with the database itself but with tuning the server: (but remember that this operation is mostly IO bound)
plug in more ram, get very fast raid 10 setup with 15krpm discs on a areca controller, etc. You get my drift.

Dropping indexes before inserting and re-creating them aftewards may or may not be faster. It depends on the involved indexes and the state of the table before the operations were begun.

Similarly, you might or might not find Oracle to be faster; asserting that it's a solution to your problem sounds more like an religious admonishment rather than any helpful advice.

You don't explain, foxon177, how you're performing your inserts. You can find varying degrees of improvement by consdiering a few different things:

1) Use BULK INSERT or FastLoad for your inserts. This avoids repeated compiling and locking, and can optimize your load time substantially.

2) If you can't use BULK INSERT or FastLoad, then use a regular INSERT statements in large batches. Make sure you're using precompiled, parameterized statements.

3) Which version of SQL Server are you using? Some editions in some versions don't support built-in partitioning, but all versions of SQL Server do support logical partitioning. The idea is to build individual tables, then create a view over them. Partitioning is generally not useful for performance, however--it's almost always useful for maintenance, though.

4) Make sure you're using the appropriate logging mode on your database.