Thank you for your detailed explanation!
First, can you please tell me how to purge the cache in Windows 7? This could
be very useful for my tests!
I'm quite sure my database itself is not fragmented, since I have only inserted
data. The file system is in good shape too; Windows reports 0% fragmentation.
Perhaps there is some other bottleneck, like disk performance in general (this
is a 5400 rpm)? Or antivirus-related CPU holds? (I did turn real-time scanning
off though.)
I have even turned Windows Search off (and got a 20% performance gain!). My
32-bit application is running under Windows 7 (64-bit). Could WOW64 have
something to do with this performance issue?
The size of the database is 1,98 GB, with 1.5M rows. Couldn't it be that the
table scan simply is darn slow for huge tables?
In an ideal world the COUNT() would be performed on the primary key in RAM.
That's not possible? :P
Thanks again!
> Date: Mon, 21 Feb 2011 10:17:03 -0500
> From: pri...@gmail.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> This is a common issue on the mailing list. The first time you do
> count(*), SQLite (actually your OS) has to load data into memory.
> Most OS's will keep the file in a buffer cache, so the 2nd count(*)
> doesn't have to read from disk.
>
> Here's a timing from my own system, after a purge command to clear the
> buffer cache:
>
> $ time sqlite3 hb.db 'select count(*) from logs'
> -- Loading resources from /Users/jim/.sqliterc
> count(*)
> ----------
> 734909
>
> real 0m0.580s
> user 0m0.190s
> sys 0m0.034s
>
> Same command again, with the file cached:
>
> $ time sqlite3 hb.db 'select count(*) from logs'
> -- Loading resources from /Users/jim/.sqliterc
> count(*)
> ----------
> 734909
>
> real 0m0.189s
> user 0m0.165s
> sys 0m0.019s
>
> This time is consistent no matter how many times I run it, because the
> file is still cached. Doing a purge command to clear the cache and
> re-running the query, we get:
>
> $ purge
> $ time sqlite3 hb.db 'select count(*) from logs'
> -- Loading resources from /Users/jim/.sqliterc
> count(*)
> ----------
> 734909
>
> real 0m0.427s
> user 0m0.175s
> sys 0m0.024s
>
> On my system, there is not a huge difference, but it is consistent.
> Now, if you have a fragmented file system, you will see a much larger
> difference. There are many posts on the mailing list about both file
> system fragmentation and logical fragmentation within the SQLite file
> itself. Your first count(*) is subject to these fragmentation
> effects, while your 2nd usually is not, because the file is in memory.
>
> Some people on the list believe fragmentation is an unimportant detail
> you shouldn't worry about, because you have little control over it.
> That may be true, but it's useful to understand how it can affect
> performance. I think you are seeing this first hand.
>
> Jim
> --
> HashBackup: easy onsite and offsite Unix backup
> http://www.hashbackup.com
>
>
>
> On Mon, Feb 21, 2011 at 9:37 AM, Sven L <larvpo...@hotmail.se> wrote:
> >
> > Same result :(
> > Note that I have compiled SQLite with the following switches:
> > SQLITE_ENABLE_STAT2
> > SQLITE_THREADSAFE=2
> >
> > I've learnt that COUNT(*) is slower than COUNT(ID), since * means the
> > engine has to traverse all columns and it might even return another value
> > if there are NULL-values...
> >
> > Also, this is quite interesting:
> >
> > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(ItemID) FROM Items;
> > 0|0|0|SCAN TABLE Items (~1000000 rows)
> > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items;
> > sqlite>
> >
> > I would expect an index scan on the first statement. The second statement
> > tells me nada?!
> >
> > Thanks for your help!
> >
> >
> >> From: slav...@bigfraud.org
> >> Date: Mon, 21 Feb 2011 14:24:50 +0000
> >> To: sqlite-users@sqlite.org
> >> Subject: Re: [sqlite] COUNT() extremely slow first time!
> >>
> >>
> >> On 21 Feb 2011, at 2:23pm, Sven L wrote:
> >>
> >> > SELECT COUNT(ItemID) FROM Items;
> >> >
> >> > This takes around 40 seconds the first time! WHY?!
> >>
> >> Try again, doing everything identically except that instead of the above
> >> line use
> >>
> >> SELECT COUNT(*) FROM Items;
> >>
> >> Simon.
> >> _______________________________________________
> >> 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-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
