Re: [sqlite] Sqlite aggregate cold/hot boot performance

2013-01-23 Thread Simon Slavin 

On 23 Jan 2013, at 9:01am, Pierre Chatelier  wrote:

> [what I do not understand]
> I perform a computer cold boot, launch my app, opens a doc, perform the
> query. The sqlite3_step() takes some time (a few seconds).It's ok, the
> query is rather complex.
> Now, I close my doc, reopens it. The same query performs very fast.
> I close my app, restart it, open the doc, perform the query, and one
> again it goes very fast.
> I stop the computer, restart it, redo the above, and that time, the
> query is slow.

What you describe is typical of a computer with a slow hard disk and lots of 
file cache space.  The first time a file is needed it has to be read from hard 
disk which is slow.  After that the data is already in (cache) memory and 
access to that is fast.  Operating systems these days do not expect to see the 
computer rebooted even once a week, so slowing the computer up only after a 
reboot isn't a problem.

There is one complicating factor under Windows which is that Windows makes 
special efforts to cache files with certain extensions on the filename.  And 
these actually make sqlite slower.  So pick a file extension for your databases 
which is something obviously unusual (.sqlite, .s, etc..) rather than one which 
Windows may think it understands (.db).  However I don't think that this is 
anything to do with the problem you're reporting.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite aggregate cold/hot boot performance

2013-01-23 Thread Pierre Chatelier 

Hi,


Is there some global cache retained by sqlite3.dll that makes my query faster ? 
Could it be rather related to some hard disk cache ?

SQLite uses normal file accesses, so the operating system will try to cache the 
data.

OK


And how many GB is the system using for file caching?  :)

I don't know ! There are so many levels of caching that I do not know how much 
can be expected at that level.

Regards,

Pierre Chatelier


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite aggregate cold/hot boot performance

2013-01-23 Thread Clemens Ladisch 
Pierre Chatelier wrote:
> Is there some global cache retained by sqlite3.dll that makes my query
> faster ? Could it be rather related to some hard disk cache ?

SQLite uses normal file accesses, so the operating system will try to
cache the data.

> (please note that my database files where the query is done are about
> 150MB)

And how many GB is the system using for file caching?  :)


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite aggregate cold/hot boot performance

2013-01-23 Thread Pierre Chatelier 

Hello,

I can observe a behaviour that I do not understand.

[context]
I have an application that links whith sqlite3.dll. I can open a
document, and perform a query on it. That query is using an aggregate
function, that I have created myself using the callbacks like
sqlite_aggr_indexset_step...
It works very well.

[what I do not understand]
I perform a computer cold boot, launch my app, opens a doc, perform the
query. The sqlite3_step() takes some time (a few seconds).It's ok, the
query is rather complex.
Now, I close my doc, reopens it. The same query performs very fast.
I close my app, restart it, open the doc, perform the query, and one
again it goes very fast.
I stop the computer, restart it, redo the above, and that time, the
query is slow.

Is there some global cache retained by sqlite3.dll that makes my query
faster ? Could it be rather related to some hard disk cache ? (please
note that my database files where the query is done are about 150MB)
Any hint ?

Regards,

Pierre Chatelier


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users