Re: [sqlite] Memory Usage/ Drawbacks of Statements
Hi Eric,
On 24/4/2012 10:03 PM, Eric Minbiole wrote:
Similar to Pavel's suggestion, our implementation maintains a simple
cache of prepared statements, keyed by the SQL query that created
them. For example:
pStatement = Cache.GetQuery("SELECT * FROM xyz");
would return the cached statement if the query had been seen before,
or would auto-create it, if needed.
This (attempts to) give the best of both worlds: Statements are only
created / prepared once, improving run-time performance. In addition,
the programmers aren't burdened with trying to figure out up-front
which queries might / might not be used later, simplifying
development.
This approach has worked well in our application.
Thanks! This is similar to what we were thinking... it's good to know
that you're successfully using it.
Best Regards,
Mohit.
25/4/2012 | 12:01 AM.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory Usage/ Drawbacks of Statements
Similar to Pavel's suggestion, our implementation maintains a simple
cache of prepared statements, keyed by the SQL query that created
them. For example:
pStatement = Cache.GetQuery("SELECT * FROM xyz");
would return the cached statement if the query had been seen before,
or would auto-create it, if needed.
This (attempts to) give the best of both worlds: Statements are only
created / prepared once, improving run-time performance. In addition,
the programmers aren't burdened with trying to figure out up-front
which queries might / might not be used later, simplifying
development.
This approach has worked well in our application.
On 4/23/12, Mohit Sindhwani wrote:
> Thanks Pavel,
>
> That gives me something new to do with SQLite over the next few weeks.
>
> On 23/4/2012 8:47 PM, Pavel Ivanov wrote:
>>> 1. Do statements do any thing that would require a lot of memory to be
>>> maintained?
>> No, they don't need a lot of memory, but still some memory is used. So
>> if you have like thousands of statements you should worry about this.
>> If you have 20 or 30 statements your database cache will likely
>> consume much more memory, so don't worry.
>>
>>> 2. Are there any known drawbacks of doing this?
>> Preparing all statements takes some time which adds to startup time of
>> your application. Also you could prepare some statements which won't
>> be used later. If those are not problems for you then preparing all
>> statements at startup is a way to go.
>>
>>> 3. Finally, if sqlite3_reset is called multiple times before a bind, is
>>> there a problem?
>> No, there's no problem in here.
>>
>>
>> Pavel
>>
>
> ___
> 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] Memory Usage/ Drawbacks of Statements
Thanks Pavel, That gives me something new to do with SQLite over the next few weeks. On 23/4/2012 8:47 PM, Pavel Ivanov wrote: 1. Do statements do any thing that would require a lot of memory to be maintained? No, they don't need a lot of memory, but still some memory is used. So if you have like thousands of statements you should worry about this. If you have 20 or 30 statements your database cache will likely consume much more memory, so don't worry. 2. Are there any known drawbacks of doing this? Preparing all statements takes some time which adds to startup time of your application. Also you could prepare some statements which won't be used later. If those are not problems for you then preparing all statements at startup is a way to go. 3. Finally, if sqlite3_reset is called multiple times before a bind, is there a problem? No, there's no problem in here. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Memory Usage/ Drawbacks of Statements
> 1. Do statements do any thing that would require a lot of memory to be > maintained? No, they don't need a lot of memory, but still some memory is used. So if you have like thousands of statements you should worry about this. If you have 20 or 30 statements your database cache will likely consume much more memory, so don't worry. > 2. Are there any known drawbacks of doing this? Preparing all statements takes some time which adds to startup time of your application. Also you could prepare some statements which won't be used later. If those are not problems for you then preparing all statements at startup is a way to go. > 3. Finally, if sqlite3_reset is called multiple times before a bind, is > there a problem? No, there's no problem in here. Pavel On Mon, Apr 23, 2012 at 8:02 AM, Mohit Sindhwani wrote: > Hi, our system does fairly predictable queries when it runs. A number of > modules all access data using a handful of queries of each. We open the > database at the start and close it at the end of the program. > > Each query follows the usual pattern of prepare - bind - step - reset - > (eventually) finalize. > > I was wondering if there is any known drawback in creating statements > up-front when the system is started and use them as and when they are > needed. > > 1. Do statements do any thing that would require a lot of memory to be > maintained? > > 2. Are there any known drawbacks of doing this? > > 3. Finally, if sqlite3_reset is called multiple times before a bind, is > there a problem? > > Thanks, > Mohit. > > ___ > 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
[sqlite] Memory Usage/ Drawbacks of Statements
Hi, our system does fairly predictable queries when it runs. A number of modules all access data using a handful of queries of each. We open the database at the start and close it at the end of the program. Each query follows the usual pattern of prepare - bind - step - reset - (eventually) finalize. I was wondering if there is any known drawback in creating statements up-front when the system is started and use them as and when they are needed. 1. Do statements do any thing that would require a lot of memory to be maintained? 2. Are there any known drawbacks of doing this? 3. Finally, if sqlite3_reset is called multiple times before a bind, is there a problem? Thanks, Mohit. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
