Hi Brad Interesting, but I wonder about another possibility..
As a partial field the select would be using an I-Descriptor or V-Descriptor. I wonder whether the overhead on that is the problem: if you crated a V-Type to access the date field in the new file (e.g. using EXTRACT(fieldname,0,0) ) would that show the same difference in performance, rather than the data structure? Just wondering.. Brian -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of BraDav Sent: 07 November 2009 20:10 To: U2 Users List Subject: [U2] Unidata - Key Stucture - Compound Keys or Sequetial Subject: Keys, Large Transaction Files, Just recently ran into an interesting phenomenon - I was working with a file with compound keys and the selects over a date range were atrocious. I copy the data to a new file, using sequential keys and the selects averages 200-2000X faster (for the doubters, I have to say is the actual # were something like 197X to 2070x, the second # being as second select after the data was cached). The avg length of key on the file was 32 characters. The avg length of a sequential keys was about 5 characters. The fields was a 'date' field. The field was indexed. The range of the select was 2 days. It seems there's a Unidata threshold large key sizes exceed with indexing that kills peformance. Also, sequential keys hash the best. I managed a file with 80M records at another site and had no problems with file sizing or overflow. Brad _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.698 / Virus Database: 270.14.55/2489 - Release Date: 11/08/09 07:37:00 _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
