Re: [sqlite] select performance with join

[Igor Tandetnik]

 wrote in message
news:20090508113252.2uqkghcsj6og8...@webmail.korg.it
> Citando Igor Tandetnik :
>
>> Andrea Galeazzi  wrote:
>>> but when I execute:
>>>
>>> SELECT S.id,title,artist,bpm,name
>>>
>>> FROM Song AS S
>>>
>>> LEFT JOIN Genre AS G ON (S.genre_id = G.id)
>>>
>>> WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)
>>>
>>> ORDER BY name DESC, S.id DESC LIMIT 20;
>>
>> Note that LEFT JOIN is pointless here, since any record with
>> G.name=NULL won't make it past the WHERE clause. Replace it with
>> plain JOIN, you should see an improvement.
>>
> I replaced LEFT JOIN with JOIN but it got worse

This means that the majority of all records in Song table satisfy the 
condition of name<= 'zUmM'. Basically, your query leaves SQLite no 
choice but to scan all records in Song. I don't see how it could be made 
to work any faster (without significant redesign of the schema - e.g. 
moving genre name into Song table).

> But I think I need to use LEFT JOIN because I have also
> to accept the records with S.genre_id = NULL.

Well, if you need such records, then you need a different query. The one 
you show doesn't return these records, despite using LEFT JOIN. Don't 
take my word for it, test it on a small sample database.

> I also tried this query:
> "SELECT S.id,title,artist,bpm,name "
> "FROM Song  AS S, Genre AS G "
> "WHERE (S.genre_id = G.id) AND (name<= 'zUmM') AND (name< 'zUmM' OR
> S.id< 8122) "
> "ORDER BY name DESC, S.id DESC LIMIT 20";
> even if it doesn't work for me because it doesn't  match  S.genre_id =
> NULL, I noticed a little improvement to 6000 ms. Then I delete S.id
> DESC and the performance has been abruptly increased to 400 ms.

Again, this means that the condition (name<= 'zUmM') matches a large 
number of records in Song. When not ordering by S.id, SQLite can do the 
following: scan Genre table starting from 'zUmM' and going down, using 
an index on Genre(name). For each record in Genre, retrieve 
corresponding records in Song using an index on Song(genre_id). As soon 
as it got 20 records, it can stop.

If you also order on S.id, SQLite must continue the process described 
above until it retieves _all_ records, sort them, and then return top 
20.

Run your queries (in sqlite3 command line utility or your favorite 
management tool) with EXPLAIN QUERY PLAN prepended. You may find the 
results illuminating.

Igor Tandetnik 



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

Re: [sqlite] select performance with join

[Igor Tandetnik]

"Jim Wilcoxson"  wrote
in message
news:c5830b750905080813p6bf901bn526c81ca8ce7a...@mail.gmail.com
> I don't know if it makes any difference, but is that where clause the
> same as:
>
> WHERE name< 'zUmM' OR (name= 'zUmM' AND S.id< 8122)

SQLite's optimizer cannot use an index for any condition involving OR. 
That's why it's common to write an equivalent but somewhat unnatural

name <= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)

This way, at least the first condition has a chance of being satisfied 
with an index.

Igor Tandetnik 



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

[sqlite] Concurrent sqlite3OsAccess() and sqlite3OsDelete() on journal file

[Dave Toll]

Hello list

 

I'm seeing an issue (on Linux build) where 2 different shared-cache DB
connections accessing the same database get confused about the status of
the journal file. One thread checks for the existence of the journal
with sqlite3OsAccess(), and at the same time the other deletes the
journal with sqlite3OsDelete(). Both calls are successful - one thread
thinks the journal exists and tries to open it read-only (resulting in
SQLITE_CANTOPEN), while the other thread has already deleted it. I
opened a ticket (http://www.sqlite.org/cvstrac/tktview?tn=3833) with all
the details for this issue against 3.6.13 and I can reproduce it against
3.6.14. I would expect these calls to be protected via a mutex or the
VFS file-locking mechanism - I've tried tracing this in the code but I'm
having difficulty following it. Can anyone shed any light on this? I'm
hoping I've missed something obvious.

 

A separate question - after a successful COMMIT or ROLLBACK, will the DB
connection ever keep a SHARED lock on the main DB file? Or should the DB
connection always fully unlock the main DB file?

 

Cheers,

Dave.

 

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

[sqlite] Compiling sqlite using chars unsigned by default

[Axel Mammes]

Great! Thanks.

On May 8, 2009, at 3:34 PM, Axel Mammes wrote:

> Hi, I am using the ARM RVDS 2.0.1 compiler to try to get SQLite on a
> VeriFone Vx570 point of sale terminal running VerixV operating
> system. I am
> still working on getting it to work, but before I continue I need to
> know if
> the fact that chars are unsigned by default will break sqlite build.


It should work fine.

D. Richard Hipp
d...@hwaci.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.14

[Mark Spiegel]

Based on what I've read, it certainly could.  As I understand it, there 
is a single queue for all the writes so the writes for multiple 
databases, journals, etc. are going to that single queue.  Based on 
that, it is certainly "possible" that your overall throughput and 
throughput for any individual database could be negatively affected.

Note that my file system knowledge is restricted to Windows platforms.  
Please don't extend my speculation to other platforms.  I also note that 
I have done no performance testing with this VFS myself and have seen no 
data provided.

Doug wrote:
> Would this perhaps affect throughput in the case where multiple database
> files are open?  For example, I have a handful of databases (10?) that are
> open at any given time, and reads and writes are taking place on separate
> threads.  Naturally writes that happen to the same database ultimately get
> serialized by the database-level locks, but writes to other databases
> continue to work.  But using the async feature would serialize all reads and
> writes to all databases, is that correct?
>
> Thanks
> Doug
>
>   
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Christian Smith
>> Sent: Friday, May 08, 2009 7:24 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SQLite version 3.6.14
>>
>> On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote:
>> 
>>> On May 8, 2009, at 5:21 PM, Christian Smith wrote:
>>>
>>>   
 On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
 
> A new optional extension is included that implements an
> asynchronous I/
> O backend for SQLite on either windows or unix.  The asynchronous
>   
>> I/O
>> 
> backend processes all writes using a background thread.  This
>   
>> gives
>> 
> the appearance of faster response time at the cost of durability
>   
>> and
>> 
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html
>   
>> for
>> 
> additional information.
>   
 What are the benefits of using async I/O over "PRAGMA synchronous =
 OFF"?
 If AIO is used for the rollback journal as well, you've lost your
 
>> ACID
>> 
 properties already, so you may as well just use "PRAGMA synchronous
 =  OFF"
 anyway and keep the code simpler.
 
>>> That's not the case. You lose the Durability property, in that a
>>>   
>> COMMIT
>> 
>>> statement may return before a transaction is stored on the persistent
>>> media,
>>> but transactions are still Atomic, Consistent and Isolated.
>>>
>>> When using the "PRAGMA synchronous=off" your database might be
>>>   
>> corrupted
>> 
>>> by a power failure or OS crash. When using asynchronous IO this
>>>   
>> should
>> 
>>> not
>>> be possible (assuming the hardware is not being untruthful - just as
>>> when
>>> using regular "PRAGMA synchronous=full" mode without the async IO
>>>   
>> VFS).
>>
>>
>> Ah, the bulb has lit. Because the writes and syncs are processed by the
>> single queue in order, journal writes are guaranteed to be synced and
>> consistent before main in-place updates to the db file.
>>
>> Might be worth mentioning this in the documentation, as this is not
>> clear without examining the source.
>>
>> In that case, I like it :)
>>
>> Is this something that might be made the default in the future, with
>> the
>> addition of some synchronization between foreground and background
>> threads
>> on the xSync messages to emulate the existing "PRAGMA synchronous=full"
>> behaviour?
>>
>> 
>>> Dan.
>>>   
>> Christian
>> ___
>> 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] Compiling sqlite using chars unsigned by default

[D. Richard Hipp]

On May 8, 2009, at 3:34 PM, Axel Mammes wrote:

> Hi, I am using the ARM RVDS 2.0.1 compiler to try to get SQLite on a
> VeriFone Vx570 point of sale terminal running VerixV operating  
> system. I am
> still working on getting it to work, but before I continue I need to  
> know if
> the fact that chars are unsigned by default will break sqlite build.


It should work fine.

D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Indexes lifespan and reindexing...

[D. Richard Hipp]

On May 8, 2009, at 3:31 PM, ioannis wrote:

> I would like to clarify my understanding in regards to lifespan of  
> indexes.
>
> 1. Once the index is created, the sequence of the items is stored in  
> the
> database, so the following example would be possible.
> a) Create an index with an overloaded NOCASE collation function on a  
> column.
> b) Sort the column with NOCASE, without loading the overloaded NOCASE
> collation extension.
> c) The sorting is expected to follow the sequence created initially  
> by the
> overloaded collation function and not the standard function.
>
> 2. I am a bit unclear what happens to an already created index once i
> INSERT, UPDATE, DELETE a row in an indexed column.
> 3. When is a REINDEX required (except when collation function  
> changes) ?


I don't really understand your question.  But I think the answer might  
be captured in these facts:

*  Whenever you change a collating function (by overloading it) you  
must run REINDEX before doing any INSERT, UPDATE, or DELETE operations  
or you will might corrupt the database file.

* The only time you should ever need to run REINDEX is after changing  
a collating function.


D. Richard Hipp
d...@hwaci.com



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

[sqlite] Compiling sqlite using chars unsigned by default

[Axel Mammes]

Hi, I am using the ARM RVDS 2.0.1 compiler to try to get SQLite on a
VeriFone Vx570 point of sale terminal running VerixV operating system. I am
still working on getting it to work, but before I continue I need to know if
the fact that chars are unsigned by default will break sqlite build.

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

Re: [sqlite] make test: Tests fail on Linux running on MIPS (Big endian)

[D. Richard Hipp]

On May 8, 2009, at 2:11 PM, mwnn wrote:

> Hi,
>   Sorry about the previous mail.
> The following test cases have failed when executing "make test" on   
> Linux
> running on MIPS platform:
> 28 errors out of 40151 tests
> Failures on these tests: count-2.1 count-2.5 in3-1.6 in3-1.7 in3-1.8  
> in3-1.9
> in3-1.11 in3-1.12 in3-1.13 in3-1.14 in3-3.5 in3-3.7 in3-4.3 in3-4.4
> insert-5.3 insert5-2.2 insert5-2.3 insert5-2.4 insert5-2.5 insert5-2.6
> insert5-2.8 misc3-6.10 nan-4.14 nan-4.15 nan-4.16 nan-4.17  
> trigger9-1.2.3
> trigger9-1.5.2

The nan-* errors are probably because your hardware does not support  
80-bit floating point using "long double" and so the value 9.88e-324  
rounds to 0.0.  Not something to worry about.

The other errors all seem to involve the use of EXPLAIN.  It would  
appear that EXPLAIN is busted on your build.  If you do not use  
EXPLAIN, then this is probably nothing to worry about either.


D. Richard Hipp
d...@hwaci.com



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

Re: [sqlite] Platforms on which SQLite test cases are run

[D. Richard Hipp]

On May 8, 2009, at 2:47 PM, mwnn wrote:

> Hi,
>  The page at http://www.sqlite.org/testing.html quotes
> "Prior to each check-in to the SQLite source tree, developers  
> typically run
> a subset (called "veryquick") of the Tcl tests consisting of about  
> 41.7
> thousand test cases and covering 97.07% of the core SQLite source  
> code."
> Are the test cases run only on x86 architecture running Linux?

Tests are run on x86 and x86_64 linux, MacOS 10.5 x86, win32, and  
win64.  No MIPS.

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Platforms on which SQLite test cases are run

[mwnn]

Hi,
  The page at http://www.sqlite.org/testing.html quotes
"Prior to each check-in to the SQLite source tree, developers typically run
a subset (called "veryquick") of the Tcl tests consisting of about 41.7
thousand test cases and covering 97.07% of the core SQLite source code."
Are the test cases run only on x86 architecture running Linux?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] make test: Tests fail on Linux running on MIPS (Big endian)

[mwnn]

Hi,
   Sorry about the previous mail.
The following test cases have failed when executing "make test" on  Linux
running on MIPS platform:
28 errors out of 40151 tests
Failures on these tests: count-2.1 count-2.5 in3-1.6 in3-1.7 in3-1.8 in3-1.9
in3-1.11 in3-1.12 in3-1.13 in3-1.14 in3-3.5 in3-3.7 in3-4.3 in3-4.4
insert-5.3 insert5-2.2 insert5-2.3 insert5-2.4 insert5-2.5 insert5-2.6
insert5-2.8 misc3-6.10 nan-4.14 nan-4.15 nan-4.16 nan-4.17 trigger9-1.2.3
trigger9-1.5.2
count-2.1...
Expected: [1]
  Got: [0]
count-2.5...
Expected: [1]
  Got: [0]
Expected: [1 1 2 3]
  Got: [0 1 2 3]
in3-1.7...
Expected: [1 1 3 5]
  Got: [0 1 3 5]
in3-1.8...
Expected: [1 1 3 5]
  Got: [0 1 3 5]
in3-1.9...
Expected: [1 1 3 5]
  Got: [0 1 3 5]
Expected: [1 1]
  Got: [0 1]
in3-1.12...
Expected: [1 3]
  Got: [0 3]
in3-1.13...
Expected: [1 1 3 5]
  Got: [0 1 3 5]
in3-1.14...
Expected: [1 1 3 5]
  Got: [0 1 3 5]
in3-3.5...
Expected: [1 1]
  Got: [0 1]
in3-3.7...
Expected: [1 1]
  Got: [0 1]
in3-4.3...
Expected: [1 1]
  Got: [0 1]
in3-4.4...
Expected: [1 none numeric real text]
  Got: [0 none numeric real text]
insert-5.3...
Expected: [1]
  Got: [0]
insert5-2.2...
Expected: [1]
  Got: [0]
insert5-2.3...
Expected: [1]
  Got: [0]
insert5-2.4...
Expected: [1]
  Got: [0]
insert5-2.5...
Expected: [1]
  Got: [0]
insert5-2.6...
Expected: [1]
  Got: [0]
insert5-2.8...
Expected: [1]
  Got: [0]
misc3-6.10...
Expected: [1]
  Got: [0]
nan-4.14...
Expected: [9.88131291682493e-324 real]
  Got: [0.0 real]
nan-4.15...
Expected: [-9.88131291682493e-324 real]
  Got: [0.0 real]
nan-4.16...
Expected: [9.88131291682493e-324 real]
  Got: [0.0 real]
nan-4.17...
Expected: [-9.88131291682493e-324 real]
  Got: [0.0 real]
trigger9-1.2.3...
Expected: [0]
  Got: [1]

trigger9-1.5.2...
Expected: [0]
  Got: [1]
Are these tests very critical to the operation of SQlite?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] make test: Tests fail on Linux running on MIPS (Big endian)

[mwnn]

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

Re: [sqlite] Newbie trying to list resultSet with C

[Kees Nuyt]

On Fri, 8 May 2009 16:26:20 +0100, Nuno Magalhães
 wrote:

>Greetings.
>
>I've managed to compile the example, after installing the amalgamation
>and using -lsqlite3 in gcc, otherwise it'll complain about undefined
>references.
>
>I can't figure out how to read a simple result set. I know i shoud use
>sqlite3_exec and/or sqlite3_step and i'm required to have a
>sqlite3_stmt* somewhere, but i can't find good examples and lots of
>the ones i find use sqlite3_prepare_v2, which i think is deprecated
>for SQLite3...
>
>Can someone please give me some nice RTFM links will good basic
>tutorials for the C API? Ones that include the aforementioned task
>preferably ;)

http://www.sqlite.org/cvstrac/wiki , more specifically:

http://www.sqlite.org/cvstrac/wiki?p=SimpleCode
Quickstart C code for executing any SQL against an SQLite
database. Very basic but fully functional nevertheless. 

http://www.sqlite.org/cvstrac/wiki?p=SampleCode
Example C code for creating / writing to / reading from a
database. 

>TIA,
>Nuno Magalhães
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.14

[Doug]

Would this perhaps affect throughput in the case where multiple database
files are open?  For example, I have a handful of databases (10?) that are
open at any given time, and reads and writes are taking place on separate
threads.  Naturally writes that happen to the same database ultimately get
serialized by the database-level locks, but writes to other databases
continue to work.  But using the async feature would serialize all reads and
writes to all databases, is that correct?

Thanks
Doug

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Christian Smith
> Sent: Friday, May 08, 2009 7:24 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SQLite version 3.6.14
> 
> On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote:
> >
> > On May 8, 2009, at 5:21 PM, Christian Smith wrote:
> >
> > > On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
> > >>
> > >> A new optional extension is included that implements an
> > >> asynchronous I/
> > >> O backend for SQLite on either windows or unix.  The asynchronous
> I/O
> > >> backend processes all writes using a background thread.  This
> gives
> > >> the appearance of faster response time at the cost of durability
> and
> > >> additional memory usage.  See http://www.sqlite.org/asyncvfs.html
> for
> > >> additional information.
> > >
> > >
> > > What are the benefits of using async I/O over "PRAGMA synchronous =
> > > OFF"?
> > > If AIO is used for the rollback journal as well, you've lost your
> ACID
> > > properties already, so you may as well just use "PRAGMA synchronous
> > > =  OFF"
> > > anyway and keep the code simpler.
> >
> > That's not the case. You lose the Durability property, in that a
> COMMIT
> > statement may return before a transaction is stored on the persistent
> > media,
> > but transactions are still Atomic, Consistent and Isolated.
> >
> > When using the "PRAGMA synchronous=off" your database might be
> corrupted
> > by a power failure or OS crash. When using asynchronous IO this
> should
> > not
> > be possible (assuming the hardware is not being untruthful - just as
> > when
> > using regular "PRAGMA synchronous=full" mode without the async IO
> VFS).
> 
> 
> Ah, the bulb has lit. Because the writes and syncs are processed by the
> single queue in order, journal writes are guaranteed to be synced and
> consistent before main in-place updates to the db file.
> 
> Might be worth mentioning this in the documentation, as this is not
> clear without examining the source.
> 
> In that case, I like it :)
> 
> Is this something that might be made the default in the future, with
> the
> addition of some synchronization between foreground and background
> threads
> on the xSync messages to emulate the existing "PRAGMA synchronous=full"
> behaviour?
> 
> >
> > Dan.
> 
> Christian
> ___
> 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] Newbie trying to list resultSet with C

[Simon Davies]

2009/5/8 Simon Davies :
> 2009/5/8 Nuno Magalhães :
>> Greetings.
>>
>> I've managed to compile the example, after installing the amalgamation
>> and using -lsqlite3 in gcc, otherwise it'll complain about undefined
>> references.
>>
>> I can't figure out how to read a simple result set. I know i shoud use
>> sqlite3_exec and/or sqlite3_step and i'm required to have a
>> sqlite3_stmt* somewhere, but i can't find good examples and lots of
>> the ones i find use sqlite3_prepare_v2, which i think is deprecated
>> for SQLite3...
>
> No - see http://www.sqlite.org/c3ref/prepare.html
>
>>
>> Can someone please give me some nice RTFM links will good basic
>> tutorials for the C API? Ones that include the aforementioned task
>> preferably ;)
>
> See http://www.sqlite.org/cintro.html
>
> The following gives an idea of how to use the prepare/step api:
>
>
> int get_telnr( char** c_telnr, sqlite3* db, char* name )
> {
>   char* sql = "SELECT telnr FROM contacts WHERE name=?;";
>   char* tail;
>   const char* data;
>   sqlite3_stmt* stmt;
>   int rc = sqlite3_prepare_v2( db,
>                                           sql,
>                                           strlen( sql ),
>                                           ,
>                                            );
>   if( SQLITE_OK == rc )
>   {
>       rc = sqlite3_bind_text( stmt, 1, name, strlen( name ), SQLITE_STATIC );
>       if( SQLITE_OK == rc )
>       {
>           rc = sqlite3_step( stmt );
>           if( SQLITE_ROW == rc )
>           {
>               data = sqlite3_column_text( stmt, 0 );
>               if( data )
>               {
>                   *c_telnr = (char*)malloc( strlen( data ) + 1 );
>                   strcpy( *c_telnr, data );
>               }
>           }
>       }
Sorry - don't forget to finalize!
 rc = sqlite3_finalize( stmt );
>   }
>   return( rc );
> }
>
>>
>> TIA,
>> Nuno Magalhães
>>
>
> Rgtds,
> Simon
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie trying to list resultSet with C

[Simon Davies]

2009/5/8 Nuno Magalhães :
> Greetings.
>
> I've managed to compile the example, after installing the amalgamation
> and using -lsqlite3 in gcc, otherwise it'll complain about undefined
> references.
>
> I can't figure out how to read a simple result set. I know i shoud use
> sqlite3_exec and/or sqlite3_step and i'm required to have a
> sqlite3_stmt* somewhere, but i can't find good examples and lots of
> the ones i find use sqlite3_prepare_v2, which i think is deprecated
> for SQLite3...

No - see http://www.sqlite.org/c3ref/prepare.html

>
> Can someone please give me some nice RTFM links will good basic
> tutorials for the C API? Ones that include the aforementioned task
> preferably ;)

See http://www.sqlite.org/cintro.html

The following gives an idea of how to use the prepare/step api:


int get_telnr( char** c_telnr, sqlite3* db, char* name )
{
   char* sql = "SELECT telnr FROM contacts WHERE name=?;";
   char* tail;
   const char* data;
   sqlite3_stmt* stmt;
   int rc = sqlite3_prepare_v2( db,
   sql,
   strlen( sql ),
   ,
);
   if( SQLITE_OK == rc )
   {
   rc = sqlite3_bind_text( stmt, 1, name, strlen( name ), SQLITE_STATIC );
   if( SQLITE_OK == rc )
   {
   rc = sqlite3_step( stmt );
   if( SQLITE_ROW == rc )
   {
   data = sqlite3_column_text( stmt, 0 );
   if( data )
   {
   *c_telnr = (char*)malloc( strlen( data ) + 1 );
   strcpy( *c_telnr, data );
   }
   }
   }
   }
   return( rc );
}

>
> TIA,
> Nuno Magalhães
>

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

[sqlite] Newbie trying to list resultSet with C

[Nuno Magalhães]

Greetings.

I've managed to compile the example, after installing the amalgamation
and using -lsqlite3 in gcc, otherwise it'll complain about undefined
references.

I can't figure out how to read a simple result set. I know i shoud use
sqlite3_exec and/or sqlite3_step and i'm required to have a
sqlite3_stmt* somewhere, but i can't find good examples and lots of
the ones i find use sqlite3_prepare_v2, which i think is deprecated
for SQLite3...

Can someone please give me some nice RTFM links will good basic
tutorials for the C API? Ones that include the aforementioned task
preferably ;)

TIA,
Nuno Magalhães

-- 
()  ascii ribbon campaign - against html e-mail
/\  ascii-rubanda kampajno - kontraŭ html-a retpoŝto
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.14

[Jim Wilcoxson]

May I suggest an extension PRAGMA SYNCHRONOUS = 3 | ASYNC so that
non-C bindings can use the async functionality?

Thanks, this is a great enhancement!
Jim

>> On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
>>>
>>> A new optional extension is included that implements an
>>> asynchronous I/
>>> O backend for SQLite on either windows or unix.  The asynchronous I/O
>>> backend processes all writes using a background thread.  This gives
>>> the appearance of faster response time at the cost of durability and
>>> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
>>> additional information.

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

Re: [sqlite] select performance with join

[Jim Wilcoxson]

I don't know if it makes any difference, but is that where clause the same as:

WHERE name< 'zUmM' OR (name= 'zUmM' AND S.id< 8122)

The original way it was coded, all 3 conditions would have to be
evaluated most of the time.  The new way might get most rows with 1
condition.  Depends on the data distribution of course.

Jim

On 5/8/09, galea...@korg.it  wrote:
> Citando Igor Tandetnik :
>
>> Andrea Galeazzi  wrote:
>>> but when I execute:
>>>
>>> SELECT S.id,title,artist,bpm,name
>>>
>>> FROM Song AS S
>>>
>>> LEFT JOIN Genre AS G ON (S.genre_id = G.id)
>>>
>>> WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)
>>>
>>> ORDER BY name DESC, S.id DESC LIMIT 20;
>>
>> Note that LEFT JOIN is pointless here, since any record with G.name=NULL
>> won't make it past the WHERE clause. Replace it with plain JOIN, you
>> should see an improvement.
>>
>> Igor Tandetnik
>>
> I replaced LEFT JOIN with JOIN but it got worse, now the the time is
> about 8700 ms! But I think I need to use LEFT JOIN because I have also
> to accept the records with S.genre_id = NULL.
> I also tried this query:
> “SELECT S.id,title,artist,bpm,name "
> "FROM Song  AS S, Genre AS G "
> "WHERE (S.genre_id = G.id) AND (name<= 'zUmM') AND (name< 'zUmM' OR
> S.id< 8122) "
> "ORDER BY name DESC, S.id DESC LIMIT 20";
> even if it doesn't work for me because it doesn't  match  S.genre_id =
> NULL, I noticed a little improvement to 6000 ms. Then I delete S.id
> DESC and the performance has been abruptly increased to 400 ms.
> Anyway probably the right statement is LEFT JOIN but how can I
> optimize this kind of task?
> Is it really an hard work or does it depend on my no knowledge about sqlite?
>>
>>
>> ___
>> 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
>


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

Re: [sqlite] Check webpage link...Download fails Precompiled Mac OS X sqlite3-3.6.14-osx-x86.bin.gz (253.83 KiB) , Linux downloads ok

[David Thompson]

Got it THANK YOU!

On May 8, 2009, at 9:42 AM, D. Richard Hipp wrote:

>
> On May 8, 2009, at 9:37 AM, David Thompson wrote:
>
>> I am on iMac trying to download the Precompiled Mac OS X
>> sqlite3-3.6.14-osx-x86.bin.gz (253.83 KiB) I get lost network
>> connection.
>> I am able to download the Linux version and documentation without
>> problems...
>> Can someone check the webpage link   
>
>
> File permission problem on the server.   Should be fixed now.  Please
> try again
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> 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] Check webpage link...Download fails Precompiled Mac OS X sqlite3-3.6.14-osx-x86.bin.gz (253.83 KiB) , Linux downloads ok

[D. Richard Hipp]

On May 8, 2009, at 9:37 AM, David Thompson wrote:

> I am on iMac trying to download the Precompiled Mac OS X
> sqlite3-3.6.14-osx-x86.bin.gz (253.83 KiB) I get lost network
> connection.
> I am able to download the Linux version and documentation without
> problems...
> Can someone check the webpage link


File permission problem on the server.   Should be fixed now.  Please  
try again

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Check webpage link...Download fails Precompiled Mac OS X sqlite3-3.6.14-osx-x86.bin.gz (253.83 KiB) , Linux downloads ok

[David Thompson]

I am on iMac trying to download the Precompiled Mac OS X  
sqlite3-3.6.14-osx-x86.bin.gz (253.83 KiB) I get lost network  
connection.
I am able to download the Linux version and documentation without  
problems...
Can someone check the webpage link  
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database creation and inserts speedup

[D. Richard Hipp]

On May 8, 2009, at 9:45 AM, J. R. Westmoreland wrote:

> I have a program that creates a database with a number of tables and  
> a few
> indexes.
>
> I'm just inserting in this program and not doing any lookups.
>
> I create the indexes after all records have been inserted.
>
> The input for the database is a LARGE number of records.
>
>
>
> The data is mapping data for the U.S. and therefore contains  
> millions of
> entries.
>
> The current run is taking about three days for the entire U.S. to  
> complete.
>
> A single state, say California, is taking several hours.
>
>
>
> Any suggestions on speed-ups?
>

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

D. Richard Hipp
d...@hwaci.com



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

[sqlite] Database creation and inserts speedup

[J. R. Westmoreland]

I have a program that creates a database with a number of tables and a few
indexes.

I'm just inserting in this program and not doing any lookups.

I create the indexes after all records have been inserted.

The input for the database is a LARGE number of records.

 

The data is mapping data for the U.S. and therefore contains millions of
entries.

The current run is taking about three days for the entire U.S. to complete.

A single state, say California, is taking several hours.

 

Any suggestions on speed-ups?

 

Granted this only has to be done once but if there is a failure along the
line it could be bad. 

 

Thanks,

J. R.

 

 

 



J. R. Westmoreland

E-mail: j...@jrw.org

Twitter: GeneralJR

 

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

Re: [sqlite] SQLite version 3.6.14

[Christian Smith]

On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote:
> 
> On May 8, 2009, at 5:21 PM, Christian Smith wrote:
> 
> > On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
> >>
> >> A new optional extension is included that implements an  
> >> asynchronous I/
> >> O backend for SQLite on either windows or unix.  The asynchronous I/O
> >> backend processes all writes using a background thread.  This gives
> >> the appearance of faster response time at the cost of durability and
> >> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
> >> additional information.
> >
> >
> > What are the benefits of using async I/O over "PRAGMA synchronous =   
> > OFF"?
> > If AIO is used for the rollback journal as well, you've lost your ACID
> > properties already, so you may as well just use "PRAGMA synchronous  
> > =  OFF"
> > anyway and keep the code simpler.
> 
> That's not the case. You lose the Durability property, in that a COMMIT
> statement may return before a transaction is stored on the persistent  
> media,
> but transactions are still Atomic, Consistent and Isolated.
> 
> When using the "PRAGMA synchronous=off" your database might be corrupted
> by a power failure or OS crash. When using asynchronous IO this should  
> not
> be possible (assuming the hardware is not being untruthful - just as  
> when
> using regular "PRAGMA synchronous=full" mode without the async IO VFS).


Ah, the bulb has lit. Because the writes and syncs are processed by the
single queue in order, journal writes are guaranteed to be synced and
consistent before main in-place updates to the db file.

Might be worth mentioning this in the documentation, as this is not
clear without examining the source.

In that case, I like it :)

Is this something that might be made the default in the future, with the
addition of some synchronization between foreground and background threads
on the xSync messages to emulate the existing "PRAGMA synchronous=full" 
behaviour?

> 
> Dan.

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

Re: [sqlite] SQLite version 3.6.14

[Dan]

On May 8, 2009, at 5:21 PM, Christian Smith wrote:

> On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
>>
>> A new optional extension is included that implements an  
>> asynchronous I/
>> O backend for SQLite on either windows or unix.  The asynchronous I/O
>> backend processes all writes using a background thread.  This gives
>> the appearance of faster response time at the cost of durability and
>> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for
>> additional information.
>
>
> What are the benefits of using async I/O over "PRAGMA synchronous =   
> OFF"?
> If AIO is used for the rollback journal as well, you've lost your ACID
> properties already, so you may as well just use "PRAGMA synchronous  
> =  OFF"
> anyway and keep the code simpler.

That's not the case. You lose the Durability property, in that a COMMIT
statement may return before a transaction is stored on the persistent  
media,
but transactions are still Atomic, Consistent and Isolated.

When using the "PRAGMA synchronous=off" your database might be corrupted
by a power failure or OS crash. When using asynchronous IO this should  
not
be possible (assuming the hardware is not being untruthful - just as  
when
using regular "PRAGMA synchronous=full" mode without the async IO VFS).

Dan.

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

Re: [sqlite] SQLite version 3.6.14

[Christian Smith]

On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote:
>
> A new optional extension is included that implements an asynchronous I/ 
> O backend for SQLite on either windows or unix.  The asynchronous I/O  
> backend processes all writes using a background thread.  This gives  
> the appearance of faster response time at the cost of durability and  
> additional memory usage.  See http://www.sqlite.org/asyncvfs.html for  
> additional information.


What are the benefits of using async I/O over "PRAGMA synchronous =  OFF"?
If AIO is used for the rollback journal as well, you've lost your ACID
properties already, so you may as well just use "PRAGMA synchronous =  OFF"
anyway and keep the code simpler.

Where I might be able to see the benefit of this background thread is if
the background thread grouped all pending write requests into a single
writev (or win32 equiv), which would reduce the system call count, but
this may be offset by all the extra memory buffer copying that is occurring
when copying a write request to the write queue. We now have 2 buffer
copies when writing a buffer (once to the AIO queue, plus the copy to the
OS.)

Are there any benchmarks numbers that indicate AIO is better than the async
PRAGMA?

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

Re: [sqlite] select performance with join

[galeazzi]

Citando Igor Tandetnik :

> Andrea Galeazzi  wrote:
>> but when I execute:
>>
>> SELECT S.id,title,artist,bpm,name
>>
>> FROM Song AS S
>>
>> LEFT JOIN Genre AS G ON (S.genre_id = G.id)
>>
>> WHERE name<= 'zUmM' AND (name< 'zUmM' OR S.id< 8122)
>>
>> ORDER BY name DESC, S.id DESC LIMIT 20;
>
> Note that LEFT JOIN is pointless here, since any record with G.name=NULL
> won't make it past the WHERE clause. Replace it with plain JOIN, you
> should see an improvement.
>
> Igor Tandetnik
>
I replaced LEFT JOIN with JOIN but it got worse, now the the time is  
about 8700 ms! But I think I need to use LEFT JOIN because I have also  
to accept the records with S.genre_id = NULL.
I also tried this query:
“SELECT S.id,title,artist,bpm,name "
"FROM Song  AS S, Genre AS G "
"WHERE (S.genre_id = G.id) AND (name<= 'zUmM') AND (name< 'zUmM' OR  
S.id< 8122) "
"ORDER BY name DESC, S.id DESC LIMIT 20";
even if it doesn't work for me because it doesn't  match  S.genre_id =  
NULL, I noticed a little improvement to 6000 ms. Then I delete S.id  
DESC and the performance has been abruptly increased to 400 ms.
Anyway probably the right statement is LEFT JOIN but how can I  
optimize this kind of task?
Is it really an hard work or does it depend on my no knowledge about sqlite?
>
>
> ___
> 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] sqlite3_open_v2 and SQLITE_BUSY

[Dan]

On May 8, 2009, at 4:37 AM, Igor Tandetnik wrote:

> Steven Fisher  wrote:
>> I was looking over the requirements for sqlite3_open_v2(), and I'm  
>> not
>> clear if this function can ever return SQLITE_BUSY.
>
> I don't believe so. As far as I know, it doesn't actually touch the  
> file
> at all, so it won't even return I/O errors (the file is physically
> opened and read when you prepare your first non-PRAGMA statement).

The file is opened when sqlite3_open_v2() is called. And I think a  
couple
of trivial methods are called - xSectorSize() and  
xDeviceCharacteristics().
But that's all, nothing is read, written or locked until later on.

So you won't get SQLITE_BUSY back from sqlite3_open_v2(). You might  
manage
an SQLITE_CANTOPEN if you tried to open a directory or, depending on the
flags passed to open_v2(), a file that does not exist.

Dan.



> Igor Tandetnik
>
>
>
> ___
> 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