Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
itziks
Starting Member
3 Posts |
 Posted - 2011-12-08 : 09:58:24
|
| 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
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-12-08 : 10:05:46
|
| 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. |
 |
|
|
itziks
Starting Member
3 Posts |
Posted - 2011-12-11 : 06:13:19
|
| 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. |
|
|
|
biswajitdas
Starting Member
44 Posts |
Posted - 2012-01-11 : 16:33:20
|
| 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 |
|
|
|
|
|
|
|
|
Bulk inserts performance