usage of join,merge and lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 60
- Joined: Sun Nov 21, 2004 2:24 am
usage of join,merge and lookup
in what scenario we used join, lookup or merge, I guess they serve common purpose
You have enough details in Parallel job developer guide. Anyway, lookups have to be done only if the data to be looked up is very small. In that case, lookups' performance will be better than counter parts. On the other hand, if the data volume is high, you have to go for join or merge, depending on what the requirement is.
Regards,
Vignesh.
Regards,
Vignesh.
-
- Participant
- Posts: 60
- Joined: Sun Nov 21, 2004 2:24 am
Hi,
Although lookup, join and merge may serve the same purpose it depends on the scenario as well as the volume of data that you are dealing with.
A lookup is nothing but a left outer join and is used for less volume. A join has more options other than left outer join but it cannot handle rejects. The merge stage is similar to the join stage and is capable of handling rejects.
HTH
--Rich
Pride comes before a fall
Humility comes before honour
Although lookup, join and merge may serve the same purpose it depends on the scenario as well as the volume of data that you are dealing with.
A lookup is nothing but a left outer join and is used for less volume. A join has more options other than left outer join but it cannot handle rejects. The merge stage is similar to the join stage and is capable of handling rejects.
HTH
--Rich
Pride comes before a fall
Humility comes before honour
-
- Participant
- Posts: 60
- Joined: Sun Nov 21, 2004 2:24 am
As its name suggests, lookups are used for looking up some values based on key values. Generally dimension tables are looked up. Volume of data lookups can handle depends on your hardware configuration of the server. Generally tables or files that do not grow enormously over a period of time are looked up. Also look up will return only one row even if there are more than one row available for the match (ODBC multirow lookup is an exception).
Vignesh.
Vignesh.
-
- Participant
- Posts: 136
- Joined: Wed Sep 29, 2004 5:56 am
- Location: Chennai
- Contact:
Re: usage of join,merge and lookup
Hi Vignesh,
If the reference to a Lookup is directly from a table, and the number of input rows is significantly more than (eg. 1:100 or more), and if job is small enough to fit into available physical memory, then go for lookup. Else go for join or Merge Stage.
If the reference to a Lookup is directly from a table, and the number of input rows is significantly more than (eg. 1:100 or more), and if job is small enough to fit into available physical memory, then go for lookup. Else go for join or Merge Stage.
legendkiller wrote:in what scenario we used join, lookup or merge, I guess they serve common purpose
It should be based on the amount of memory you have available to you at that particular moment. This is not a cut n' dry answer, but with some smart calculations, and careful observation of the job during its run, you can determine what your general limits are.legendkiller wrote:but agin my question remains unanswerd. Are there are certain ranges of volume of data for which we can say if this is range use join. if this is range then use lookup etc.
And be careful -- I'm referring to production machines. That is why it is always best to have an equivalent machine for development with PX -- easy to tune without risking a production box due to mischeft.