[sqlite] subquery performance
Hi, Given the following two tables: TABLE tracks ... composer REFERENCES artists(id) conductor REFERENCES artists(id) performer REFERENCES artists(id) TABLE artists id name I'm trying efficiently to find a list of tracks given the name of an artist (used in composer or conductor or performer). I had come up with the following 2 solutions: 1) SELECT * FROM tracks WHERE (SELECT id FROM artists WHERE name LIKE 'John Williams') IN (performer,conductor,composer); query plan: 0|0|0|SCAN TABLE tracks 0|0|0|EXECUTE LIST SUBQUERY 1 0|0|0|EXECUTE SCALAR SUBQUERY 1 1|0|0|SCAN TABLE artists 2) WITH artist_match(id) AS (SELECT id FROM artists WHERE name LIKE 'John Williams') SELECT * FROM tracks WHERE (composer IN artist_match OR conductor IN artist_match OR performer IN artist_match); query plan: 0|0|0|SCAN TABLE tracks 0|0|0|EXECUTE LIST SUBQUERY 1 1|0|0|SCAN TABLE artists 0|0|0|EXECUTE LIST SUBQUERY 2 2|0|0|SCAN TABLE artists 0|0|0|EXECUTE LIST SUBQUERY 3 3|0|0|SCAN TABLE artists Both seem to run pretty quickly. But I'm wondering whether there's anything I can improve here. Timing these queries, it seems that 2) is a bit faster (which I can see why). But It still seems to use 3 separate subqueries? What does the EXECUTE LIST and EXECUTE SCALAR mean? Biggest question, is there a way to do this more efficiently? Thanks, Sander ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Serialized + Prepared Statement Clarification
On Wed, May 23, 2012 at 1:05 PM, Igor Tandetnik wrote: > On 5/23/2012 1:51 PM, Sander Jansen wrote: >> >> I was always under the impression that prepared statements can only be >> used from one thread at a time, so if 2 threads need to perform the >> same query independently, you need to have a prepared statement for >> each thread. Now I came across the following which seems to contradict >> this: >> >> " >> http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized > > > There is no contradiction. Basically, there's a mutex associated with a > connection, and sqlite3_step as well as other API functions acquire it on > entry and release it on exit. So, while it is safe to call sqlite3_step on > the same statement from multiple threads, it is rather pointless, since a) > all these calls are going to be serialized on the mutex so you won't > actually get any parallelism out of this, and b) the statement is traversing > the same single resultset, so each thread will see some random subset of the > rows, depending on how their calls happen to interleave. Ah ok. so for 2 different result sets you need 2 statements. Thanks! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Serialized + Prepared Statement Clarification
Hi, I was always under the impression that prepared statements can only be used from one thread at a time, so if 2 threads need to perform the same query independently, you need to have a prepared statement for each thread. Now I came across the following which seems to contradict this: " http://www.sqlite.org/c3ref/c_config_getmalloc.html#sqliteconfigserialized SQLITE_CONFIG_SERIALIZED There are no arguments to this option. This option sets the threading mode to Serialized. In other words, this option enables all mutexes including the recursive mutexes on database connection and prepared statement objects. In this mode (which is the default when SQLite is compiled with SQLITE_THREADSAFE=1) the SQLite library will itself serialize access to database connections and prepared statements so that the application is free to use the same database connection or the same prepared statement in different threads at the same time. If SQLite is compiled with the SQLITE_THREADSAFE=0 compile-time option then it is not possible to set the Serialized threading mode and sqlite3_config() will return SQLITE_ERROR if called with the SQLITE_CONFIG_SERIALIZED configuration option. " Or is this section specifically talking about a statement (whose multiple) row results may be processed by independent threads? Thanks, Sander ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Retrieving NANs
On Thu, Jan 8, 2009 at 7:03 PM, Gerry Snyder wrote: > Sander Jansen wrote: >> I need to store NaNs in my database and able to retrieve them as well. >> >> Since sqlite will give me back 0.0 when I call sqlite3_column_double >> and a result contains a NAN, >> I was wondering what the best way is to retrieve a NAN from the database. >> >> Here's what I currently do in peudo code: >> >> if (sqlite3_column_type(column)==SQLITE_FLOAT) >>value = sqlite3_column_double(column); >> else >>value = NAN; >> >> Now, the doc says that >> >> "The value returned by sqlite3_column_type() is only meaningful if no >> type conversions have occurred as described below." >> >> Am I correctly assuming the "no type conversions have occurred" means >> "no type conversions on that (row,column) of the result set"? I mean >> next time I call sqlite3_column_type() on the same column but on the >> next row of the result set, it will still give the correct answer? >> >> Thanks, >> >> Sander >> > That sounds correct, yes. > > One suggestion--if the column will always have either a float or NAN, > why not just not insert anything if you have NAN, and then test for NULL? > I see your point, but the query is already a prepared statement. So having a double as input, I can either check for a NAN and do a bind_null in that case, or just don't check it at all and do a bind_double. Even in your suggested case, I would need to check whether or not the double is a NaN or not. So I take it the easy way and always do a bind_double. Sander ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Retrieving NANs
I need to store NaNs in my database and able to retrieve them as well. Since sqlite will give me back 0.0 when I call sqlite3_column_double and a result contains a NAN, I was wondering what the best way is to retrieve a NAN from the database. Here's what I currently do in peudo code: if (sqlite3_column_type(column)==SQLITE_FLOAT) value = sqlite3_column_double(column); else value = NAN; Now, the doc says that "The value returned by sqlite3_column_type() is only meaningful if no type conversions have occurred as described below." Am I correctly assuming the "no type conversions have occurred" means "no type conversions on that (row,column) of the result set"? I mean next time I call sqlite3_column_type() on the same column but on the next row of the result set, it will still give the correct answer? Thanks, Sander -- "And any fool knows a dog needs a home A shelter from pigs on the wing" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN expression performance
On Thu, Dec 18, 2008 at 7:44 PM, Griggs, Donald wrote: > Regarding: > Is there a way to use a prepared statement and bind a (variable) > array of integers? > >Like in: >SELECT FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15 > ... ) > >Becomes: >SELECT FROM table WHERE someinteger IN ( ? ) > > > I don't know that you can bind these, but > do I remember correctly that these integers are selected by human users? > If so, I guess the savings in time would be unmeasurably small, right? > Or do your users type the string directly were you aiming to reduce sql > injections? > Yes, these come in from user input through multi-select lists (it's a itunes like interface), so I'm not worried about SQL injections. When the selection changes, the resulting query gets build up, compiled and executed. The query is the same everytime, so only the contents of the "someinteger IN " changes. I thought it would be nicer, at least from a coding standpoint, if I could make use of prepared statements. Faster would be nice, but not a requirement. Slower would be a big no no ofcourse :) So it seems I could use a temp table that gets filled with the new selection everytime the user changes the selection. I guess there is some overhead there as well, but on the other hand, the parsing of the query everytime also has a overhead. I'll give it a try! Thanks, Sander -- "And any fool knows a dog needs a home A shelter from pigs on the wing" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN expression performance
One more question though, Is there a way to use a prepared statement and bind a (variable) array of integers? Like in: SELECT FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15 ... ) Becomes: SELECT FROM table WHERE someinteger IN ( ? ) Thanks, Sander On Mon, Dec 15, 2008 at 4:16 PM, Igor Tandetnik wrote: > Sander Jansen wrote: >> Suppose I have query that does: >> >> SELECT FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15 >> ); >> >> The list of numbers comes from a user selection, so it doesn't come >> from some other table. Would it make a big difference in performance >> if I pre-sort this list of numbers or does SQLITE automatically sort >> this (and perhaps maintains an index as well)? > > Looking at EXPLAIN output for such a query, SQLite creates ephemeral > (in-memory) B-tree for the list of integers (essentially, just the index > without the underlying table). So, the numbers will be automatically > arranged into B-tree for fast lookup: their order in the query is > irrelevant (except perhaps for the marginal speed-up in adding them to > the B-tree in the first place, which is probably slightly faster if the > numbers are pre-sorted - but if you don't happen to have them sorted > already, you'll probably spend as much time doing so as SQLite would). > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- "And any fool knows a dog needs a home A shelter from pigs on the wing" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] IN expression performance
thanks! On Mon, Dec 15, 2008 at 4:16 PM, Igor Tandetnik wrote: > Sander Jansen wrote: >> Suppose I have query that does: >> >> SELECT FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15 >> ); >> >> The list of numbers comes from a user selection, so it doesn't come >> from some other table. Would it make a big difference in performance >> if I pre-sort this list of numbers or does SQLITE automatically sort >> this (and perhaps maintains an index as well)? > > Looking at EXPLAIN output for such a query, SQLite creates ephemeral > (in-memory) B-tree for the list of integers (essentially, just the index > without the underlying table). So, the numbers will be automatically > arranged into B-tree for fast lookup: their order in the query is > irrelevant (except perhaps for the marginal speed-up in adding them to > the B-tree in the first place, which is probably slightly faster if the > numbers are pre-sorted - but if you don't happen to have them sorted > already, you'll probably spend as much time doing so as SQLite would). > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- "And any fool knows a dog needs a home A shelter from pigs on the wing" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] IN expression performance
Suppose I have query that does: SELECT FROM table WHERE someinteger IN ( 2,18,19,340,1,72, 15 ); The list of numbers comes from a user selection, so it doesn't come from some other table. Would it make a big difference in performance if I pre-sort this list of numbers or does SQLITE automatically sort this (and perhaps maintains an index as well)? Thanks, Sander -- "And any fool knows a dog needs a home A shelter from pigs on the wing" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite_prepare returns SQLITE_OK but returns NULL statement
Ok, thanks. The reason I am asking this of course is whether this a common thing to occur. I assumed the statement would always be non-NULL if the prepare command return SQLITE_OK. I'll adjust my code to assume it might be null and only raise error when prepare itself returns an error as well. Sander On 3/1/07, Dennis Cote <[EMAIL PROTECTED]> wrote: Sander Jansen wrote: > Using SqLite 3.3.3 I'm trying to prepare the following statement: > > "CREATE INDEX IF NOT EXISTS someindexname ON sometable(somecolumn);" > > It returns SQLITE_OK but returns a NULL statement. ( I think a newer > version doesn't have this behaviour). Does this actually mean that it > is a unsupported SQL query? If I remove > "IF NOT EXISTS" the prepare statement will actually return an error > saying the Index already exists. Sander, If the index already exists, then a null statement is all you need to create it. :-) I assume you are saying that sqlite3_prepare is setting the statement pointer to NULL. I think there was a bug report about this behavior in an earlier version (< 3.3.13) of sqlite. You might want to try it with a current version to see if you get the same behavior. As a work around, you could simply not execute (i.e. don't call sqlite3_step) a NULL statement. Now that I think about it, sqlite may already have that check in place so that sqlite_step(NULL) does nothing and returns. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite_prepare returns SQLITE_OK but returns NULL statement
Using SqLite 3.3.3 I'm trying to prepare the following statement: "CREATE INDEX IF NOT EXISTS someindexname ON sometable(somecolumn);" It returns SQLITE_OK but returns a NULL statement. ( I think a newer version doesn't have this behaviour). Does this actually mean that it is a unsupported SQL query? If I remove "IF NOT EXISTS" the prepare statement will actually return an error saying the Index already exists. Thanks, Sander - To unsubscribe, send email to [EMAIL PROTECTED] -
