I think that the "maximum number of records in a group" of 18032 is really hurting the file performance. I would find that group, and see what keys are being hashed to it. How many other groups approach this number of records? How bad are the other partfiles in this regard?
Something about your keys is causing a lot of them to hash to one group or a selected subset of groups. Also, how many groups out there have absolutely no records in them? You've got 3,000,000 groups but only 2,000,000 records - so you should have something in the neighborhood of 1,000,000 empty groups. If you have 2,500,000 empties - then your key values are not hashing well under type 18. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of roy Sent: Wednesday, October 17, 2007 9:52 AM To: [email protected] Subject: [U2] Size of key question _____ From: Roy Beard [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 16, 2007 10:33 PM To: '[email protected]' Subject: File key question Wow! I got so many ideas from this group that I thought I would give some more Information. This file is a distributed file in 2 parts The 3rd field (separated by *) determines which partfile with "1" going to the file I sent before and the rest going to this file There are no triggers on the file. The system was 'fast' until recently when tens of thousands of new records were added. I agree the file is poorly sized, it was an attempt to solve one problem but created another. The keys, I believe, are an issue Here is a sample &SUMMARY&*PRO*1*&SUMMARY&*CDJ*9876***WS 407*BAR*1*498*GLU*2274***&SUMMARY& 491*BAR*1*498*GLU*2274***&SUMMARY& &SUMMARY&*BAR*1*498*GLU*2274***&SUMMARY& 1896*BAR*1*498*GLU*2274***&SUMMARY& 460*BAR*1*498*GLU*2274***&SUMMARY& 1199*&SUMMARY&*1*465*432*9999*** 1185*&SUMMARY&*1*412*ABE*&SUMMARY&***BLIN 3281*&SUMMARY&*1*412*ABE*&SUMMARY&***BLIN &SUMMARY&*&SUMMARY&*1*412*ABE*&SUMMARY&***BLIN &SUMMARY&*&SUMMARY&*1*450*HIR*&SUMMARY&***AMEL 3558*&SUMMARY&*1*450*HIR*&SUMMARY&***AMEL 3811*&SUMMARY&*1*450*HIR*&SUMMARY&***AMER &SUMMARY&*&SUMMARY&*1*450*HIR*&SUMMARY&***AMER 3558*&SUMMARY&*1*450*HIR*&SUMMARY&***AMER &SUMMARY&*CAG*1*429*LOR*1810***BURN 252*PON*1*640*SHE*FRT*** 2177*CS1*1*491*ABE*&SUMMARY&*** 590*PRO*1*491*JOR*&SUMMARY&***WW8RHH 3715*PRO*1*491*JOR*&SUMMARY&***WW8RHH Press any key to continue... Here is the file stat from the other half of the file File name = SALES-HIST-BRS File type = 18 Number of groups in file (modulo) = 3000017 Separation = 1 Number of records = 2071678 Number of physical bytes = 1894588928 Number of data bytes = 356514112 Average number of records per group = 0.6906 Average number of bytes per group = 118.8374 Minimum number of records in a group = 0 Maximum number of records in a group = 18032 Average number of bytes per record = 172.0895 Minimum number of bytes in a record = 64 Maximum number of bytes in a record = 2704 Average number of fields per record = 25.7575 Minimum number of fields per record = 11 Maximum number of fields per record = 41 Groups 25% 50% 75% 100% 125% 150% 175% 200% full 2789365 44352 31348 23325 33111 9221 9893 59402 Press any key to continue... I'll be working on this for a while. Any resize, copy etc. has such an impact on the system that my access is limited. Thanks for the input so far. Roy Roy C. Beard Distributor Solutions Inc P.O. Box 110520 Palm Bay, FL 32911-0520 321-956-6500 501-642-8698 Fax ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
