Re: [sqlite] pre-compiling prepared statements
chetana bhargav wrote: If any one can explain me correctly what sqlite3_prepare does apart from preparing the statement, and does prepare means generating the byte codes necessary. Chetana, sqlite3_prepare does nothing other than preparing an SQL statement, and yes, that does include generating the byte codes. It prepares the SQL to be executed by the VDBE engine. The actual execution (interpretation of the byte code) is done by calling sqlite3_step. sqlite3_prepare takes a SQL statement in the form of a text string and parses it in the context of the current connection and open database(s). It then compiles it to a VDBE byte code program that will implement the SQL statement. This program is called a statement since it implements the meaning of a single SQL statement. These prepared statements can be executed as many times as needed, simply call sqlite3_step (possible multiple times for a query) and then sqlite3_reset to reset the statement so it will execute from the beginning again. Since you may want to vary some of the sub-expressions within the SQL statement between executions, you can include variable parameters in your original SQL statement, and then assign values to those variable before each execution using one of the sqlite3_bind... functions. When you have no further use for the prepared statement you call sqlite3_finalize to release its resources, primarily the memory that holds the byte code program and the values assigned to the variable parameters. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pre-compiling prepared statements
chetana bhargav wrote: It does make a difference with embedded deivces, where both speed and memory constraints matter a lot. -Chetan. Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 9/1/06, John Stanton wrote: I believe that Dr Hipp has available a special version of Sqlite which stores prepared statements. It has restrictions which may make it unsuitable for general purpose applications, but could be the answer this user is looking for. For the benefit of the user, sqlite3_prepare compiles an Sqlite statement but the compilation is only valid for the life of the process and while the schema is not altered. It also requires that the raw SQL be in memory at some stage. What's the benefit there? Isn't preparation time so minimal as to be insignificant? If the few milliseconds your program will save are significant you probably should be using something other than sql to store the data. The benefit is that the Sqlite does not need to have the SQL compiler included and can have a much smaller footprint. - To unsubscribe, send email to [EMAIL PROTECTED] - - Stay in the know. Pulse on the new Yahoo.com. Check it out. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pre-compiling prepared statements
"Jay Sprenkle" <[EMAIL PROTECTED]> wrote: > On 9/1/06, chetana bhargav <[EMAIL PROTECTED]> wrote: > > It does make a difference with embedded deivces, where both speed and > > memory constraints matter a lot. > > I'll check my assumptions when I get some time but I thought > interpreting an sql statement cost only a few milliseconds of time. I > would think saving it to a rotating disk would be worse. It would cost > on average a half disk rotation of latency to read it. Flash memory > has no rotational latency so it might be faster. Maybe my > understanding is wrong but it seems saving prepared statements would > be either worse, in terms of time, or of minimal benefit. You might > get a few milliseconds at startup but only if your storage medium has > no latency. > When the SQL parser/compiler the SQLite library footprint drops dramatically - as small as 60KiB. It's the library footprint that concerns embedded systems people. They typically cannot spare the additional 120KiB of program space needed to host the parser/compiler. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pre-compiling prepared statements
On 9/1/06, chetana bhargav <[EMAIL PROTECTED]> wrote: Hi All, I know that we can use sqlite3_prepare, proabably my perception is wrong, when I say sqlite3_prepare I am thinking the opcodes which ever is necessary to run the query is created upon this call, and we can keep filling the various values by just resetting the prepared statements and re use it over and over, What I want to know is this whether the opcode generation will happen in compile time or when you make a call to sqlite3_prepare while running. If during running can I make some of the statements selectively to generate these opcodes during compile time itself. You want to bind your variables to a prepared statement. Prepare just parses the sql. If you use bound variables then you can use the same prepared statement over and over with different values. If you're inserting rows into a table it works very well to speed it up because it moves the parsing outside the insertion loop. When you're doing a very large number of insertions the small parsing time can add up. . - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pre-compiling prepared statements
On 9/1/06, chetana bhargav <[EMAIL PROTECTED]> wrote: It does make a difference with embedded deivces, where both speed and memory constraints matter a lot. I'll check my assumptions when I get some time but I thought interpreting an sql statement cost only a few milliseconds of time. I would think saving it to a rotating disk would be worse. It would cost on average a half disk rotation of latency to read it. Flash memory has no rotational latency so it might be faster. Maybe my understanding is wrong but it seems saving prepared statements would be either worse, in terms of time, or of minimal benefit. You might get a few milliseconds at startup but only if your storage medium has no latency. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] pre-compiling prepared statements
It does make a difference with embedded deivces, where both speed and memory constraints matter a lot. -Chetan. Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 9/1/06, John Stanton wrote: > I believe that Dr Hipp has available a special version of Sqlite which > stores prepared statements. It has restrictions which may make it > unsuitable for general purpose applications, but could be the answer > this user is looking for. > > For the benefit of the user, sqlite3_prepare compiles an Sqlite > statement but the compilation is only valid for the life of the > process and while the schema is not altered. It also requires that > the raw SQL be in memory at some stage. What's the benefit there? Isn't preparation time so minimal as to be insignificant? If the few milliseconds your program will save are significant you probably should be using something other than sql to store the data. - To unsubscribe, send email to [EMAIL PROTECTED] - - Stay in the know. Pulse on the new Yahoo.com. Check it out.
Re: [sqlite] pre-compiling prepared statements
Hi All, I know that we can use sqlite3_prepare, proabably my perception is wrong, when I say sqlite3_prepare I am thinking the opcodes which ever is necessary to run the query is created upon this call, and we can keep filling the various values by just resetting the prepared statements and re use it over and over, What I want to know is this whether the opcode generation will happen in compile time or when you make a call to sqlite3_prepare while running. If during running can I make some of the statements selectively to generate these opcodes during compile time itself. If any one can explain me correctly what sqlite3_prepare does apart from preparing the statement, and does prepare means generating the byte codes necessary. Thanks in advace, -Chetan. Jay Sprenkle <[EMAIL PROTECTED]> wrote: On 9/1/06, chetana bhargav wrote: > Hi, > > Is there any way to pre compile some of the prepared statements during > compile time. I am having 4 tables of which two tables doesn't create any > triggers/joins. I am basically trying to speed up the queries on these tables > (as they are most frequently used). I am looking for ways so that I can keep > them prepared always, not in memory though as that would be too much. Certainly! http://sqlite.org/capi3ref.html#sqlite3_prepare - To unsubscribe, send email to [EMAIL PROTECTED] - - Get your own web address for just $1.99/1st yr. We'll help. Yahoo! Small Business.
Re: [sqlite] pre-compiling prepared statements
Jay Sprenkle wrote: On 9/1/06, chetana bhargav <[EMAIL PROTECTED]> wrote: Hi, Is there any way to pre compile some of the prepared statements during compile time. I am having 4 tables of which two tables doesn't create any triggers/joins. I am basically trying to speed up the queries on these tables (as they are most frequently used). I am looking for ways so that I can keep them prepared always, not in memory though as that would be too much. Certainly! http://sqlite.org/capi3ref.html#sqlite3_prepare I believe that Dr Hipp has available a special version of Sqlite which stores prepared statements. It has restrictions which may make it unsuitable for general purpose applications, but could be the answer this user is looking for. For the benefit of the user, sqlite3_prepare compiles an Sqlite statement but the compilation is only valid for the life of the process and while the schema is not altered. It also requires that the raw SQL be in memory at some stage. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] pre-compiling prepared statements
Hi, Is there any way to pre compile some of the prepared statements during compile time. I am having 4 tables of which two tables doesn't create any triggers/joins. I am basically trying to speed up the queries on these tables (as they are most frequently used). I am looking for ways so that I can keep them prepared always, not in memory though as that would be too much. -Chetan. - Stay in the know. Pulse on the new Yahoo.com. Check it out.
