Hashed files - delete recs vs reload

ASU_ETL_DEV · Post by **ASU_ETL_DEV** » Thu Mar 04, 2010 9:21 am

Hello,
If I delete records from a hashed file does that significantly influence the file's performance? Is it better to unload/reload the entire file, filtering out the records I do not need?
Thanks

ArndW · Post by **ArndW** » Thu Mar 04, 2010 9:28 am

While pure inserts are more efficient than delete/update on an existing table, the performance difference on a well hashed file (dynamic files are well-hashed) isn't great.

ASU_ETL_DEV · Post by **ASU_ETL_DEV** » Thu Mar 04, 2010 9:36 am

If I keep deleteing records daily, should I expect over time a degradation of performance that will eventually require a reload? I am trying to asses the maintenance impact of one method versus the other.
Thanks

ArndW · Post by **ArndW** » Thu Mar 04, 2010 9:51 am

Hashed files don't suffer from fragmentation in that manner; but the default settings of 20% merge 80% split can cause overhead if you go past those limits. The hashed file uses a hashing algorithm on the key to decide into which group a record is placed, the number of groups is also seen as the MODulo of a file. Within these groups you have a linked list of values, so removing an element is not a big issue.

ASU_ETL_DEV · Post by **ASU_ETL_DEV** » Thu Mar 04, 2010 10:09 am

All right, thank you.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 04, 2010 2:52 pm

There's more. Hashed files automatically re-use space freed by deleting records.

chulett · Post by **chulett** » Thu Mar 04, 2010 3:01 pm

So... deleting from hashed files... we're talking UV stage wearing, SQL deletes here I assume?

ASU_ETL_DEV · Post by **ASU_ETL_DEV** » Thu Mar 04, 2010 3:03 pm

Yes, SQL delete in a UV stage versus a reload of the file.