2008/7/29 Robert Citek <[EMAIL PROTECTED]>: > On Tue, Jul 29, 2008 at 2:35 AM, <[EMAIL PROTECTED]> wrote: >> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote: >>> $ sqlite3 -version >>> 3.4.2 >> >> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell >> pipe. Could you tell us more about the contents of your database? > > The column contains a list of text items. This script demonstrates > the phenomenon: > > true && ( set -x > sqlite3 sample.db 'create table bar (foo text)' > seq -w 1 2000000 | sed 's/^/id/' > list.txt > sqlite3 sample.db '.imp "list.txt" "bar"' > time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc > -l > time -p sqlite3 sample.db 'select count(distinct foo) from bar ; ' > ) > > Output: > > + sqlite3 sample.db 'create table bar (foo text)' > + seq -w 1 2000000 > + sed 's/^/id/' > + sqlite3 sample.db '.imp "list.txt" "bar"' > + sqlite3 sample.db 'select foo from bar ; ' > + uniq > + sort > + uniq > + wc -l > 2000000 > real 3.25 > user 3.71 > sys 0.47 > + sqlite3 sample.db 'select count(distinct foo) from bar ; ' > 2000000 > real 22.48 > user 20.98 > sys 0.28 > > Regards, > - Robert
Hi Robert, on my XP machine I get: > sqlite3 -version 3.6.0 > true && ( set -x > sqlite3 sample.db 'create table bar (foo text)' > seq -w 1 2000000 | sed 's/^/id/' > list.txt > sqlite3 sample.db '.imp "list.txt" "bar"' > time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc > -l > time -p sqlite3 sample.db 'select count(distinct foo) from bar ; ' > ) + sqlite3 sample.db 'create table bar (foo text)' + seq -w 1 2000000 + sed 's/^/id/' + sqlite3 sample.db '.imp "list.txt" "bar"' + sqlite3 sample.db 'select foo from bar ; ' + uniq + sort + uniq + wc -l 2000000 real 12.67 user 10.63 sys 3.91 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 2000000 real 13.59 user 0.01 sys 0.01 > alias sqlite3=sjd_old_sqlite3.exe > sqlite3 -version 3.4.2 > time sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l 2000000 real 0m12.883s user 0m10.870s sys 0m3.856s > time sqlite3 sample.db 'select count( distinct foo ) from bar ;' 2000000 real 0m14.888s user 0m0.015s sys 0m0.015s -Nothing like the discrepancy you are seeing... Rgds, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
