6 Replies - 14532 Views - Last Post: 21 May 2008 - 05:50 PM Rate Topic: -----

#1 foxon177  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 3
  • Joined: 07-May 08

Inserting 25 millions records SQL server takes 3hrs.

Post icon  Posted 15 May 2008 - 11:03 PM

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

Is This A Good Question/Topic? 0
  • +

Replies To: Inserting 25 millions records SQL server takes 3hrs.

#2 RodgerB  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 66
  • View blog
  • Posts: 2,284
  • Joined: 21-September 07

Re: Inserting 25 millions records SQL server takes 3hrs.

Posted 15 May 2008 - 11:05 PM

I suggest you read the forum rules and post in the appropriate section.
Was This Post Helpful? 0
  • +
  • -

#3 KYA  Icon User is offline

  • Wubba lubba dub dub!
  • member icon

Reputation: 3196
  • View blog
  • Posts: 19,226
  • Joined: 14-September 07

Re: Inserting 25 millions records SQL server takes 3hrs.

Posted 15 May 2008 - 11:05 PM

Wrong forum

Try Databases

\0/ rodger

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

Was This Post Helpful? 0
  • +
  • -

#4 thor78  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 1
  • View blog
  • Posts: 121
  • Joined: 06-May 08

Re: Inserting 25 millions records SQL server takes 3hrs.

Posted 16 May 2008 - 12:08 AM

That long huh? :D

Move to database forum?
Was This Post Helpful? 0
  • +
  • -

#5 Amadeus  Icon User is offline

  • g+ + -o drink whiskey.cpp
  • member icon

Reputation: 250
  • View blog
  • Posts: 13,507
  • Joined: 12-July 02

Re: Inserting 25 millions records SQL server takes 3hrs.

Posted 16 May 2008 - 05:34 AM

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?
Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Inserting 25 millions records SQL server takes 3hrs.

Posted 20 May 2008 - 04:38 AM

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.
Was This Post Helpful? 0
  • +
  • -

#7 mikeblas  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 44
  • View blog
  • Posts: 390
  • Joined: 08-February 08

Re: Inserting 25 millions records SQL server takes 3hrs.

Posted 21 May 2008 - 05:50 PM

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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1