Re: [U2] Unidata - Key Stucture - Compound Keys or Sequetial
Not in this case, but when I converted the file to a test file using sequential ids (same exact data pushed down 10 positions and putting the original key in the first field) I had no issues with the date select. It was as fast as 14ms on a cached index, which tells me the issue is the size of the keys. I did 2 selects for each file, 2x. Interesting, it's intuitive to think that the cached data would even things out, but it didn't. Once the selects were in memory, I got another 10x performance increase with sequential keys (200x to 2000x better). It must be some kind of overflow condition. Nothing I tried worked. It's an internal issue with UD. Note: this senario gets worse, the less resource the system has. It was probably a 10,000x+ difference or more at one time. We had 2 runaway processes on the system and it taking 10-15minutes to select 2 days difference. We fixed those and got the #s above... - Original Message - From: Brian Whitehorn brian.whiteh...@pentanasolutions.com To: 'U2 Users List' u2-users@listserver.u2ug.org Sent: Wednesday, November 18, 2009 11:10 PM Subject: Re: [U2] Unidata - Key Stucture - Compound Keys or Sequetial 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 br...@brianleach.co.uk To: 'U2 Users List' u2-users@listserver.u2ug.org 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 U2-Users@listserver.u2ug.org 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing
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 br...@brianleach.co.uk To: 'U2 Users List' u2-users@listserver.u2ug.org 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 U2-Users@listserver.u2ug.org 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Unidata - Key Stucture - Compound Keys or Sequetial
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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 br...@brianleach.co.uk To: 'U2 Users List' u2-users@listserver.u2ug.org 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: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] 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 U2-Users@listserver.u2ug.org 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org 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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[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 U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
