0
528
Question Romero Terrones Esteve · Nov 10, 2017

Best way for autocomplete fast search

#Caché #SQL #iFind

Hello All,

what is the best method for search a portion of text in a non-indexed global?

I need to implement an autocomplete kind of search, in a global of >1M registers (text type, not $lb)

Maybe the best way would be use a SQL mapped class, with 'Bitmap' indexes?

Thanks in advance!

Discussion (4)0
Log in or sign up to continue

Comments

Vitaliy Serdtsev · Nov 10, 2017

See aside iFind.

I long ago did the tests, using Bible, at searching for of any substring.

  1. without an index
    <FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">Id</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">Para </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">BookPara </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">Para </FONT><FONT COLOR="#000000">like </FONT><FONT COLOR="#008080">'%огон%'</FONT>
  2. with index
    <FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008000">Id</FONT><FONT COLOR="#000000">, </FONT><FONT COLOR="#008000">Para </FONT><FONT COLOR="#000080">from </FONT><FONT COLOR="#008000">BookPara </FONT><FONT COLOR="#000080">where </FONT><FONT COLOR="#008000">id </FONT><FONT COLOR="#000000">%FIND </FONT><FONT COLOR="#808000">search_index</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">ParaStemmedI</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008080">'огон'</FONT><FONT COLOR="#000000">,1)</FONT>
The number of found rowsPerformance (sec.)Global references
The result:
without an index2870.518151845
with index2870.0091006
The difference is obvious.
0
Evgeny Shvarov · Nov 10, 2017

Hi, Romero!

Look at this project which does search for Documentation with iKnow and iFind by @Konstantin.Eremin. Demo. Github.

It uses iKnow for autocomplete functionality in a search box.

0