Does SQLite `NOT IN` parameter have any size limit?

user_9317122 0
Viewed 1223

I have an SQLite DB where I perform a query like 

  Select * from table where col_name NOT IN ('val1','val2')

Basically I'm getting a huge list of values from server and I need to select the ones which is not present in the list given.

Currently its working fine, No issues. But the number of values from server becomes huge as the server DB is getting updated frequently.

So, I may get thousands of String values which I need to pass to the NOT IN

My question is, Will it cause any perfomance issue in the future? Does the NOT IN parameter have any size restriction? (like max 10000 values you can check)?

Will it cause any crash at some point?

android-sqlite sqlite android mysql
3 Answers

This is an official reference about various limitation in sqlite. I think the Maximum Length Of An SQL Statement may related to your case. Default value is 1000000, and it is adjustable.

Except this I don't think any limitation existed for numbers of parameter of NOT IN clause.

user_17530730

With more than a few values to test for, you're better off putting them in a table that has an index on the column holding them. Then things like

SELECT *
FROM table
WHERE col_name NOT IN (SELECT value_col FROM value_table);

or

SELECT *
FROM table AS t
WHERE NOT EXISTS (SELECT 1 FROM value_table WHERE value_col = t.col_name);

will be reasonably efficient no matter how many records are in value_table because that index will be used to find entries.

Plus, of course, it makes it a lot easier to re-use prepared statements because you don't have to create a new one and re-bind every value (You are using prepared statements with placeholders for these values, right, and not trying to put their contents inline into a string?) every time you add a value to the ones you need to check. You just insert it into value_table instead.

user_99521960
user_100305270
Related