When we need to persist data, Core Data is always my first choice. However, there are some cases when Core Data isn’t the best solution. If we have a considerable data set, and we need to perform high-speed search queries, full-text search (FTS) is the solution. Unfortunately for us, Core Data doesn’t support the FTS. Therefore we need to use SQLite.
Before you continue reading, I want to let you know that the code you are going to see is just a proof of concept. Many things can be better, handling errors properly for a start. So please do have that in mind.
Adding SQLite to project
To use SQLite in the ios project, we need to add it. In your target, select the Build Phases tab. Open Link binary with libraries and add libsqlite3.tbd.
If you want to work with SQLite in swift, you need to create a bridging header. In the header import sqlite3.h file. The project is now ready to work with SQLite.
Firstly we create an SQLite manager. His primary purpose is to open and maintain a connection to the database.
We initialize the SQLite manager using FileManager. We need to find a path to our database. Then we can open a connection. We are going to inject SQLiteManager to some service. That service will use sqliteDB opaque pointer to access the database.
SQLite FTS services
For every table in our database, we create a new service. There we create tables, populate them, and perform queries.
We initialize our SQLiteFTSServices. For every transaction, we need to have a pointer to the database, so we need to inject SQLiteManager. Also, during the process, we need to create a product table if it doesn’t exist.
For creating tables, we use SQL statements. If you want to find out more about FTS, you can find it here. In our example, our FTS table has only two columns: description and id.
Important note. SQLite is accessed using C API, so do have in mind that you have to cast Strings to NSStrings, and to do the same for all other Swift types.
After creating a table, we need to populate it. There are a few ways to do this. However, we want to have a table with over a million records. So the fastest way to insert that much data is a bulk insert. Unfortunately, there is no out of the box approach to do this. We need to be a bit creative.
The code you see here works like a charm. The acknowledgments go to StackOverflow user DevineDesert. You can see his original answer here.
In our view controller, we have a table view that should show either all rows from the database or rows filtered by the search string. To show all the data, we use the following code.
This code is really straight forward. However, we have micro-optimization, we add the limit. Adding a limit increases the query execution speed. We don’t need to show all the data. If the user scrolls to the end of the list next 100 items should appear. Unfortunately, I didn’t include that part of the code in this example.
If a user types some text in the search field, we should filter the data. Now we use the FTS.
If you look at the query, you can see ‘*’ at the end of the search string. It means that we want all the rows that contain the search string. Without ‘*’ query would return only rows that match the string. Query with ‘*’ is about 8 times slower than one without ‘*.’
Maybe you have noticed we don’t have the ORDER BY clause in any query. If we use it, the result is a much slower query, so we need some workaround. The best solution is to keep your database preordered, so you don’t have to use the ORDER BY clause.
View controller implementation is pretty much straightforward. We use a table view for displaying the results, and search controller for performing a search. However, there are some optimizations here, as well.
Most notably, you don’t want to call FTSService’s methods in the main queue. If we do, the user will experience glitches when typing on a keyboard. Finally, when we fetch products, we call the reload data method in the main queue.
Later we will talk more about FTS performance, but for now, it’s necessary to know that FTS queries work much faster with the larger search string. So if you type fast enough, and for each letter, you create a new FTS query, it’s possible to get the wrong result in the table view. The solution is to synchronize queries.
We are synchronizing queries by taking care of the state of the search. If the user is in the middle of the search, we won’t generate a new search query right away. We wait for the first query to finish first.
Now everything is set up, and we can test the solution.
Testing the FTS
For this test, we’ve populated products table with over 1.2 million rows. Each row has 3 fields. Unfortunately, I can’t share the data with you, but I can share the results, and they were quite impressing. Still, do have in mind that in the real app these queries are going to be a bit faster because we don’t have to log time. These results are from the logs of the iPhone 7.
The search string “a” time: 0.48s
The search string “ar” time: 0.32s
The search string “art” time: 0.25s
The search string “arti” time: 0.20s
The search string “m” time: 0.40s
The search string “mo” time: 0.32s
The search string “mod” time: 0.27s
The search string “model” time: 0.20s
Here are the results of core data implementation. We have a table with 4 fields. Same three as in the FTS table, and FTS field with the data of all previous three. We’ve put indexes on the FTS field. Again we assume that the table is preordered.
The search string “m” time: 0.0007s
The search string “mo” time: 0.01s
The search string “mod” time: 0.024s
The search string “model” time: 0.3644s
The search string “model f” time: 1.95s
As you can see with the bigger search string, we need to have to wait more time to get the results. I won’t post the core data solution. However, here is the fetch request. Very much the same as the FTS query.
Even if we got the same results as FTS, FTS has other advantages over Core Data as well. If we type “model” in the search field, FTS would return rows that have the word “modél” too. Core data won’t.
If you have an offline app with a significant data source and have to perform various types of search, I think FTS is a clear winner. However, this doesn’t mean that that you shouldn’t use Core Data. On the current project, we ended up using both.