Merge or Join, Which is more efficient

[Munish]

Hi All,
My job can be done by
either Merge or Join.

I was just wondering which one is more efficient, considering the DataSet may have around 300 mills rows.

Thanks,
Munish

[ray.wurlod]

Define "efficient".

They use very similar algorithms for memory management.

Do you need just a join, or do you need to be able to capture, separately, rows for which there was no "update" available? The decision is largely driven by functionality.

Why not perform some benchmarks and document your results here?

[Munish]

Sure,
Would definitely do that.
It is one of things to do in our SVT.

In current job,
Source 1: Key values + sum
Source 2 : Key valued + count

join

output: Keyvalues + Sum + Count.

Thus, it is very simple inner join.

We have started the development with JOIN stage, however we are going to compare it with Merge stage once we have real time 300 mill data.

Thanks Ray
Munish

[ray.wurlod]

If source1 and source2 are in the same database, also consider doing it in the SQL, where the query may benefit from the table's indexes and/or statistics.

[kumar_s]

Once I made similar benchmarking, Join gave be better result. But I dont have any of those details right now. But I will still wait for your results to be published, Munish.
Make sure your CPU usage idle for all the cases. Do also measure the memory and CPU usage during the operation.

[vijayrc]

Once I made similar benchmarking, Join gave be better result. But I dont have any of those details right now. But I will still wait for your results to be published, Munish.
Make sure your CPU usage idle for all the cases. Do also measure the memory and CPU usage during the operation.

Any Updates on this Munish. I'm curious too, as I am also debating on the usage of MERGE over JOIN in certain scenarios in our development. Your results would help me. Thanks

[DSguru2B]

Search for it. Vincent also has the comparisons in his blog. Click on the signature of vmcburney.