Did you do multiple runs and average? And how are you measuring time? Did you
do buffered reads?
And you're MB/sec isn't accurate for the count() test. I think it was already
said that count() doesn't read the entire record.
The SQLite numbers are due to SQLite caching. If you make page size
On Sun, Feb 27, 2011 at 3:11 PM, Max Vlasov wrote:
> Hmm, yesterday something struck me I can do similar tests on an Asus T91MT
> having SSD as the storage. I got similar timings to Greg's. So it seems like
> page size is a very sensitive parameter for solid state drives. Looks like
> having the p
On Sat, Feb 26, 2011 at 1:03 AM, Greg Barker wrote:
> harddrive. Can you confirm this?
>
> > Giving this, if the fastest is 3.6 seconds, you have a very fast
> I can confirm this, my tests were run on a machine that uses a solid state
> drive.
>
>
Hmm, yesterday something struck me I can do simi
On 25 Feb 2011, at 10:03pm, Greg Barker wrote:
> Thanks for the responses guys.
>
>> I would never have any table with 150 columns. It should be possible to
>> keep the schema for your table in your head.
>
> Unfortunately those are the cards I have been dealt. The columns are just
> buckets o
Thanks for the responses guys.
> I would never have any table with 150 columns. It should be possible to
keep the schema for your table in your head.
Unfortunately those are the cards I have been dealt. The columns are just
buckets of data (bucket1, bucket2, bucket3, etc). Each bucket could be 1
On Fri, Feb 25, 2011 at 1:51 AM, Greg Barker wrote:
> I ran my tests again, this time setting the cache_size based on the
> page_size. For some reason I wasn't able to replicate the timing I got
> before for the 1024 page size (40.2 seconds) which is really confusing me,
> since I was careful to
On 24 Feb 2011, at 10:51pm, Greg Barker wrote:
> What do
> you do if there could be anywhere between 30-150 columns?
I would never have any table with 150 columns. It should be possible to keep
the schema for your table in your head.
> Optimizing performance
> for an application where both th
I ran my tests again, this time setting the cache_size based on the
page_size. For some reason I wasn't able to replicate the timing I got
before for the 1024 page size (40.2 seconds) which is really confusing me,
since I was careful to make sure nothing was running in the background while
I was te
Average payload per entry for my test table was 409.00. Sounds about right
since the db has 4 columns and each is filled with a random string of length
100.
I've uploaded the full output from the sqlite3_analyzer for a few different
page sizes:
http://fletchowns.net/files/1024-analyzer.txt
http:/
t; To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> Is the record size you refer to here the same as the "Average payload per
> entry" that sqlite3_analyzer determines for me?
>
> On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov wrote:
&g
Yes, Greg, please post this value for this table you use in count query
Max
On Wed, Feb 23, 2011 at 9:58 PM, Greg Barker wrote:
> Is the record size you refer to here the same as the "Average payload per
> entry" that sqlite3_analyzer determines for me?
>
> On Wed, Feb 23, 2011 at 5:09 AM, Max
Is the record size you refer to here the same as the "Average payload per
entry" that sqlite3_analyzer determines for me?
On Wed, Feb 23, 2011 at 5:09 AM, Max Vlasov wrote:
> Greg, you should also take the record size into account. My hypothesis is
> that if your record is comparatively small (s
2011 10:47:03 -0500
>> From: pri...@gmail.com
>> To: t...@djii.com; sqlite-users@sqlite.org
>> Subject: Re: [sqlite] COUNT() extremely slow first time!
>>
>> The SQLite cache size is in pages (2000 by default), so by increasing
>> the page size 8x, you're
characters):
MyID|MyText
With a page size of 4096, will SQLite put ~200 rows in one page?
> Date: Wed, 23 Feb 2011 10:47:03 -0500
> From: pri...@gmail.com
> To: t...@djii.com; sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> The SQLit
The SQLite cache size is in pages (2000 by default), so by increasing
the page size 8x, you're also increasing the cache size and memory
requirements by 8x. Not saying it's a bad thing, just something to be
aware of.
If you want to compare 1K and 8K page size and only compare the effect
page size
On Wed, Feb 23, 2011 at 03:10:02PM +0100, Sven L scratched on the wall:
>
> Does this trick work on the primary key? If not, why?
Yes, all the time.
Defining a column as a PK automatically creates a UNIQUE index
over that column. The only exception is when the column is an
INTEGER PRI
Hello Greg,
I found this to be the case too. The difference between 1K and 8K is
staggering. I default all my windows DB's to 8K now.
Tuesday, February 22, 2011, 1:59:29 PM, you wrote:
GB> I'm currently dealing with a similar issue. I've found that the page_size
GB> PRAGMA setting can have a dr
Does this trick work on the primary key? If not, why?
> From: max.vla...@gmail.com
> Date: Wed, 23 Feb 2011 16:09:04 +0300
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker wrote:
&g
On Tue, Feb 22, 2011 at 9:59 PM, Greg Barker wrote:
> I'm currently dealing with a similar issue. I've found that the page_size
> PRAGMA setting can have a dramatic effect on how long it takes to "warm up"
> the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column)
> takes 40.2 se
g
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
> I'm currently dealing with a similar issue. I've found that the page_size
> PRAGMA setting can have a dramatic effect on how long it takes to "warm up"
> the table. On Windows 7, with page_
I'm currently dealing with a similar issue. I've found that the page_size
PRAGMA setting can have a dramatic effect on how long it takes to "warm up"
the table. On Windows 7, with page_size=1024, a SELECT COUNT(last_column)
takes 40.2 seconds. page_size=4096 takes 15.5 seconds. page_size=8192 takes
On Mon, Feb 21, 2011 at 11:35 AM, Jim Wilcoxson wrote:
> On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote:
>>
>> 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!
>
> Sorry, dunno for Windows.
lav...@bigfraud.org
> Date: Mon, 21 Feb 2011 16:56:01 +
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] COUNT() extremely slow first time!
>
>
> On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote:
>
> > On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote:
> >>
>
On 21 Feb 2011, at 4:35pm, Jim Wilcoxson wrote:
> On Mon, Feb 21, 2011 at 11:05 AM, Sven L wrote:
>>
>> 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!
>
> Sorry, dunno for Windows. On M
is just what happens on my system (Mac OSX),
and may have nothing at all to do with the performance on yours.
Jim
>
> Thanks again!
>
>
>> Date: Mon, 21 Feb 2011 10:17:03 -0500
>> From: pri...@gmail.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite]
On Mon, Feb 21, 2011 at 03:37:50PM +0100, Sven L scratched on the wall:
> I've learnt that COUNT(*) is slower than COUNT(ID),
That's usually not true.
> since * means the engine has to traverse all columns
Actually, count(*) is the one case when the engine does *not* need to
traverse any
>
>> > 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:
&g
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
> cou
t; sqlite> EXPLAIN QUERY PLAN SELECT COUNT(ItemID) FROM Items;
> > 0|0|0|SCAN TABLE Items (~100 rows)
> > sqlite> EXPLAIN QUERY PLAN SELECT COUNT(*) FROM Items;
> > sqlite>
> >
> > I would expect an index scan on the first statement. The second statement
>
SCAN TABLE Items (~100 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
&g
!
Thanks for your help!
> From: slav...@bigfraud.org
> Date: Mon, 21 Feb 2011 14:24:50 +
> 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
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 3.7.5.
I have a table (13 columns with 1 primary key, 1 index on a date column, 1
UNIQUE-constraint on two columns).
I insert ~130 rows, 1000 at a time (transaction-wise).
I close the database, reopen it and immediately perform a COUNT-operation:
SELECT COUNT(ItemID) FROM Items;
T
33 matches
Mail list logo
