Have you defined NO.NULLS on the DATE field index? The condition less than equal to will be catering for "" or Null as being less than 0, whereas the SELECTINDEX in the program version will be using Date values (hence missing the less than equals part.
$0.02, HTH. Cheers, Brian. -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of BraDav Sent: Thursday, 19 November 2009 3:02 PM To: U2 Users List Subject: Re: [U2] Unidata - Key Stucture - Compound Keys or Sequetial I do not know what is causing the peformance problem with Unidata and indexed fields, using large key. In the case of this particular file, there are translated fields that have to be built separately from the other 'D' type fields. The field in questions, that I was selecting on, was a date field. The date range was one day. What I ended up doing was a for/next loop selecting on each day in the date range. Dates in this file are fairly densly used, so there are not that many days that have no data. Regardless, the direct select is much faster than the date range. This is very fast code in Unidata: SUBROUTINE SELECT.DATE.RANGE(START,LAST,FILE) DATELIST="" FOR DATE=START TO LAST SELECTINDEX "DATE",DATE FROM FILE TO 1 READLIST LIST FROM 1ELSE LIST ="" IF LIST ELSE CONTINUE DATE.LIST<-1>=LIST NEXT FORMLIST LIST TO 0 RETURN However, I would prefer to do this: SELECT FILE WITH DATE >= 'START' AND DATE <= 'LAST' ----- Original Message ----- From: "Brian Leach" <[email protected]> To: "'U2 Users List'" <[email protected]> Sent: Sunday, November 08, 2009 8:37 AM Subject: Re: [U2] Unidata - Key Stucture - Compound Keys or Sequetial > 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 _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users -- Message protected by DealerGuard: e-mail anti-virus, anti-spam and content filtering. http://www.pentanasolutions.com Click here to report this message as spam: https://login.mailguard.com.au/report/1yI4xZInoq/1fKqhkxB9S90xqvK6lOGGn/6.2 _______________________________________________ U2-Users mailing list [email protected] http://listserver.u2ug.org/mailman/listinfo/u2-users
