Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Other SQL Server Topics (2005)
 Bulk inserts performance
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 12/08/2011 :  09:58:24  Show Profile  Reply with Quote

I've encountered an interesting phenomenon when i was using bulk inserts into MSSQL 2005, and i guess it will be true for any other relational DB:
suppose i have 2000 customers, and each customer has 100 orders (200,000 orders totaly) and i would like to insert these customers with their orders.

I've created bulks of 1000 INSERT statements.

Now, i sow that if i have a mix of INSERTs in each bulk, meaning, customer and orders - the performance are really bad.
On the other hand, if i first execute all the bulks of customers and only then the bulks of orders - it is much faster even in an order of magnitude.

Is this a well known phenomenon?

Thanks :)

Flowing Fount of Yak Knowledge

United Kingdom
3385 Posts

Posted - 12/08/2011 :  10:05:46  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
How are you doing these inserts? Bulk insert does a single table so you couldn't do customers and orders together.

If you are inserting in transactions then the server can wait until the end of the transaction befor sorting out insexes and pages and such.

It's always better to insert into a single tables as the pages are more likely to be consecutive and take less memory and writes.

Do you hev any indexes on the tables?

Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Starting Member

3 Posts

Posted - 12/11/2011 :  06:13:19  Show Profile  Reply with Quote
hi nigelrivett,

Thanks for your reply.
I guess i wasn't accurate with my terminology - when i said "bulk insert " i meant sending 1000 INSERT statements in one batch to the DB.
Go to Top of Page

Starting Member

44 Posts

Posted - 01/11/2012 :  16:33:20  Show Profile  Reply with Quote
agree with @Izziks.

if there is a PK-FK relation between the table and when you insert the record , for each record insert into the order table there will be check in the customer table .so it will be a impact of performances.Also when you bulk insert there will be other factor impact the performances like the database recovery model(simple, full, and bulk-logged).

But in your part I think customer is the Primary table relete to order table.
Insert all the record into the customer table and then insert all the subsequent record into the order table which will be faster.If you want to insert as a whole then we have other option we can play around to make the process faster.

Sr Sql server DBA/Artitech
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000