Varchar Type key field in Universe Hash file Stage

nishant_prakash · Post by **nishant_prakash** » Sat May 12, 2007 12:50 am

Hi,

I am using a combination of Varchar type key field and an integer type key field to be looked up through a hash file in a universe stage for date range lookup.

The lookup file contains merely 50000 records, however the lookup operation on this file has become damn slow @ 3 rows/sec. It may be noted that earlier I used the same hash file but with integer type key fields as lookup and then I had faced no problems with the speed of lookup operation.
Is there any way to improve the speed? At this rate it will take hours to load the data for just 0.1 million records in the source.

Please help.

Thanks,
Nick

ray.wurlod · Post by **ray.wurlod** » Sat May 12, 2007 1:56 am

Since there are no data types in hashed files, I think you need to look elsewhere for the cause of your problem if you're using the Hashed File stage.

If you're using the UniVerse stage then there ARE "casting" issues, as the data types are checked.

Is there any reason not to use a Hashed File stage?

chulett · Post by **chulett** » Sat May 12, 2007 6:53 am

You'll need to create indexes for this, have you? Search the forums for CREATE.INDEX and BUILD.INDEX for the gory details.

nishant_prakash · Post by **nishant_prakash** » Sun May 13, 2007 7:31 pm

Hash file is already supposed to be indexed on the key field. Please explain what do you mean by creating index on hash files???

chulett · Post by **chulett** » Sun May 13, 2007 7:41 pm

Did you try searching the forums? It's all out there and I don't really see why I should type it up one more time. You'd need to decide if it was applicable to your situation or not.

nishant_prakash · Post by **nishant_prakash** » Sun May 13, 2007 8:22 pm

Hi Chulette,

I searched the forums and got the CREATE.INDEX command.

I used the command int he following way -

CREATE.INDEX <hash file path>/<hash file name> <field1 to be indexed> <field2 to be indexed>

However, this command give me an error saying "<hash file path>/<hash file name>" is not a file name..

Confused, please help.

ray.wurlod · Post by **ray.wurlod** » Sun May 13, 2007 8:28 pm

This is a SYNTAX description, not the actual command that you, individually, need to use.

You are expected to replace the metasyntactic variables (the parts encased in angle brackets) with your own values. Further, the pipe character in a syntax description indicates an alternative - you must provide a hashed file name (that is in the VOC file) or a hashed file pathname.

nishant_prakash · Post by **nishant_prakash** » Sun May 13, 2007 8:31 pm

Hi ray,

Yes i did replace the syntax with actual file names and field names. I typed it this way just for everyone else who reads this topic to understand what I am trying to say

But the point is that i still get the error.

It may be noted that this hash file resides in unix file system, not in the UV account.

ray.wurlod · Post by **ray.wurlod** » Sun May 13, 2007 8:34 pm

Use SETFILE in a project to create a VOC pointer. Then issue the CREATE.INDEX command. You can remove the VOC pointer afterwards if you wish.

nishant_prakash · Post by **nishant_prakash** » Sun May 13, 2007 8:40 pm

ray.wurlod wrote:Use SETFILE in a project to create a VOC pointer. Then issue the CREATE.INDEX command. You can remove the VOC pointer afterwards if you wish. ...

Hi ray, thanks for pointing this out. I already had VOC pointers created for the hash files , however I was trying to create indexes on the physical hash file.

Now that I have created and then built indexes on VOC pointers, the problem is resolved.

Cheers,