Re: [sqlite] Getting a list of items on a column

[jose isaias cabrera]

Thanks.


- Original Message - 
From: "C.Peachment" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, May 05, 2007 9:40 PM
Subject: Re: [sqlite] Getting a list of items on a column



On Sat, 5 May 2007 21:03:27 -0400, jose isaias cabrera wrote:


Ok, I am going to push my luck.  :-)  Imagine the same data, what if I
wanted to get the sum of the other two columns also?  I know how to do it
one at a time, so in this instance I would have to do 3 separate call 
using
sum or total, but is there a way that I could do it on the same call and 
get

the sum of columns c2 and c3, so that I would get something like this,



a,11,19
b,3,4
c,6,9



Yes, I know.  I am pushing my luck. :-)



create table t1 (c1,c2,c3);
insert into t1 values ('a', 1, 2);
insert into t1 values ('b', 3, 4);
insert into t1 values ('c', 5, 6);
insert into t1 values ('a', 9, 8);
insert into t1 values ('a', 1, 9);
insert into t1 values ('c', 1, 3);

select c1, sum(c2), sum(c3) from t1 group by c1;




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Getting a list of items on a column

[jose isaias cabrera]

Thanks.  Yes, that works.

- Original Message - 
From: <[EMAIL PROTECTED]>

To: 
Sent: Saturday, May 05, 2007 9:37 PM
Subject: Re: [sqlite] Getting a list of items on a column



"jose isaias cabrera" <[EMAIL PROTECTED]> writes:


"C.Peachment" wrote,



On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote:



Greetings!



imagine this data content,



c1, c2, c3
a, 1, 2
b, 3, 4
c, 5, 6
a, 9, 8
a, 1, 9
c, 1, 3


I would like to get a list of all the items in c1.  So, the return 
would be,



a,
b,
c



I know how to do it programmatically, but is there a way to get it right
from SQLite?


select distinct c1 from tablename;


Thanks.

Ok, I am going to push my luck.  :-)  Imagine the same data, what if I 
wanted
to get the sum of the other two columns also?  I know how to do it one at 
a

time, so in this instance I would have to do 3 separate call using sum or
total, but is there a way that I could do it on the same call and get the 
sum

of columns c2 and c3, so that I would get something like this,

a,11,19
b,3,4
c,6,9

Yes, I know.  I am pushing my luck. :-)


How about something like

 SELECT c1, SUM(c2), SUM(c3)
   FROM tablename
   GROUP BY c1;

Cheers,

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Getting a list of items on a column

[C.Peachment]

On Sat, 5 May 2007 21:03:27 -0400, jose isaias cabrera wrote:

>Ok, I am going to push my luck.  :-)  Imagine the same data, what if I 
>wanted to get the sum of the other two columns also?  I know how to do it 
>one at a time, so in this instance I would have to do 3 separate call using 
>sum or total, but is there a way that I could do it on the same call and get 
>the sum of columns c2 and c3, so that I would get something like this,

>a,11,19
>b,3,4
>c,6,9

>Yes, I know.  I am pushing my luck. :-)


create table t1 (c1,c2,c3);
insert into t1 values ('a', 1, 2);
insert into t1 values ('b', 3, 4);
insert into t1 values ('c', 5, 6);
insert into t1 values ('a', 9, 8);
insert into t1 values ('a', 1, 9);
insert into t1 values ('c', 1, 3);

select c1, sum(c2), sum(c3) from t1 group by c1;




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Getting a list of items on a column

[Derrell . Lipman]

"jose isaias cabrera" <[EMAIL PROTECTED]> writes:

> "C.Peachment" wrote,
>
>
>> On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote:
>>
>>
>>>Greetings!
>>
>>>imagine this data content,
>>
>>>c1, c2, c3
>>>a, 1, 2
>>>b, 3, 4
>>>c, 5, 6
>>>a, 9, 8
>>>a, 1, 9
>>>c, 1, 3
>>
>>> I would like to get a list of all the items in c1.  So, the return would be,
>>
>>>a,
>>>b,
>>>c
>>
>>>I know how to do it programmatically, but is there a way to get it right
>>>from SQLite?
>>
>> select distinct c1 from tablename;
>
> Thanks.
>
> Ok, I am going to push my luck.  :-)  Imagine the same data, what if I wanted
> to get the sum of the other two columns also?  I know how to do it one at a
> time, so in this instance I would have to do 3 separate call using sum or
> total, but is there a way that I could do it on the same call and get the sum
> of columns c2 and c3, so that I would get something like this,
>
> a,11,19
> b,3,4
> c,6,9
>
> Yes, I know.  I am pushing my luck. :-)

How about something like

  SELECT c1, SUM(c2), SUM(c3)
FROM tablename
GROUP BY c1;

Cheers,

Derrell

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Getting a list of items on a column

[jose isaias cabrera]

"C.Peachment" wrote,



On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote:



Greetings!



imagine this data content,



c1, c2, c3
a, 1, 2
b, 3, 4
c, 5, 6
a, 9, 8
a, 1, 9
c, 1, 3


I would like to get a list of all the items in c1.  So, the return would 
be,



a,
b,
c



I know how to do it programmatically, but is there a way to get it right
from SQLite?


select distinct c1 from tablename;


Thanks.

Ok, I am going to push my luck.  :-)  Imagine the same data, what if I 
wanted to get the sum of the other two columns also?  I know how to do it 
one at a time, so in this instance I would have to do 3 separate call using 
sum or total, but is there a way that I could do it on the same call and get 
the sum of columns c2 and c3, so that I would get something like this,


a,11,19
b,3,4
c,6,9

Yes, I know.  I am pushing my luck. :-)

thanks.

josé 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Getting a list of items on a column

[C.Peachment]

On Sat, 5 May 2007 19:58:19 -0400, jose isaias cabrera wrote:


>Greetings!

>imagine this data content,

>c1, c1, c3
>a, 1, 2
>b, 3, 4
>c, 5, 6
>a, 9, 8
>a, 1, 9
>c, 1, 3

>I would like to get a list of all the items in c1.  So, the return would be,

>a,
>b,
>c

>I know how to do it programmatically, but is there a way to get it right 
>from SQLite?

>thanks,

>josé

select distinct c1 from tablename;




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Getting a list of items on a column

[jose isaias cabrera]

Greetings!

imagine this data content,

c1, c1, c3
a, 1, 2
b, 3, 4
c, 5, 6
a, 9, 8
a, 1, 9
c, 1, 3

I would like to get a list of all the items in c1.  So, the return would be,

a,
b,
c

I know how to do it programmatically, but is there a way to get it right 
from SQLite?


thanks,

josé


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Powered by SQLite image?

[Alberto Simões]

On 5/5/07, A. Pagaltzis <[EMAIL PROTECTED]> wrote:

Hi Alberto,

Maybe contribute both to SQLite? They'd make excellent additions
to the art/ directory in the source tarball. (I'm not sure what
it would take for Dr. Hipp to include them?)



The xcf file can be downloaded from
http://dicionario-aberto.net/powered_by_sqlite.xcf

Of course I would be very happy if the image(s) is included in the
source tarball.

Kind Regards
Alberto
--
Alberto Simões

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Powered by SQLite image?

[A. Pagaltzis]

Hi Alberto,

* Alberto Simões <[EMAIL PROTECTED]> [2007-05-04 22:10]:
> Ok, I had some time today. What do you think of the one shown
> in: http://dicionario-aberto.net/bin/dic.pl

excellent! That looks exactly the way I imagined such a button
should look. :-)

* Alberto Simões <[EMAIL PROTECTED]> [2007-05-04 23:05]:
> On 5/4/07, Cesar Rodas <[EMAIL PROTECTED]> wrote:
> >and this image is public domain?
> 
> Sure. Be free to use it. Also, I have the xcf file. So if
> anybody knows how to tweak Gimp, I'll be pleased to send it.

Maybe contribute both to SQLite? They’d make excellent additions
to the art/ directory in the source tarball. (I’m not sure what
it would take for Dr. Hipp to include them?)

Regards,
-- 
Aristotle Pagaltzis // 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Curious performance issue with large number of inserts

[Tim Bradshaw]

On 5 May 2007, at 17:11, Joe Wilson wrote:


What timings do you get when you run the perl script in my last email?


I'll try it Tuesday, I'm not back at work till then.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Curious performance issue with large number of inserts

[Joe Wilson]

--- Tim Bradshaw <[EMAIL PROTECTED]> wrote:
> > I can't reproduce your problem. I can insert 16M records into your  
> > table
> > schema in 25 minutes on a 5 year old Windows machine. The sqlite3  
> > process
> > had peak RAM usage of less than 20M.
> 
> Rats, I suspect it must be some compiler/architecture specific thing.  

I run GCC-compiled sqlite3 on sparc/solaris without such performance issues.

> I can rebuild it for x86, since I don't really care where it runs. It  
> definitely isn't running out of memory though (at least, not unless  
> it's throttling itself somehow, the machine has loads  free when it  
> gets sick).

What timings do you get when you run the perl script in my last email?

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Curious performance issue with large number of inserts

[Tim Bradshaw]

On 5 May 2007, at 15:59, Joe Wilson wrote:

Ignore the idea above - an insert of NULL or an incrementing  
integer for the

INTEGER PRIMARY KEY yields the same timings.


I might try taking it out anyway - I don't really need it other than  
to be able to say "look at row n" to someone.


I did originally have indices before doing the inserts, but I took  
them away.




I can't reproduce your problem. I can insert 16M records into your  
table
schema in 25 minutes on a 5 year old Windows machine. The sqlite3  
process

had peak RAM usage of less than 20M.


Rats, I suspect it must be some compiler/architecture specific thing.  
I can rebuild it for x86, since I don't really care where it runs. It  
definitely isn't running out of memory though (at least, not unless  
it's throttling itself somehow, the machine has loads  free when it  
gets sick).



Thanks

--tim

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Curious performance issue with large number of inserts

[Joe Wilson]

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> > create table filemap(id integer primary key,
> >uid integer, gid integer, mtime integer,
> >vol integer,
> >path varchar(1024));
> > 
> > It has no indices built yet.
> > 
> > I'm adding quite a lot of records to it using a perl script which  
> > generates SQL like this:
> > 
> > begin;
> >   insert into filemap values(null, 1, 1, , 0, "/path/to/file");
> >   ...  more like this ...
> > commit;
> > ... repeat above ...
> > 
> > The uid, gid and mtime fields vary obviously, but there are very many  
> > paths for each uid/gid pair.  The idea is that I need to be able to  
> > say `show me all the files owned by UID x on volume y?', and we have  
> > enough data that awk can't hack it.
> > 
> > before doing this I've done a
> > 
> > pragma synchronous=off;
> > 
> > All this is just being piped into sqlite3 (I know I should use the  
> > proper interface, but it's a very quick & dirty hack).
> > 
> > I have about 16,000,000 records.  When adding them it goes really  
> > quickly for about the first 1,000,000 (using the big transaction  
> > trick made it much faster, as did the synchronous=off thing).  But  
> > then it slows down dramatically, perhaps by a factor of 100 or 1000  
> > or something.  I've actually left it running, but I'm not convinced  
> > it will have done all 16 million by Monday.
> > 
> > I have not looked at what the program is doing in the sense of system  
> > calls or any more detailed profiling.  It is clear that disk activity  
> > falls right off when it becomes slow.
> > 
> > Am I doing anything obviously stupid here?  I suspect I must be.
> 
> The batch insert you describe ought to be pretty fast since you're only
> appending data.
> 
> This is a guess - instead of inserting NULL for the INTEGER PRIMARY KEY 
> column try assigning an increasing number for each new row.
> This would avoid an OP_NewRowid per insert, which I would not think to
> be slow, but it's worth trying.

Ignore the idea above - an insert of NULL or an incrementing integer for the
INTEGER PRIMARY KEY yields the same timings.

I can't reproduce your problem. I can insert 16M records into your table
schema in 25 minutes on a 5 year old Windows machine. The sqlite3 process
had peak RAM usage of less than 20M.

If you had additional indexes on the table prior to insert (other than the 
INTEGER PRIMARY KEY), that could explain the slow results you are seeing.


$ cat ins.pl

print "
pragma page_size=8192;
pragma temp_store=memory;
pragma synchronous=off;
create table filemap(id integer primary key, uid integer, gid integer,
 mtime integer, vol integer, path varchar(1024));
begin;
";
for (my $i = 1; $i <= 1600; ++$i) {
  if ($i % 1000 == 0) { print "commit;\nbegin;\n"; }
  print "insert into filemap values(null,1,1,,0,'/path/to/file');\n";
}
print "commit;\n";

$ rm -f ins.db ; perl ins.pl | time ./sqlite3 ins.db
1389.89user 61.98system 25:13.57elapsed 95%CPU (0avgtext+0avgdata 
218880maxresident)k
0inputs+0outputs (4938major+0minor)pagefaults 0swaps

$ ls -l ins.db
-rw-r--r-- 1 User Nobody 512417792 May  5 10:47 ins.db

$ ./sqlite3 ins.db "select count(*) from filemap"
1600


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Curious performance issue with large number of inserts

[Joe Wilson]

> create table filemap(id integer primary key,
>uid integer, gid integer, mtime integer,
>vol integer,
>path varchar(1024));
> 
> It has no indices built yet.
> 
> I'm adding quite a lot of records to it using a perl script which  
> generates SQL like this:
> 
> begin;
>   insert into filemap values(null, 1, 1, , 0, "/path/to/file");
>   ...  more like this ...
> commit;
> ... repeat above ...
> 
> The uid, gid and mtime fields vary obviously, but there are very many  
> paths for each uid/gid pair.  The idea is that I need to be able to  
> say `show me all the files owned by UID x on volume y?', and we have  
> enough data that awk can't hack it.
> 
> before doing this I've done a
> 
> pragma synchronous=off;
> 
> All this is just being piped into sqlite3 (I know I should use the  
> proper interface, but it's a very quick & dirty hack).
> 
> I have about 16,000,000 records.  When adding them it goes really  
> quickly for about the first 1,000,000 (using the big transaction  
> trick made it much faster, as did the synchronous=off thing).  But  
> then it slows down dramatically, perhaps by a factor of 100 or 1000  
> or something.  I've actually left it running, but I'm not convinced  
> it will have done all 16 million by Monday.
> 
> I have not looked at what the program is doing in the sense of system  
> calls or any more detailed profiling.  It is clear that disk activity  
> falls right off when it becomes slow.
> 
> Am I doing anything obviously stupid here?  I suspect I must be.

The batch insert you describe ought to be pretty fast since you're only
appending data.

This is a guess - instead of inserting NULL for the INTEGER PRIMARY KEY 
column try assigning an increasing number for each new row.
This would avoid an OP_NewRowid per insert, which I would not think to
be slow, but it's worth trying.

You might also experiment with the sqlite3 import facility which should be
slightly faster.

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to add a new column quickly

[Tomash Brechko]

On Sat, May 05, 2007 at 19:30:59 +0800, ronggui wong wrote:
> Thanks. But there is no typo, what I want is a general solution.
> 
> 2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>:
> >On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote:
> >> . update tablename set newcolname=1 where ROWID=1
> >> . update tablename set newcolname=2 where ROWID=2
> >> . update tablename set newcolname=2 where ROWID=3

If there is no correlation between newcolname and other columns that
can be expressed as a formula, but rather you want to set newcolname
to some known Func(ROWID), you may register this function with
sqlite3_create_function() (or its equivalent for your language
bindings), and then do a single statement

  UPDATE tablename SET newcolname = Func(ROWID);

This will be faster then repeatedly searching for a row with a given
ROWID.


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to add a new column quickly

[ronggui wong]

Thanks. But there is no typo, what I want is a general solution.

2007/5/5, Tomash Brechko <[EMAIL PROTECTED]>:

On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote:
> . update tablename set newcolname=1 where ROWID=1
> . update tablename set newcolname=2 where ROWID=2
> . update tablename set newcolname=2 where ROWID=3
> .
>
> My question is: how to add the above task efificiently? Thanks!

If there is a typo in the last line, and it should have been
'newcolname=3' (not 2), then your operation is effectively

  UPDATE tablename SET newcolname = ROWID;


--
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] how to add a new column quickly

[Tomash Brechko]

On Sat, May 05, 2007 at 14:01:56 +0800, ronggui wong wrote:
> . update tablename set newcolname=1 where ROWID=1
> . update tablename set newcolname=2 where ROWID=2
> . update tablename set newcolname=2 where ROWID=3
> .
> 
> My question is: how to add the above task efificiently? Thanks!

If there is a typo in the last line, and it should have been
'newcolname=3' (not 2), then your operation is effectively

  UPDATE tablename SET newcolname = ROWID;


-- 
   Tomash Brechko

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] how to add a new column quickly

[ronggui wong]

I would like to add a new column to an existing table. I use
.alter table tablename add newcolname
to add a new col, and use
. update tablename set newcolname=1 where ROWID=1
. update tablename set newcolname=2 where ROWID=2
. update tablename set newcolname=2 where ROWID=3
.

My question is: how to add the above task efificiently? Thanks!

Ronggui Huang

-
To unsubscribe, send email to [EMAIL PROTECTED]
-