Combining a large number of conditions in a SQLite WHERE clause

Question

I need to get the entries corresponding to the identifiers stored in the list. The query generated at runtime is simple:

SELECT [whatever FROM sometable] WHERE (id = 1) or (id = 5) or (id = 33). 

Which is equivalent

 SELECT [whatever FROM sometable] WHERE [id] IN (1, 5, 33); 

This is good, but what if the list contains hundreds or thousands of identifiers? The statement will be huge, and at some point, the SQL parser may be compressed, or if it is not, the performance is likely to be pretty poor. How can I make it so that it is not so sensitive to the number of records retrieved?

(The reason I can't just iterate over the list and retrieve the records one by one is because I need a database to execute ORDER BY for me. The records should come from a database sorted by a specific field, while the list represents the records selected by the user in the grid that can be sorted in any number of ways.And yes, I could sort the entries in the code after receiving them, but this plan is B, since I do not even need to keep them all in one data structure, only for the right streamlining.)

Answer 1

If you really have so many identifiers that you are worried about the SQL parser framework, you can save them to a temporary table and cross-join.

Just create a table with one (primary key) column, identifier, then fill it with the desired identifiers and use something like:

 SELECT [whatever] FROM [sometable] st, [idtable] it WHERE st.id = it.id 

This query will not strangle any parser, and the resulting rows will be limited to those who have an identifier in the temporary table.

This is not necessarily a temporary table, of course, you can leave it lying if you ensure its use with only one “thing”.

Answer 2

Could you add these elements to the table and then join it?

 SELECT Whatever FROM TableA CROSS JOIN TableB ON TableA.ID = TableB.ID 

Answer 3

Presumably, there is some logic involved in defining a list of identifiers to retrieve, can this logic not be included in the where clause? Perhaps you could join a table containing some session identifier along with the required identifiers?

Answer 4

If the values ​​in the where clause are in a table, you can do this.

 select id from foo where id in (select id from bar) 

Answer 5

Your programming language should support prepared queries, i.e.

  SELECT [whatever FROM sometable] WHERE (id = ?); 

or

  SELECT [whatever FROM sometable] WHERE (id = @id); 

When preparing a query, the query is available for reuse, and you can bind the parameters to a variable in your own programming language. If this search is high frequency, you should leave these prepared queries for a while.

It is useful to discuss here:

• Alternatives to PreparedStatement IN?
• In SQLite, do prepared statements really improve performance?
• How to use prepared instructions in SQlite on Android?