Accessing Hashed files using UV stage

dhiraj · Post by **dhiraj** » Thu Apr 22, 2004 11:42 pm

Hi,

I am using an UV stage to access a hashed file.Hash file is defined with three keys A,B and C. In which A is of type char and B & C are timestamps.My look up condition to the hashed file(using UV stage) has an equal to (=) contion on column A ,Less Than ( <) condition on column B and greater than(>) condition on column C. This look up is performing very poorly in terms of throughput.

NOw my Question is that whether the UV stage will use the Hashinhg algo rithm to do the look up or do i need to seperately define an index on these fields. Is there any other way to improve the performance. The size of the hash file is around 7 million records.

Thanks,

Dhiraj

WoMaWil · Post by **WoMaWil** » Fri Apr 23, 2004 5:15 am

Hi Dhiraj,

I don't understand what you are doing with you lookup but the way you describe it you seam to do something wrong.

Please give some further detail, what you want to do and what you did for to solve the problem, so that we can help you.

Wolfgang

chulett · Post by **chulett** » Fri Apr 23, 2004 6:49 am

If your goal is to emulate the behaviour of a SQL Lookup using "between" you might want to check out this post by the ever popular Mr Kenneth Bland. It describes an alternate methodolgy to do what (I believe) you are trying to do.

ray.wurlod · Post by **ray.wurlod** » Sun Apr 25, 2004 2:42 am

The answer to the question is NO.

The hashing algorithm can only be used (and only makes sense) if the exact value of the key is being sought. It is the value of the key that is processed by the hashing algorithm to determine the location of the record.

You can improve performance markedly by indexing the key or its individual columns.

If you created the hashed file via the UV stage (that is, using CREATE TABLE), then you use the SQL form of CREATE INDEX and you can index the entire key.
If you created the hashed file via a Hashed File stage (that is, using CREATE.FILE or mkdbfile), then you use the non-SQL form CREATE.INDEX and must index @ID to index the entire key.

Given the nature of your query it makes better sense to index the individual columns of the key than to index the entire key.