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 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-Users mailing list
U2-Users@listserver.u2ug.org
http://listserver.u2ug.org/mailman/listinfo/u2-users
