August 31, 2011 at 3:29 pm
Hi,
I have tried all the options to improve the perfromance of merge join. Please help me in this. Rows are coming into merge join (outer join) but, to come out of merge join the data is taking long time. I am trying to load million rows.
Thanks
September 1, 2011 at 12:16 pm
Easiest solution would be to just do the joins on the database level.
When you're doing a merge join in SSIS, it has to do all the manipulations in memory, which means the speed of the merge join is going to be entirely dependant upon how your system is set up.
In the database, you can set your tables up with proper indexes to make the joins faster.
If you absolutely can't re-write your queries to do the joins at the source, then your next best alternative would be to create two tables that you can use to store the results of the left and right parts of the merge join.
Before your data flow task begins, add an Execute SQL task, and truncate the two tables you've created to store the results. Also, have another Execute SQL task that will disable the index on the field that is being joined.
Next, take your existing data flow task, and split it into two. In the first data flow task, put all the steps right up to the point where your merge join happened. Then, instead of doing the merge join, have two OLE DB destinations, and output the records that would be joined into the two tables you created.
Connect that to a new Execute SQL task, which will rebuild the indexes on the two tables.
Then, connect that to another data flow task. This data flow task will have an OLE DB source which will be constructed from a SQL query, in which you are doing the join against the tables in question. Continue your task as you had after the merge join.
The advantage of this is that you're not using the memory for any of the activities. The only part that will be slow now is the part where you are building the indexes, but that shouldn't be a problem - an index across a million rows won't take much more than a minute or two to construct.
September 1, 2011 at 12:23 pm
Excellent. I am very thankful to your response. I will try that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply