Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Shaun Seckman (Firaxis) wrote: > This is a side question to the topic, but is it possible to generate the > prepared statement opcodes at compile-time and store them in some sort > of data file instead of running through the lexical parser? It seems > like for embedded tightly controlled systems where the database schema > will rarely change then this is a performance hit that could be > bypassed. Baking the prepared statement to a file seems like it would > save quite a bit of CPU time as well as frequent small memory > allocations. Exactly such a mechanism has been mentioned in the past by DRH although it isn't currently listed as available: http://www.mail-archive.com/sqlite-users@sqlite.org/msg19961.html http://www.hwaci.com/sw/sqlite/prosupport.html The byte code is stored in the SQLite database and you run statements by number binding values as appropriate. A restriction is that you can't alter the database schema since that would need the byte code to be updated. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqTLAMACgkQmOOfHg372QSqogCfSZpS+qC1kO7KxT440UoP23cP ttQAoNCsxI3nAeDhS3nsS0ts2XWeSx3e =lmYW -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
This is a side question to the topic, but is it possible to generate the prepared statement opcodes at compile-time and store them in some sort of data file instead of running through the lexical parser? It seems like for embedded tightly controlled systems where the database schema will rarely change then this is a performance hit that could be bypassed. Baking the prepared statement to a file seems like it would save quite a bit of CPU time as well as frequent small memory allocations. -Shaun -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Monday, August 24, 2009 10:28 AM To: General Discussion of SQLite Database Subject: [sqlite] can sqlite result be sorted by using "prepare-step" API > I am currently involved in porting sqlite on mobile phone As an aside, you do realize that most smartphone OSes already have SQLite available? WinCE, iPhone OS, Symbian, PalmOS, Pre, and Android either have SQLite libs built-in to the SDK, or have a version of SQLite that compiles without modification. If you're using one of those environments, there is no reason to re-port SQLite unless you need a cutting-edge feature that just appeared in a newer version. > And that's why I am interested to see if the first awailable row > can return ASAP. This gets back to the original presumption that if you want it sorted, you want it sorted, and all the wishing and hoping isn't going to change the fact that sorting is often more computationally complex than returning rows in random order. The database engine will do what it takes to return a sorted result. If that takes more time, the database engine doesn't care. The database engine will not return an incorrect unsorted result if you asked for a sorted result, regardless of the API. If you have an extremely tight reset timer you have to deal with, you can just have the rows returned and sort them yourself. The overall time is not likely to be much faster, but if you don't do the sort in one pass, you can keep the phone from resetting. > And if "prepare-step" can not do better than "execute" or "GetTable", As I said before, prepare/step is the only way to get data out of the database. sqlite3_exec() and sqlite3_get_table() call prepare and step internally. exec and get_table are just wrapper functions. There is nothing special about them. You could write identical functions yourself with the public prepare/step API. > then what is meaning to use "prepare-step"? That's a long discussion. I'd suggest you start here: http://sqlite.org/cintro.html A quick summary: 1) The "prepare" process is fairly expensive. Using bound variables, you can prepare a statement once and use it over and over without having to re-prepare it. 2) Prepare/step is required to use bound variables. Bound variables prevent SQL injection attacks and a number of other issues. In general, you should be using bound variables and not doing string manipulations on SQL statements. If it was up to me, functions like sqlite3_exec, sqlite3_get_table, and sqlite3_vmprintf wouldn't be included in the API unless you compiled SQLite with SQLITE_BIG_SECURITY_HOLE or some similar flag. 3) Prepare/step is generally faster and uses less memory. All the other APIs return results as strings, which requires further processing and memory management. 4) The step API allows you to break out of queries mid-way through and allows better processing and flow-control. I'd also point out that the prepare/step paradigm is not unique to SQLite. Most database APIs have a similar construction, even if they use different terms. > if I create a view with clause of "order by" to ask sqlite to sort > on that index. Can I get sorted result by querying the view with > prepare-step? Sure, but it is going to take the same amount of processing as just issuing whatever the underlying query is. Views don't pre-sort or cache results, they're more like named sub-SELECTs. > > sqlite3_step() returns rows as they become available. You're reading > > too deeply into "as they become available", however. The database > > engine is still required to return the correct result set. If the > > query needs to be sorted, it needs to be sorted. That may or may not > > require computing the full result set before returning the first row. > > It depends on the query. But either way, the database will do the > > right thing and return the correct results. > According to your explanation, it seems the sorting prevents > "prepare-step" from returning faster than "execute". If you're talking about "time to first row r
[sqlite] can sqlite result be sorted by using "prepare-step" API
> I am currently involved in porting sqlite on mobile phone As an aside, you do realize that most smartphone OSes already have SQLite available? WinCE, iPhone OS, Symbian, PalmOS, Pre, and Android either have SQLite libs built-in to the SDK, or have a version of SQLite that compiles without modification. If you're using one of those environments, there is no reason to re-port SQLite unless you need a cutting-edge feature that just appeared in a newer version. > And that's why I am interested to see if the first awailable row > can return ASAP. This gets back to the original presumption that if you want it sorted, you want it sorted, and all the wishing and hoping isn't going to change the fact that sorting is often more computationally complex than returning rows in random order. The database engine will do what it takes to return a sorted result. If that takes more time, the database engine doesn't care. The database engine will not return an incorrect unsorted result if you asked for a sorted result, regardless of the API. If you have an extremely tight reset timer you have to deal with, you can just have the rows returned and sort them yourself. The overall time is not likely to be much faster, but if you don't do the sort in one pass, you can keep the phone from resetting. > And if "prepare-step" can not do better than "execute" or "GetTable", As I said before, prepare/step is the only way to get data out of the database. sqlite3_exec() and sqlite3_get_table() call prepare and step internally. exec and get_table are just wrapper functions. There is nothing special about them. You could write identical functions yourself with the public prepare/step API. > then what is meaning to use "prepare-step"? That's a long discussion. I'd suggest you start here: http://sqlite.org/cintro.html A quick summary: 1) The "prepare" process is fairly expensive. Using bound variables, you can prepare a statement once and use it over and over without having to re-prepare it. 2) Prepare/step is required to use bound variables. Bound variables prevent SQL injection attacks and a number of other issues. In general, you should be using bound variables and not doing string manipulations on SQL statements. If it was up to me, functions like sqlite3_exec, sqlite3_get_table, and sqlite3_vmprintf wouldn't be included in the API unless you compiled SQLite with SQLITE_BIG_SECURITY_HOLE or some similar flag. 3) Prepare/step is generally faster and uses less memory. All the other APIs return results as strings, which requires further processing and memory management. 4) The step API allows you to break out of queries mid-way through and allows better processing and flow-control. I'd also point out that the prepare/step paradigm is not unique to SQLite. Most database APIs have a similar construction, even if they use different terms. > if I create a view with clause of "order by" to ask sqlite to sort > on that index. Can I get sorted result by querying the view with > prepare-step? Sure, but it is going to take the same amount of processing as just issuing whatever the underlying query is. Views don't pre-sort or cache results, they're more like named sub-SELECTs. > > sqlite3_step() returns rows as they become available. You're reading > > too deeply into "as they become available", however. The database > > engine is still required to return the correct result set. If the > > query needs to be sorted, it needs to be sorted. That may or may not > > require computing the full result set before returning the first row. > > It depends on the query. But either way, the database will do the > > right thing and return the correct results. > According to your explanation, it seems the sorting prevents > "prepare-step" from returning faster than "execute". If you're talking about "time to first row returned", then yes. The time it takes for the first row to be available via step when using prepare/step and the time it takes for your first callback using exec is going to be the same. exec is actually a pretty thin wrapper. All it does is call prepare on the SQL you've provided, then calls step. Each time it calls step, it extracts the results and calls your callback. The exec callback is called once per row, just like step. Both APIs should provide more or less the same performance in all situations. > the implication is that without using "order by" clause the prep > are-step API would return much quicker than "execute" because it > doesn't have to wait for all result set being available. sqlite3_exec doesn't wait for the whole result set to be available either. Take a closer look at the sqlite3_exec API call. It doesn't return anything other than an error code. You provide exec with a callback function, which is called once
Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
On 24 Aug 2009, at 9:13am, nick huang wrote: > This is just the usual case when all query results are retrieved and > then sorted. What I am insterested in is if there is any method to > get the sorted rows by "step" as I am working on mobile phone system > where time-consumed operation would probably reset the system. If you want to make sure that the _prepare and each _step execute quickly, make sure that you have an index on your files which is ideal for your SELECT command. Take a look at the WHERE and ORDER BY clauses of your SELECT command and CREATE an index which would be ideal for executing that command. This will allow SQLite to do the least amount of work when you're retrieving records. If you do this, then none of the library functions should take much time. SQLite is very efficient internally. Please do not worry about the time SQLite takes until you have tried a little sample of your own and seen that it really is a problem. > If sqlite's prepare cannot do better than "execute" in this aspect, > then what is meaning to use prepare/step? The document says sqlite > is especially suitable for embedded system and that is why I wonder > if sqlite has some revolutionary way to solve this problem. _execute must do the _prepare and all the _step commands at once, and reserve enough memory to keep all the results in at once. Breaking it down into _prepare and _step will be faster. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
hi, Thanks for your reply. > nick huang wrote: > > For example, query statement is something like "select * from > > sometable order by somefield;" and we call prepare followed by step. > > And are all results returned from "step" in order of "somefield"? > > Of course. What would be the point of specifying "order by somefield" > otherwise? > I cannot agree with you any more. > > As I read the document, it seems the step will return the first > > awailable row ASAP. That is why I wonder the sorting is not possible > > as according to what we learned from books the sorting of dataset is > > done at the last stage of SQL query when all result set is available. > > ASAP stands for "as soon as possible". For a query with ORDER BY clause, > "possible" is after the whole resultset is retrieved and sorted > (assuming the order cannot be satisfied using an index). > This is just the usual case when all query results are retrieved and then sorted. What I am insterested in is if there is any method to get the sorted rows by "step" as I am working on mobile phone system where time-consumed operation would probably reset the system. If sqlite's prepare cannot do better than "execute" in this aspect, then what is meaning to use prepare/step? The document says sqlite is especially suitable for embedded system and that is why I wonder if sqlite has some revolutionary way to solve this problem. > > However, this also seems to contradictive to that all other query API > > like "exec", "getTable" etc. which all support "sorting" are all > > based on prepare-step. Therefore the only conclusion is that "exec", > > "getTable" etc. retrieve dataset and sort by themselves after they > > call "prepare-step". > > You can look at the source code for sqlite3_exec and sqlite3_get_table, > and convince yourself that they do no such thing. > The source code is a bit complex and at this stage I am still doing some feasibility study for evaluation. > Igor Tandetnik > nick _ Stay in the loop and chat with friends, right from your inbox! http://go.microsoft.com/?linkid=9671354 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
On Mon, Aug 24, 2009 at 01:55:41AM +, nick huang scratched on the wall: > > I am a kind of new to Sqlite and just wonder if the query result row > could be sorted by using Sqlite_prepare followed by Sqlite_Step. Prepare/step is the only way to get data out of the database, so yes. > For example, query statement is something like "select * from > sometable order by somefield;" and we call prepare followed by step. > And are all results returned from "step" in order of "somefield"? Yes. Did you try it and see? > As I read the document, it seems the step will return the first awailable > row ASAP. That is why I wonder the sorting is not possible as according > to what we learned from books the sorting of dataset is done at the > last stage of SQL query when all result set is available. It depends. If SQLite is sorting based off an indexed column, it may be able to start returning rows right away before the full result set has been computed. There are plenty of cases when the query optimizer can rearrange the query pipeline to produce "presorted" in-order results without the whole result set at hand. On the other hand, if you're sorting on a non-indexed column or computed result-set column, then the database engine has to compute the entire result, sort it, and then start to return it. In the first case, the cost of doing the query will be spread across each call to sqlite3_step(). In the second case, the first call to sqlite3_step() may be quite long, but all calls after that should be quite fast. > However, this also seems to contradictive to that all other query API > like "exec", "getTable" etc. which all support "sorting" are all > based on prepare-step. Therefore the only conclusion is that "exec", > "getTable" etc. retrieve dataset and sort by themselves after they > call "prepare-step". No, the short-cut functions are not that smart. The much simpler and more logical conclusion is that the database engine does the sorting. sqlite3_step() returns rows as they become available. You're reading too deeply into "as they become available", however. The database engine is still required to return the correct result set. If the query needs to be sorted, it needs to be sorted. That may or may not require computing the full result set before returning the first row. It depends on the query. But either way, the database will do the right thing and return the correct results. -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] can sqlite result be sorted by using "prepare-step" API
nick huang wrote: > For example, query statement is something like "select * from > sometable order by somefield;" and we call prepare followed by step. > And are all results returned from "step" in order of "somefield"? Of course. What would be the point of specifying "order by somefield" otherwise? > As I read the document, it seems the step will return the first > awailable row ASAP. That is why I wonder the sorting is not possible > as according to what we learned from books the sorting of dataset is > done at the last stage of SQL query when all result set is available. ASAP stands for "as soon as possible". For a query with ORDER BY clause, "possible" is after the whole resultset is retrieved and sorted (assuming the order cannot be satisfied using an index). > However, this also seems to contradictive to that all other query API > like "exec", "getTable" etc. which all support "sorting" are all > based on prepare-step. Therefore the only conclusion is that "exec", > "getTable" etc. retrieve dataset and sort by themselves after they > call "prepare-step". You can look at the source code for sqlite3_exec and sqlite3_get_table, and convince yourself that they do no such thing. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] can sqlite result be sorted by using "prepare-step" API
hi all, I am a kind of new to Sqlite and just wonder if the query result row could be sorted by using Sqlite_prepare followed by Sqlite_Step. For example, query statement is something like "select * from sometable order by somefield;" and we call prepare followed by step. And are all results returned from "step" in order of "somefield"? As I read the document, it seems the step will return the first awailable row ASAP. That is why I wonder the sorting is not possible as according to what we learned from books the sorting of dataset is done at the last stage of SQL query when all result set is available. However, this also seems to contradictive to that all other query API like "exec", "getTable" etc. which all support "sorting" are all based on prepare-step. Therefore the only conclusion is that "exec", "getTable" etc. retrieve dataset and sort by themselves after they call "prepare-step". Anybody has any idea about this? Nick Huang/Qingzhe Huang _ Stay in the loop and chat with friends, right from your inbox! http://go.microsoft.com/?linkid=9671354 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
