John, here are som example keys:
80-022169
2934-021083
2876-067779
2678-029637
11-009619
11-017780
11-034102
4733-007068
4776-8482879
4776-997639
11-001458
4797-000849
4797-041654
2903-041820
7085-000907
4761-092588
4759-000206
1311-003714
2948-610630
1330-008116
2896-2791389
2678-943669
2678-951830
2678-959991
2678-968152
9623-041705
1880-171193
4751-006084
2790-094243
4788-001341
1392-007447
1392-015608
11-025941
4797-009010
The I-desc looks like this:
FAKT.FINR I FIELD(@ID,'-',1) 4R S
The index looks like this:
Alternate Key Index Details for File FAKT_REG Page 1
File.................. FAKT_REG
Alternate key length.. 20
Node/Block size....... 4K
OV blocks............. 1 (1 in use, 0 overflowed)
Indices............... 5 (3 D-type)
Index updates......... Enabled, No updates pending
Index-Name...... F-type K-type Built Empties Dups In-DICT S/M F-no/VF-expr....
FAKT.FINR V Num Yes Yes Yes Yes S FIELD(@ID,'-',1)
I've tried the index with "no nulls" also.
Then I use the following selection criteria:
:SELECT FAKT_REG WITH FAKT.FINR = 66
Bjvrn Eklund
Anknytning: 2088
-----Ursprungligt meddelande-----
Fren: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] John Jenkins
Skickat: den 16 februari 2006 15:23
Till: [email protected]
Dmne: RE: [U2] [u2][ud] Poor select performance
Bjvrn
You should aim for a key structure that avoids "overloading" a single key
value with records and at the same time gives you a good hit rate.
Typical problem areas (though not yours in this case) are keys of 0 and 1,
or with high numbers of null (empty) string entries.
If you can post some example keys and selection criteria used we will see if
we can optimise it for you. At the moment I suspect it will be a composite
key manipulated in a virtual - but let's see some samples first.
Regards
JayJay
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
[EMAIL PROTECTED]
Sent: 15 February 2006 17:27
To: [email protected]
Subject: SV: [U2] [u2][ud] Poor select performance
Hi JayJay,
I guess you meant that I should create an index on the @id field. The keys
look like this 66-0000112. I'm not sure if you say I should try:
SELECT A_FILE WITH @ID <= '66-000001' AND @ID >= '66-9999999'
PErhaps this is not what you mean since it's difficult to tell if 66-0000012
is bigger than 66-0000013. I've tried it but with less performance than my
original i-descriptor index.
Bjvrn Eklund
-------
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/
