Page 1 of 1
Performing Lookups on Large tables
Posted: Thu May 31, 2007 4:10 am
by Das
Hi ,
I wanted to do a self join with a table which it is having data around 10 crores of rows. ,Which is the best method to do that.
Please suggest..
Thanks in advance..
hi
Posted: Thu May 31, 2007 4:18 am
by ajay.prakash03
I think u sud wirte a user defined sql query.
Re: Performing Lookups on Large tables
Posted: Thu May 31, 2007 6:36 am
by caltog
Das wrote:Hi ,
I wanted to do a self join with a table which it is having data around 10 crores of rows. ,Which is the best method to do that.
Please suggest..
Thanks in advance..
Sorry, but could you please specify what are 10 crores ? 10 millions rows ? In that case I would check if this is really usefull to read 10mio rows.
Thanks in advance
Re: Performing Lookups on Large tables
Posted: Thu May 31, 2007 7:12 am
by Abburi
caltog wrote:Das wrote:Hi ,
I wanted to do a self join with a table which it is having data around 10 crores of rows. ,Which is the best method to do that.
Please suggest..
Thanks in advance..
Sorry, but could you please specify what are 10 crores ? 10 millions rows ? In that case I would check if this is really usefull to read 10mio rows.
Thanks in advance
10 crores = 100,000,000 rows, before self join make sure that you have indexes on this table or make partition and use the table then only performance will be good.
Posted: Thu May 31, 2007 5:29 pm
by vmcburney
You could use a database input stage and a lookup stage to join the table to itself - but you would be reading the 10 crores of data in twice. The user-defined SQL in a database input stage lets you join the table on the database engine and bring the data to your ETL server just once. It performs a single intensive SQL statement rather than millions of small SQL statements which should overall be a lighter load on your database - as long as you have the right indexing.