Re: [sqlite] any command to find last rowid in a table

[Robert Citek]

On Mon, Feb 8, 2010 at 9:31 AM, Vasanta  wrote:
> Can I use this function call in C code to return last rowid to be inserted?.

On Fri, Feb 5, 2010 at 2:50 PM, Petite Abeille  wrote:
> Help Vampires: A Spotter’s Guide
> http://slash7.com/2006/12/22/vampires/

Nice read. - Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parallelizing an update

[Robert Citek]

On Tue, Feb 2, 2010 at 11:20 AM, Nicolas Williams
 wrote:
> On Tue, Feb 02, 2010 at 09:23:36AM +0100, Sylvain Pointeau wrote:
>> I would be very interested to see some benchmark, just to see.
>
> Feel free to write the relevant program, schema, SQL statements and run
> benchmarks against it.  What performance you get will depend on: what
> the long-running processes are doing (are they CPU bound or I/O bound?)
> and what CPU or I/O resources you have.

Apparently, my bash has a bug with trap as I was unable to get your
code to work, although I like its design.

And while I try to understand how mapreduce works, I thought I would
create a working example of what I am trying to accomplish (see script
below.)  It includes data and timings for both a single process and
for 6 parallel processes.

Regards,
- Robert

--

#!/bin/bash

debug() {
  1>&2 echo "$@"
}

long_running_process() {
  foo=$1
  debug "lrp -- $foo"
  sleep 10
  echo $foo
}

subprocess() {
  segment=$1
  {
  echo "begin transaction ;"
  echo "select rowid, item from sample where segment=${segment} ;" |
  sqlite3 -separator $'\t' sample.db |
  while read rowid item ; do
status=$(long_running_process "${item}" )
echo "update sample set status=${status} where rowid=${rowid} ;"
  done
  echo "commit transaction ;"
  } > update.${segment}.sql
}

multi_update() {
  eval $(
sqlite3 sample.db 'select distinct segment from sample ; ' |
while read segment ; do
  echo "subprocess "${segment}" &"
done
echo wait
  )
}

segment() {
  segments=$1
  echo "update sample set segment=(rowid-1) % ${segments}  ; " |
  sqlite3 sample.db
}

cleanup() {
  rm update.*.sql >& /dev/null
}

createdb() {
  { cat <> 50 ;
insert into sample (item) select random() >> 50 ;
insert into sample (item) select random() >> 50 ;
insert into sample (item) select random() >> 50 ;
insert into sample (item) select random() >> 50 ;
insert into sample (item) select random() >> 50 ;
COMMIT;
eof
  } | sqlite3 sample.db
}

initialize(){
  rm sample.db >& /dev/null
  cleanup
  createdb
}

timing() {
  segments=$1
  echo -- initialize
  initialize
  sqlite3 -header -column sample.db 'select rowid, * from sample ; '
  echo -- processing ${segments}
  segment ${segments}
  time -p multi_update
  echo -- updating ${segments}
  time -p cat update.*.sql | sqlite3 sample.db
  sqlite3 -header -column sample.db 'select rowid, * from sample ; '
  cleanup
}

main() {
  # parallel process
  timing 6
  # single process
  timing 1
}

main
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parallelizing an update

[Robert Citek]

On Tue, Feb 2, 2010 at 3:13 PM, John Elrick <john.elr...@fenestra.com> wrote:
> Robert Citek wrote:
>> Are there some white papers or examples of how to do updates in
>> parallel using sqlite?
>
> I could be misunderstanding your requirements, but this sounds a little
> like Map Reduce:
>
> http://labs.google.com/papers/mapreduce.html

Not sure, but quite possibly.  I'm reading up more on mapreduce.

> The only point I'd question is your assertion that you could speed up
> the overall time by running more than one long running process at the
> same time.  You *might* be able to do so up to the limit of the cores in
> the machine or by distributing the load over many machines, however, the
> implication to me of a long running process is something that is
> consuming large amounts of CPU time.

What I mean is that long_running_process (LRP) takes a long time to
run relative to updating a record in a sqlite database.  LRP's
bottleneck could be CPU or I/O or network lag or something else.  I'm
also assuming that if multiple LRPs are running, then they will
negligibly compete with each other for resources.  For example, if the
LRP is CPU-limited and the machine has only 4 cores, then there will
be at most 4 LRPs running at any given time.

> It is possible that running
> multiple processes per processor could actually increase the total
> amount of time due to process swap overhead.

The ideal would be to have a general framework that works on a single
CPU, multiple-CPUs/Cores, and multiple machines.

A google search for mapreduce led to this project:

http://github.com/erikfrey/bashreduce

I'll probably give that a try if for no other reason than to get more
familiar with mapreduce.

Thanks, John.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] parallelizing an update

[Robert Citek]

Sure.  This script can use a lot of aesthetic improvement, but it
highlights processing the data in a single process.

The question would be, how to modify the script to process the data in
with parallel processes?

Regards,
- Robert

On Sat, Jan 30, 2010 at 4:36 AM, Sylvain Pointeau
<sylvain.point...@gmail.com> wrote:
> a good thing would have been to generate one file with all the statements...
> if you do that then you run sqlite with this file surrounded by transaction
> begin/commit
>
> echo "begin transaction" >> update.sql
>
> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
> while read rowid item ; do
>  status=$(long_running_process "${item}" )
>  echo "update foo set status=${status} where rowid=${rowid} ;" >> update.sql
> done
>
> echo "commit transaction" >> update.sql
>
> sqlite3 sample.db < update.sql
>
> Best regards,
> Sylvain
>
> On Sat, Jan 30, 2010 at 12:04 AM, Robert Citek <robert.ci...@gmail.com>wrote:
>
>> Are there some white papers or examples of how to do updates in
>> parallel using sqlite?
>>
>> I have a large dataset in sqlite that I need to process outside of
>> sqlite and then update the sqlite database.  The process looks
>> something like this:
>>
>> sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
>> while read rowid item ; do
>>  status=$(long_running_process "${item}" )
>>  sqlite3 sample.db "update foo set status=${status} where rowid=${rowid} ;"
>> done
>>
>> Because long_running_process takes a long time, I could speed up the
>> overall time by running more than one long_running_process at the same
>> time.  One way to do this would be to segment the data and run a
>> separate process on each segment.  For the update each process would
>> collect the status data "outside" of the sample.db, e.g in a separate
>> database.  When all the processes have finished, the parent process
>> would attach the separate databases and update the original database.
>> When all is done, the parent process would clean up the ancillary
>> databases.
>>
>> I was just wondering if there are other ways to do this that I may be
>> overlooking.
>>
>> Thanks in advance to pointers to any references.
>>
>> 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

[sqlite] parallelizing an update

[Robert Citek]

Are there some white papers or examples of how to do updates in
parallel using sqlite?

I have a large dataset in sqlite that I need to process outside of
sqlite and then update the sqlite database.  The process looks
something like this:

sqlite3 -separator $'\t' sample.db 'select rowid, item from foo;' |
while read rowid item ; do
  status=$(long_running_process "${item}" )
  sqlite3 sample.db "update foo set status=${status} where rowid=${rowid} ;"
done

Because long_running_process takes a long time, I could speed up the
overall time by running more than one long_running_process at the same
time.  One way to do this would be to segment the data and run a
separate process on each segment.  For the update each process would
collect the status data "outside" of the sample.db, e.g in a separate
database.  When all the processes have finished, the parent process
would attach the separate databases and update the original database.
When all is done, the parent process would clean up the ancillary
databases.

I was just wondering if there are other ways to do this that I may be
overlooking.

Thanks in advance to pointers to any references.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 1:54 PM, Pavel Ivanov  wrote:
>> Why the difference in search time between searching individually and
>> searching together?
>
> Apparently SQLite is not smart enough to optimize the search for both
> min and max to make double entrance to the index - first from the
> beginning, then from the end. It does search through the full index
> instead which is not much better than search through the full table
> (as it does without the index). But when searched separately SQLite
> understands that it can pick up just first or just last entry from the
> index.

Fair enough.  Thanks. - Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
> I thought at the first moment that Pavel's suggestion on using extra index
> is very "expensive" in terms of megabytes on the disk, but despite this
> drawback it's actually more robust.

For my own curiosity I created a table with random text data and
discovered there was only a negligible difference between using
indexes and not when search for min and max in the same query.
However, when searching for min or max in separate queries the answer
was almost instantaneous. (details below).

Why the difference in search time between searching individually and
searching together?

Regards,
- Robert

-

$ sqlite3 sample.db 'drop table if exists sample ;
create table sample (foo text) ; '

$ jot -r -c 8000 a z |
tr -d '\n' |
fold -w 8 |
fmt -w 8 |
sqlite3 sample.db '.imp "/dev/stdin" "sample"'

$ sqlite3 sample.db 'select count(*) from sample ; '
1000

$ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
aaaq|ytyd
real 3.24
user 3.10
sys 0.11

$ time -p sqlite3 sample.db 'create index sample_foo on sample (foo) ; '
real 838.92
user 53.68
sys 38.46

$ time -p sqlite3 sample.db 'select min(foo), max(foo) from sample ; '
aaaq|ytyd
real 3.19
user 3.13
sys 0.06

$ time -p sqlite3 sample.db 'select min(foo) from sample ; '
aaaq
real 0.00
user 0.00
sys 0.00

$ time -p sqlite3 sample.db 'select max(foo) from sample ; '
ytyd
real 0.00
user 0.00
sys 0.01

$ sqlite3 --version
3.6.10
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

On Wed, Jan 20, 2010 at 8:52 AM, Max Vlasov  wrote:
>> One of many ways would be to precompute the min/max into a separate
>> table and then query that table whenever you need the min/max.
>
> I thought at the first moment that Pavel's suggestion on using extra index
> is very "expensive" in terms of megabytes on the disk, but despite this
> drawback it's actually more robust.

Agreed.  While storing the min/max in a separate table may be one of
the fastest ways, it is unlikely to be the optimal way, as we do not
know all the constraints (e.g. robustness, datatype, table size, use
cases)

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] fastest way to get the min/max

[Robert Citek]

> On Wed, Jan 20, 2010 at 6:24 PM, hi  wrote:
>> For my application I am storing about "1770" rows into sqlite table,
>> and when taking 'min' or 'max' it takes about ~7 to 10 seconds.
>>
>> Can you please suggest effective ways to get min/max values.

How are you currently getting the min/max values?  What have you tried so far?

One of many ways would be to precompute the min/max into a separate
table and then query that table whenever you need the min/max.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] way to get a list with column names

[Robert Citek]

Nothing with just SQL alone, although you can get close:

http://www.sqlite.org/faq.html#q7

You could use a command pipeline, but that only works if the table has
at least one record:

$ sqlite3 -separator ", " -header sample.db 'select * from
sqlite_master limit 1; ' |
head -1
type, name, tbl_name, rootpage, sql

Or you can do it from within a scripting language, e.g. ruby:

$ ruby -e '
require "sqlite3" ;
db=SQLite3::Database.new("sample.db") ;
row=db.execute2("select * from sqlite_master limit 0 ") ;
puts row.join(", ") ;
'
type, name, tbl_name, rootpage, sql

Good luck and let us know what works for you.

Regards,
- Robert

On Tue, Jan 12, 2010 at 6:27 AM, Oliver Peters  wrote:
> Hello out there,
>
> is there a quick way to get a comma separated list of column names as a 
> result of a query?
> (I couldn't find a PRAGMA or a dot command for this).
>
> Example:
>
> col01,col02,col03
>
>
> Greetings
> Oliver
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] graphs and sql

[Robert Citek]

On Sun, Jan 10, 2010 at 8:06 PM, Dan Bishop <danbisho...@gmail.com> wrote:
> Robert Citek wrote:
>> Does anyone have any recommendations for books or other resources that
>> deal with working with graphs (i.e. vertexes and edges) using sql?
>>
> I don't think that SQL is the best language for working with graphs, but:
>
> CREATE TABLE Graph (
> NodeFrom INTEGER,
> NodeTo INTEGER
> );

Yes, the Koenigsberg bridge problem is just one example of what I am
referring to.

I was working on creating a more general model initially with just two
tables: one for vertexes and one for edges, which is a pairing of
vertexes.  For example:

create table vertexes ( vertex integer ) ;
create table edges ( v1 integer, v2 integer ) ;
BEGIN;
INSERT INTO vertexes VALUES (1);
INSERT INTO vertexes VALUES (2);
INSERT INTO vertexes VALUES (3);
INSERT INTO vertexes VALUES (4);
COMMIT;

To create a complete graph:

INSERT INTO edges
SELECT v1.vertex, v2.vertex
FROM vertexes v1 JOIN vertexes v2
WHERE v2.vertex > v1.vertex

To generate the irreflexive symmetric relation on vertexes:

CREATE VIEW isr AS
SELECT v1.vertex, v2.vertex
FROM vertexes v1 JOIN vertexes v2
WHERE v2.vertex != v1.vertex

So, there seem to be ways of working with graphs within sql.  I was
just curious to know if there were texts that cover this subject.  Joe
Celko's books seem like one source.  Are there others?

As for SQL not being the best for working with graphs, I'm ok with
that as I can use sqlite3 for storing and retrieving the graph data as
well as some of the more simpler manipulations, and then use some
other language for more sophisticated manipulations.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] graphs and sql

[Robert Citek]

On Sat, Jan 9, 2010 at 5:44 PM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Sat, Jan 09, 2010 at 03:41:24PM -0500, Robert Citek scratched on the wall:
>> Does anyone have any recommendations for books or other resources that
>> deal with working with graphs (i.e. vertexes and edges) using sql?
>
>  Joe Celko's "Trees and Hierarchies in SQL for Smarties" has a bit of
>  information, but the primary focus is trees and DAGs.
>
>  His more general "SQL For Smarties, Advanced SQL Programming" has a
>  number of pages on the topic as well.  That book is extremely useful
>  for any number of other topics as well.

Thanks.  I'll have a look at both of those for a start.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] graphs and sql

[Robert Citek]

Hello all,

Does anyone have any recommendations for books or other resources that
deal with working with graphs (i.e. vertexes and edges) using sql?

For example, if I could store a graph in a sqlite database, I'd like
to query the database to know if the graph contains a Eulerian
path[1].

[1] http://en.wikipedia.org/wiki/Eulerian_path

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] temp directory?

[Robert Citek]

On Thu, Jan 7, 2010 at 12:54 PM, Kris Groves  wrote:
> So it seems as if TMPDIR will work in two different OSes.

What would be an easy test to verify if setting TMPDIR works or not?

I did this, which shows that TMPDIR is indeed being used, but I think
this test is a bit cumbersome:

$ sqlite3 /dev/null
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create temp table foo (bar int) ;
sqlite>
[1]+  Stopped sqlite3 /dev/null

$ ls -l /proc/$(jobs -p %1)/fd
total 0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 0 -> /dev/pts/0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 1 -> /dev/pts/0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 2 -> /dev/pts/0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 3 -> /dev/null
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 4 ->
/var/tmp/etilqs_4GCNtDifceoskIh (deleted)
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:29 5 ->
/var/tmp/etilqs_dy4Trta7FQrYQT8 (deleted)

$ fg
sqlite3 /dev/null
.q

$ TMPDIR=/dev/shm/ sqlite3 /dev/null
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create temp table foo (bar int) ;
sqlite>
[1]+  Stopped TMPDIR=/dev/shm/ sqlite3 /dev/null

$ ls -l /proc/$(jobs -p %1)/fd
total 0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 0 -> /dev/pts/0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 1 -> /dev/pts/0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 2 -> /dev/pts/0
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 3 -> /dev/null
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 4 ->
/dev/shm/etilqs_MOz4R9xoAMB1i6U (deleted)
lrwx-- 1 rwcitek rwcitek 64 2010-01-07 13:33 5 ->
/dev/shm/etilqs_IXXkIEReSwGcwL5 (deleted)

$ fg
TMPDIR=/dev/shm/ sqlite3 /dev/null
.q

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] temp directory?

[Robert Citek]

On Thu, Jan 7, 2010 at 10:42 AM, Jay A. Kreibich <j...@kreibi.ch> wrote:
> On Thu, Jan 07, 2010 at 10:35:21AM -0500, Robert Citek scratched on the wall:
>> You mention a temp environment variable.  I've googled through the
>> sqlite.org site and haven't found any mention of an environment
>> variable.  What environment variable can I set to change the default
>> value for the temporary directory?
>
>  http://sqlite.org/pragma.html#pragma_temp_store_directory
>
>  It is a PRAGMA, not an env var.  Also see "PRAGMA temp_store".

Yes, I was aware of the pragma.  I was hoping for an environment
variable so that I don't have to write pragmas in my code.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] temp directory?

[Robert Citek]

You mention a temp environment variable.  I've googled through the
sqlite.org site and haven't found any mention of an environment
variable.  What environment variable can I set to change the default
value for the temporary directory?

Regards,
- Robert

On Fri, Nov 20, 2009 at 5:04 AM, Kris Groves  wrote:
> I suppose another possibility is to set the temp environment variable.
>
> I have just noticed that your version of sqlite is way old... so I'm
> very unsure if anything I've suggested is of any use.  I know the
> environment variable did not work in 3.5.9, but does in 3.6 and up.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] long time to sort list of random integers

[Robert Citek]

On Sat, Dec 19, 2009 at 10:18 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> On 19 Dec 2009, at 9:27am, Robert Citek wrote:
>
>> Does anyone have any pointers on how I can speed up a sqlite3 query
>> that sorts a list of random integers?
>
> What are  you doing that sorts them?

An "order by" clause.

> Are they in an indexed column, or are you doing it an ORDER BY clause ?

Not an indexed column.

>  If you have an index, delete it; if you don't, make one.  See whether that 
> speeds it up.

Building an index works well for 1 MM records. The build+query time
(~40s=27+10) is about 6x faster than querying without an index
(~240s), but still 6x slower than querying and piping to the sort
command (~6s).

However, times take noticeably longer when the dataset size is doubled
to 2 MM records.  Querying+index (~160=136+20) took 4x longer than
with the 1 MM dataset, although it was still 4x faster than querying
without an index (~610s).  Piping to the sort command was still the
fastest at 12s, more than 13x faster than querying with an index and
50x faster than querying without an index.

Below is version information and the timing data along with the script
that creates and queries the database.

Regards,
- Robert

- version info -

$ ( set -x ; sqlite3 -version ; lsb_release -a ; ruby --version )
+ sqlite3 -version
3.4.2
+ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:Ubuntu 8.04.3 LTS
Release:8.04
Codename:   hardy
+ ruby --version
ruby 1.8.6 (2007-09-24 patchlevel 111) [i486-linux]


- Timing data -

 1 MM records:

=== load data
100
real 12.42
user 18.61
sys 0.74
=== sort random
100
real 238.73
user 19.60
sys 10.81
=== shell sort random
100
real 5.92
user 5.79
sys 0.34
=== build index
real 27.01
user 14.51
sys 5.44
=== sort random with index
100
real 9.77
user 6.84
sys 3.00

 2 MM records:

=== load data
200
real 21.86
user 36.94
sys 1.34
=== sort random
200
real 613.56
user 39.49
sys 23.59
=== shell sort random
200
real 12.10
user 11.90
sys 0.76
=== build index
real 136.45
user 31.30
sys 14.20
=== sort random with index
200
real 20.07
user 14.04
sys 6.21


 script -
#!/bin/bash
[ -f sample.db ] && \rm sample.db
echo "=== load data"
time -p ruby -e '
size=100
srand=1234
(1..size).each do |i|
  puts [i, rand(2**15)].join("\t")
end
' |
sqlite3 -init <(echo '
CREATE TABLE sample ( samp1 INTEGER, samp2 INTEGER)
;
.mod tabs
.imp "/dev/stdin" "sample"
select count(*) from sample
;
') sample.db .quit

echo "=== sort random"
time -p { grep -v ^# <http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] long time to sort list of random integers

[Robert Citek]

On Sat, Dec 19, 2009 at 12:46 PM, Roger Binns <rog...@rogerbinns.com> wrote:
> Robert Citek wrote:
>> and much longer than
>> piping the list of random integers into the sort command.
>
> A considerable amount of time in your test script is actually spent in print
> calls to pipes.

I don't follow.  An unordered query takes 3 seconds while an ordered
query takes 99 seconds.  Are you saying the sorting process is
printing to pipes behind the scenes?

>  Here are some timings I got.  Note that used tmpfs so that
> disk speeds are not a factor.

What I suspect is that I am hitting some limit which indeed causes
disk access to be a factor.  While I can avoid disk access for smaller
datasets, I will not be able to for larger ones.

>> Any pointers in the right direction are greatly appreciated,
>> especially given that this dataset only needs to be queried and sorted
>> once.
>
> If this is really what your data looks like the just use the sort command.
> If you have to use SQLite (which will involve import, sorting, output) then
> you need to play with page and cache_sizes and indices.

Thanks for the pointers.  I'll try out various values for page and cache_size.

> However this overhead is unlikely to be as fast as sort.

If I could get sqlite3 to be within a factor of 2 of sort, that would work fine.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] long time to sort list of random integers

[Robert Citek]

Does anyone have any pointers on how I can speed up a sqlite3 query
that sorts a list of random integers?

I've noticed that sqlite3 takes a long time to sort random integers,
much longer than sorting a series of integers and much longer than
piping the list of random integers into the sort command. The script
below tests those three methods by generating 1 MM random integers,
loading them into a sqlite3 database, and then querying them.  This
process models a real process in which a several million record
dataset is to be queried and sorted exactly once.

For this model process I've included data from a sample run.  Notice
that querying the random integers takes about 7X longer than querying
the series and 16X longer than piping to the sort command.

Any pointers in the right direction are greatly appreciated,
especially given that this dataset only needs to be queried and sorted
once.

Regards,
- Robert

--- data ---

=== load data
100
real 38.80
user 37.31
sys 0.58
=== no sort
100
real 2.38
user 2.30
sys 0.16
=== sort series
100
real 14.17
user 12.63
sys 0.45
=== sort random
100
real 99.02
user 18.86
sys 9.87
=== shell sort random
100
real 5.91
user 5.76
sys 0.40



- script ---
#!/bin/bash

echo "=== load data"
time -p echo {1..100}$'\t'${RANDOM} |
tr ' ' '\n' |
sqlite3 -init <(echo '
CREATE TABLE sample ( samp1 INTEGER, samp2 INTEGER)
;
.mod tabs
.imp "/dev/stdin" "sample"
select count(*) from sample
;
') sample.db .quit

echo "=== no sort"
time -p { grep -v ^# 

Re: [sqlite] How to find out which row had been modified by an UPDATE statement?

[Robert Citek]

On Thu, Dec 17, 2009 at 12:05 PM, Martin Kirsche  wrote:
> is it possible in SQLite to find out which row had been modified by an
> UPDATE statement?

Not automatically.  You would have to track it yourself with some code
like this:

sqlite> create temporary table update_list as select rowid as "id"
from foo where bar=2 ;
sqlite> select * from foo where rowid in (select id from update_list) ;
sqlite> update foo set bar=1 where bar=2 ;
sqlite> select * from foo where rowid in (select id from update_list) ;

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to find out which row had been modified by an UPDATE statement?

[Robert Citek]

On Thu, Dec 17, 2009 at 1:43 PM, P Kishor  wrote:
> On Thu, Dec 17, 2009 at 11:05 AM, Martin Kirsche  
> wrote:
>> Hi,
>> is it possible in SQLite to find out which row had been modified by an
>> UPDATE statement?
>
> just SELECT with the same params that you used in your UPDATE.

Unless the params were modified by the update.  For example,

sqlite> update foo set bar=1 where bar=2;
sqlite> select rowid from foo where bar=2;

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] speed up a sorted union all query

[Robert Citek]

On Thu, Dec 17, 2009 at 1:32 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> Robert Citek <robert.ci...@gmail.com>
> wrote:
>> How can I speed up the sorting of a union all query?
>
> Basically, you can't. An index can't be used for this. Consider changing your 
> requirements, or your design, to make such a query unnecessary.

Thanks, Igor.

A quick workaround was for me to trade space for time by creating a
temporary table, creating an index, and then querying the temporary
table.  Seems faster, but will post numbers in a bit.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] speed up a sorted union all query

[Robert Citek]

On Thu, Dec 17, 2009 at 11:20 AM, Robert Citek <robert.ci...@gmail.com> wrote:
> How can I speed up the sorting of a union all query?

BTW, here is a method for creating a sample dataset:

$ echo {1..1000}$'\t'{1..200}$'\t'1 |
tr ' ' '\n' |
sqlite3 -init <(echo '
create table foo (amp1 INTEGER, amp2 INTEGER, tag INTEGER) ;
.mod tabs
.imp "/dev/stdin" "foo"
') foo.db .quit

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] speed up a sorted union all query

[Robert Citek]

Hello all,

How can I speed up the sorting of a union all query?

Here are two queries which effectively create the same output.
However the first one pipes the output from the union all to the
shell's sort command whereas the second one used an order by clause.
In addition, the second query explodes in the amount of time it takes
for large data sets, becoming unusable after about 500,000 records.
In contrast, the first is still reasonable even past several million
records.

$ time -p { grep -v ^# 

Re: [sqlite] Passing all columns as arguments to a function?

[Robert Citek]

On Thu, Sep 10, 2009 at 8:37 AM, Jean-Denis Muys  wrote:
> Is it possible to pass all columns of a table as arguments to a function in
> a implicit way?

As Igor wrote: no.

> I tried this but it did not work:
>
> Create table studentMarks (French, English, Maths, Physics);
> Insert into studentMarks values (12, 9, 15, 14);
> Insert into studentMarks values (14, 13, 12, 8);
> Select max(*) from studentMarks where French > 13;
>
> I could always iterate from my code and build an explicit argument list for
> the max function, but I'm lazy and I'm intrigued...
>
> Is there a way to do this?

Sort of.  It would mean rearranging your data structure, e.g. (ID,
Language, Mark).

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite - text files are not really portable

[Robert Citek]

Yes, one big, long line.

As for displaying, depending on the program you use, \r may get
displayed as ^M.  For example:

$ echo -ne '\r\n' | cat -A
^M$

$ echo -ne '\r\n' | od -An -abcx
  cr  nl
 015 012
  \r  \n
 0a0d

Regards,
- Robert

> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kavita Raghunathan
> Sent: Tuesday, September 08, 2009 2:14 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Importing data into SQLite
>
> FYI: Mac excel does not separate rows with \r, but inserts a ^M instead.
> (I dont have a windows machine with me, I wonder if this is Mac specific)
> Sqlite does not like this because a large file with many rows appears as
> 1 huge infinite line to sqlite.
> Kavita
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite

[Robert Citek]

Just a guess, but you may be running into issues with the end-of-line
character as they are different under linux (\n), Mac (\r), and
Windows/DOS (\r\n).

Linux has a tool to convert Windows/DOS end-of-lines to linux-style
called dos2unix.  There may be one for Mac, too, but I'm not sure.  If
not, you could use tr.  For example:

$ < mac.csv  tr '\r' '\n' > unix.csv

Good luck and let us know how things go.

Regards,
- Robert

On Tue, Sep 8, 2009 at 1:47 PM, Kavita
Raghunathan wrote:
> Yes, this works. Must have been my original csv file.
> I was using mac based excel and I'll now try using the windows based excel.
> Thanks to all for the awesome support.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite

[Robert Citek]

Sounds good.  Let us know how things go.

P.S. one thing to try may be to use dos2unix to convert any text files
created in the Windows/DOS world to unix-format text files.

Regards,
- Robert

On Mon, Sep 7, 2009 at 7:22 PM, Kavita
Raghunathan wrote:
> The difference between what you did and what I did was perhaps that
>  I had excel make the .csv file, and maybe the excel version had something
> to do with it ?
> I'll retry with the exact same steps, and let you know the result. Thanks
> for your help!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Importing data into SQLite

[Robert Citek]

On Sun, Sep 6, 2009 at 9:32 PM, Kavita
Raghunathan wrote:
> Timothy and all,
> When I try to import a .csv, I get a segmentation fault:
> 1) First I set .seperator to ,
> 2) Then I type .import  
> 3) I see "Segmentation fault"
>
> Any ideas ?

Here's an example of how it worked for me.

$ cat data.csv
"a",1
"b",2
"c",3

$ sqlite3 sample.db .schema
CREATE TABLE data (foo text, bar int);

$ sqlite3 -separator , sample.db '.imp "data.csv" "data" '

$ sqlite3 -header -column sample.db 'select * from data ;'
foo bar
--  --
"a" 1
"b" 2
"c" 3

More details here, including caveats:

http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles

Personally, I prefer to used tab-delimited files and then import by
specifying the separator as a tab:

$ sqlite3 -separator $'\t' sample.db '.imp "data.tsv" "data" '

This takes advantage of the bash shell's use of $'\t' to encode a tab.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite to Access

[Robert Citek]

The ODBC connector for SQLite will allow you to connect Access to
SQLite without having to import/export:

http://www.ch-werner.de/sqliteodbc/

Regards,
- Robert

On Thu, Sep 3, 2009 at 2:33 PM, Pighin, Ryan wrote:
> Hi All - We have a new utility in our environment using SQLite and I was
> wondering if there was a way to dump all the database into Access so we
> can create reports on the databases?
>
>
>
> Thanks,
>
> Ryan Pighin
>
>
>
>
>
> ___
> 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] Trouble running sqlite3 in ubuntu linux 9.04

[Robert Citek]

FWIW, I've been running sqlite3 on Ubuntu 9.04 for a while without
issue for several months.

$ cat /etc/issue.net
Ubuntu 9.04

$ dpkg -l sqlite3
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Cfg-files/Unpacked/Failed-cfg/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Hold/Reinst-required/X=both-problems (Status,Err: uppercase=bad)
||/ Name  Version
 Description
+++-=-=-==
ii  sqlite3   3.6.10-1ubuntu0.2
 A command line interface for SQLite 3

$ sqlite3 test.db
SQLite version 3.6.10
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

And there doesn't seem to be anything mentioning segfaults on launchpad:

https://launchpad.net/ubuntu/jaunty/+source/sqlite3

My first guess would be to uninstall and then re-install the sqlite3
package, as possibly something got borked during the installation.
Beyond that, I'm not sure.

Good luck and let us know how things go.

Regards,
- Robert

On Wed, Aug 26, 2009 at 12:01 AM, Paul Whipp wrote:
> Hi there,
>
> I just installed sqlite3 using synaptic. Install went fine but:
>
> ~$ sqlite3 test.db
> Segmentation fault
>
> Can anyone point me to the relevant documentation/solution?
[
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Rows where people connected in the past 2 weeks?

[Robert Citek]

To expand on things to try:

sqlite> select julianday('now');

sqlite> select julianday('2009-08-01');

sqlite> select julianday('now') - julianday('2009-08-01');

And maybe have a look here:

http://sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

Regards,
- Robert

On Tue, Aug 18, 2009 at 1:25 PM, Pavel Ivanov wrote:
> Just try these and you'll see why.
>
> sqlite> select julianday('18-08-2009');
>
> sqlite> select julianday('now') - julianday('18-08-2009');
>
>
> Pavel
>
> On Tue, Aug 18, 2009 at 12:58 PM, Gilles Ganault 
> wrote:
>> Hello
>>
>> I thought this query would work to read a date column that holds the
>> DD-MM- date when the user last logged on, and check how many of
>> them logged on in the past two weeks for the last time:
>>
>> SELECT COUNT(id) FROM members WHERE (julianday('now') -
>> julianday(dateconnection)) < 15;
>>
>> This is returning no row, even though I know a lot of rows have a
>> connection date within the last two weeks.
>>
>> Any idea why this is wrong?
>>
>> Thank you.
>>
>> ___
>> 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] migrating a pipeline to using sqlite

[Robert Citek]

On Tue, Jul 7, 2009 at 10:20 AM, Michal Seliga<michal.sel...@visicom.sk> wrote:
> Robert Citek wrote:
>> create table foo (
>>  col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9,
>>  col_10, col_11, col_12, col_13, col_14, col_15, col_16, col_17,
>>  col_18, col_19, col_20, col_21, col_22 ) ;
>> .mode tab
>> .imp "foo.tsv" "foo"
>> select col_9, col_22, count(*) as "count" from foo group by col_9, col_22 ;
>
> Try this with index created on foo(col_9, col_22).

Significantly worse than not creating an index:

$ time -p {
grep -v '^#' <http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] The SQL Guide to SQLite

[Robert Citek]

On Sun, Jul 19, 2009 at 11:23 AM, Rick
Ratchford wrote:
> Anyway, when I've gathered enough info from the book I'll share my thoughts
> on it. It will be from the perspective of a person new to SQLite and SQL.

Anyone know of a way to preview the index, the table of contest, or
some sample chapters?  I tried viewing form the Lulu site, but no
luck.

http://www.lulu.com/content/7251432

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] New Book Available

[Robert Citek]

On Thu, Jul 9, 2009 at 8:50 AM,  wrote:
> On Thu, 9 Jul 2009, Rich Shepard wrote:
>
>>   Rick van der Laans, who wrote the excellent "Introduction to SQL, 4th Ed."
>> (and eariler editions, of course) has just had his new book specific to
>> SQLite published. It is another resource for those who want a detailed
>> explanation of how to get the most from this great tool.
>
> What is the title? I am not finding a new book by van der
> Laans book on Google or Amazon.

Took a bit of googling but eventually found this:

http://www.r20.nl/SQLGuidetoSQLite_V1_1.htm

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] migrating a pipeline to using sqlite

[Robert Citek]

I have a command pipeline that I would like to transfer to sqlite.
However, the performance under sqlite is significantly slower, which
makes me think that I am doing something not quite correctly.  How can
I improve the performance of this task using sqlite?

foo.tsv is a tab-delimited file with 22 fields with over 5 MM records.

$ time -p wc -l foo.tsv
5603674 foo.tsv
real 0.49
user 0.34
sys 0.15

I need an aggregate count based on the values in fields 9 and 22.
Here is my current pipeline:

$ time -p cat foo.tsv | cut -f9,22 | sort | uniq -c | perl -lane
'print join("\t", @F[1,2,0])' | wc -l
1992301
real 15.69
user 15.63
sys 1.07

Here is my first attempt at an equivalent pipeline in sqlite3:

$ time -p {  grep -v '^#' 

[sqlite] importing data to sqlite from stdin

[Robert Citek]

On occasion I have had a need to import large amounts of data from
standard input via a pipe.  Here's an example of how to import from a
pipe using sqlite3 on Ubuntu:

$ { grep -v '^#' 

Re: [sqlite] sequential row numbers from query

[Robert Citek]

That would work.  In fact, my current solution, which actually pipes
to perl, works pretty well.  It's just that I have to then import the
data back into the database.  So, I'd prefer to do the process
entirely in SQL.

I was thinking maybe a view, but that didn't work.  Apparently, there
is no rowid with views (or is there something equivalent that I'm
overlooking?).

$ sqlite3 db 'create view bar as select * from foo order by field desc;
select rowid, * from bar ; '
|c
|b
|a

Substituting a temporary table for the view works:

$ sqlite3 db 'create temporary table bat as select * from foo order by
field desc;
select rowid, * from bat ; '
1|c
2|b
3|a

Any limitations to consider when using a temporary table?  Any other ideas?

Regards,
- Robert

On Fri, Mar 27, 2009 at 3:37 PM, Thomas Briggs  wrote:
>   Holy cow that feels inefficient.
>
>   It's a bit clunky, but why not insert into a temporary table,
> ordered as desired, and then use the rowid from the temp table?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selecting the top 3 in a group

[Robert Citek]

Thanks, Igor.  That worked perfectly.  Time for me to read up on rowid
and the subtleties of subselects.

Regards,
- Robert

On Thu, Jan 8, 2009 at 6:48 AM, Igor Tandetnik  wrote:
> select div, team from teams t1 where rowid in
> (select rowid from teams t2 where t1.div = t2.div
>  order by wins desc limit 3);
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selecting the top 3 in a group

[Robert Citek]

Turning the pseudo code into a bash script produced the desired output:

for i in $(sqlite3 team.db 'select distinct div from teams ' ) ; do
  sqlite3 -separator $'\t' team.db '
select div, team, wins
from teams
where div="'$i'"
order by wins+0 desc
limit 3 ;'
done

I am still curious to know if there is a purely SQL way to do the same.

Regards,
- Robert

On Thu, Jan 8, 2009 at 12:06 AM, Robert Citek <robert.ci...@gmail.com> wrote:
> In pseudocode, I want to do something similar to this:
>
> for $i in (select div from teams) {
>  select div, team, wins from teams where div=$i order by wins desc limit 3 ;
> }
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] selecting the top 3 in a group

[Robert Citek]

That gets me the best team in the first five divisions.  I would like
the top three teams within each division.

Regards,
- Robert

On Thu, Jan 8, 2009 at 12:19 AM, aditya siram  wrote:
> Hi Robert,
> SQL has a LIMIT keyword. I have used it to take the top 'x' entries of a
> large table , so for example:
> SELECT * from table LIMIT 20
>
> You should be able to use it in your query like so:
> select div, team, max(wins) from teams group by div limit 5;
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] selecting the top 3 in a group

[Robert Citek]

How can I construction a SQL query to pick the top three (3) items in a group?

I have a list of sports teams which are grouped into divisions, say A,
B, C, D, etc.  At the end of the season I would like to get a list of
the top three teams (those with the most wins) in each division.  If I
wanted the best team from each division, I could write this:

select div, team, max(wins) from teams group by div ;

Unfortunately, there's no option to max to specify more than one item,
e.g. max(wins,3) to specify the top 3.

In pseudocode, I want to do something similar to this:

for $i in (select div from teams) {
  select div, team, wins from teams where div=$i order by wins desc limit 3 ;
}

Is there a way to do the equivalent using only SQL?

Thanks in advance for any pointers.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] replacing underscore with a tab

[Robert Citek]

On Tue, Sep 16, 2008 at 3:49 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote:
> But if you insist on doing it in SQL, this should work:
>
> sqlite3 foobar.db "select replace(id,'_',cast(x'09' as text)) from bar;"

That worked:

$ sqlite3 foobar.db 'select replace(id,"_",cast(x"09" as text)) from bar;'

Although, using cast was non-obvious to me from reading the docs:

http://www.sqlite.org/lang_expr.html

Here's another version using bash syntax to insert the tab character:

$ sqlite3 foobar.db 'select replace(id,"_","'$'\t''") from bar;'

Thanks, Igor.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] replacing underscore with a tab

[Robert Citek]

How can I replace an underscore ("_") in a field with a tab?

This works but seems like suck a hack:

$ sqlite3 foobar.db 'select replace(id,"_","{tab}") from bar;' |
  sed -e 's/{tab}/\t/'

I was hoping for a char(9) or similar but couldn't find anything in the docs:

http://www.sqlite.org/lang_corefunc.html

Pointers to references greatly appreciated.

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

[Robert Citek]

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

[Robert Citek]

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

[Robert Citek]

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

[Robert Citek]

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

[sqlite] db vs shell

[Robert Citek]

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] SQL error: disk I/O error

[Robert Citek]

Hello all,

I recently got an error while running a long query with sqlite3 on
Ubuntu Gutsy 7.10:

SQL error: disk I/O error

I googled for a solution but didn't find anything relevant.  After a
bit of sleuthing on my machine, I discovered that I was running out of
disk space.  I freed up some disk space, reran the query, and it
finished this time without giving an error.

Figured I'd post just in case someone else might run into something similar.

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Value between changes

[Robert Citek]

On Fri, Mar 14, 2008 at 8:07 AM, BandIT <[EMAIL PROTECTED]> wrote:
>  I would like a query to output the the time between the status codes (sum)
>  to know how long each status has been active. I am not so very familiar with
>  SQL and SQLite, and I hope I am posting to the correct forum.

Can you give an example of the output?  For example, do you want to
know the difference between A0001 and A0002 or between to records with
the same code?

Regards,
- Robert
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users