Re: [sqlite] what are the limitations for IN() lists?
On Jan 29, 2010, at 8:10 PM, Tim Romano wrote: > Dan, > Thanks for that detail about the b-tree for IN-list queries. When I > examine a query plan for a query like the one below: > > explain query plan > select * from title where id IN(10,20,30,40) > > the plan indicates that an index is used (there's a unique index on > title.id) : > > TABLE title WITH INDEX TITLE_ID_UIX I didn't give you the whole story it seems... In this case, if you have an index on title(id), it will use build the temporary b-tree containing (10,20,30,40), then loop through that table doing lookups on the title(id) index. So, 4 lookups in total. The reason it builds the temporary b-tree at all in this case is in case the user specifies duplicate values (i.e. if title(id) is a unique index, "id IN (10,20,20,30)" should return at most 3 rows, not 4). There is cost based analyzer making the decision. If it determines that a linear scan of table "title" is cheaper than the 4 lookups, it will do that instead. In this case the output of EXPLAIN QUERY PLAN would not mention an index at all, so that isn't happening in your case. Cost based analysis is described in comments in where.c. Function bestBtreeIndex(). Run ANALYZE if SQLite's cost based analyzer is making the wrong decision for your data. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Dan, Thanks for that detail about the b-tree for IN-list queries. When I examine a query plan for a query like the one below: explain query plan select * from title where id IN(10,20,30,40) the plan indicates that an index is used (there's a unique index on title.id) : TABLE title WITH INDEX TITLE_ID_UIX Does SQLite iterate every item in the unique index and look for it in the transient b-tree structure? And if so, does SQLite do this regardless of the relative number of items in each structure, index versus b-tree? We could have 1,000,000 titles and 200 items in the IN-list, but each of the million items would be looked for in the b-tree? Regards Tim Romano On 1/28/2010 12:26 PM, Dan Kennedy wrote: > On Jan 28, 2010, at 10:26 PM, Tim Romano wrote: > > >> Thanks for this clarification. >> >> Wouldn't SQLite simply rewrite my IN-list query, transparently, as an >> equijoin against a transient table that has been populated with the >> values in the IN-list? I don't understand why the IN-list should have >> to be avoided. >> > It creates a temporary b-tree structure and inserts all the values in > the IN(...) clause into it. Then for each row evaluating "? IN (...)" > can be done with a single lookup in the b-tree. > > Dan. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On 28 Jan 2010, at 4:07pm, Tim Romano wrote: > Rather than make a dozen or a hundred round-trips to the webserver, > passing one or a few zipcodes at a time, because that would have > significant latency through the cloud, I am passing the entire list of > desired zip-codes, and getting a single freight train of data in > response (about 200K of data which isn't too bad over broadband). Concatenate the ZIPcode list into a long separated string: ,zc1,zc2,zc3,zv4, Search it, instead of using IN, by using LIKE. See 'The LIKE and GLOB operators' in http://www.sqlite.org/lang_expr.html and section 4 of http://www.sqlite.org/optoverview.html Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On Jan 28, 2010, at 10:26 PM, Tim Romano wrote: > Thanks for this clarification. > > Wouldn't SQLite simply rewrite my IN-list query, transparently, as an > equijoin against a transient table that has been populated with the > values in the IN-list? I don't understand why the IN-list should have > to be avoided. It creates a temporary b-tree structure and inserts all the values in the IN(...) clause into it. Then for each row evaluating "? IN (...)" can be done with a single lookup in the b-tree. Dan. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
The front-end could be any client that can issue a RESTful request and POST parameters to the server. Mine happens to be written in Adobe FlashBuilder ( née FlexBuilder). I wrote the webservice in ASP.NET using Robert Simpson's System.Data.SQLite ADO.NET provider against SQLite3. The main challenge relating to the IN-list question I had (i.e. what is max # of items in an IN-list) is that the user on the front-end is presented with a grid analogous to this: Zip| Type (urban, suburban, rural) | City| State | Population |AverageIncome | AverageIQ | NumberOfFishingPonds My application has nothing to do with fishing, it is lexicographical in nature, but the structure of the problem is analogous: the user may see up to a thousand zip codes in a grid, which can be sorted in any number of ways and which offers the user the ability to check which zip codes for which they want to see greater detail. They might choose everything in OKLAHOMA. Or all rural ZIPS. There is also a SelectAll button which selects the whole kit and kaboodle. This list of selected zip codes gets POSTED to the webserver in one fell swoop. Rather than make a dozen or a hundred round-trips to the webserver, passing one or a few zipcodes at a time, because that would have significant latency through the cloud, I am passing the entire list of desired zip-codes, and getting a single freight train of data in response (about 200K of data which isn't too bad over broadband). The zip-list is simply plugged into an IN-list : select columns from foo where zip in ( 10024, 89445, etc ) I could inject those zip values into a TEMP table and rewrite my query as an equijoin, but I don't see why SQLite wouldn't do that transparently "behind the scenes", in any case. What would prevent such an internal optimization of the query? I understand Jay's point about avoiding the construction of SQL statement strings, but I don't consider that a hard-and-fast rule; it's simply a desideratum. It's main value, IMO, is for inserts where you don't want to recompile the same insert statement again and again and again and would use a parameter to avoid that problem. Regards Tim Romano On 1/27/2010 11:30 AM, Simon Slavin wrote: > > mm. A couple of things worth considering: first that JavaScript under HTML5 > has its own access to SQL commands. If this system is for use only inside an > organisation, and you can say everyone must use a modern browser, then you > can use the HTML5 tools which automatically ensure keep the databases local > (in fact, on the client's hard disk, not the server !). By the way, all the > browsers I've seen that support this actually use sqlite3 internally. > > However, you might be planning to do this on the server using PHP. And PHP > has more than one SQLite library and you should be sure you're using one that > uses sqlite3, not the original sqlite library. > > So part of your design decision is whether some of the presentation work can > be done in JavaScript on the client. > > >> 2c) issues query to disk-database to fetch random hex value to >> ensure temp table is named uniquely >> > You don't need this. If you're using a TEMP table, or keeping the table in > :memory:, then you can call it whatever you want: only the single connection > you're using right now can see it, and it will vanish as soon as Apache (or > whatever) has finished serving that particular web page. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On Thu, Jan 28, 2010 at 10:26:45AM -0500, Tim Romano scratched on the wall: > Thanks for this clarification. > > Wouldn't SQLite simply rewrite my IN-list query, transparently, as an > equijoin against a transient table that has been populated with the > values in the IN-list? I don't understand why the IN-list should have > to be avoided. It might do that when using the sub-SELECT or table syntax. I'm not sure. But I know it does not do that when you provide an explicit list of test expressions. For example, this: t IN ( e1, e2, e3 ,... ) it is simply transformed into a sequence of equality tests, similar to: t == e1 OR t == e2 OR t == e3 ... except that "t" is only evaluated once. SQLite is actually pretty fast at this for a reasonable size chain of expressions, but I won't want to expand that out too far. Overall, I wouldn't say IN is to be avoided. It is just my personal opinion that the intention of the IN operator is for small to moderate size datasets, especially when using an explicit value list. If you're dealing with more than a dozen or so, an equi-JOIN is more along the lines of what you're trying to do from a high level perspective. I also have a serious personal dislike of any solution that involves manually building SQL statements in code. It is required from time to time since SQL just isn't that flexible, but I also view them as an indicator that things are starting to get ugly. Both concerns are more of a "code elegance" type thing, however, so we can argue about it all day long without anyone being right or wrong. At the end of the day you need something that works correctly, provides the required performance, is easy to maintain, and that you can get out the door in a reasonable amount of time. My biggest suggestion is to try a few different approaches and see. Throw a few larger datasets and your problem and see if it does what you need it to do, what you're most comfortable maintaining, and what will get the job done. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
SQLite doesn't have this type of optimization. Internally IN-list will be converted by SQLite parser to sequence of equality checks that will be performed on each row. So generally the more the IN-list the worse the performance of the query. So with IN-list growing to 100 or more elements I'd say the performance would be much worse than with temporary table with index on the value. Without index it will be the same unless query is such that temporary table can become driving one and another table has an index on the field in front of IN-list. Pavel On Thu, Jan 28, 2010 at 10:26 AM, Tim Romano wrote: > Thanks for this clarification. > > Wouldn't SQLite simply rewrite my IN-list query, transparently, as an > equijoin against a transient table that has been populated with the > values in the IN-list? I don't understand why the IN-list should have > to be avoided. > > Thanks > Tim Romano > > > On 1/27/2010 12:28 AM, Jay A. Kreibich wrote: >> [] temp database are always cleaned up when the database >> connection is closed. And since temp tables and indexes go into the >> temp database, and not your main active database, there is no >> long-term maintenance. >> > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Thanks for this clarification. Wouldn't SQLite simply rewrite my IN-list query, transparently, as an equijoin against a transient table that has been populated with the values in the IN-list? I don't understand why the IN-list should have to be avoided. Thanks Tim Romano On 1/27/2010 12:28 AM, Jay A. Kreibich wrote: >[] temp database are always cleaned up when the database >connection is closed. And since temp tables and indexes go into the >temp database, and not your main active database, there is no >long-term maintenance. > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On 27 Jan 2010, at 1:28pm, Tim Romano wrote: > The question in my mind is whether the following is any more > performance-efficient than the approach above (note 2a-2e and 5a-5b): > 2. Webservice: > 1) receives the request > 2) instantiates a database connection > 2a) creates an in-memory database > 2b) attaches in-memory database Hmm. A couple of things worth considering: first that JavaScript under HTML5 has its own access to SQL commands. If this system is for use only inside an organisation, and you can say everyone must use a modern browser, then you can use the HTML5 tools which automatically ensure keep the databases local (in fact, on the client's hard disk, not the server !). By the way, all the browsers I've seen that support this actually use sqlite3 internally. However, you might be planning to do this on the server using PHP. And PHP has more than one SQLite library and you should be sure you're using one that uses sqlite3, not the original sqlite library. So part of your design decision is whether some of the presentation work can be done in JavaScript on the client. > 2c) issues query to disk-database to fetch random hex value to > ensure temp table is named uniquely You don't need this. If you're using a TEMP table, or keeping the table in :memory:, then you can call it whatever you want: only the single connection you're using right now can see it, and it will vanish as soon as Apache (or whatever) has finished serving that particular web page. [later: I see Jay explained this bit better, so read his response.] Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On Wed, Jan 27, 2010 at 08:28:15AM -0500, Tim Romano scratched on the wall: > The question in my mind is whether the following is any more > performance-efficient than the approach above (note 2a-2e and 5a-5b): > 2. Webservice: > 1) receives the request > 2) instantiates a database connection > 2a) creates an in-memory database > 2b) attaches in-memory database You create the database by attaching it, so these are one step. > 2c) issues query to disk-database to fetch random hex value to > ensure temp table is named uniquely > 2d) creates temporary table in the in-memory database > 2e) populates temporary table with values that would otherwise be > placed in the IN-list You're making this much too complex. Without attaching a new ":memory:" or "" database or doing anything else different from what you're already doing, you can simply give the command: CREATE TEMP TABLE in_args (val); And then insert your values into it. The "TEMP" will make SQLite automatically create a temp database (that is, an internal equivalent to "ATTACH AS 'temp'"), but because it is known to be a temp database, several performance-related configurations are made, such as setting the database to exclusive locking mode. Temp databases are also exclusive to the database connection, so there is no need for unique names or nonsense like that. You couldn't share a temp database if you wanted to. This kind of thing is an OLD problem in databases, and it was solved a long time ago. The temp database will either be an in-memory database, or it can be a "file-backed" database. Which depends on the value of PRAGMA temp_store and some compile-time settings. "File-backed" is not exactly the same as "file-based." IIRC, a file-backed database will only actually hit disk if it over-flows the cache (which defaults to 500 pages); most of the time even a "file" temp database won't actually hit disk, so the performance is very good. > 3) creates a command with SQL statement (now joining disk-tables to > in-memory table) No need to re-write the query with a JOIN. The IN expression supports table names. You can simply say "...IN temp.in_args" (note: no column name; it must be a one-column table). > 4) executes the command > 5) grabs the results > 5a) drops the temporary table in the IN-memory database > 5b) detaches the memory-database If you close the database connection all TEMP stuff is automatically cleaned up. Again, this is an old problem built very deeply into the core of any modern RDBMS. They're designed to be used this way. > 6) closes the database connection > 7) sends the results to the browser-agent > > At what point does step #3) in the top IN-list approach become more > expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach? Using an actual temp table, I wouldn't be too concerned about this. Further, doing it this way avoids the need to build any SQL statements with string manipulations-- always a very dangerous thing that can lead to problems. Using a temp table, you should be able to do everything with static SQL statements and binds. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Thanks for the suggestion of a memory-database, Jean-Christophe. It is not something I've used so far with SQLite but I have some preliminary questions in the abstract. The typical scenario with a webservice goes like this (database connections are ephemeral, not persistent): 1. User visits URL, passing parameters to the webservice in query-string and/or in the form-fields. 2. Webservice: 1) receives the request 2) instantiates a database connection 3) creates a command with SQL statement (in my case, using IN-list) 4) executes the command 5) grabs the results 6) closes the database connection 7) sends the results to the browser-agent The question in my mind is whether the following is any more performance-efficient than the approach above (note 2a-2e and 5a-5b): 2. Webservice: 1) receives the request 2) instantiates a database connection 2a) creates an in-memory database 2b) attaches in-memory database 2c) issues query to disk-database to fetch random hex value to ensure temp table is named uniquely 2d) creates temporary table in the in-memory database 2e) populates temporary table with values that would otherwise be placed in the IN-list 3) creates a command with SQL statement (now joining disk-tables to in-memory table) 4) executes the command 5) grabs the results 5a) drops the temporary table in the IN-memory database 5b) detaches the memory-database 6) closes the database connection 7) sends the results to the browser-agent At what point does step #3) in the top IN-list approach become more expensive than steps 2a-2e and 5a-5b in the bottom in-memory approach? Regards Tim Romano ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On Wed, Jan 27, 2010 at 12:40:52AM +, Simon Slavin scratched on the wall: > > On 27 Jan 2010, at 12:25am, Jean-Christophe Deschamps wrote: > > > Why don't you use a :memory: database for this? > > This has the advantage of removing the chance of a name-space collision. Can't happen with a temp database anyways. Like in-memory databases, temp databases are only associated with one database connection. Also, if you set "PRAGMA temp_store=memory" then your temp database *is* an in-memory database. Regardless, temp database are always cleaned up when the database connection is closed. And since temp tables and indexes go into the temp database, and not your main active database, there is no long-term maintenance. -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
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
Re: [sqlite] what are the limitations for IN() lists?
Hello Simon, >This has the advantage of removing the chance of a name-space collision. That's true as well: it is an added free bonus. But honestly I would say that for such transient usage a random generated name is fairly unlikely to cause real-world problem. select hex(randomblob(16)); just gave "in a row": 207FA9389DDD09302E61D45E08571BD7 D8D15A725C34263099BCC95373596214 7458A094F3EF6673A7ADADFBF0F54EB3 I doubt somebody would be able to come up with a collision easily. Even if a collision is utterly unlikely but still possible, I wouldn't worry that much for transient table names or transaction ID having only few seconds lifespan. And NO, I don't open here the GUID can of worms for _permanent_ IDs, which is a very different story... ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
On 27 Jan 2010, at 12:25am, Jean-Christophe Deschamps wrote: > Why don't you use a :memory: database for this? This has the advantage of removing the chance of a name-space collision. :memory: databases are not stored in any file on disk, and they can be seen only by the process which creates them. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
Hi Tim, >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? Why don't you use a :memory: database for this? I'm doing this in some applications and it works very well with little fuss. I open my main (disk-based) base(s) and also create a :memory: one which I attach to the main. Now I can use it at will. As has been discussed here recently, you can't have FK with parents/children in separate bases, or other types of split constraints/triggers. Using an intermediate table like this also gives you more freedom for fancy behavior that would be much more difficult or even impossible to achieve using only SQL and the main base only (without a temp table). I mean indexing and/or ordering (possibly with complex index/collation) your memory table so that a match occurs with best speed, a trigger to record which word did the client match in the list, a.s.o. You can create separate tables having unique name or put some randomized client transaction_id in a unique table... Simply drop the table as soon as you (or your client) don't need it anymore. Cheers, ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] what are the limitations for IN() lists?
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,...) >> > > 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
Re: [sqlite] what are the limitations for IN() lists?
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,...) If you are using any more than a few values for IN, then using IN is probably not the way to go. Make another table with all your valid values, and use a JOIN to get the results you want. > I didn't see the answer here: http://www.sqlite.org/limits.html > > My queries could have more than 255 values from time to time 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 ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] what are the limitations for IN() lists?
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,...) I didn't see the answer here: http://www.sqlite.org/limits.html My queries could have more than 255 values from time to time, which exceeds the limit here on some databases I've worked with in the past. 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. Thanks Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
