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 -----Ursprungligt meddelande----- Fren: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] John Jenkins Skickat: den 10 februari 2006 20:12 Till: [email protected] Dmne: RE: [U2] [u2][ud] Poor select performance Try building an index on the whole filed and using a RANGE selection ... i.e. >= xxxx and <= yyyy Let us know how it goes - it may do the job. Remember to use no.dups and no.nulls for a bit more pep (pattern matches can't use the index) Regards JayJay -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: 10 February 2006 13:09 To: [email protected] Subject: [U2] [u2][ud] Poor select performance Hi there, we are having very slow respone times on a select statement. We have a file with a total of 5 million records in where the key is like nnnn-nnnnnn. The first part(nnnn) is a customer number(numeric) and the second part(nnnnnn) is a sequential no. We have created an i-descriptor on the customer number, FIELD(@ID,'-',1). On this i-descriptor we have put an index: 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) Selecting a customer with 570 000 records can take 30 seconds. (example of statement SELECT A_FILE WITH FAKT.FINR = 66) Selecting other indexed values in the same file which gives me 5 records out of the 5 million is very very fast. I've tried to rebuild.index with no improvment in performance. The file doesen't show any sign of level 2 overflow. I am single user on a powerful Sun server with SAN disks. We are on Solaris 8 and Unidata 6.1.10. Shouldn't it be faster than this? Perhaps this is normal and we have to take another approach to get the data we need? Thanks in advance! Bjorn Eklund Faktab Finans AB ------- 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/ ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
