Re: [sqlite] db vs shell
Careful when using time. The bash built-in called time times 1 shell statement (including pipes). The binary in /usr/bin/time only times the command given - it does not span pipes. [EMAIL PROTECTED] wrote: > On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote: > >> Are you sure time ignores everything after the pipe? >> > > Seems to depend on shell version - when I tested it here it definitely > ignored everything after. Yours seems to do the right thing, which makes > your sqlite issue an interesting find indeed. > > Cheers, Peter > ___ > 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] db vs shell
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 200 | 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 200 + sed 's/^/id/' + sqlite3 sample.db '.imp "list.txt" "bar"' + uniq + sort + uniq + sqlite3 sample.db 'select foo from bar ; ' + wc -l 110 real 7.05 user 8.71 sys 0.36 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 110 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 200 | 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 200 > + sed 's/^/id/' > + sqlite3 sample.db '.imp "list.txt" "bar"' > + sqlite3 sample.db 'select foo from bar ; ' > + uniq > + sort > + uniq > + wc -l > 200 > real 3.25 > user 3.71 > sys 0.47 > + sqlite3 sample.db 'select count(distinct foo) from bar ; ' > 200 > 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
Re: [sqlite] db vs shell
Do things improve any if you increase the temporary cache size? Compile with -DSQLITE_DEFAULT_TEMP_CACHE=100 or something? How much memory does the [sort] process consume in the shell version? What percentage of records are being trimmed by the first [uniq] in the pipeline? Dan. On Jul 29, 2008, at 1:26 PM, Robert Citek wrote: > Was doing some DB operations and felt they were going slower than they > should. So I did this quick test: > > $ time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | > uniq | wc -l > 209 > real 5.64 > user 5.36 > sys 1.51 > > $ time -p sqlite3 sample.db 'select count(distinct foo) from bar ; ' > 209 > real 29.71 > user 26.09 > sys 1.32 > > Why the difference in time? > What can I do to make the DB operate closer to the times within the > shell? > > 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
Re: [sqlite] db vs shell
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 200 | 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 200 + sed 's/^/id/' + sqlite3 sample.db '.imp "list.txt" "bar"' + uniq + sort + uniq + sqlite3 sample.db 'select foo from bar ; ' + wc -l 110 real 7.05 user 8.71 sys 0.36 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 110 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 200 | 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 200 > + sed 's/^/id/' > + sqlite3 sample.db '.imp "list.txt" "bar"' > + sqlite3 sample.db 'select foo from bar ; ' > + uniq > + sort > + uniq > + wc -l > 200 > real 3.25 > user 3.71 > sys 0.47 > + sqlite3 sample.db 'select count(distinct foo) from bar ; ' > 200 > 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
Re: [sqlite] db vs shell
On Tue, Jul 29, 2008 at 4:25 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote: >> real 3.25 > .. >> real 22.48 > > I'm seeing the second being twice as -fast- as the first one here, still. I don't follow. 22/3 ~ 7. Or do you mean when you run the same script on your machine? > How many CPU cores are in your testing machine? Parallel execution > -might- explain the difference. Tried this on two different machine, both dual cores. Same sqlite3 version but with slightly different kernels (2.6.22 vs 2.6.24). Similar results: + sqlite3 sample.db 'select foo from bar ; ' + uniq + sort + uniq + wc -l 200 real 4.33 user 5.01 sys 0.66 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 200 real 29.67 user 29.12 sys 0.43 Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
On Tue, Jul 29, 2008 at 03:27:20AM -0500, Robert Citek wrote: > real 3.25 .. > real 22.48 I'm seeing the second being twice as -fast- as the first one here, still. How many CPU cores are in your testing machine? Parallel execution -might- explain the difference. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
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 200 | 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 200 > + sed 's/^/id/' > + sqlite3 sample.db '.imp "list.txt" "bar"' > + sqlite3 sample.db 'select foo from bar ; ' > + uniq > + sort > + uniq > + wc -l > 200 > real 3.25 > user 3.71 > sys 0.47 > + sqlite3 sample.db 'select count(distinct foo) from bar ; ' > 200 > 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 200 | 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 200 + sed 's/^/id/' + sqlite3 sample.db '.imp "list.txt" "bar"' + sqlite3 sample.db 'select foo from bar ; ' + uniq + sort + uniq + wc -l 200 real 12.67 user 10.63 sys 3.91 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 200 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 200 real0m12.883s user0m10.870s sys 0m3.856s > time sqlite3 sample.db 'select count( distinct foo ) from bar ;' 200 real0m14.888s user0m0.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
Re: [sqlite] db vs shell
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 200 | 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 200 + sed 's/^/id/' + sqlite3 sample.db '.imp "list.txt" "bar"' + sqlite3 sample.db 'select foo from bar ; ' + uniq + sort + uniq + wc -l 200 real 3.25 user 3.71 sys 0.47 + sqlite3 sample.db 'select count(distinct foo) from bar ; ' 200 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
Re: [sqlite] db vs shell
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? Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
On Tue, Jul 29, 2008 at 2:23 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote: >> Are you sure time ignores everything after the pipe? > > Seems to depend on shell version - when I tested it here it definitely > ignored everything after. Yours seems to do the right thing, which makes > your sqlite issue an interesting find indeed. Some more info which may help: $ sqlite3 -version 3.4.2 $ uname -a Linux Ubuntu804 2.6.24-19-generic #1 SMP Fri Jul 11 23:41:49 UTC 2008 i686 GNU/Linux $ echo $BASH_VERSION 3.2.39(1)-release Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
On Tue, Jul 29, 2008 at 02:15:54AM -0500, Robert Citek wrote: > Are you sure time ignores everything after the pipe? Seems to depend on shell version - when I tested it here it definitely ignored everything after. Yours seems to do the right thing, which makes your sqlite issue an interesting find indeed. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
On Tue, Jul 29, 2008 at 1:31 AM, <[EMAIL PROTECTED]> wrote: > On Tue, Jul 29, 2008 at 01:26:54AM -0500, Robert Citek wrote: >> Why the difference in time? > > Your first test is only measuring how long sqlite needs to 'select foo from > bar'; > all the commands after the pipe are ignored by 'time'. Are you sure time ignores everything after the pipe? $ time -p echo | uniq | sort | uniq | wc -l 1 real 0.00 user 0.00 sys 0.00 $ time -p echo | uniq | sort | uniq | wc -l | sleep 10 real 10.00 user 0.00 sys 0.00 > Try this: time -p sh -c "sqlite3 sample.db 'select foo from bar ; ' | uniq | > sort | uniq | wc -l" $ time -p sh -c "sqlite3 refseq.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l" 209 real 5.76 user 5.35 sys 1.61 Regards, - Robert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] db vs shell
On Tue, Jul 29, 2008 at 01:26:54AM -0500, Robert Citek wrote: > Why the difference in time? Your first test is only measuring how long sqlite needs to 'select foo from bar'; all the commands after the pipe are ignored by 'time'. Try this: time -p sh -c "sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l" Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
