Hash File Tuning

Kalyan3699 · Post by **Kalyan3699** » Wed Oct 11, 2006 3:18 pm

I am trying to read 2 million row table (60 Columns of which 4 Columns are keys) into a hash file.its been taking around 2 hrs to load the data into the file with the default options that are provided with the Hash file.I tried to optimize the hash file using HFC.exe(Hash File Calculator tool),it said either to use a File Type of 14,18 or Modulo of 367097,still the performance is no different.Could someone provide me any options in tuning the hash file.

Thanks

ArndW · Post by **ArndW** » Wed Oct 11, 2006 3:36 pm

pre-sizing the modulo on a dynamic file and/or using a static hashed file of appropriate size works well. Also, do you have row buffering enabled? That will increase your speed. Are you sure that your hashed file write is the bottleneck? If you do short test and change your hashed file stage into a sequential file writing to /dev/null do you get a much better speed?

ray.wurlod · Post by **ray.wurlod** » Wed Oct 11, 2006 7:05 pm

You might also try using Write Cache, having allowed the maximum possible cache size (999MB).

Kalyan3699 · Post by **Kalyan3699** » Wed Oct 11, 2006 7:23 pm

Ray,
I am writing to the same hash file and reading from the same hash file by which i cant select the write cache option.

Thanks

chulett · Post by **chulett** » Wed Oct 11, 2006 9:23 pm

Ok... that would have been a nice fact to mention up front. Next question, why are you doing that? You are obviously doing a wee bit more than simply reading 2 million rows into a hashed file...

ray.wurlod · Post by **ray.wurlod** » Wed Oct 11, 2006 10:41 pm

With a hashed file that size, do the initial population in a separate job, WITH write cache enabled. Be amazed!