[sqlite] Is there a limit for the number of items in an IN clause?

[Simon Slavin]

On 21 Mar 2015, at 9:19pm, Simon Slavin  wrote:

> Can someone point me to that discussion somewhere that doesn't need a 
> password ?

Sorry, I see that it is still going on, entitled "Query times vary between 0.2 
s and 30 s for very".  The logic looks very weird.

Simon.

[sqlite] Is there a limit for the number of items in an IN clause?

[Simon Slavin]

On 21 Mar 2015, at 6:46pm, Mario M. Westphal  wrote:

> In a recent question 
> (http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2015-March/058668.html)
>  I found out that joining with a single-column temporary table with 500 rows 
> is sometimes several hundred times (!) slower than using an IN clause.

Can someone point me to that discussion somewhere that doesn't need a password ?

> So far my code switched to using a temporary table and a JOIN instead of an 
> IN clause when the IN clause would contain more than 500 elements (numbers). 
> I would like to use larger IN clauses if that?s possible to avoid using 
> temporary tables, but I could not find a limit for how many elements I can 
> use in IN().

If it's anywhere, it's here:



I don't see it, so I suspect you're limited only by maximum statement length.  
However, if your statement is very long, I wonder if your first paragraph will 
still hold.

Simon.

[sqlite] Is there a limit for the number of items in an IN clause?

[Mario M. Westphal]

In a recent question 
(http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2015-March/058668.html)
 I found out that joining with a single-column temporary table with 500 rows is 
sometimes several hundred times (!) slower than using an IN clause.



So far my code switched to using a temporary table and a JOIN instead of an IN 
clause when the IN clause would contain more than 500 elements (numbers). I 
would like to use larger IN clauses if that?s possible to avoid using temporary 
tables, but I could not find a limit for how many elements I can use in IN().