Non-standard approach to "improving the performance" of select queries in SQLite

Hello!

First of all, I would like to note 2 points:
- this article describes the algorithm of work, and not how to optimize specific SQL queries;
- the approach described in the article is not relevant for the “productive” iron, it will be discussed how it was possible to increase the “responsiveness” of the application interface that is intensively working with the database on budget smartphones running Android;

Briefly about the application

To make it clear what is at stake, in 2 words I will say about the application itself: it was a system for organizing mobile commerce, the majority of users are sales agents (TA). When a TA agent arrives at a point of sale, he needs to very quickly find the necessary to see the goods in the warehouses (at the time of the last synchronization) and note the quantity needed by the customer. Those. The TA operation algorithm looks something like this:
- find item 1;
- enter the number;
- find item 2;
- enter the number;
...

They do it very quickly :)
')

Search Requirements

- Search by product name (and it can be both in Russian and in English);
- Each client can have “his” price list;
- You need to be able to see all the products, and only those that are in the remnants;
- Have the ability to filter the list of products for the selected category with all subcategories enclosed in it;

Initial data

The figures that I give are real, otherwise I would not have to invent all this.
Number of clients: 500
Products: 5000
When assigning each client an individual price list, we get: 5000 * 500 = 2 500 000 lines in the price table.

Despite the fact that SQLite is very fast, it was not possible to solve the problem head-on. Or rather, to say that everything worked, but not fast enough.

Decision

The first obvious thing is that the SQL query is formed dynamically (in order to exclude from it unnecessary join'y and conditions, if they are not necessary). Also in the course of the experiments it turned out that the union of the tables in the form:

select * from Table1, Table2 where Table1.Table2Id = Table2.Id 

works a little faster than:

 select * from Table1 join Table2 on Table1.Table2Id = Table2.Id 

Well, one more, quite natural moment (although it didn’t immediately occur), this is what the query of the form:

 select Table1.Id from Table1, Table2 ... TableN where Table1.Table2Id = Table2.Id ... 

will be executed much faster than:

 select Table1.Id, ... TableN.FieldN from Table1, Table2 ... TableN where Table1.Table2Id = Table2.Id ... 

Those. the same query, but returning only one field will be executed much faster than if you try to “pull out” all the necessary fields at once.

What about the rest of the data?

And the rest of the data we get a series of simple queries like:

 select * from Table1 where Id = ? 

This request is very fast. Id is the primary key.

Total

The main optimization factor was splitting the process of receiving data into several queries: one of them returns a list of identifiers, and the rest of the data is returned by much simpler queries.

As I said, this approach has significantly accelerated the speed of the application on budget models of smartphones. When scrolling through the list of products (when several “simple” requests are executed for each appearing list item), no visible slowdowns are observed.