Lookup Performance, Table or Hash
Posted: Wed Mar 05, 2003 6:54 am
Hi All,
In one of our job, we need to detect the new rows that come and generate the surrogate key for those rows. The thing is that, the 'lookup' table are quite big, more than 1M rows. The data can come in multiple sources, and we have a field SOURCE_ID so that for each job that handle different source will use 'WHERE SOURCE_ID = SourceName' as condition to minimize the number of rows to lookup.
I am wondering if it's better to do the lookup by using the Hash file. So I will export all the rows from the Lookup table with the 'WHERE SOURCE_ID = SourceName' condition to the hash file, and then do the lookup to the hash file instead.
Will the performance improved significantly? Because right now, we are accessing one table only, and if we run multiple sources at the same time, the performance will be very slow.
Kindly Advised [:o)]
Regards
ND
In one of our job, we need to detect the new rows that come and generate the surrogate key for those rows. The thing is that, the 'lookup' table are quite big, more than 1M rows. The data can come in multiple sources, and we have a field SOURCE_ID so that for each job that handle different source will use 'WHERE SOURCE_ID = SourceName' as condition to minimize the number of rows to lookup.
I am wondering if it's better to do the lookup by using the Hash file. So I will export all the rows from the Lookup table with the 'WHERE SOURCE_ID = SourceName' condition to the hash file, and then do the lookup to the hash file instead.
Will the performance improved significantly? Because right now, we are accessing one table only, and if we run multiple sources at the same time, the performance will be very slow.
Kindly Advised [:o)]
Regards
ND