Re: [sqlite] SQLITE3 Prepare / Step
Got it. Thanks a million - Uma John Stanton <[EMAIL PROTECTED]> wrote: The prepare creates a virtual machine which can be rused. A useful way to implement Sqlite is to use prepare to compile all the SQL in the initialization phase of the program and then to execute the virutal machines using step. By compiling a SQL in advance you can ensure that the program will not fail in mid execution with an SQl error. Uma Krishnan wrote: > In SQLite3 one uses prepare/step to execute query. The question that I have > is, when your stepping yields no more rows, and one has to re-execute the > query, does one have to call the prepare statement again. If that's the case, > what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has > to reissue the query. > > In standard DB/JDBC parlance, one prepares (one time, unless recompiled), > executes, loops for next (/step) until all rows fetched, then closes. > Subsequently one can skip prepare and proceed to execute. > > Thanks in advance > > Uma > > > > > Uma Krishnan wrote: Yes. Makes sense (not to cache query results for > embedded apps). So what is cached. Just dirty pages? or are raw tables cached > when queried? > > Thanks > > Uma > > Scott Hess wrote: On 10/17/07, Trevor Talbot wrote: > >>On 10/17/07, Uma Krishnan wrote: >> >>>One other question, when a query is issued, does SQLite cache the results, >>>so that future queries can be processed off the cache (I think not) >> >>Like the "query cache" in some other databases? No. >> >>SQLite does have a cache of database pages, but they mimic what's on >>disk, not the results of a particular query. >> >>A query cache would not be very useful for an embedded database. If >>you're caching results, you might as well do it in the application's >>native form -- it's the same process after all. > > > To add another nail, the reason a query cache is often useful in > database servers is because you can usually share the cache across all > the front-ends. Since SQLite effectively lives inside the front-end, > this sharing goes away. Worse, any caching SQLite does is adding to > the memory footprint of the containing app (or, put another way, > stealing memory the app could use in other ways). > > -scott > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLITE3 Prepare / Step
Not usere if it will help, but here is what I do in my C++ code:
I have a wrapper on top of sqlite API.
The prepare function in my wrapper will
-either prepare the query
or
- simply reset it if it has already been prepared
For each query, I have a static instance of my wrapper that I use in my code.
Hre is the prepare function of my wrapper:
void SQLiteQuery::prepare( SQLiteDatabase *db, const AMD_Char *text){
_sqlStatement = text;
if ( _stmt ) {
// already prepared, just reset sqlite3_reset( _stmt );
sqlite3_clear_bindings( _stmt ); } else {// First parsing const
AMD_Char *dummy; AMD_SInt32 rc = sqlite3_prepare_v2( db->getDB(), text , -1,
&_stmt, &dummy );
if ( rc ) throw runtime_error( "Error when preparing " + _sqlStatement + "
- " + sqlite3_errmsg( db->getDB() ) + " " ); }
}
This way, my (static) queries get prepared only the first time they are used.
Best Regards.
Renaud
> Date: Wed, 17 Oct 2007 19:55:57 -0500> From: [EMAIL PROTECTED]> To:
> sqlite-users@sqlite.org> Subject: Re: [sqlite] SQLITE3 Prepare / Step> > The
> prepare creates a virtual machine which can be rused. A useful way > to
> implement Sqlite is to use prepare to compile all the SQL in the >
> initialization phase of the program and then to execute the virutal >
> machines using step.> > By compiling a SQL in advance you can ensure that the
> program will not > fail in mid execution with an SQl error.> > Uma Krishnan
> wrote:> > In SQLite3 one uses prepare/step to execute query. The question
> that I have is, when your stepping yields no more rows, and one has to
> re-execute the query, does one have to call the prepare statement again. If
> that's the case, what's the advantage of pre-compiling. If not, how does
> Sqlite3 knows it has to reissue the query.> > > > In standard DB/JDBC
> parlance, one prepares (one time, unless recompiled), executes, loops for
> next (/step) until all rows fetched, then closes. Subsequently one can skip
> prepare and proceed to execute.> > > > Thanks in advance> > > > Uma> > > > >
> > > > > > Uma Krishnan <[EMAIL PROTECTED]> wrote: Yes. Makes sense (not to
> cache query results for embedded apps). So what is cached. Just dirty pages?
> or are raw tables cached when queried?> > > > Thanks> > > > Uma> > > > Scott
> Hess wrote: On 10/17/07, Trevor Talbot wrote:> > > >>On 10/17/07, Uma
> Krishnan wrote:> >>> >>>One other question, when a query is issued, does
> SQLite cache the results, so that future queries can be processed off the
> cache (I think not)> >>> >>Like the "query cache" in some other databases?
> No.> >>> >>SQLite does have a cache of database pages, but they mimic what's
> on> >>disk, not the results of a particular query.> >>> >>A query cache would
> not be very useful for an embedded database. If> >>you're caching results,
> you might as well do it in the application's> >>native form -- it's the same
> process after all.> > > > > > To add another nail, the reason a query cache
> is often useful in> > database servers is because you can usually share the
> cache across all> > the front-ends. Since SQLite effectively lives inside the
> front-end,> > this sharing goes away. Worse, any caching SQLite does is
> adding to> > the memory footprint of the containing app (or, put another
> way,> > stealing memory the app could use in other ways).> > > > -scott> > >
> >> >
> ->
> To unsubscribe, send email to [EMAIL PROTECTED]>
> ->
>
_
Explore the seven wonders of the world
http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE
Re: [sqlite] SQLITE3 Prepare / Step
The prepare creates a virtual machine which can be rused. A useful way to implement Sqlite is to use prepare to compile all the SQL in the initialization phase of the program and then to execute the virutal machines using step. By compiling a SQL in advance you can ensure that the program will not fail in mid execution with an SQl error. Uma Krishnan wrote: In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query. In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute. Thanks in advance Uma Uma Krishnan <[EMAIL PROTECTED]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried? Thanks Uma Scott Hess wrote: On 10/17/07, Trevor Talbot wrote: On 10/17/07, Uma Krishnan wrote: One other question, when a query is issued, does SQLite cache the results, so that future queries can be processed off the cache (I think not) Like the "query cache" in some other databases? No. SQLite does have a cache of database pages, but they mimic what's on disk, not the results of a particular query. A query cache would not be very useful for an embedded database. If you're caching results, you might as well do it in the application's native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLITE3 Prepare / Step
Hi, Uma Krishnan wrote: In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query. You would use sqlite3_reset() or its equivalent in your wrapper's API. Regards, Eugene Wee - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE3 Prepare / Step
In SQLite3 one uses prepare/step to execute query. The question that I have is, when your stepping yields no more rows, and one has to re-execute the query, does one have to call the prepare statement again. If that's the case, what's the advantage of pre-compiling. If not, how does Sqlite3 knows it has to reissue the query. In standard DB/JDBC parlance, one prepares (one time, unless recompiled), executes, loops for next (/step) until all rows fetched, then closes. Subsequently one can skip prepare and proceed to execute. Thanks in advance Uma Uma Krishnan <[EMAIL PROTECTED]> wrote: Yes. Makes sense (not to cache query results for embedded apps). So what is cached. Just dirty pages? or are raw tables cached when queried? Thanks Uma Scott Hess wrote: On 10/17/07, Trevor Talbot wrote: > On 10/17/07, Uma Krishnan wrote: > > One other question, when a query is issued, does SQLite cache the results, > > so that future queries can be processed off the cache (I think not) > > Like the "query cache" in some other databases? No. > > SQLite does have a cache of database pages, but they mimic what's on > disk, not the results of a particular query. > > A query cache would not be very useful for an embedded database. If > you're caching results, you might as well do it in the application's > native form -- it's the same process after all. To add another nail, the reason a query cache is often useful in database servers is because you can usually share the cache across all the front-ends. Since SQLite effectively lives inside the front-end, this sharing goes away. Worse, any caching SQLite does is adding to the memory footprint of the containing app (or, put another way, stealing memory the app could use in other ways). -scott - To unsubscribe, send email to [EMAIL PROTECTED] -
