0

I am developing an app similar to iOS contact app . In my app it would be around 20000 contacts in the list . I would like to perform universal search in this app. Also the search should happen as we type in the search field

, Searching "John Australia" will bring the contact where John is in the first name field and Australia in the address field . I used coredata but its performance is very poor (I dont know may be I am using not proper predicates, I used a predicate with combination of OR and AND).

So now I moved to sqlite FTS4 data base and using FMDB to search the contacts .Performance is much better than core data .

Here is what I did with FTS4

imported all contacts to coredata file (uniqID,name,age,email,company,title,address,note,). Now created a fts4 virtual table with uniqID ,searchText,displayName

create virtual table searchData using FTS4 (guid, searchText, displayName)

And inserted all fields to fts4 table. ie , eg , 

uniqId  name age email               company    title  address             note     
12312 , John,32,johnsock@style.com , style Inc, CEO , Australia Street-4 , bla bla bla

This the row in core data and it is moved to FTS4 table like this 

  uniqID searchText               displayName
    12312 John                     John
    12312 johnsock@style.com       John
    12312 Style Inc                John
    12312 CEO                      John
    12312 Australia Street-4       John
    12312 bla bla bla              John

Now I am performing search in this sqlite fts 4 table . This is my SQL query for "John Australia"

SELECT guid,displayName FROM searchData WHERE searchText MATCH John* INTERSECT SELECT guid,displayName FROM searchData WHERE searchText MATCH Australia*

If I have 3 words then I will have intersection 3 select query . The output I am getting from this query is working perfectly .

Once I getting result from the query I used the display name to populate in tableview

I have 2 problems .

1 still I am facing performance issue while comparing to iOS native contact app if I have multiple word.

  1. If I am searching only one word the performance is very good , but not satisfied with multiword search.

  2. My query wont search email id with like (that is I need to get result even if I search with the work "sock").

Is there any algorithm or logic that I could apply in this search ,so that my searching is fast (please consider the multiword search). Also the same time how could I search email id with like query keeping the same better performance

Reference :http://www.sqlite.org/fts3.html

http://www.swwritings.com/post/2013-04-30-searching-for-speedy-searching

Raj
  • 5,895
  • 4
  • 27
  • 48

1 Answers1

1

We had a similar perfomance issue in our project and pragma commands in sqlite helped us. You can use following commands for performance upgrade

  1. Change cache size (each page is of 1024 bytes)

    PRAGMA cache_size = Number-of-pages;

  2. Change synchronous writing behaviour

    "PRAGMA synchronous = OFF"

  3. Change the temporory store to memory

    "PRAGMA temp_store = MEMORY"

Most of these commands are per session, so you may have to execute them after each connection. You may also need to go through the documentation before you implement this as these commands improve the performance at certain cost. These pragma commands are documented here.

Sachin
  • 139
  • 7