Lookup with ODBC

[NEO]

Can we do a lookup with an ODBC stage?? what could be the difference of doing it with hash stage and a ODBC stage. Do I get any performance issues if I do a lookup with ODBC stage??

thanks

[mandyli]

[color=blue][b]Yes some performance issue is there .b'cus if u use ODBC stage it will call odbc driver for connection any database. Any way it will act like layer 3... If u use hash file for lookup purpose once u extracted data from database for hash file. The same hash file u can use any no of jobs. That means reusability and also each and every time not necessary to connect database for fetching data.

So Hash file only good for lookup. If u wants more information please see the ref document.[/b][/color]

[amitdurve]

Hash files are recommended for lookups but yes an database stage can also be used for lookups.
Hash file will definitely give better performance as data is divided into groups based on the key column and hence search for the required data is faster as less amount of data needs to be looked into.
DB stages are good if you dont want to read the entire data and store it in the hash file. Something like loading data from a transaction table. Here you might want to use a DB stage for lookup instead of a hash file.

[Sreenivasulu]

ODBC lookup should be at best avoided. Whenever possible we should
go for Hash lookup. ODBC lookup is much slower (probably more than 10 times slower) than hash lookup for records above 1,00,000.

Key columns in ODBC lookup can be matched using :1,:2... with
the columns on the source table

[ogmios]

I try to avoid both ODBC and hash lookups. If at at all possible try to do your lookup in the main query, it beats both ODBC and hash lookups. And especially hash lookups start behaving very badly when the server is saturated with hash files.

Ogmios

[dstechdev]

In my experiences with lookups, I have found that if you are using constraints on a lookup, the NOTFOUND variable on the link works only with hash lookups. If you need to put a constraint on an ODBC lookup you have to check for ISNULL(hashkey)


John F.

[ray.wurlod]

Can we do a lookup with an ODBC stage??

Yes

what could be the difference of doing it with hash stage and a ODBC stage.

ODBC generates SQL which has to be processed by an ODBC driver then by a database server to access disk-based tables; hashed files can be loaded into memory. Hashed file stage can only be used when there is an "=" lookup on the key; ODBC (and other SQL-based stage types) can implement other kinds of lookup, such as a date within a specified range.

Do I get any performance issues if I do a lookup with ODBC stage??

Yes, huge differences, particularly if hashed file is cached in memory.