How to enhance Hash file performance ?

majisukumar · Post by **majisukumar** » Thu Jun 26, 2014 7:14 am

Hi,
I am processing around 24 Million records and the job takes around 7 hrs.

How should i tune the performance of the hash file.

qt_ky · Post by **qt_ky** » Thu Jun 26, 2014 7:37 am

It takes 7 hours to read the hashed file?

chulett · Post by **chulett** » Thu Jun 26, 2014 8:45 am

I'm going to assume the issue is writing to it...

Use the Hashed File Calculator that (I believe) still ships with the product to determine how large to pre-create the hashed file, i.e. what Minimum Modulus to specify. The default is 1 which means it starts off tiny and spends a great deal of time expanding and reallocating space over and over.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 26, 2014 4:22 pm

Performance on read is not tunable. If the hashed file is properly sized, this should be one I/O operation per key-based access (lookup), or one I/O operation per group (stream).

Performance on write is tunable by preallocating the space for the hashed file, as Craig suggests. The Hashed File Calculator utility does still ship with the product - it's in the unsupported utilities folder on the installation media.
Write can be made still faster by using the write cache, but only if the total amount of data fits in the hashed file cache (the size of which is set in the Administrator client, and is limited to 999MB).