Re: [sqlite] Performance problem with count(*) calculation

[Alexey Pechnikov]

Hello!

On Thursday 01 April 2010 18:04:10 Adam DeVita wrote:
> How does
> $ time sqlite3 test32k.db "select count(1) from role_exist"
> perform?

Equal to count(*).

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem with count(*) calculation

[Jay A. Kreibich]

On Thu, Apr 01, 2010 at 10:44:51AM -0400, Pavel Ivanov scratched on the wall:
> > So 58s for count of all records! The count(*) for all records may use
> > the counter from primary key b-tree, is't it?
> 
> What does this mean? I believe there's no any kind of counters in
> b-tree. If you meant counter from auto-increment key then how about
> gaps in the middle?

  If a statement is in the form "SELECT count(*) FROM " there is
  an optimization that short-cuts the standard aggregate functions and
  gets the leaf count directly from a BTree.

  However, I believe you are correct that the BTree does not keep a
  global leaf count in the header, so the whole tree must be loaded and
  walked.  The optimization will favor a low-column index BTree over the
  table root BTree, since this will typically require less I/O.  Time is
  also saved in short-cutting the column loading and aggregation functions
  (countStep() and countFinalize()).  But in the end, it looks like
  most of the tree is still loaded.

  See the function isSimpleCount() in the source for more specifics.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance problem with count(*) calculation

[Pavel Ivanov]

> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?

What does this mean? I believe there's no any kind of counters in
b-tree. If you meant counter from auto-increment key then how about
gaps in the middle?


Pavel

On Thu, Apr 1, 2010 at 5:52 AM, Alexey Pechnikov  wrote:
> Hello!
>
> $ time sqlite3 test32k.db "select count(*) from role_exist"
> 1250
>
> real    0m58.908s
> user    0m0.056s
> sys     0m0.864s
>
> $ sqlite3 test32k.db
> SQLite version 3.6.23
> sqlite> .schema role_exist
> CREATE TABLE role_exist (
>  id INTEGER PRIMARY KEY,
>  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
> );
>
> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?
> ==
> HARDWARE:
>
> $ grep CPU /proc/cpuinfo
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
> model name      : Intel(R) Core(TM)2 Quad CPU    Q6700  @ 2.66GHz
>
> $ free
>             total       used       free     shared    buffers     cached
> Mem:       8310892    7552880     758012          0      29496    6667708
> -/+ buffers/cache:     855676    7455216
> Swap:      3903784     301240    3602544
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> 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] Performance problem with count(*) calculation

[Adam DeVita]

How does
$ time sqlite3 test32k.db "select count(1) from role_exist"

perform?

On Thu, Apr 1, 2010 at 5:52 AM, Alexey Pechnikov wrote:

> Hello!
>
> $ time sqlite3 test32k.db "select count(*) from role_exist"
> 1250
>
> real0m58.908s
> user0m0.056s
> sys 0m0.864s
>
> $ sqlite3 test32k.db
> SQLite version 3.6.23
> sqlite> .schema role_exist
> CREATE TABLE role_exist (
>  id INTEGER PRIMARY KEY,
>  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
> );
>
> So 58s for count of all records! The count(*) for all records may use
> the counter from primary key b-tree, is't it?
> ==
> HARDWARE:
>
> $ grep CPU /proc/cpuinfo
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
> model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
>
> $ free
> total   used   free sharedbuffers cached
> Mem:   83108927552880 758012  0  294966667708
> -/+ buffers/cache: 8556767455216
> Swap:  3903784 3012403602544
>
> Best regards, Alexey Pechnikov.
> http://pechnikov.tel/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
VerifEye Technologies Inc.
905-948-0015x245
7100 Warden Ave, Unit 3
Markham ON, L3R 8B5
Canada
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Performance problem with count(*) calculation

[Alexey Pechnikov]

Hello!

$ time sqlite3 test32k.db "select count(*) from role_exist"
1250

real0m58.908s
user0m0.056s
sys 0m0.864s

$ sqlite3 test32k.db
SQLite version 3.6.23
sqlite> .schema role_exist
CREATE TABLE role_exist (
  id INTEGER PRIMARY KEY,
  uid BLOB NOT NULL DEFAULT (randomblob(16)) UNIQUE ON CONFLICT REPLACE
);

So 58s for count of all records! The count(*) for all records may use
the counter from primary key b-tree, is't it?
==
HARDWARE:

$ grep CPU /proc/cpuinfo 
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz
model name  : Intel(R) Core(TM)2 Quad CPUQ6700  @ 2.66GHz

$ free
 total   used   free sharedbuffers cached
Mem:   83108927552880 758012  0  294966667708
-/+ buffers/cache: 8556767455216
Swap:  3903784 3012403602544

Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users