June 25, 2012 at 2:11 pm
If my source is single table and my destination table is a single very large table as well, does it make sense to try and implement parallelism in SSIS to speed up the overall extraction timing or is it gonna cause lock issues? Is parallelism implemented in this kind of scenario?
June 25, 2012 at 11:28 pm
It depends 🙂
Parallellism can give locking in your destination table if you are using a lot of indexes, but in such a large table you'd better drop the indexes before the load and recreate them after the load.
There is a new component from MS (free to download) that can help you to introduce parallellism:
The “Balanced Data Distributor” for SSIS
The blog points out that inserting data into a heap is really fast.
If your destination table is really huge and you are using Enterprise Edition, it may be worth to check out partitioning.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 26, 2012 at 1:54 am
Koen Verbeeck (6/25/2012)
It depends 🙂Parallellism can give locking in your destination table if you are using a lot of indexes, but in such a large table you'd better drop the indexes before the load and recreate them after the load.
There is a new component from MS (free to download) that can help you to introduce parallellism:
The “Balanced Data Distributor” for SSIS
The blog points out that inserting data into a heap is really fast.
If your destination table is really huge and you are using Enterprise Edition, it may be worth to check out partitioning.
Koen, Thanks for your suggestion. My scenerio is using ADO.net provider so i will be unable to fully exploit sql query semantics. also my destination is sql heap table.do u still the probability of parallism and bringing down the over all processing time, given that table is really huge? i am really worried about deadlocks, if you have multiple i/o happening on a table at the same time?
June 26, 2012 at 2:22 am
Why don't you try it out and see what happens?
The BDD component will help you to create multiple streams. Just deselect "table lock" in the OLE DB Destination with Fast Load.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply