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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

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

Mario M. Westphal-2
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().

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is there a limit for the number of items in an IN clause?

Simon Slavin-3

On 21 Mar 2015, at 6:46pm, Mario M. Westphal <[hidden email]> 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:

<https://www.sqlite.org/limits.html>

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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is there a limit for the number of items in an IN clause?

Simon Slavin-3

On 21 Mar 2015, at 9:19pm, Simon Slavin <[hidden email]> 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-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Is there a limit for the number of items in an IN clause?

dcorcoran
This post has NOT been accepted by the mailing list yet.
This post was updated on .
In reply to this post by Mario M. Westphal-2
I tested this and was able to specify 8000  elements in the 'IN' clause (did not test beyond that). The character length of the query was 38927 characters. The list of items was a sequential list of numbers from 1 to 8000.

create table foo(i int);
select * from foo where i in (1,2,3..8000); -- I use a shell script to generate the list
Loading...