Word Index Performance

Posted by Blake Stanford on 05-Jan-2016 08:02

Openedge 11.5   Windows Client  Unix DB Server

We are use a word index for some tables in our system.  We allow the users to put their own keywords in a field on the record, then we append our own internal system word containing multiple key fields from the record.  An example value would be: "white shingle ridge @SysId%0501@Act%Y@Stk%Y".  The table has > 4 million records, when a query is performed on the table using the word index, I get the following results:

With a search value of "white* @SysId%0501@Act@Y@Stk%Y" the query takes 22.4 seconds returns 138 records and has 3,508,392 index reads.  

With a search value of "white* @SysId%0501@Act%Y@Stk%Y*" (* on the end of our internal system word), the query takes 2.9 seconds returns 138 records and has 3,526,291 index reads.

It seems counter-intuitive that the query would run faster with more index reads, can anyone explain what is going on here?

Example query:   for each item_branch no-lock where

                                                item_branch.keyword_string contains "white* @SysId%0501@Act%Y@Stk%Y" : 


Thx, Blake

All Replies

Posted by James Palmer on 05-Jan-2016 08:08

Sorry if this is a stupid question, but are you running the two queries one after another? Could it be the second is quicker because the first has loaded stuff into the buffer pool?

What are the record reads for the queries?

Posted by Blake Stanford on 05-Jan-2016 08:51

No problem, I ran them multiple times each so that the records would be cached then dropped the first run etime and averaged the rest.

Record reads are 138.

Posted by George Potemkin on 05-Jan-2016 09:23

Blake, start a session with the -zqil parameter and check statistics in db log.

This thread is closed