Doubt regd look up on ODBC

[raj_cipher]

HI ,

1) While looking up an odbc stage will it hit the database for each record or will it maintain a cache in which it'll make the look up ?

2) Does it hv a concept of recently looked up cache to speed up the look up.For eg., If the value of the look up key is 1 for the first record 2 for the second record and 1 for the third record once again then while looking up for the third record will it read it from a cache or from the Database ?

3) Is it advisable to use ODBC look ups for large volumes ?

[ogmios]

1) No

2) No "recently looked up" cache

3) No :D, the usual advise is to preload your lookup data to a hash file and do the lookups from there. Since I don't like hash files that much, I usually move the "lookup table" to the database where the main query is executing in (if it's not already the same database) and do the lookup in the input SQL statement via joins.

Ogmios

Hint: try to minimize lookups, I've seen people use 12 lookups in a single job and then complain later on that their job is slow... go figure.

[kcbland]

1. Every source row generates a query. If the database supports query cache, and the query is still cached, then you may be lucky. DataStage has no mechanism in ODBC/OCI for this. This is why hash files exist, the technology involved is superior.

2. Every source row generates a query. 1 million rows in a source data stream will generate 1 million queries for each OCI/ODBC lookup, but there's only one connection per lookup stage done at job startup.

3. No. There are techniques discussed on this forum that while seemingly laborious, have significant performance advantages.