date:20080918

Re: [sqlite] Can SQLite take advantage of multiple CPU's and/ormultiple cores?

2008-09-18 Thread Graeme

On Friday 19 September 2008 09:50:53 Igor Tandetnik wrote:
> <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
>
> > Can SQLite take advantage of multiple CPU's and/or multiple
> > cores?
>
> Not automatically: SQLite API functions run on whatever thread you call
> them on. Of course, you can create multiple threads and open multiple
> SQLite connections - preferably to different DB files, to maximize
> concurrency.

Looking at the docs and the wiki it looks to me that there are no problems 
with multiple processes using the same DB, but one needs to be a bit more 
careful with threads (and you have to be sure it is compiled with threads 
enabled).

Also, are there any disadvantages to compiling threadsafe? I imagine there is 
some impact on performance. Anything else?

Graeme

-- 
Graeme Pietersz
http://moneyterms.co.uk/
http://pietersz.co.uk/

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

Re: [sqlite] Can SQLite take advantage of multiple CPU's and/ormultiple cores?

2008-09-18 Thread Igor Tandetnik

<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Can SQLite take advantage of multiple CPU's and/or multiple
> cores?

Not automatically: SQLite API functions run on whatever thread you call 
them on. Of course, you can create multiple threads and open multiple 
SQLite connections - preferably to different DB files, to maximize 
concurrency.

Igor Tandetnik



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

Re: [sqlite] Can SQLite take advantage of multiple CPU's and/or multiple cores?

2008-09-18 Thread P Kishor

On 9/18/08, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> Can SQLite take advantage of multiple CPU's and/or multiple
>  cores?
>  Thank you,
>  Malcolm

I probably don't know what I am talking about, but my sense is that
SQLite itself can't, or rather, isn't designed to work that way. On
the other hand, you can write your applcation, which is using SQLite,
to take advantage of multiple CPUs.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Can SQLite take advantage of multiple CPU's and/or multiple cores?

2008-09-18 Thread python

Can SQLite take advantage of multiple CPU's and/or multiple
cores?
Thank you,
Malcolm
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vista frustrations

2008-09-18 Thread Mark Spiegel

The SQLite part was an analogy.  That must have been beyond you.  You 
can have the last word.  You're beyond my help.

Fred Williams wrote:
> I never said a word aboout SQLite.  You ass U Me too much I suspect.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Mark Spiegel
> Sent: Thursday, September 18, 2008 11:25 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
>
> I'm sorry, I have to take issue with that statement.  The design of the
> file system/cache manager is not "pitiful".  It strives to provide good
> performance in the entire application space, not just your little corner
> of it.  It is doing the best it can with the "hint" you've given it.  If
> another (or no) hint provides better performance in your application,
> who's fault is that?  Do you realize that without the cache manager,
> fast I/O would not be possible?  Run on a debug system where only IRP
> based I/O is possible any you will be singing another tune in a hurry.
> Why do you think these hints are even available?  It is to help you
> optimize your application.
>
> The SQLite memory subsystem doesn't work well on my platform  I don't
> run around calling SQLite "pitiful".  I recognize that the authors'
> implementation(s) is probably a good performance compromise in the
> generic case.  If it is a big enough problem (which it is for me), I
> write my own version to optimize my performance.  While better, the
> integer encoding is not as good as it could be for me.  Does that mean
> the SQLite is pitiful?
>
> I should also note that as of the last time I talked to her, Molly is no
> longer handling the cache manager.  I believe she has moved back into
> the kernel group after a brief departure, but is working on something
> else.  I haven't seen the talks that Robert refers to, but suspect they
> are close to the versions I have seen in person.  I would bet they are
> still very useful and relevant.
>
> Fred Williams wrote:
>   
>> Is a sad day when an application program is forced to compensate for
>> 
> pitiful
>   
>> OS design and performance :-(
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
>> Sent: Thursday, September 18, 2008 10:31 AM
>> To: 'General Discussion of SQLite Database'
>> Subject: Re: [sqlite] Vista frustrations
>>
>>
>> After watching Molly Brown's Channel9 videos on the cache manager, I'm
>> convinced the behavior for SQLite should be to not give the filesystem any
>> hints about caching and let the cache manager itself figure it out.  The
>> exception being Windows CE, where we can confirm that when this flag is
>> 
> not
>   
>> set, the device will use compression in memory and degrade performance.
>>
>> If that's the general consensus, I'll open a ticket.
>>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
>> Sent: Thursday, September 18, 2008 7:56 AM
>> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
>> Subject: Re: [sqlite] Vista frustrations
>>
>> FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
>> cache manager (CC) in Windows and the underlying file system(s).  With
>> respect to the cache manager, it is going to affect whether or not there
>> is read ahead, how much read ahead will be used, and how long data will
>> remain in the cache (or another way, how quickly it will be dropped).
>> It has been some time since I've talked to the Queen of Cache Manger
>> about this, but as I recall CC will try to figure out what you are doing
>> if you don't give it a hint.  If you do give it a hint, then it is going
>> to run with that hint no matter what the cost.  Note that CC or the file
>> system are perfectly within their right to ignore your hints.  CC
>> generally does honor them.  NTFS, well that's another matter.
>>
>> It has been MY experience (YMMV) that database and temp file reads are
>> fairly random.  Database files also have the "nice" property that read
>> and writes are often sector (page) aligned.  Journal files should be
>> opened for sequential scan and are generally not sector (page) aligned.
>> Setting SQLite aside for a moment, for very large files that are only
>> going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
>> huge performance gains.  However, if most or all of a file is going to
>> be touched, even in random order, then it doesn't get you much and can
>> hurt you.  Most SQLite data bases _probably_ fall into that second
>> case.  If you have enough memory and a small enough file such that the
>> cache manager can hold the entire file, you are golden.  That's why some
>> people see such great SQLite performance by just sequentially reading
>> their DB files before running their SQLite application.
>>
>> The elephants in the room with that previous paragraph is 1) the amount
>> of RAM in the system and 2)

Re: [sqlite] Vista frustrations

2008-09-18 Thread Roger Binns

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jay A. Kreibich wrote:
>   SQLite is not "lying."  After poking around a bit to refresh my
>   understanding of SQLite's file structure, I think it is safe to say
>   that SQLite will almost never do a sequential file read, even if
>   you're doing a sequential table scan.
> 
> sequential table scan != sequential file access

That statement is true in general but in this specific case we are
talking about a multi-gigabyte database.  It would be fair to say that
large chunks of the database would have been created in a transaction
and that there wouldn't have been a lot of row updates/deletes/inserts
throughout it causing something similar to fragmentation.

I made the vfs track sequential vs random read accesses and then created
a 3 column table with each row being a random int, a string between 0
and 1024 chars long and another random int.  A 2,000,000 row database
occupied 1.4GB.  Page/cache size etc were all left at their defaults.
Testing was done on 64 bit Linux.

   create table foo(x,y,z)

Full scan query:  select max(z) from foo

  seq 1,298,231 random 93,599  - 93% sequential

Create index:  create index foo1 on foo(z)

  seq 244,920 random 3,261,252  - 7% sequential

Indexed query: select max(z) from foo where z>9900

  seq 0 random 7- 0% sequential

In this particular case SQLite is very much lying when a full table scan
was done as the vast majority of access is indeed sequential.  Of course
several factors will affect this such as typical size of each row,
schema, how much prior "thrashing" about of contents the database has
had etc.  I also didn't count nearly sequential access, for example if
the operating system does reads in 4kb chunks and one read was for the
first kb and the next read was for the third kb.

An example of how the numbers can be skewed for the full scan:

%Sequential   StringSize PageSize

  8 1-2KB  1KB
 99 0-1KB  4KB
 99.9   0-1KB 32KB
 97 0-.5KB 1KB

It is fair to say that SQLite does not know in advance what the access
pattern is going to be, especially without analysing the database
contents, rows per page and queries that will be executed.  Queries that
predominantly use indices (a desirable trait) are mostly random access.
 Full table scans where the row fits within the page are mostly sequential.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFI0xBomOOfHg372QQRAnXXAJ0VGwzzrOwkWF0q4Za2QpuTKnQD+QCfblin
DeHukDoCs809uawnlt5GYLc=
=l25n
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newbie problem with select statement using 'AND'

2008-09-18 Thread hugh111111


It's very strange. I used your example but I still get the same problem. I
think I'll try this on a different machine tomorrow.


P Kishor-3 wrote:
> 
> here is my simple test that works fine for me --
> 
> -- db --
> 
> [05:38 PM] ~/Sites/test$sqlite3 foo
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> .s
> CREATE TABLE members (userID INTEGER PRIMARY KEY, userName TEXT,
> userOccupation TEXT);
> sqlite> SELECT * FROM members;
> 1|richard|db maker
> 2|igor|db wizard
> 3|darren|db guru
> 4|dan|db master
> sqlite>.q
> 
> -- script --
> 
> [05:40 PM] ~/Sites/test$less foo.cgi
> 
> #!/usr/local/bin/perl
> 
> use strict;
> use warnings;
> 
> use DBI;
> 
> my $dbh = DBI->connect("dbi:SQLite:foo", "", "");
> my $sth = $dbh->prepare(qq{
> SELECT userID FROM members WHERE userName = ? AND userOccupation = ?
> });
> 
> $sth->execute('richard', 'db maker');
> 
> my $output;
> while (my @row = $sth->fetchrow_array) {
> $output .= "@row\n";
> }
> 
> $sth->finish;
> $dbh->disconnect;
> 
> print "Content-type:text/html\n\n";
> print "Hello World $output ";
> 
> -- web page --
> Hello World
> 
> 1
> -- end --
> 
> On 9/18/08, P Kishor <[EMAIL PROTECTED]> wrote:
>> On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>>  >
>>
>> >  Hi P Kishor-3
>>  >  Thanks for your reply. I have altered my script according to your
>>  >  recommendation, unfortunately the problem remains. I have also used
>> the
>>  >  eval{ } function to capture the log messages. The log said "no such
>> column:
>>  >  userOccupation(1)" Not sure why it should append (1) to my column
>> name.
>>
>>
>> Post your db schema and a few rows... let's see what is going on
>>
>>
>>
>>  >
>>  >  my new perl script is...
>>  >
>>  >
>>  >  #!/usr/bin/perl
>>  >
>>  >  use DBI;
>>  >
>>  >
>>  >
>>  >
>>  >  my $sth = $dbh->prepare(qq{
>>  >  SELECT userID FROM members WHERE userName= ? AND userOccupation= ?
>>  >  });
>>  >
>>  >
>>  > eval{ $sth->execute('John', 'Carpenter') }; warn("My warning is: $@")
>> if $@
>>  >
>>  > ;
>>  >
>>  >  my $output;
>>  >  while (my @row = $sth->fetchrow_array ) {
>>  >   $output .= join(' ', @row) . "\n";
>>  >  }
>>  >
>>  >  $dbh -> disconnect;
>>  >
>>  >  print "Content-type:text/html\n\n";
>>  >  print "Hello World $output ";
>>  >
>>  >
>>  >
>>  > and a section of the log error message is...
>>  >
>>  >  18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
>>  >  INFO: cgi: runCGI (stderr):DBD::SQLite::db prepare failed: no such
>> column:
>>  >  userOccupation(1) at dbdimp.c line 271 at
>>  >  C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 6.
>>  >  18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
>>  >  INFO: cgi: runCGI (stderr):My warning is: Can't call method "execute"
>> on an
>>  >  undefined value at C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl
>> line 10.
>>  >
>>  >
>>  >
>>  >
>>  >
>>  >  P Kishor-3 wrote:
>>  >  >
>>  >  > On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>>  >  >>
>>  >  >>  I'm using sqlite for my perl cgi website but I've got a problem
>> with the
>>  >  >>  following select statement...
>>  >  >>
>>  >  >>  SELECT userID FROM members WHERE userName='John' AND
>>  >  >>  userOccupation='Carpenter'
>>  >  >>
>>  >  >>  Yet if I input this statement from the sqlite3 command prompt it
>> works!
>>  >  >> The
>>  >  >>  problem seems to be with the 'and', the following statement works
>> fine
>>  >  >> in
>>  >  >>  both cgi script and from the sqlite3 prompt...
>>  >  >>
>>  >  >>  SELECT userID FROM members WHERE userName='John'
>>  >  >>
>>  >  >>  I hope somebody can help with this, I'm new to both perl and
>> sqlite but
>>  >  >> very
>>  >  >>  keen on using sqlite for future projects.
>>  >  >>
>>  >  >>
>>  >  >>
>>  >  >>  here is my perl script...
>>  >  >>
>>  >  >>
>>  >  >>  #!/usr/bin/perl
>>  >  >>
>>  >  >>  use DBI;
>>  >  >>
>>  >  >>  my $output;
>>  >  >>  my $sql = "SELECT userID FROM members WHERE userName='John' AND
>>  >  >>  userOccupation='Carpenter'";
>>  >  >>
>>  >  >>  my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>>  >  >>  my $sth = $dbh->prepare($sql);
>>  >  >>  $sth->execute();
>>  >  >>
>>  >  >>   while ( @row = $sth->fetchrow_array ) {
>>  >  >> $output .= "@row\n";
>>  >  >>   }
>>  >  >>
>>  >  >>  $dbh -> disconnect;
>>  >  >>
>>  >  >>  print "Content-type:text/html\n\n";
>>  >  >>  print "Hello World $output ";
>>  >  >>
>>  >  >>
>>  >  >
>>  >  > always bind variables. See untested code below
>>  >  >
>>  >  > #!/usr/bin/perl
>>  >  >
>>  >  > use DBI;
>>  >  > my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>>  >  >
>>  >  > my $sth = $dbh->prepare(qq{
>>  >  >   SELECT userID
>>  >  >   FROM members
>>  >  >   WHERE userName = ? AND userOccupation = ?
>>  >  > });
>>  >  >
>>  >  > $sth->execute('John', 'Carpenter');
>>  >  >
>>  >  > my $output;
>>  >  > while (my

Re: [sqlite] newbie problem with select statement using 'AND'

2008-09-18 Thread P Kishor

here is my simple test that works fine for me --

-- db --

[05:38 PM] ~/Sites/test$sqlite3 foo
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> .s
CREATE TABLE members (userID INTEGER PRIMARY KEY, userName TEXT,
userOccupation TEXT);
sqlite> SELECT * FROM members;
1|richard|db maker
2|igor|db wizard
3|darren|db guru
4|dan|db master
sqlite>.q

-- script --

[05:40 PM] ~/Sites/test$less foo.cgi

#!/usr/local/bin/perl

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect("dbi:SQLite:foo", "", "");
my $sth = $dbh->prepare(qq{
SELECT userID FROM members WHERE userName = ? AND userOccupation = ?
});

$sth->execute('richard', 'db maker');

my $output;
while (my @row = $sth->fetchrow_array) {
$output .= "@row\n";
}

$sth->finish;
$dbh->disconnect;

print "Content-type:text/html\n\n";
print "Hello World $output ";

-- web page --
Hello World

1
-- end --

On 9/18/08, P Kishor <[EMAIL PROTECTED]> wrote:
> On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>  >
>
> >  Hi P Kishor-3
>  >  Thanks for your reply. I have altered my script according to your
>  >  recommendation, unfortunately the problem remains. I have also used the
>  >  eval{ } function to capture the log messages. The log said "no such 
> column:
>  >  userOccupation(1)" Not sure why it should append (1) to my column name.
>
>
> Post your db schema and a few rows... let's see what is going on
>
>
>
>  >
>  >  my new perl script is...
>  >
>  >
>  >  #!/usr/bin/perl
>  >
>  >  use DBI;
>  >
>  >
>  >
>  >
>  >  my $sth = $dbh->prepare(qq{
>  >  SELECT userID FROM members WHERE userName= ? AND userOccupation= ?
>  >  });
>  >
>  >
>  > eval{ $sth->execute('John', 'Carpenter') }; warn("My warning is: $@") if $@
>  >
>  > ;
>  >
>  >  my $output;
>  >  while (my @row = $sth->fetchrow_array ) {
>  >   $output .= join(' ', @row) . "\n";
>  >  }
>  >
>  >  $dbh -> disconnect;
>  >
>  >  print "Content-type:text/html\n\n";
>  >  print "Hello World $output ";
>  >
>  >
>  >
>  > and a section of the log error message is...
>  >
>  >  18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
>  >  INFO: cgi: runCGI (stderr):DBD::SQLite::db prepare failed: no such column:
>  >  userOccupation(1) at dbdimp.c line 271 at
>  >  C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 6.
>  >  18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
>  >  INFO: cgi: runCGI (stderr):My warning is: Can't call method "execute" on 
> an
>  >  undefined value at C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 10.
>  >
>  >
>  >
>  >
>  >
>  >  P Kishor-3 wrote:
>  >  >
>  >  > On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>  >  >>
>  >  >>  I'm using sqlite for my perl cgi website but I've got a problem with 
> the
>  >  >>  following select statement...
>  >  >>
>  >  >>  SELECT userID FROM members WHERE userName='John' AND
>  >  >>  userOccupation='Carpenter'
>  >  >>
>  >  >>  Yet if I input this statement from the sqlite3 command prompt it 
> works!
>  >  >> The
>  >  >>  problem seems to be with the 'and', the following statement works fine
>  >  >> in
>  >  >>  both cgi script and from the sqlite3 prompt...
>  >  >>
>  >  >>  SELECT userID FROM members WHERE userName='John'
>  >  >>
>  >  >>  I hope somebody can help with this, I'm new to both perl and sqlite 
> but
>  >  >> very
>  >  >>  keen on using sqlite for future projects.
>  >  >>
>  >  >>
>  >  >>
>  >  >>  here is my perl script...
>  >  >>
>  >  >>
>  >  >>  #!/usr/bin/perl
>  >  >>
>  >  >>  use DBI;
>  >  >>
>  >  >>  my $output;
>  >  >>  my $sql = "SELECT userID FROM members WHERE userName='John' AND
>  >  >>  userOccupation='Carpenter'";
>  >  >>
>  >  >>  my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>  >  >>  my $sth = $dbh->prepare($sql);
>  >  >>  $sth->execute();
>  >  >>
>  >  >>   while ( @row = $sth->fetchrow_array ) {
>  >  >> $output .= "@row\n";
>  >  >>   }
>  >  >>
>  >  >>  $dbh -> disconnect;
>  >  >>
>  >  >>  print "Content-type:text/html\n\n";
>  >  >>  print "Hello World $output ";
>  >  >>
>  >  >>
>  >  >
>  >  > always bind variables. See untested code below
>  >  >
>  >  > #!/usr/bin/perl
>  >  >
>  >  > use DBI;
>  >  > my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>  >  >
>  >  > my $sth = $dbh->prepare(qq{
>  >  >   SELECT userID
>  >  >   FROM members
>  >  >   WHERE userName = ? AND userOccupation = ?
>  >  > });
>  >  >
>  >  > $sth->execute('John', 'Carpenter');
>  >  >
>  >  > my $output;
>  >  > while (my @row = $sth->fetchrow_array ) {
>  >  >   $output .= join(' ', @row) . "\n";
>  >  > }
>  >  >
>  >  > $dbh -> disconnect;
>  >  >
>  >  > print "Content-type:text/html\n\n";
>  >  > print "Hello World $output ";
>  >
>  > > ___
>  >  > sqlite-users mailing list
>  >  > sqlite-users@sqlite.org
>  >  >

Re: [sqlite] newbie problem with select statement using 'AND'

2008-09-18 Thread P Kishor

On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>
>  Hi P Kishor-3
>  Thanks for your reply. I have altered my script according to your
>  recommendation, unfortunately the problem remains. I have also used the
>  eval{ } function to capture the log messages. The log said "no such column:
>  userOccupation(1)" Not sure why it should append (1) to my column name.

Post your db schema and a few rows... let's see what is going on


>
>  my new perl script is...
>
>
>  #!/usr/bin/perl
>
>  use DBI;
>
>
>
>
>  my $sth = $dbh->prepare(qq{
>  SELECT userID FROM members WHERE userName= ? AND userOccupation= ?
>  });
>
>
> eval{ $sth->execute('John', 'Carpenter') }; warn("My warning is: $@") if $@
>
> ;
>
>  my $output;
>  while (my @row = $sth->fetchrow_array ) {
>   $output .= join(' ', @row) . "\n";
>  }
>
>  $dbh -> disconnect;
>
>  print "Content-type:text/html\n\n";
>  print "Hello World $output ";
>
>
>
> and a section of the log error message is...
>
>  18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
>  INFO: cgi: runCGI (stderr):DBD::SQLite::db prepare failed: no such column:
>  userOccupation(1) at dbdimp.c line 271 at
>  C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 6.
>  18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
>  INFO: cgi: runCGI (stderr):My warning is: Can't call method "execute" on an
>  undefined value at C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 10.
>
>
>
>
>
>  P Kishor-3 wrote:
>  >
>  > On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>  >>
>  >>  I'm using sqlite for my perl cgi website but I've got a problem with the
>  >>  following select statement...
>  >>
>  >>  SELECT userID FROM members WHERE userName='John' AND
>  >>  userOccupation='Carpenter'
>  >>
>  >>  Yet if I input this statement from the sqlite3 command prompt it works!
>  >> The
>  >>  problem seems to be with the 'and', the following statement works fine
>  >> in
>  >>  both cgi script and from the sqlite3 prompt...
>  >>
>  >>  SELECT userID FROM members WHERE userName='John'
>  >>
>  >>  I hope somebody can help with this, I'm new to both perl and sqlite but
>  >> very
>  >>  keen on using sqlite for future projects.
>  >>
>  >>
>  >>
>  >>  here is my perl script...
>  >>
>  >>
>  >>  #!/usr/bin/perl
>  >>
>  >>  use DBI;
>  >>
>  >>  my $output;
>  >>  my $sql = "SELECT userID FROM members WHERE userName='John' AND
>  >>  userOccupation='Carpenter'";
>  >>
>  >>  my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>  >>  my $sth = $dbh->prepare($sql);
>  >>  $sth->execute();
>  >>
>  >>   while ( @row = $sth->fetchrow_array ) {
>  >> $output .= "@row\n";
>  >>   }
>  >>
>  >>  $dbh -> disconnect;
>  >>
>  >>  print "Content-type:text/html\n\n";
>  >>  print "Hello World $output ";
>  >>
>  >>
>  >
>  > always bind variables. See untested code below
>  >
>  > #!/usr/bin/perl
>  >
>  > use DBI;
>  > my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>  >
>  > my $sth = $dbh->prepare(qq{
>  >   SELECT userID
>  >   FROM members
>  >   WHERE userName = ? AND userOccupation = ?
>  > });
>  >
>  > $sth->execute('John', 'Carpenter');
>  >
>  > my $output;
>  > while (my @row = $sth->fetchrow_array ) {
>  >   $output .= join(' ', @row) . "\n";
>  > }
>  >
>  > $dbh -> disconnect;
>  >
>  > print "Content-type:text/html\n\n";
>  > print "Hello World $output ";
>
> > ___
>  > sqlite-users mailing list
>  > sqlite-users@sqlite.org
>  > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>  >
>  >
>
>
> --
>  View this message in context: 
> http://www.nabble.com/newbie-problem-with-select-statement-using-%27AND%27-tp19561141p19562855.html
>
> Sent from the SQLite mailing list archive at Nabble.com.
>
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://punkish.eidesis.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] newbie problem with select statement using 'AND'

2008-09-18 Thread hugh111111


Hi P Kishor-3
Thanks for your reply. I have altered my script according to your
recommendation, unfortunately the problem remains. I have also used the
eval{ } function to capture the log messages. The log said "no such column:
userOccupation(1)" Not sure why it should append (1) to my column name.

my new perl script is...


#!/usr/bin/perl

use DBI;



my $sth = $dbh->prepare(qq{
SELECT userID FROM members WHERE userName= ? AND userOccupation= ?
}); 

eval{ $sth->execute('John', 'Carpenter') }; warn("My warning is: $@") if $@
;

my $output; 
while (my @row = $sth->fetchrow_array ) { 
  $output .= join(' ', @row) . "\n"; 
} 

$dbh -> disconnect;

print "Content-type:text/html\n\n";
print "Hello World $output ";


and a section of the log error message is...

18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
INFO: cgi: runCGI (stderr):DBD::SQLite::db prepare failed: no such column:
userOccupation(1) at dbdimp.c line 271 at
C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 6.
18-Sep-2008 22:56:37 org.apache.catalina.core.ApplicationContext log
INFO: cgi: runCGI (stderr):My warning is: Can't call method "execute" on an
undefined value at C:\tomcat\webapps\test\WEB-INF\cgi-bin\test.pl line 10.




P Kishor-3 wrote:
> 
> On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>>
>>  I'm using sqlite for my perl cgi website but I've got a problem with the
>>  following select statement...
>>
>>  SELECT userID FROM members WHERE userName='John' AND
>>  userOccupation='Carpenter'
>>
>>  Yet if I input this statement from the sqlite3 command prompt it works!
>> The
>>  problem seems to be with the 'and', the following statement works fine
>> in
>>  both cgi script and from the sqlite3 prompt...
>>
>>  SELECT userID FROM members WHERE userName='John'
>>
>>  I hope somebody can help with this, I'm new to both perl and sqlite but
>> very
>>  keen on using sqlite for future projects.
>>
>>
>>
>>  here is my perl script...
>>
>>
>>  #!/usr/bin/perl
>>
>>  use DBI;
>>
>>  my $output;
>>  my $sql = "SELECT userID FROM members WHERE userName='John' AND
>>  userOccupation='Carpenter'";
>>
>>  my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>>  my $sth = $dbh->prepare($sql);
>>  $sth->execute();
>>
>>   while ( @row = $sth->fetchrow_array ) {
>> $output .= "@row\n";
>>   }
>>
>>  $dbh -> disconnect;
>>
>>  print "Content-type:text/html\n\n";
>>  print "Hello World $output ";
>>
>>
> 
> always bind variables. See untested code below
> 
> #!/usr/bin/perl
> 
> use DBI;
> my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
> 
> my $sth = $dbh->prepare(qq{
>   SELECT userID
>   FROM members
>   WHERE userName = ? AND userOccupation = ?
> });
> 
> $sth->execute('John', 'Carpenter');
> 
> my $output;
> while (my @row = $sth->fetchrow_array ) {
>   $output .= join(' ', @row) . "\n";
> }
> 
> $dbh -> disconnect;
> 
> print "Content-type:text/html\n\n";
> print "Hello World $output ";
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/newbie-problem-with-select-statement-using-%27AND%27-tp19561141p19562855.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] newbie problem with select statement using 'AND'

2008-09-18 Thread P Kishor

On 9/18/08, hugh11 <[EMAIL PROTECTED]> wrote:
>
>  I'm using sqlite for my perl cgi website but I've got a problem with the
>  following select statement...
>
>  SELECT userID FROM members WHERE userName='John' AND
>  userOccupation='Carpenter'
>
>  Yet if I input this statement from the sqlite3 command prompt it works! The
>  problem seems to be with the 'and', the following statement works fine in
>  both cgi script and from the sqlite3 prompt...
>
>  SELECT userID FROM members WHERE userName='John'
>
>  I hope somebody can help with this, I'm new to both perl and sqlite but very
>  keen on using sqlite for future projects.
>
>
>
>  here is my perl script...
>
>
>  #!/usr/bin/perl
>
>  use DBI;
>
>  my $output;
>  my $sql = "SELECT userID FROM members WHERE userName='John' AND
>  userOccupation='Carpenter'";
>
>  my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
>  my $sth = $dbh->prepare($sql);
>  $sth->execute();
>
>   while ( @row = $sth->fetchrow_array ) {
> $output .= "@row\n";
>   }
>
>  $dbh -> disconnect;
>
>  print "Content-type:text/html\n\n";
>  print "Hello World $output ";
>
>

always bind variables. See untested code below

#!/usr/bin/perl

use DBI;
my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");

my $sth = $dbh->prepare(qq{
  SELECT userID
  FROM members
  WHERE userName = ? AND userOccupation = ?
});

$sth->execute('John', 'Carpenter');

my $output;
while (my @row = $sth->fetchrow_array ) {
  $output .= join(' ', @row) . "\n";
}

$dbh -> disconnect;

print "Content-type:text/html\n\n";
print "Hello World $output ";
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] newbie problem with select statement using 'AND'

2008-09-18 Thread hugh111111


I'm using sqlite for my perl cgi website but I've got a problem with the
following select statement...

SELECT userID FROM members WHERE userName='John' AND
userOccupation='Carpenter'

Yet if I input this statement from the sqlite3 command prompt it works! The
problem seems to be with the 'and', the following statement works fine in
both cgi script and from the sqlite3 prompt...

SELECT userID FROM members WHERE userName='John'

I hope somebody can help with this, I'm new to both perl and sqlite but very
keen on using sqlite for future projects.



here is my perl script...


#!/usr/bin/perl

use DBI;

my $output;
my $sql = "SELECT userID FROM members WHERE userName='John' AND
userOccupation='Carpenter'";

my $dbh = DBI->connect("dbi:SQLite:test.db", "", "");
my $sth = $dbh->prepare($sql);
$sth->execute();

  while ( @row = $sth->fetchrow_array ) {
$output .= "@row\n";
  }

$dbh -> disconnect;

print "Content-type:text/html\n\n";
print "Hello World $output ";

-- 
View this message in context: 
http://www.nabble.com/newbie-problem-with-select-statement-using-%27AND%27-tp19561141p19561141.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Looong sql queries (>9 seconds)

2008-09-18 Thread Jeffrey Becker

Actually I think you can drop the index on just rate.

On Thu, Sep 18, 2008 at 3:12 PM, John Stanton <[EMAIL PROTECTED]> wrote:
> I would get rid of your separate columns for day, month and year and use
> the Sqlite date format and use an index on it.
>
> chris wrote:
>> I'm at a loss and need some guidance.  My queries are taking way longer than 
>> I can use but I'm not sure what steps to take next.
>>
>> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with 
>> 256M ram.  I'm using Python to access the database.  I've created a very 
>> simple database file with two tables:
>>
>> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, 
>> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
>> seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT )
>>
>> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, 
>> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
>> seconds INTEGER, point TEXT )
>>
>> The SETTINGS table has about 95,000 entries.
>>
>> This single query takes between 9 and 12 seconds to run:
>> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM 
>> settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate
>>
>> Is there something I can do to get that time down substantially?
>>
>> Thank you for any suggestions.
>>
>>
>>
>> ___
>> 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] Vista frustrations

2008-09-18 Thread Fred Williams

I never said a word aboout SQLite.  You ass U Me too much I suspect.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Mark Spiegel
Sent: Thursday, September 18, 2008 11:25 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

I'm sorry, I have to take issue with that statement.  The design of the
file system/cache manager is not "pitiful".  It strives to provide good
performance in the entire application space, not just your little corner
of it.  It is doing the best it can with the "hint" you've given it.  If
another (or no) hint provides better performance in your application,
who's fault is that?  Do you realize that without the cache manager,
fast I/O would not be possible?  Run on a debug system where only IRP
based I/O is possible any you will be singing another tune in a hurry.
Why do you think these hints are even available?  It is to help you
optimize your application.

The SQLite memory subsystem doesn't work well on my platform  I don't
run around calling SQLite "pitiful".  I recognize that the authors'
implementation(s) is probably a good performance compromise in the
generic case.  If it is a big enough problem (which it is for me), I
write my own version to optimize my performance.  While better, the
integer encoding is not as good as it could be for me.  Does that mean
the SQLite is pitiful?

I should also note that as of the last time I talked to her, Molly is no
longer handling the cache manager.  I believe she has moved back into
the kernel group after a brief departure, but is working on something
else.  I haven't seen the talks that Robert refers to, but suspect they
are close to the versions I have seen in person.  I would bet they are
still very useful and relevant.

Fred Williams wrote:
> Is a sad day when an application program is forced to compensate for
pitiful
> OS design and performance :-(
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
> Sent: Thursday, September 18, 2008 10:31 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Vista frustrations
>
>
> After watching Molly Brown's Channel9 videos on the cache manager, I'm
> convinced the behavior for SQLite should be to not give the filesystem any
> hints about caching and let the cache manager itself figure it out.  The
> exception being Windows CE, where we can confirm that when this flag is
not
> set, the device will use compression in memory and degrade performance.
>
> If that's the general consensus, I'll open a ticket.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
> Sent: Thursday, September 18, 2008 7:56 AM
> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
> FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
> cache manager (CC) in Windows and the underlying file system(s).  With
> respect to the cache manager, it is going to affect whether or not there
> is read ahead, how much read ahead will be used, and how long data will
> remain in the cache (or another way, how quickly it will be dropped).
> It has been some time since I've talked to the Queen of Cache Manger
> about this, but as I recall CC will try to figure out what you are doing
> if you don't give it a hint.  If you do give it a hint, then it is going
> to run with that hint no matter what the cost.  Note that CC or the file
> system are perfectly within their right to ignore your hints.  CC
> generally does honor them.  NTFS, well that's another matter.
>
> It has been MY experience (YMMV) that database and temp file reads are
> fairly random.  Database files also have the "nice" property that read
> and writes are often sector (page) aligned.  Journal files should be
> opened for sequential scan and are generally not sector (page) aligned.
> Setting SQLite aside for a moment, for very large files that are only
> going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
> huge performance gains.  However, if most or all of a file is going to
> be touched, even in random order, then it doesn't get you much and can
> hurt you.  Most SQLite data bases _probably_ fall into that second
> case.  If you have enough memory and a small enough file such that the
> cache manager can hold the entire file, you are golden.  That's why some
> people see such great SQLite performance by just sequentially reading
> their DB files before running their SQLite application.
>
> The elephants in the room with that previous paragraph is 1) the amount
> of RAM in the system and 2) the other applications running.  Windows
> will try to share its resources among all the applications running as
> best it can.
>
> I have not seen any "bugs" in SQLite in this area.  It gives a
> reasonable hint for the general case.  To be fair however, I should note
> that I have my own VFS.

Re: [sqlite] Looong sql queries (>9 seconds)

2008-09-18 Thread John Stanton

I would get rid of your separate columns for day, month and year and use 
the Sqlite date format and use an index on it.

chris wrote:
> I'm at a loss and need some guidance.  My queries are taking way longer than 
> I can use but I'm not sure what steps to take next.
> 
> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with 
> 256M ram.  I'm using Python to access the database.  I've created a very 
> simple database file with two tables:
> 
> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, 
> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
> seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT )
> 
> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, 
> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
> seconds INTEGER, point TEXT )
> 
> The SETTINGS table has about 95,000 entries.
> 
> This single query takes between 9 and 12 seconds to run:
> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM 
> settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate
> 
> Is there something I can do to get that time down substantially?
> 
> Thank you for any suggestions.
> 
> 
>   
> ___
> 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] Looong sql queries (>9 seconds)

2008-09-18 Thread jason weaver

On Thu, Sep 18, 2008 at 11:58 AM, D. Richard Hipp <[EMAIL PROTECTED]> wrote:

>
> On Sep 18, 2008, at 12:46 PM, chris wrote:
>
> I'm at a loss and need some guidance.  My queries are taking way longer
>> than I can use but I'm not sure what steps to take next.
>>
>> 
>> Try this and see if it helps:
>>  CREATE INDEX idx1 ON settings(rate, year, month);
>>
>
Wow!  Thanks, Jeffrey and Richard!  Just adding the index dropped the query
time down into the 3 second range.
These two versions had a similiar effects:
CREATE INDEX idx1 ON settings(rate, year, month);
CREATE INDEX idx1 ON settings(rate);
I'll experiment with this whole new world of INDEX and maybe I can get it
down even further.  Thank you!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [?? Probable Spam] Re: Vista frustrations

2008-09-18 Thread Bogusław Brandys

Virgilio Alexandre Fornazin pisze:
> Sure. I just tell to do this test to check if the bug is related to this
> component, since it debuted on Vista.
>  

Silly thought,but could it be related to database file extension ? I 
mean , is it possible that OS is caching some files depending on 
extension  like it was with Windows XP system restore "feature".
I know it is almost impossible in this case but who knows...\

Boguslaw

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

[sqlite] Static embedding sqlite and compiling in gcc

2008-09-18 Thread icantthinkofone


> icantthinkofone wrote:
> 
> >I lost my note on what I did to make this work.  I've rearranged my
> >computers for work and installed sqlite3 on Ubuntu.  When I installed
> >sqlite3, I did ./configure --disable-shared. When I recompile my working
> >code on the new machine:
> >gcc -static -L/usr/local/lib -I/usr/local/include mycode.o -lsqlite3
> >-ldl -lpthread -o mycode
> >the code compiles and runs on a server but the compiler gives the
> >warning "Using 'dlopen' in statically linked applications requires the
> >shared libraries from the glibc version used for linking".  It doesn't
> >do that on the old one but I remember having this issue.  I just don't
> >recall how to solve it.  A little help would be appreciated.
> >  
> >
> Why do you use -ldl in the link step? That brings in the dynamic loader 
> dlopen.
> An alternative would be to explicitly specify libsqlite3.a instead of 
> -lsqlite3.
> 
> Regards,
> 
> Arjen

I tried both but get the complaint of undefined reference to "dlOpen" and 
"dlClose".

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

Re: [sqlite] Looong sql queries (>9 seconds)

2008-09-18 Thread Jeffrey Becker

Try creating an index on settings(rate)

On Thu, Sep 18, 2008 at 12:46 PM, chris <[EMAIL PROTECTED]> wrote:
> I'm at a loss and need some guidance.  My queries are taking way longer than 
> I can use but I'm not sure what steps to take next.
>
> I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with 
> 256M ram.  I'm using Python to access the database.  I've created a very 
> simple database file with two tables:
>
> CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, 
> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
> seconds INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT )
>
> CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, 
> year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
> seconds INTEGER, point TEXT )
>
> The SETTINGS table has about 95,000 entries.
>
> This single query takes between 9 and 12 seconds to run:
> SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM 
> settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate
>
> Is there something I can do to get that time down substantially?
>
> Thank you for any suggestions.
>
>
>
> ___
> 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] Program is crashed on sqlite3_finalize(pStmt);

2008-09-18 Thread Joanne Pham

Thanks Robert!
 
> So your solution is to NULL your pointer after calling finalize() and don't
> call finalize() again if your pointer is NULL.
I think I need to do so.
Again thanks,
JP





- Original Message 
From: Robert Simpson <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Thursday, September 18, 2008 8:54:49 AM
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

You can't.  The memory pStmt points to is freed and invalid after the call
to finalize.  Worse, that freed memory could've already been reallocated for
some other purpose by the time the call to finalize() returns control to
you.

So your solution is to NULL your pointer after calling finalize() and don't
call finalize() again if your pointer is NULL.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joanne Pham
Sent: Thursday, September 18, 2008 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called
sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, September 17, 2008 10:14:18 AM
Subject: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize
so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP

 


___
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-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] Looong sql queries (>9 seconds)

2008-09-18 Thread chris

I'm at a loss and need some guidance.  My queries are taking way longer than I 
can use but I'm not sure what steps to take next.

I'm using SQLite 3.6.2 on a small reasonable powerful embedded platform with 
256M ram.  I'm using Python to access the database.  I've created a very simple 
database file with two tables:

CREATE table IF NOT EXISTS settings( id INTEGER PRIMARY KEY AUTOINCREMENT, year 
INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, seconds 
INTEGER, curtemp TEXT, relhum TEXT, watts TEXT, rate TEXT )

CREATE table IF NOT EXISTS setpoints( id INTEGER PRIMARY KEY AUTOINCREMENT, 
year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minutes INTEGER, 
seconds INTEGER, point TEXT )

The SETTINGS table has about 95,000 entries.

This single query takes between 9 and 12 seconds to run:
SELECT rate, day, month, rate * SUM(watts) / 12000 AS TotalBillAtRate FROM 
settings WHERE (day <= 18) AND (month = 9) AND (year = 2008) GROUP BY rate

Is there something I can do to get that time down substantially?

Thank you for any suggestions.


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

Re: [sqlite] Vista frustrations

2008-09-18 Thread Mark Spiegel

I'm sorry, I have to take issue with that statement.  The design of the 
file system/cache manager is not "pitiful".  It strives to provide good 
performance in the entire application space, not just your little corner 
of it.  It is doing the best it can with the "hint" you've given it.  If 
another (or no) hint provides better performance in your application, 
who's fault is that?  Do you realize that without the cache manager, 
fast I/O would not be possible?  Run on a debug system where only IRP 
based I/O is possible any you will be singing another tune in a hurry.  
Why do you think these hints are even available?  It is to help you 
optimize your application.

The SQLite memory subsystem doesn't work well on my platform  I don't 
run around calling SQLite "pitiful".  I recognize that the authors' 
implementation(s) is probably a good performance compromise in the 
generic case.  If it is a big enough problem (which it is for me), I 
write my own version to optimize my performance.  While better, the 
integer encoding is not as good as it could be for me.  Does that mean 
the SQLite is pitiful?

I should also note that as of the last time I talked to her, Molly is no 
longer handling the cache manager.  I believe she has moved back into 
the kernel group after a brief departure, but is working on something 
else.  I haven't seen the talks that Robert refers to, but suspect they 
are close to the versions I have seen in person.  I would bet they are 
still very useful and relevant.

Fred Williams wrote:
> Is a sad day when an application program is forced to compensate for pitiful
> OS design and performance :-(
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
> Sent: Thursday, September 18, 2008 10:31 AM
> To: 'General Discussion of SQLite Database'
> Subject: Re: [sqlite] Vista frustrations
>
>
> After watching Molly Brown's Channel9 videos on the cache manager, I'm
> convinced the behavior for SQLite should be to not give the filesystem any
> hints about caching and let the cache manager itself figure it out.  The
> exception being Windows CE, where we can confirm that when this flag is not
> set, the device will use compression in memory and degrade performance.
>
> If that's the general consensus, I'll open a ticket.
>
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
> Sent: Thursday, September 18, 2008 7:56 AM
> To: [EMAIL PROTECTED]; General Discussion of SQLite Database
> Subject: Re: [sqlite] Vista frustrations
>
> FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
> cache manager (CC) in Windows and the underlying file system(s).  With
> respect to the cache manager, it is going to affect whether or not there
> is read ahead, how much read ahead will be used, and how long data will
> remain in the cache (or another way, how quickly it will be dropped).
> It has been some time since I've talked to the Queen of Cache Manger
> about this, but as I recall CC will try to figure out what you are doing
> if you don't give it a hint.  If you do give it a hint, then it is going
> to run with that hint no matter what the cost.  Note that CC or the file
> system are perfectly within their right to ignore your hints.  CC
> generally does honor them.  NTFS, well that's another matter.
>
> It has been MY experience (YMMV) that database and temp file reads are
> fairly random.  Database files also have the "nice" property that read
> and writes are often sector (page) aligned.  Journal files should be
> opened for sequential scan and are generally not sector (page) aligned.
> Setting SQLite aside for a moment, for very large files that are only
> going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
> huge performance gains.  However, if most or all of a file is going to
> be touched, even in random order, then it doesn't get you much and can
> hurt you.  Most SQLite data bases _probably_ fall into that second
> case.  If you have enough memory and a small enough file such that the
> cache manager can hold the entire file, you are golden.  That's why some
> people see such great SQLite performance by just sequentially reading
> their DB files before running their SQLite application.
>
> The elephants in the room with that previous paragraph is 1) the amount
> of RAM in the system and 2) the other applications running.  Windows
> will try to share its resources among all the applications running as
> best it can.
>
> I have not seen any "bugs" in SQLite in this area.  It gives a
> reasonable hint for the general case.  To be fair however, I should note
> that I have my own VFS.  It does unbuffered I/O for database files and
> sequential, cached I/O for journal files.  If you think you can get
> better performance with different flags, create your own VFS, starting
> with the Windows VFS and make the changes.  You can get as sophisticated
> with your

Re: [sqlite] Vista frustrations

2008-09-18 Thread Robert Simpson

Designing operating systems is hard work, and I don't envy the teams that do
it.  I've found my Vista experience to be rather enjoyable, especially post
SP1.  I don't think there's an OS out today that isn't annoying in one
fashion or another.  Having an application or library implement a workaround
for an OS design flaw is not unique to Windows by any stretch.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Fred Williams
Sent: Thursday, September 18, 2008 8:54 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

Is a sad day when an application program is forced to compensate for pitiful
OS design and performance :-(

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
Sent: Thursday, September 18, 2008 10:31 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations

After watching Molly Brown's Channel9 videos on the cache manager, I'm
convinced the behavior for SQLite should be to not give the filesystem any
hints about caching and let the cache manager itself figure it out.  The
exception being Windows CE, where we can confirm that when this flag is not
set, the device will use compression in memory and degrade performance.

If that's the general consensus, I'll open a ticket.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
Sent: Thursday, September 18, 2008 7:56 AM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
cache manager (CC) in Windows and the underlying file system(s).  With
respect to the cache manager, it is going to affect whether or not there
is read ahead, how much read ahead will be used, and how long data will
remain in the cache (or another way, how quickly it will be dropped).
It has been some time since I've talked to the Queen of Cache Manger
about this, but as I recall CC will try to figure out what you are doing
if you don't give it a hint.  If you do give it a hint, then it is going
to run with that hint no matter what the cost.  Note that CC or the file
system are perfectly within their right to ignore your hints.  CC
generally does honor them.  NTFS, well that's another matter.

It has been MY experience (YMMV) that database and temp file reads are
fairly random.  Database files also have the "nice" property that read
and writes are often sector (page) aligned.  Journal files should be
opened for sequential scan and are generally not sector (page) aligned.
Setting SQLite aside for a moment, for very large files that are only
going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
huge performance gains.  However, if most or all of a file is going to
be touched, even in random order, then it doesn't get you much and can
hurt you.  Most SQLite data bases _probably_ fall into that second
case.  If you have enough memory and a small enough file such that the
cache manager can hold the entire file, you are golden.  That's why some
people see such great SQLite performance by just sequentially reading
their DB files before running their SQLite application.

The elephants in the room with that previous paragraph is 1) the amount
of RAM in the system and 2) the other applications running.  Windows
will try to share its resources among all the applications running as
best it can.

I have not seen any "bugs" in SQLite in this area.  It gives a
reasonable hint for the general case.  To be fair however, I should note
that I have my own VFS.  It does unbuffered I/O for database files and
sequential, cached I/O for journal files.  If you think you can get
better performance with different flags, create your own VFS, starting
with the Windows VFS and make the changes.  You can get as sophisticated
with your hints as you want.  You can write your own caching system if
you've ingested way too much caffeine.  (Did I mention that the VFS
stuff is great!)

I would not as a general rule advise people (customers) to change the
way their Windows system caches globally for the benefit of one of your
applications.  Eventually, that is going to bite you with some support
calls.

Jay A. Kreibich wrote:
> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the
wall:
>
>
>> The second is that SQLite when opening a file under Windows explicitly
>> tells Windows that the file will be used for random access even though
>> that is not the case.  Windows uses this hint to override its builtin
>> heuristics which can cause bug #1.
>>
>
>
>> Bug #2 is that SQLite is lying to the operating system and could result
>> in performance degradation if the operating system actually pays
>> attention to the hint.
>>
>
>   SQLite is not "lying."  After poking around a bit to refresh my
>   understanding of SQLite's file structure, I think it is safe to say
>

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

2008-09-18 Thread Mihai Limbasan

Or to simply not release the same resource twice. That is clearly a bug 
- the control flow in your application is incorrect if it does this. You 
could work around it with the NULL value hack if you're short on time, 
but I recommend trying to find out why this happens since bugs like 
these might come back and bite you in the ass when you least expect it...


Robert Simpson wrote:

You can't.  The memory pStmt points to is freed and invalid after the call
to finalize.  Worse, that freed memory could've already been reallocated for
some other purpose by the time the call to finalize() returns control to
you.

So your solution is to NULL your pointer after calling finalize() and don't
call finalize() again if your pointer is NULL.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joanne Pham
Sent: Thursday, September 18, 2008 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called

sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP
  


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

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

2008-09-18 Thread Robert Simpson

You can't.  The memory pStmt points to is freed and invalid after the call
to finalize.  Worse, that freed memory could've already been reallocated for
some other purpose by the time the call to finalize() returns control to
you.

So your solution is to NULL your pointer after calling finalize() and don't
call finalize() again if your pointer is NULL.

Robert



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Joanne Pham
Sent: Thursday, September 18, 2008 8:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called
sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, September 17, 2008 10:14:18 AM
Subject: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize
so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP

 


___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Vista frustrations

2008-09-18 Thread Fred Williams

Is a sad day when an application program is forced to compensate for pitiful
OS design and performance :-(

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Robert Simpson
Sent: Thursday, September 18, 2008 10:31 AM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Vista frustrations

After watching Molly Brown's Channel9 videos on the cache manager, I'm
convinced the behavior for SQLite should be to not give the filesystem any
hints about caching and let the cache manager itself figure it out.  The
exception being Windows CE, where we can confirm that when this flag is not
set, the device will use compression in memory and degrade performance.

If that's the general consensus, I'll open a ticket.

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Mark Spiegel
Sent: Thursday, September 18, 2008 7:56 AM
To: [EMAIL PROTECTED]; General Discussion of SQLite Database
Subject: Re: [sqlite] Vista frustrations

FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the
cache manager (CC) in Windows and the underlying file system(s).  With
respect to the cache manager, it is going to affect whether or not there
is read ahead, how much read ahead will be used, and how long data will
remain in the cache (or another way, how quickly it will be dropped).
It has been some time since I've talked to the Queen of Cache Manger
about this, but as I recall CC will try to figure out what you are doing
if you don't give it a hint.  If you do give it a hint, then it is going
to run with that hint no matter what the cost.  Note that CC or the file
system are perfectly within their right to ignore your hints.  CC
generally does honor them.  NTFS, well that's another matter.

It has been MY experience (YMMV) that database and temp file reads are
fairly random.  Database files also have the "nice" property that read
and writes are often sector (page) aligned.  Journal files should be
opened for sequential scan and are generally not sector (page) aligned.
Setting SQLite aside for a moment, for very large files that are only
going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show
huge performance gains.  However, if most or all of a file is going to
be touched, even in random order, then it doesn't get you much and can
hurt you.  Most SQLite data bases _probably_ fall into that second
case.  If you have enough memory and a small enough file such that the
cache manager can hold the entire file, you are golden.  That's why some
people see such great SQLite performance by just sequentially reading
their DB files before running their SQLite application.

The elephants in the room with that previous paragraph is 1) the amount
of RAM in the system and 2) the other applications running.  Windows
will try to share its resources among all the applications running as
best it can.

I have not seen any "bugs" in SQLite in this area.  It gives a
reasonable hint for the general case.  To be fair however, I should note
that I have my own VFS.  It does unbuffered I/O for database files and
sequential, cached I/O for journal files.  If you think you can get
better performance with different flags, create your own VFS, starting
with the Windows VFS and make the changes.  You can get as sophisticated
with your hints as you want.  You can write your own caching system if
you've ingested way too much caffeine.  (Did I mention that the VFS
stuff is great!)

I would not as a general rule advise people (customers) to change the
way their Windows system caches globally for the benefit of one of your
applications.  Eventually, that is going to bite you with some support
calls.

Jay A. Kreibich wrote:
> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the
wall:
>
>
>> The second is that SQLite when opening a file under Windows explicitly
>> tells Windows that the file will be used for random access even though
>> that is not the case.  Windows uses this hint to override its builtin
>> heuristics which can cause bug #1.
>>
>
>
>> Bug #2 is that SQLite is lying to the operating system and could result
>> in performance degradation if the operating system actually pays
>> attention to the hint.
>>
>
>   SQLite is not "lying."  After poking around a bit to refresh my
>   understanding of SQLite's file structure, I think it is safe to say
>   that SQLite will almost never do a sequential file read, even if
>   you're doing a sequential table scan.
>
> sequential table scan != sequential file access
>
>   There are some specific situations when you might get bursts of
sequential
>   reads, but only for very specific page layouts with very specific
>   types of queries.  In short, not the common case.  Furthermore, even
>   those patterns can get broken up and shuffled around depending on the
>   state of SQLite's page cache-- especially if it is bumped up a few
>   dozen megs.  So simply running different types of queries can

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

2008-09-18 Thread Joanne Pham

Hi All,
How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again. I think I called 
sqlite3_finalize(pStmt) twice so my application is crashed.
Thanks,
JP



- Original Message 
From: Joanne Pham <[EMAIL PROTECTED]>
To: General Discussion of SQLite Database 
Sent: Wednesday, September 17, 2008 10:14:18 AM
Subject: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Hi All,
I have c++ application which is used SQLite 3.5.9.
Occasionally  the application is crash on  
 sqlite3_finalize(pStmt);
Is that true the sqlite3_finalize(pStmt) invoked twice?
How to avoid this crashed problem? How to check if the pStmt is not finalize so 
sqlite3_finalize(pStmt) can be call again.
Thanks,
JP

 


___
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] Vista frustrations

2008-09-18 Thread Mark Spiegel

FILE_FLAG_RANDOM_ACCESS and FILE_FLAG_SEQUENTIAL_SCAN are hints to the 
cache manager (CC) in Windows and the underlying file system(s).  With 
respect to the cache manager, it is going to affect whether or not there 
is read ahead, how much read ahead will be used, and how long data will 
remain in the cache (or another way, how quickly it will be dropped).  
It has been some time since I've talked to the Queen of Cache Manger 
about this, but as I recall CC will try to figure out what you are doing 
if you don't give it a hint.  If you do give it a hint, then it is going 
to run with that hint no matter what the cost.  Note that CC or the file 
system are perfectly within their right to ignore your hints.  CC 
generally does honor them.  NTFS, well that's another matter.

It has been MY experience (YMMV) that database and temp file reads are 
fairly random.  Database files also have the "nice" property that read 
and writes are often sector (page) aligned.  Journal files should be 
opened for sequential scan and are generally not sector (page) aligned.  
Setting SQLite aside for a moment, for very large files that are only 
going to be touched in a few places FILE_FLAG_RANDOM_ACCESS can show 
huge performance gains.  However, if most or all of a file is going to 
be touched, even in random order, then it doesn't get you much and can 
hurt you.  Most SQLite data bases _probably_ fall into that second 
case.  If you have enough memory and a small enough file such that the 
cache manager can hold the entire file, you are golden.  That's why some 
people see such great SQLite performance by just sequentially reading 
their DB files before running their SQLite application.

The elephants in the room with that previous paragraph is 1) the amount 
of RAM in the system and 2) the other applications running.  Windows 
will try to share its resources among all the applications running as 
best it can.

I have not seen any "bugs" in SQLite in this area.  It gives a 
reasonable hint for the general case.  To be fair however, I should note 
that I have my own VFS.  It does unbuffered I/O for database files and 
sequential, cached I/O for journal files.  If you think you can get 
better performance with different flags, create your own VFS, starting 
with the Windows VFS and make the changes.  You can get as sophisticated 
with your hints as you want.  You can write your own caching system if 
you've ingested way too much caffeine.  (Did I mention that the VFS 
stuff is great!)

I would not as a general rule advise people (customers) to change the 
way their Windows system caches globally for the benefit of one of your 
applications.  Eventually, that is going to bite you with some support 
calls.

Jay A. Kreibich wrote:
> On Wed, Sep 17, 2008 at 06:00:45PM -0700, Roger Binns scratched on the wall:
>
>   
>> The second is that SQLite when opening a file under Windows explicitly
>> tells Windows that the file will be used for random access even though
>> that is not the case.  Windows uses this hint to override its builtin
>> heuristics which can cause bug #1.
>> 
>
>   
>> Bug #2 is that SQLite is lying to the operating system and could result
>> in performance degradation if the operating system actually pays
>> attention to the hint.
>> 
>
>   SQLite is not "lying."  After poking around a bit to refresh my
>   understanding of SQLite's file structure, I think it is safe to say
>   that SQLite will almost never do a sequential file read, even if
>   you're doing a sequential table scan.
>
> sequential table scan != sequential file access
>   
>   There are some specific situations when you might get bursts of sequential
>   reads, but only for very specific page layouts with very specific
>   types of queries.  In short, not the common case.  Furthermore, even
>   those patterns can get broken up and shuffled around depending on the
>   state of SQLite's page cache-- especially if it is bumped up a few
>   dozen megs.  So simply running different types of queries can change
>   the access patterns (this is true of the OS's file system cache as
>   well, of course).
>
>   It might be worth instrumenting a few systems and having a look, but
>   in general, if you had to label SQLite's access pattern, I think
>   "random" would be the most appropriate label.
>
>
>
>   I also contend that if the Windows file cache becomes some kind of
>   bumbling idiot if you actually try to define an access pattern, then
>   something is wrong.  There is a very good reason why the POSIX
>   functions for doing this kind of thing are called "*advise()".  You
>   might seed the heuristic statistics in a specific direction, but they
>   should never be totally over-ridden.  That quickly leads to stupid
>   behaviors, like grabbing all the RAM on the system and not letting go.
>
>
>
>   Of course, we could argue philosophy for a long time.  In the here
>   and now to work around MS's inconsistencies, it looks like the best
>

Re: [sqlite] INTEGER store size

2008-09-18 Thread Jay A. Kreibich

On Thu, Sep 18, 2008 at 04:58:35PM +0300, Nick Shaforostoff scratched on the 
wall:
> Hi. I'm deciding between having several INTEGER fields and only one,
> which is a bit combination (i'd access it using e.g. main.bits&0x0011,
> main.bits&0x0100 and so on).
> 
> The docs say "INTEGER. The value is a signed integer, stored in 1, 2,
> 3, 4, 6, or 8 bytes depending on the magnitude of the value."
> So: which value is used to determine magnitude?

  The value you pass via INSERT.  43 takes one byte.  243 takes two
  (signed).  90435 takes three, etc.  Each individual row/element is
  custom sized.

> Will the database size be larger if I use several fields (containing
> numbers smaller than e.g. 255) instead of a one bits field?

  If you're bit-packing at something less than the byte level, then
  clearly more fields is going to take more space.  Additionally, there
  is some small amount of space required to designate each column in a
  row.  So overall, yes, the individual fields are likely to be a bit
  larger.

  However, depending on what you're doing with the data, chances are
  the multi-column format is going to be much more useful (and faster)
  in nearly every other way.  You can't put an index on the middle 5
  bits of an integer field, for example.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Triggers on attached databases

2008-09-18 Thread Dennis Volodomanov

Hello all,

Is it possible to create a trigger on an attached database? I can't seem 
to, so I was wondering whether my trigger SQL is wrong (although it 
works if I remove the attached database's name from the table).

The only other way that I see to do what I need would be to create 
temporary tables in the main database, copy data there, then update it 
and then copy that data to the destination tables...

Maybe there's a different way to do this, can someone please advise me 
if there's a better way to accomplish the following:

In a simple scenario, I have 2 tables, data in one of them depends on 
the other:

CREATE TABLE table1 ( ID INTEGER PRIMARY KEY, Name);
CREATE TABLE table2 ( ID INTEGER PRIMARY KEY, NameID);

where NameID in table 2 == ID in table 1. Almost always table2 has 
multiple entries with the same NameID, thus linking a list of items to 
one item from table1.

Now, I've extracted this data from the database and saved it as a small 
database that I later want to add to another database. When I'll be 
inserting items from the small database's table1 into the large 
database's table1, the ID in table1 will change, because there'll be 
entries with the same ID. At this point I need to update the NameID in 
table2 to reflect this change before I insert items from table2 into the 
main database. That's where I wanted to use a trigger (I attach the 
small database to the main one).

I'll appreciate any thoughts on how best to accomplish this.

Thank you in advance,

   Dennis

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

[sqlite] pagecache?

2008-09-18 Thread Kenneth Long

Hi list,

I'm trying to get sqlite to utilize the pagecache and scratch buffers.

But when the code exits it returns pagecache overflow and scratch buffer 
overflow .

The to configure the pagecache and scratch are done in the main section of 
code  as follows:
Then connections to sqlite are made in other threads.

sqlite3_initialize() ;
sqlite3_config(SQLITE_CONFIG_MEMSTATUS, 1 );
sqlite3_config(SQLITE_CONFIG_LOOKASIDE, 64, 64 );

sz = 8 * pgSz ;
 pBuf = malloc ( (sz * threads) + 2048 )  ;
 if ( pBuf != NULL) {
 sqlite3_config(SQLITE_CONFIG_SCRATCH,pBuf, sz, threads);
 }

if (pgCache > 0 && pgSz == 4096) {
    npgSz =  4368 ;   // Divisible by 16
    sz = npgSz * pgCache ;
    pBuf = malloc(sz) ;
    if ( pBuf != NULL) {
    sqlite3_config(SQLITE_CONFIG_PAGECACHE,pBuf, npgSz,  pgCache  );
    } 

 }

Any ideas why the pagecache or scratch is  not utilized at all ?

Thanks,
Ken

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

[sqlite] Triggers on attached databases

2008-09-18 Thread Dennis Volodomanov

Hello all,

Is it possible to create a trigger on an attached database? I can't seem
to, so I was wondering whether my trigger SQL is wrong (although it
works if I remove the attached database's name from the table).

The only other way that I see to do what I need would be to create
temporary tables in the main database, copy data there, then update it
and then copy that data to the destination tables...

Maybe there's a different way to do this, can someone please advise me
if there's a better way to accomplish the following:

In a simple scenario, I have 2 tables, data in one of them depends on
the other:

CREATE TABLE table1 ( ID INTEGER PRIMARY KEY, Name);
CREATE TABLE table2 ( ID INTEGER PRIMARY KEY, NameID);

where NameID in table 2 == ID in table 1. Almost always table2 has
multiple entries with the same NameID, thus linking a list of items to
one item from table1.

Now, I've extracted this data from the database and saved it as a small
database that I later want to add to another database. When I'll be
inserting items from the small database's table1 into the large
database's table1, the ID in table1 will change, because there'll be
entries with the same ID. At this point I need to update the NameID in
table2 to reflect this change before I insert items from table2 into the
main database. That's where I wanted to use a trigger (I attach the
small database to the main one).

I'll appreciate any thoughts on how best to accomplish this.

Thank you in advance,

   Dennis


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

Re: [sqlite] Static embedding sqlite and compiling in gcc

2008-09-18 Thread Arjen Markus

icantthinkofone wrote:

>I lost my note on what I did to make this work.  I've rearranged my
>computers for work and installed sqlite3 on Ubuntu.  When I installed
>sqlite3, I did ./configure --disable-shared. When I recompile my working
>code on the new machine:
>gcc -static -L/usr/local/lib -I/usr/local/include mycode.o -lsqlite3
>-ldl -lpthread -o mycode
>the code compiles and runs on a server but the compiler gives the
>warning "Using 'dlopen' in statically linked applications requires the
>shared libraries from the glibc version used for linking".  It doesn't
>do that on the old one but I remember having this issue.  I just don't
>recall how to solve it.  A little help would be appreciated.
>  
>
Why do you use -ldl in the link step? That brings in the dynamic loader 
dlopen.
An alternative would be to explicitly specify libsqlite3.a instead of 
-lsqlite3.

Regards,

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

Re: [sqlite] Can SQLite take advantage of multiple CPU's and/ormultiple cores?

Re: [sqlite] Can SQLite take advantage of multiple CPU's and/ormultiple cores?

Re: [sqlite] Can SQLite take advantage of multiple CPU's and/or multiple cores?

[sqlite] Can SQLite take advantage of multiple CPU's and/or multiple cores?

Re: [sqlite] Vista frustrations

Re: [sqlite] Vista frustrations

Re: [sqlite] newbie problem with select statement using 'AND'

Re: [sqlite] newbie problem with select statement using 'AND'

Re: [sqlite] newbie problem with select statement using 'AND'

Re: [sqlite] newbie problem with select statement using 'AND'

Re: [sqlite] newbie problem with select statement using 'AND'

[sqlite] newbie problem with select statement using 'AND'

Re: [sqlite] Looong sql queries (>9 seconds)

Re: [sqlite] Vista frustrations

Re: [sqlite] Looong sql queries (>9 seconds)

Re: [sqlite] Looong sql queries (>9 seconds)

Re: [sqlite] [?? Probable Spam] Re: Vista frustrations

[sqlite] Static embedding sqlite and compiling in gcc

Re: [sqlite] Looong sql queries (>9 seconds)

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

[sqlite] Looong sql queries (>9 seconds)

Re: [sqlite] Vista frustrations

Re: [sqlite] Vista frustrations

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Re: [sqlite] Vista frustrations

Re: [sqlite] Program is crashed on sqlite3_finalize(pStmt);

Re: [sqlite] Vista frustrations

Re: [sqlite] INTEGER store size

[sqlite] Triggers on attached databases

[sqlite] pagecache?

[sqlite] Triggers on attached databases

Re: [sqlite] Static embedding sqlite and compiling in gcc

33 matches

Site Navigation

Mail list logo

Footer information