CODEWITHSUNDEEP
sqlperformancesqlite
Marek Jedliński
Marek Jedliński

Reputation: 7316

Combining a large number of conditions in SQLite WHERE clause

I need to retrieve records that match IDs stored in a list. The query, generated at runtime, is simple:

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

Which is equivalent to

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

This is fine, but what if the list contains hundreds or thousands of IDs? The statement will be huge and at some point the SQL parser might croak, or if it does not, performance will probably be quite bad. How can I do this in a way that is not so sensitive to the number of records being retrieved?

(The reason I can't just loop over the list and retrieve records one by one is that I need the database to do ORDER BY for me. Records must come from the DB ordered by a specific field, while the list represents records selected by the user in a grid which can be sorted in any number of ways. And yes, I could sort the records in code after I retrieve them, but that's plan B, since I don't even need to hold them all in one data structure, only to come properly ordered.)

Upvotes: 3

Views: 3125

Answers (5)

paxdiablo
paxdiablo

Reputation: 881553

If you're really going to have so many IDs that you're worried about the SQL parser croaking, you can store them into a temporary table and do a cross-join.

Simply create the table with one (primary key) column, the ID, then populate it with the desired IDs and use something like:

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

That query won't choke any parser and the rows retrieved will be limited to those having the ID in the temporary table.

This doesn't have to be a temporary table, of course, you can leave it lying around provided you ensure only one "thing" uses it at a time.

Upvotes: 5

Stephen Quan
Stephen Quan

Reputation: 25966

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

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

or:

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

By preparing a query, the query is available for repeated used and you can bind the parameters to a variable in your native programming language. If this search is high frequency, its worthwhile to keep these prepared queries for some duration.

There's a useful discussion here:

Upvotes: 0

Matt
Matt

Reputation: 1431

Could you add these items to a table and then join to it?

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

Upvotes: 1

Alex
Alex

Reputation: 2420

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

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

Upvotes: 0

ninesided
ninesided

Reputation: 23263

Presumably there is some logic involved in determining the list of ids to retrieve, could this logic not be incorporated into the where clause? Perhaps you could join to a table that contains some kind of session id along with the required ids?

Upvotes: 0

Related Questions