DSXchange

Hi,
is there an performance improvement if we dump an odbc table in to a hash file and look up this hash file using an UV stage instead of looking up the odbc table directly.
The reason why i am using an UV stage is that i have ' >' and '<' in look up conditions, and this can be achieved only through UV stage.


The odbc table size is 50- 60 k records.

dhiraj

Actually, you can do this kind of thing with Hash files as our own illustrious Kenneth Bland has shown in this post.

I believe that the basic answer to your question is 'yes' but this technique will get you orders of magnitude in performance improvement. There was another recent post where this was discussed. It linked back to the above post as well and mentioned some specific timings and how far you can push a solution like this. Shouldn't be too hard to find...

The answer, as always, is "depends". You'll get only a slight gain, maybe none, if you're comparing a local Oracle instance to a (the) local UV instance. Indexing the search columns will achieve gains, of course. And if the comparison is between a remote Oracle instance and a local UV instance, 'twill definitely go faster!