SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

itziks
Starting Member

3 Posts

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

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 :)

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 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

itziks
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

biswajitdas
Starting Member

USA
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  
 New 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.05 seconds. Powered By: Snitz Forums 2000