Use index in combination with sample in a query.

[JOSE L MARTINEZ-AVIAL]

Hello,

  We are using jbase 5.11.43. We have a table called FBNK.PARTY, which contains people information. In order to search easily, we have created an attribute QUERY.FIELD as follows:

    QUERY.FIELD
001 I
002 @ID:"*":MANUAL.NAME
003
004 QUERY.FIELD
005 80L
006 S
007
008
009
010
011
012
013
014 @ID:"*":MANUAL.NAME

In order to speed up the search we have created an index over the field QUERY.FIELD

jsh miapre02 ~ -->LIST-INDEX FBNK.PARTY
INDEX definitions for file FBNK.PARTY at 15:33:01  26 JUL 2019                                                                                                                       PAGE    1

INDEX NAME    LOCALE NAME    SORT KEYS.    LOOKUP....    INDEX DEFINITION...................

PTY.LEGAL.    C              AL                          BY 28
ID
QUERY.FIEL    C              AL                          BY-AL ITYPE(\@ID:"*":MANUAL.NAME\)
D

 2 Records Listed

jsh miapre02 ~ -->

If we use that query field, the lookup is pretty quick

jsh miapre02 ~ -->time COUNT FBNK.PARTY WITH QUERY.FIELD LIKE '...20899...'

 1 Records counted

usr: 0.18   sys: 0.02   elapsed: 0m0.20s

jsh miapre02 ~ -->

But if we add the clause SAMPLE, in order to limit the number of records to return, it slows down the query,

jsh miapre02 ~ -->time COUNT FBNK.PARTY WITH QUERY.FIELD LIKE '...20899...' SAMPLE 15

 1 Records counted

usr: 3.88   sys: 1.68   elapsed: 0m5.57s
jsh miapre02 ~ -->
jsh miapre02 ~ -->

Is there any way to avoid that?

Thank you

JL

[Joshua Camacho]

Hi JL,

I've had similar undesired experiences in my years as a jBASE developer. In summary, the answer is simple:

Performing complex queries resorts back to using DICTionary rather than INDEX definitions.

I have two tips for you:

• To ensure you are querying with the index rather than the dictionary, change your command from SELECT to QUERY-INDEX:
  
  QUERY-INDEX FBNK.PARTY WITH QUERY.FIELD = '[20899]'
  
  
  
• If you need to perform a double selection, where one field is indexed and the other isn't, separate it into two stacked queries:
  
  jsh ~ -->QUERY-INDEX FBNK.PARTY WITH QUERY.FIELD = '[20899]'

  
  

   7 Records selected

  
  

  >SELECT FBNK.PARTY WITH [Finish your stacked query]

  
  

   5 Records selected

  
  

  >

I hope this helps.

Josh C