On Jan 26, 2010, at 7:41 PM, Tim Romano wrote: > 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?
Yes and yes. > Maintenance: > Is the space occupied by a temp table reclaimed automatically when it > is dropped? I guess technically no. Temp tables are stored in a temporary file created in (and automatically removed from) the filesystem. If you drop a table the space will not be reclaimed until the connection is closed. It will be reused if you put data into another temp table. You cannot vacuum the temp database in which temp tables are stored. Dan. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
