Thanks for the reply, Simon, and the suggestion. You asked if there was a problem with creating a TEMP table. I was disinclined to use a joined temporary table instead of the IN-list for several reasons.
First and foremost, it is a query-only database and rarely will the number of items in the IN-list exceed several dozen. Max would be about 1000 in the rarest of cases. SQLite performance is excellent; I don't mind a little performance lag when the user says "show me everything". My central concern is that the query not fail because the IN-list contained too many values. I am also somewhat in the dark about concurrency issues (if any) in a webservice scenario: -- Do TEMP tables have database-connection-scope so that there is no need to name the TEMP table uniquely? Does the table get deleted automatically when the connection is closed if the client-app neglected to DROP it? My webservice establishes a new connection to the database before each query and immediately closes the connection after the results are returned. Maintenance: Is the space occupied by a temp table reclaimed automatically when it is dropped? Or does the use of TEMP tables require periodic maintenance using VACUUM? Regards Tim Romano On 1/25/2010 11:47 AM, Simon Slavin wrote: > On 25 Jan 2010, at 1:40pm, Tim Romano wrote: > > >> What is the maximum number of literal values that can be put inside the IN ( >> ) list ? >> >> select * from T where aColumn in (1,2,3,4,...) >> > <snip> > How many more ? 1000 ? > > That limit is higher than you're worried about. However, there are other > limits which are inherent in processing a SELECT command. For instance > there's a limit on the total length of the SELECT command expressed as a > string. And a limit on the total number of tokens the command is turned > into. And, of course, the longer the command, the slower it will be > processed. > > >> BTW, the remote client is passing these explilcit values over the internet >> to the server --i.e. the query cannot be rewritten as follows: >> >> select * from T where aColumn in ( select values from T2 where...) >> >> at least not without creating temporary tables to hold the value-list sent >> by the client. >> > Ah, you agree with my suggestion (the sub-select is more usually represented > as a JOIN, sometimes with T2 as the primary rather than the joined table). > Is there a problem creating the temporary table ? > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
