Performance issue in SELECT statement in JBASE R9

Performance issue in SELECT statement in JBASE R9
If we use more than one field in SELECT statement (the two fields are indexed) the statement execution take a long time. If we use only one field, the execution is very fast.

Example :
We have two indexes on FBNK.SECURITY.POSITION : SECURITY.ACCOUNT and SECURITY.NUMBER

jsh t24r9 ~ -->time SELECT FBNK.SECURITY.POSITION WITH SECURITY.ACCOUNT EQ "111111"
No Records selected
usr: 0.00 sys: 0.00 elapsed: 0m0.00s

jsh t24r9 ~ -->time SELECT FBNK.SECURITY.POSITION WITH SECURITY.NUMBER EQ "001111-000"
13697 Records selected
usr: 0.00 sys: 0.00 elapsed: 0m0.00s
>
jsh t24r9 ~ -->time SELECT FBNK.SECURITY.POSITION WITH SECURITY.ACCOUNT EQ "111111" AND WITH SECURITY.NUMBER EQ "001111-000"
No Records selected
usr: 24.89 sys: 11.57 elapsed: 0m36.50s

Is somebody face this problem and have solution?

Thanks

To improve the performance you can index the most-used fields ie., SECURITY.ACCOUNT, DEPOSITORY

Sorry i jus now noticed that you have mentioned that both the fields are indexed. May be you can try rebuilding the indexes and try. Because we have also faced in our client site once and after rebuilding the indexes problem got solved.

thanks
for your replay

see the index

sh t24r9 ~ -->CREATE-INDEX -vw FBNK.SECURITY.POSITION SECURITY.ACCOUNT BY SECURITY.ACCOUNT
Notice: Command converted to 'CREATE-INDEX -vw FBNK.SECURITY.POSITION SECURITY.ACCOUNT BY-AL 1'
Index definition 'SECURITY.ACCOUNT' created successfully
Deleting index records for definition 'SECURITY.ACCOUNT' in file 'SP.BAK' ...
Building index records in file 'SP.BAK' ...
.......................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
.....................
jsh t24r9 ~ -->
jsh t24r9 ~ -->CREATE-INDEX -vw FBNK.SECURITY.POSITION SECURITY.NUMBER BY SECURITY.NUMBER
Notice: Command converted to 'CREATE-INDEX -vw FBNK.SECURITY.POSITION SECURITY.NUMBER BY-AL 2'
Index definition 'SECURITY.NUMBER' created successfully
Deleting index records for definition 'SECURITY.NUMBER' in file 'SP.BAK' ...
Building index records in file 'SP.BAK' ...
...................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..................................................
..........................................
jsh t24r9 ~ -->

3.LIST-INDEX:
=================

jsh t24r9 ~ -->LIST-INDEX -a FBNK.SECURITY.POSITION

INDEX definitions for file FBNK.SECURITY.POSITION at 13:26:11 15 JUN 2010 PAGE 1

INDEX NAME................. SECURITY.ACCOUNT
LOCALE NAME................ C
SORT KEYS.................. AL
BASE FID................... 89305088
CREATE DATE................ 15 JUN 10
CREATE TIME................ 13:13:07
MODIFIED DATE.............. 15 JUN 10
MODIFIED TIME.............. 13:22:54
INDEX NUMBER............... 0
DEBUG PSEUDO CODE?......... NO
NULL INDEX VALUES ALLOWED?. YES
CASE INSENSITIVE?.......... NO
MULTI VALUE EXTRACT?....... YES
DATA AND INDEX IN SYNC?.... YES
MAXIMUM VALUES EXTRACTED... UNLIMITED
WRITE mode................. YES
INDEX DEFINITION........... BY-AL 1
LOOKUP.....................

INDEX NAME................. SECURITY.NUMBER
LOCALE NAME................ C
SORT KEYS.................. AL
BASE FID................... 106930176
CREATE DATE................ 15 JUN 10
CREATE TIME................ 13:22:54
MODIFIED DATE.............. 15 JUN 10
MODIFIED TIME.............. 13:22:54
INDEX NUMBER............... 1
DEBUG PSEUDO CODE?......... NO
NULL INDEX VALUES ALLOWED?. YES
CASE INSENSITIVE?.......... NO
MULTI VALUE EXTRACT?....... YES
DATA AND INDEX IN SYNC?.... YES
MAXIMUM VALUES EXTRACTED... UNLIMITED
WRITE mode................. YES
INDEX DEFINITION........... BY-AL 2
LOOKUP.....................


2 Records Listed

After rebuilding indexes, did you check how much time system took for the select statement on SECURITY.MASTER on the indexed fields?

I think starting R08, ff you are going to use the SELECT command in your program, I suggest to use DAS instead. There are pre-define DAS for some application which is like a pre-define select based on ID, fields, etc.