sorry, my reaction is at the bottom ;) On 25-06-2011 13:59, Black, Michael (IS) wrote: > Ummmm....caching? > > > > Try doing your first select last and see what happens. I'm betting your > first "select min(*) from test" will be a lot slower. > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > ________________________________ > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Luuk [luu...@gmail.com] > Sent: Saturday, June 25, 2011 6:25 AM > To: General Discussion of SQLite Database > Subject: EXT :[sqlite] question about sqlite aggregate funcitons > > I create a test database with almost 10 milion rows. > I'm surprised to see that the first SELECT below is much slower than the > sum of the next three SELECTs. > > Can anyone give a hint why this is the case? > > SQLite version 3.7.2 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> .timer on > sqlite> .schema test > CREATE TABLE test (i integer primary key); > sqlite> select min(i), max(i), count(i) from test; > 1|9999999|9999999 > CPU Time: user 4.508429 sys 0.265202 > sqlite> select min(i) from test; > 1 > CPU Time: user 0.000000 sys 0.000000 > sqlite> select max(i) from test; > 9999999 > CPU Time: user 0.000000 sys 0.000000 > sqlite> select count(i) from test; > 9999999 > CPU Time: user 1.497610 sys 0.390002 > sqlite> > > -- > Luuk
Giving the SELECTs i a different order does not improve thing (much). BTW, i'm doing this on a Windows 7 machine, and will test what happens on a Linux machine too, to see if there's difference. SQLite version 3.7.2 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> .timer on sqlite> select min(i) from test; 1 CPU Time: user 0.000000 sys 0.000000 sqlite> select max(i) from test; 9999999 CPU Time: user 0.000000 sys 0.000000 sqlite> select count(i) from test; 9999999 CPU Time: user 1.560010 sys 0.296402 sqlite> select min(i),max(i),count(i) from test; 1|9999999|9999999 CPU Time: user 4.430428 sys 0.312002 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
