Using sqlite 3.5.9: My results: Piped shell real 27.91 user 27.21 sys 0.78 DB : real 29.59 user 28.57 sys 0.96
Perhaps this is an issue with 3.6.0 ? Piped: real 28.44 user 27.85 sys 0.86 DB: real 74.24 user 72.96 sys 0.94 I compliled sqlite 3.6.0 from the full source. When running both seem to consume approximately the same amount of ram. Ken --- On Tue, 7/29/08, Stephen Woodbridge <[EMAIL PROTECTED]> wrote: From: Stephen Woodbridge <[EMAIL PROTECTED]> Subject: Re: [sqlite] db vs shell To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org> Date: Tuesday, July 29, 2008, 10:35 AM 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
