July 24, 2014 at 12:22 pm
Hello All,
I am using sort on two tables before using merge join but it is effecting the performance. I am loading aroiund 1 millions records and taking enormous amount of time. So is it neccessary to do sorting or is it any other way around.
Reagrds
Shaun
July 24, 2014 at 2:27 pm
Are you uing T-SQL or SSIS?
If using T-SQL, can you post your query? Are you using JOIN Hints? Why?
July 25, 2014 at 8:46 am
I am using SSIS, merge join as i am replacing the values after comparing from the second table.
July 25, 2014 at 9:39 am
I'm not sure, but you could use a lookup transformation instead of the merge join. It's hard to know without much details on what you're doing.
July 25, 2014 at 12:09 pm
Shaun2012 (7/24/2014)
Hello All,I am using sort on two tables before using merge join but it is effecting the performance. I am loading aroiund 1 millions records and taking enormous amount of time. So is it neccessary to do sorting or is it any other way around.
Reagrds
Shaun
In SSIS, the streams must be sorted in the join order for merge join to function at a reasonable speed. It is typically only used when you're going from two foreign systems into a third foreign system.
Sort, Merge, and aggregation (anything besides a multisplit to a simple * rowcount) should be left in the toolbox if you at ANY point touch the database engine (even at the ends). SSIS is horrid at this, and should only be used if you are doing the equivalent of combining two flat files into a third flat file and don't care about the SQL Engine at all.
If you absolutely need to do these, your best bet is to pre-sort the data inbound, and then set the stream as sorted in the advanced properties. There's multiple blogs on this so I won't go into the details. When the stream is sorted this way, and in a matching way, you don't have to perform a sort in memory.
If you are trying to join a small subset for additional attributes to the main stream, use the lookup component. Be aware it's case sensitive, but it's MUCH more efficient as it doesn't rely on row ordering. There's a limit as to how much you want to pack into the lookup cache, however. It will depend on your system.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply