Is bulk insert faster then OLE DB copy using data flow task?
-
I am copying data from one SQL server instance to another SQL Server 2008 instance using the dataflow task and using OLE DB connections. The data move takes a day for moving 11829403 rows. Is there a process that I can use to speeden this up. Bulk insert using a file for the copy. Do I have to create the intermediate file and use bulk insert? Will that speeden the job up? I checked the Network activity on the 2 boxes and that is negligible and is not an issue.
Any tips / pointers to speeded this job up will be much appreciated.
Best regards,
Monisha 🙂
-
Have a look at the properties of the OLEDB Destination. I suspect that the "Data access mode" may be set to "Table or view". If it is, change this to "Table or view - fast load".
You may also consider whether you want to specify values for "Rows per batch" and "Maximum insert commit size". If you do, I suggest that you use values of 10,000 or more. This is not necessary. If you do not specify a value, all records will be inserted as a single transaction.
-
Thanks for the suggestion. I have tried this. Always wondered what table / view fast load meant.
Best wishes,
Monisha:-)
-
M Shah (3/17/2010)
I am copying data from one SQL server instance to another SQL Server 2008 instance using the dataflow task and using OLE DB connections. The data move takes a day for moving 11829403 rows. Is there a process that I can use to speeden this up. Bulk insert using a file for the copy. Do I have to create the intermediate file and use bulk insert? Will that speeden the job up? I checked the Network activity on the 2 boxes and that is negligible and is not an issue.Any tips / pointers to speeded this job up will be much appreciated.
Best regards,
Monisha 🙂
Monisha,
Which machine is doing the actual package execution? Is it one of the machines participating in the transfer or it is a third separate machine?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Good question. It is a third separate machine. So is the data brought into a buffer on this third separate machine? Please let me know?
Thank you!
-
M Shah (3/18/2010)
Good question. It is a third separate machine. So is the data brought into a buffer on this third separate machine? Please let me know?Thank you!
Yes, exactly. This is your biggest issue. The data travels first to this third machine before being redirected to the destination. What I would recommend for you to try is setup a linked server on the destination server. And then use regular SQL like SELECT FROM ... INSERT INTO to transfer the data. In this way you will make the transfer without going thru the third-machine.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/ -
Using linked server with select into will lock the table right until the insert is done? The server that the data is finally moved to is used for reporting purposes and we cannot have tables locking up there. Do u still think linked servers is a better option? Please let me know?
Thank you!
-
M Shah (3/18/2010)
Using linked server with select into will lock the table right until the insert is done? The server that the data is finally moved to is used for reporting purposes and we cannot have tables locking up there. Do u still think linked servers is a better option? Please let me know?Thank you!
In terms of speed and efficiency definitely!
Regarding the locking, you might be able to turn-off this temporary, but I don't have the T-SQL on hand. Perhaps someone else knows about this?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply