I'm seeing a similar speed different with the 3X performance difference: [EMAIL PROTECTED]:~/work$ 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"' + uniq + sort + uniq + sqlite3 sample.db 'select foo from bar ; ' + wc -l 1100000 real 7.05 user 8.71 sys 0.36 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 1100000 real 21.53 user 21.03 sys 0.24
SQLite version 3.6.0 Linux carto 2.6.15-1-em64t-p4-smp #2 SMP Tue Mar 7 08:19:39 UTC 2006 x86_64 GNU/Linux -Steve Robert Citek wrote: > 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 > _______________________________________________ > 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
