Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Roger,

I have deliberately tried to avoid giving too much detail on the
architecture of the index since that was not the point and i didnt
want to end up debating it.

The design of the index is not the issue, suffice to say that i think
you are over complicating things. It is a desceptively simple problem,
and i really am not insisting on anything.  At the end of the day i
can just use another database, but the similicity and low maintenance
of sqlite appeals to me.

I did make an attempt to explain that A and B could not be done at the
same time in a previous message, but perhaps its been lost in the
conversation.  The process involves several stages some of which are
database operations and some of which are file operations and that the
operations are not separable.  They must be done in sequential order.

At this stage nothing is premature optimisation since i am merely
running performance tests and modelling the architecture. I can see
where the time is being spent using high resolution timers so that is
my empirical evidence.

The database operations, though very small still consume the most time
and are the most sensetive to how the synchronisation takes place and
where the transactions are placed.  I dont think custom functions are
appropriate for what im doing and im not sure how virtual tables would
be either, i rather suspect that would be a very complicated approach.
The schema is extemely simple, and there is barely any logic too the
indexing process at all.

The biggest impact on the performance is the transactions, and since
its hard to dispute that a single global transaction is the fastest
way to operate when you are using a single thread it is easy to see
that being able to use one global transaction and multiple threads
within that transaction is the simplest and easiest way to gain
perofrmance.  Unfortunately i cannot do this with sqlite at the
moment...

A few threads and two or three mutex lock/unlock statements is all it
takes with the design that i have now. The fact that each thread must
use its own connection is a hastle and does impact on the performance,
but not nearly as much as the inability to wrap the whole thing in a
single transaction.


On 12/30/06, Roger Binns <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

You never answered the bit about whether indexing A can be done at the
same time as B.  (I get the feeling you have a certain design in mind
and insist that SQLite changes to meet that design, rather than change
your own design around the constraints of SQLite).

Emerson Clarke wrote:
| In general worker threads is not an efficient solution to the problem
| even if parallelism is available.  There is nothing to be gained by
| having one thread handing off A to another set of worker threads
| because you have to synchronise on whatever queue/list/pipe you use to
| transfer the information between threads anyway.

The synchronisation is only at the begining and end of a "job".  The big
efficiency gain is that you can write synchronous code in the
threads/processes which is way easier to write and less likely to have
bugs.  It is correct that you can write the same thing using a single
thread and using asynchronous I/O but that is way harder.

| So you may as well
| just have multiple threads all performing their A,B, and C operations
| in parallel and they will naturally interleave their operations based
| on the synchronisation that you use.

You haven't said why that won't work in practise.

| threads with mutexes cant do in the foreground and only introduces
| overhead and delay in the queueing process and context switching.

Sounds like premature optimization to me.  Compared to any I/O you are
going to have to do (all of which involves interrupts, DMA transfers and
context switching), other context switching is going to be negligible.

| On the second point, i cant see any reason why the VDBE design would
| be a problem for what i want to do.

Did you run explain?  Quite simply SQLite is designed to keep the
database locked for the minimum amount of time, rather than having
multiple VDBE's having shared locks and doing lots of concurrent locking
for longer periods of time.  Fundamentally changing the design of SQLite
is not a quick thing.

| Taking out a table wide lock is
| fine, so long as i can make changes to that table from multiple
| threads using the same connection and lock.

"Taking out a table lock is fine, as long as the table is not locked" :-)

If you have actual empirical evidence that SQLite is a bottleneck, then
there are two other approaches that may help:

- - Use multiple databases (eg split on first letter of document name) and
use ATTACH to have them all available at the same time

- - Use the new virtual tables feature and user defined functions.  You
can have the code consult other tables, store up information and do
almost any other workflow and locking scheme you want.  You can copy
batches of data from your 

Re: [sqlite] Using sqlite.exe

[P Kishor]

On 12/29/06, Michael Hooker <[EMAIL PROTECTED]> wrote:

I'm a fairly competent but amateur Delphi programmer and I can construct all
the SQL statements I need.  But I'm having enormous trouble making
sqlite.exe work.

Can some kind person please tell me  what, right down to the last
dot and semi-colon, I should type in at the sqlite> prompt to open a SQLite3
database called "BaseStation.sqb" located in the same folder as sqlite and
then to get a simple SQL query like "select * from Aircraft" to come up with
some data on screen, or preferably save it to a disk file?  Whatever I try I
get either a syntax error response or one saying that table Aircraft does
not exist.  ..


ya, I've found getting to stuff from within the shell program tricky
at times. Here is the easiest way, esp. since your db is in the same
directory as the .exe (assuming that directory to be C:\sqlite3...
replace as needed)

C:\sqlite3\sqlite3.exe BaseStation.sqb
..
that will launch sqlite3 as well as open up your database all in one
command. Then you will get a sqlite prompt like so
..
SQLite version 3.3.8
Enter ".help" for instructions
..
well, go ahead an enter .help (you can also shorten it and enter .h).
It will spew out a list of dot commands that you can fool around with.
Of course, pure SQL would straightforward like so
..
sqlite> select * from Aircraft;
..
note that dot commands don't end with a semi-colon, while SQL
statements do. This allows you to enter SQL statements on multiple
lines like so, if you desire
..
sqlite> select
   ...> *
   ...> from
   ...> Aircraft
   ...> ;

Happy SQLiting.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation https://edu.osgeo.org/
-
collaborate, communicate, compete
=

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

Re: [sqlite] sqlite performance, locking & threading

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

You never answered the bit about whether indexing A can be done at the
same time as B.  (I get the feeling you have a certain design in mind
and insist that SQLite changes to meet that design, rather than change
your own design around the constraints of SQLite).

Emerson Clarke wrote:
| In general worker threads is not an efficient solution to the problem
| even if parallelism is available.  There is nothing to be gained by
| having one thread handing off A to another set of worker threads
| because you have to synchronise on whatever queue/list/pipe you use to
| transfer the information between threads anyway.

The synchronisation is only at the begining and end of a "job".  The big
efficiency gain is that you can write synchronous code in the
threads/processes which is way easier to write and less likely to have
bugs.  It is correct that you can write the same thing using a single
thread and using asynchronous I/O but that is way harder.

| So you may as well
| just have multiple threads all performing their A,B, and C operations
| in parallel and they will naturally interleave their operations based
| on the synchronisation that you use.

You haven't said why that won't work in practise.

| threads with mutexes cant do in the foreground and only introduces
| overhead and delay in the queueing process and context switching.

Sounds like premature optimization to me.  Compared to any I/O you are
going to have to do (all of which involves interrupts, DMA transfers and
context switching), other context switching is going to be negligible.

| On the second point, i cant see any reason why the VDBE design would
| be a problem for what i want to do.

Did you run explain?  Quite simply SQLite is designed to keep the
database locked for the minimum amount of time, rather than having
multiple VDBE's having shared locks and doing lots of concurrent locking
for longer periods of time.  Fundamentally changing the design of SQLite
is not a quick thing.

| Taking out a table wide lock is
| fine, so long as i can make changes to that table from multiple
| threads using the same connection and lock.

"Taking out a table lock is fine, as long as the table is not locked" :-)

If you have actual empirical evidence that SQLite is a bottleneck, then
there are two other approaches that may help:

- - Use multiple databases (eg split on first letter of document name) and
use ATTACH to have them all available at the same time

- - Use the new virtual tables feature and user defined functions.  You
can have the code consult other tables, store up information and do
almost any other workflow and locking scheme you want.  You can copy
batches of data from your virtual tables into the real ones, have
virtual functions that look in virtual tables for updates, then fallback
on original data or any other design that suits you.

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

iD8DBQFFlbQUmOOfHg372QQRAn/OAJwKH6O3nWmHDRdn4ZsF+wcTaV71VQCg1zjs
AmBJS5ujkKDou83gWc+Inj8=
=GQLS
-END PGP SIGNATURE-

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

[sqlite] Using sqlite.exe

[Michael Hooker]

I'm a fairly competent but amateur Delphi programmer and I can construct all
the SQL statements I need.  But I'm having enormous trouble making
sqlite.exe work.

Can some kind person please tell me  what, right down to the last
dot and semi-colon, I should type in at the sqlite> prompt to open a SQLite3
database called "BaseStation.sqb" located in the same folder as sqlite and
then to get a simple SQL query like "select * from Aircraft" to come up with
some data on screen, or preferably save it to a disk file?  Whatever I try I
get either a syntax error response or one saying that table Aircraft does
not exist.  Which is strange as other programs like SQLiteSpy will quite
happily display all 7000 records in this supposedly non-existent table.  And
yes, I have read the available guidance and done my best to follow it, to no
avail.

I would like to feed instructions into sqlite.exe from my own Delphi program
and then manipulate the result. I've done that before with other command
line programs. I've tried various Delphi sqlite wrappers but they won't
cooperate with me either.  The database is created by a commercial program
and is certainly constructed and manipulated through an accompanying
sqlite3.dll.  I just need to get more detailed data out of it than I can
using the limited analyser built into the program, and it's fairly important
that it all happens with one click of a GO button that can be programmed to
happen automatically in my absence at a certain time - otherwise I'd just
use SQLiteSpy and copy and paste the data it displays.

Thanks in advance.

Michael Hooker
Surrey, England

-Original Message-
From: Ken [mailto:[EMAIL PROTECTED]
Sent: 29 December 2006 23:04
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Joining tables in a single file


I think the attach is the way to go, but no need to insert, just select from
the attached databases.

 sqlite3 master.db (master is empty).
  attach a.db A
  attach b.db  B
  attach c.db C

 Then  :
   select from a.A,   b.b,  c.c where  


Alberto Simões <[EMAIL PROTECTED]> wrote: Hi

I am using SQLite to store ngrams from texts (bigrams, trigrams and
tetragrams). This is not really important for the question; just
imagine I have three tables A (int,int), B (int, int, int) and C (int,
int, int, int). As the table keys are full rows and the tables get
big, it is not quite efficient com compute bigrams, trigrams and
tetragrams at the same time.

Given that I have access to a cluster, I split the job in three tasks
that can be computed separately on different cluster nodes. One
calculates bigrams, another trigrams, and other to calculate
tetragrams.

So far, everything fine. The problem is that this results in three
different files each with a table. I need to join tables in the same
file. There are no dependencies between tables, thus you can imagine
that I need something like:

  cat A.db B.db C.db > full.db  # kidding

I can do an export and import for each table. But I would like to know
if there is any faster method.
Thank you

Kind regards,
Alberto

--
Alberto Simões


-
To unsubscribe, send email to [EMAIL PROTECTED]

-



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.29/608 - Release Date: 29/12/2006

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.15.29/608 - Release Date: 29/12/2006


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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Michael Sizaki wrote:
| I'm really puzzled why my system hits the disk so heavily

Windows XP limits the maximum size of the cache (default 10MB!).  There
are zillions of pseudo-freeware programs out there to change it.  You
can also change it using the control panel and/or registry:

~  http://support.microsoft.com/kb/308417  (system cache)

~  http://mywebpages.comcast.net/SupportCD/XPMyths.html (large system cache)

~  http://www.jsifaq.com/SF/Tips/Tip.aspx?id=9200

~  http://www.techspot.com/tweaks/memory-winxp/

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

iD8DBQFFla84mOOfHg372QQRAssiAJ99Hzrw6/9Nn8EscwqkV7Dsn/OYKgCcCOjC
BZlsagFsYZ2pNRc/21g5MsU=
=qdN5
-END PGP SIGNATURE-

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

Re: [sqlite] pragma read_uncommitted and threading

[Ken]

I found part of the issue, in switching servers around the test version did not 
enable the shared cache... My fault.. however I still recieved a RC 5 on a 
different write operation.
 
 Modifed the test_server code to repost the step and all was well. A better 
solution however is to cause the server to filter, based upon the thread id's 
this saves a small amount of time due to not recalling the sqlite3 functions 
just to get another server is locked message.
 
 
Ken <[EMAIL PROTECTED]> wrote: 
 I ran everything through the test_server "server" and get the following 
results when using read_uncommitted=1
 
 rc=[5] msg=[database is locked]
 when performing a "client_step" against a prepared select 
statement.  
 
 I'd really like to track this down and find out if my code is incorrect or if 
I've hit a bug.
 
 Thanks,
 Ken
 
 [EMAIL PROTECTED] wrote:  Ken  wrote:
>  
> My thought was that if i wanted to perform selects concurrently on SMP
> system I would need 2 threads and each thread would be able to read
> concurrently
>  
> I just don't see how this [test_server.c] improves concurrency...
>  

It improves write concurrancy.  One client can read from table A
at the same time another client is writing to table B.  Or if
read_uncommitted is turned on, one client can read from table A
at the same time that another client is writing to th e same table.

--
D. Richard Hipp  


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

 
 

[EMAIL PROTECTED] wrote: Ken  wrote:
>  
> My thought was that if i wanted to perform selects concurrently on SMP
> system I would need 2 threads and each thread would be able to read
> concurrently
>  
> I just don't see how this [test_server.c] improves concurrency...
>  

It improves write concurrancy.  One client can read from table A
at the same time another client is writing to table B.  Or if
read_uncommitted is turned on, one client can read from table A
at the same time that another client is writing to th e same table.

--
D. Richard Hipp  


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

Re: [sqlite] Joining tables in a single file

[Ken]

I think the attach is the way to go, but no need to insert, just select from 
the attached databases.
 
 sqlite3 master.db (master is empty).
  attach a.db A
  attach b.db  B
  attach c.db C
 
 Then  :  
   select from a.A,   b.b,  c.c where   
 

Alberto Simões <[EMAIL PROTECTED]> wrote: Hi

I am using SQLite to store ngrams from texts (bigrams, trigrams and
tetragrams). This is not really important for the question; just
imagine I have three tables A (int,int), B (int, int, int) and C (int,
int, int, int). As the table keys are full rows and the tables get
big, it is not quite efficient com compute bigrams, trigrams and
tetragrams at the same time.

Given that I have access to a cluster, I split the job in three tasks
that can be computed separately on different cluster nodes. One
calculates bigrams, another trigrams, and other to calculate
tetragrams.

So far, everything fine. The problem is that this results in three
different files each with a table. I need to join tables in the same
file. There are no dependencies between tables, thus you can imagine
that I need something like:

  cat A.db B.db C.db > full.db  # kidding

I can do an export and import for each table. But I would like to know
if there is any faster method.
Thank you

Kind regards,
Alberto

-- 
Alberto Simões

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

Re: [sqlite] pragma read_uncommitted and threading

[Ken]

 I ran everything through the test_server "server" and get the following 
results when using read_uncommitted=1
 
 rc=[5] msg=[database is locked]
 when performing a "client_step" against a prepared select 
statement.  
 
 I'd really like to track this down and find out if my code is incorrect or if 
I've hit a bug.
 
 Thanks,
 Ken
 
 [EMAIL PROTECTED] wrote:  Ken  wrote:
>  
> My thought was that if i wanted to perform selects concurrently on SMP
> system I would need 2 threads and each thread would be able to read
> concurrently
>  
> I just don't see how this [test_server.c] improves concurrency...
>  

It improves write concurrancy.  One client can read from table A
at the same time another client is writing to table B.  Or if
read_uncommitted is turned on, one client can read from table A
at the same time that another client is writing to th e same table.

--
D. Richard Hipp  


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

 
 

[EMAIL PROTECTED] wrote: Ken  wrote:
>  
> My thought was that if i wanted to perform selects concurrently on SMP
> system I would need 2 threads and each thread would be able to read
> concurrently
>  
> I just don't see how this [test_server.c] improves concurrency...
>  

It improves write concurrancy.  One client can read from table A
at the same time another client is writing to table B.  Or if
read_uncommitted is turned on, one client can read from table A
at the same time that another client is writing to th e same table.

--
D. Richard Hipp  


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

Re: [sqlite] Joining tables in a single file

[Alberto Simões]

On 12/29/06, Griggs, Donald <[EMAIL PROTECTED]> wrote:

Regarding:
 " I need something like:
  cat A.db B.db C.db > full.db  # kidding"
-

Hi Alberto,

My first inclination is to consider this a classic use of the
   ATTACH DATABASE command
http://www.sqlite.org/lang_attach.html


SQLITE3 A.db
>ATTACH 'B.db' as B;
>ATTACH 'C.db' as C;

>Create tableFromB as select * from B.tablename;
>Create tableFromC as select * from C.tablename;



Hmms, I think this will do the trick.
Thank you :)
Alberto
--
Alberto Simões

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

RE: [sqlite] Joining tables in a single file

[Griggs, Donald]

Regarding:
 " I need something like:
  cat A.db B.db C.db > full.db  # kidding"
-

Hi Alberto,

My first inclination is to consider this a classic use of the 
   ATTACH DATABASE command
http://www.sqlite.org/lang_attach.html

 
SQLITE3 A.db
>ATTACH 'B.db' as B;
>ATTACH 'C.db' as C;

>Create tableFromB as select * from B.tablename;
>Create tableFromC as select * from C.tablename;

Create indices as needed.



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

[sqlite] Joining tables in a single file

[Alberto Simões]

Hi

I am using SQLite to store ngrams from texts (bigrams, trigrams and
tetragrams). This is not really important for the question; just
imagine I have three tables A (int,int), B (int, int, int) and C (int,
int, int, int). As the table keys are full rows and the tables get
big, it is not quite efficient com compute bigrams, trigrams and
tetragrams at the same time.

Given that I have access to a cluster, I split the job in three tasks
that can be computed separately on different cluster nodes. One
calculates bigrams, another trigrams, and other to calculate
tetragrams.

So far, everything fine. The problem is that this results in three
different files each with a table. I need to join tables in the same
file. There are no dependencies between tables, thus you can imagine
that I need something like:

 cat A.db B.db C.db > full.db  # kidding

I can do an export and import for each table. But I would like to know
if there is any faster method.
Thank you

Kind regards,
Alberto

--
Alberto Simões

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

Re: [sqlite] pragma read_uncommitted and threading

[drh]

Ken <[EMAIL PROTECTED]> wrote:
>  
> My thought was that if i wanted to perform selects concurrently on SMP
> system I would need 2 threads and each thread would be able to read
> concurrently
>  
> I just don't see how this [test_server.c] improves concurrency...
>  

It improves write concurrancy.  One client can read from table A
at the same time another client is writing to table B.  Or if
read_uncommitted is turned on, one client can read from table A
at the same time that another client is writing to th e same table.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] Creating a database from inside a program

[Ken]

I have a nice solution that really works well, at least from C... 
1. Create a template Database. (using whatever method you like, either 
embedded in your code or via sqlite3).
 2. Copy the database to a new file, using plane old cp, copy, or if you 
like an in code copy using open, read/write, close... Then open the newly 
created copy.
 
 I've found that if you need mulitples of a single database structure this is a 
very fast way to do this, a lot faster than creating the DB and then creating 
the individual tables/indices.
 
 If you want really, really fast, you could load your templated db into memory 
and then just write this to disk whenever you need a new DB.
 
 
 
Kees Nuyt <[EMAIL PROTECTED]> wrote: On Fri, 29 Dec 2006 12:33:46 -0500, you 
wrote:

> Sqlite3 newdatabase.db .read schemafile.txt
>
> But, when I issue this command from the DOS prompt, 
> it gives me an error message saying that there 
> is no command named "read".  

Try input redirection:

Sqlite3 newdatabase.db 

If your schemafile.txt contains valid SQLite statements, it
should work.

HTH
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] sqlite performance, locking & thread

[Ken]

Emereson,
 
 And you can't do this with oracle either, That is create a connection and pass 
it around between multiple threads.. Oracle would be very very unhappy if you 
did that. Oracle utilizes a context variable to distinguish between threads and 
utilizes precompiler flags to enable thread support.
 
 

Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,

Yes you cannot have multiple threads within the same transaction, and
you cannot pass a connection between threads.

I think we have an undestanding about the performance situation, and
we are getting to the real heart of the issue, which why it is not
possible to have a single transaction, single connection and multiple
threads.

Provided the user synchronises the access to the connection, this
should offer the highest performance.  But there is some technical
reason why sqlite has this (in my opinion unusual behaviour).  Perhaps
DRH can explain this...

Emerson


On 12/29/06, Ken  wrote:
> The test server.c code is an example that has a single thread that performs 
> the DB interactions each client thread, communicates via a queue interface. 
> Each client will get serialized into the DBserver thread and get its work 
> done. Thus eliminating any lower level locking and mutexing inside the 
> application code.
>
> Your assumption regarding 1 thread/1 cache is pretty accurate. This is what 
> the test_server.c code does, however each client thread does however get a 
> database connection handle.
>
> If you have a single global transaction you can do insr/selec/upd/delete.. 
> The thing is that the connection may not be passed around amongst threads.
>
> You cannot have multiple threads executing within the same transaction!!! To 
> my understanding, that is not allowed. Please someone else correct me if I'm 
> wrong...
>
> The difference is with other DB's they utilize a finer grain of locking 
> internally, either page locking, row locking etc.. Sqlite uses Database 
> LOCKING, which is full file. So its really only designed to be used by a 
> single thread of execution. (again DRH please correct me here if I'm wrong).
>
>
> Emerson Clarke  wrote: Ken,
>
> Thanks.
>
> Ok, i think i did miss the point with your test case.  I assumed that
> it was always possible to perform multiple select, insert, delete, and
> update statements within a single transaction anyway.  Which i guess
> relates to you last point.  I dont see why if i have a single global
> transaction i cant perform all the operations i want within that
> transaction to maximise performance.  In fact i know i can. because
> thats what i do with a single thread.
>
> I have always assumed that the simplest and highest performance
> solution would be to have multiple threads with one transaction and
> one cache.  I will take a look at the test_server.c code and see if i
> can understand what you are suggesting.
>
> I dont see why writer starvation would be a problem, but again i
> probably just dont understand what your suggesting there.  Anyway,
> thanks for your help.  I suspect that you are right and the ultimate
> solution will be to write my own synchronisation code, since that will
> allow me to have a consistent api across multiple databases like
> sqlite and mysql.
>
> I could achieve this if i could have multiple threads executing
> statements within the same transaction since that is the effective
> behaviour i get from most other databases which are not in process.
> In those cases it is the process boundary and the tcp/ip connections
> which are effectivley serialising all interactions which is equivalent
> of having only one thread when using sqlite.
>
> Emerson

[sqlite] pragma read_uncommitted and threading

[Ken]

Hi all,
  
  I have a piece of code that utilizes test_server.c, (master thread) 
  
  there are 3 threads, each performing seperate tasks, that get a conection 
(shared) and set 
 PRAGMA read_uncommitted=1. 
  My understanding is that this would allow each individual thread to 
concurrently execute a select statement?
  I get the following errors:

  Thread Load: ( begin txn, insert, commit) using the master thread since 
it is a write. )
 rc=[10] msg=[disk I/O error]  (after thread proc disconnects)
  
  Thread proc:  (select not using the master_thread queue, updates using 
thread queu )
  rc=[11] msg=[malformed database schema - table sqlite_master already 
exists]
  
  Thread purg:(select not  using the master_thread queue, delete using 
the  master  thread queue). 
  rc=[10] msg=[disk I/O error] (on delete statement after thread proc 
disconnects).
  
  I was under the impression that using the PRAGM read uncommitted would not 
cause blocking amongst readers/writers? So I built an interface that would send 
write operations to the Writer Queue and would call sqlite3_ commands for 
selects... 
  
  Is this not a correct utilization? Also the code dumps core, but sometimes 
will run to completion as above.
  
  Note that If i use and api that sen

Re: [sqlite] Creating a database from inside a program

[Kees Nuyt]

On Fri, 29 Dec 2006 12:33:46 -0500, you wrote:

> Sqlite3 newdatabase.db .read schemafile.txt
>
> But, when I issue this command from the DOS prompt, 
> it gives me an error message saying that there 
> is no command named "read".  

Try input redirection:

Sqlite3 newdatabase.db 

Re: [sqlite] detect afs, --enable-locking-style, non-darwin fs detection

[David Crawshaw]

On 24/12/06, Adam Megacz <[EMAIL PROTECTED]> wrote:

AFS (the Andrew FileSystem) supports whole-file locks but not
byte-range locks.  Unfortunately, it has a problematic "feature"
whereby it will claim to grant requests for byte-range locks, but not
actually perform any locking activity.  This unfortunately can easily
lead to corruption for applications like sqlite.


I would like to see this patch incorporated. AFS seamlessly folds into
the unix file system and at the moment I have to remember that it is
unsafe to store an SQLite database file on the share.

David

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

Re: [sqlite] multiple selects in a single prepare

[drh]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > SELECT count(state='Normal'), count(state='Critical') FROM tbl1;
> 
> Wouldn't that just return the number of all rows in tbl1, twice? That 
> probably should be
> 
> SELECT sum(state='Normal'), sum(state='Critical') FROM tbl1;
> 

Yes, Igor, you are correct.  I was thinking "sum" but my
fingers hit "count".  
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

[sqlite] Re: [updated] detect afs, --enable-locking-style, non-darwin fs detection

[Adam Megacz]

For the record,

  "The author or authors of this code dedicate any and all copyright
  interest in this code to the public domain. We make this dedication
  for the benefit of the public at large and to the detriment of our
  heirs and successors. We intend this dedication to be an overt act
  of relinquishment in perpetuity of all present and future rights
  this code under copyright law."

I have mailed off the copyright release.

  - a

Adam Megacz <[EMAIL PROTECTED]> writes:
> In addition to the functionality in the previous patch, this patch
> includes a new function testProcessLockingBehavior(), which is
> conceptually similar to testThreadLockingBehavior but using fork()
> instead of pthread_create().
>
> This might sound obvious: lock the first byte of a file, fork() a
> child, and have the child attempt to lock that same byte -- this
> should always fail.  Unfortunately all currently-deployed non-Linux
> AFS clients will actually "grant" locks to both processes.
>
> The added code checks for this behavior empirically before proceeding
> with posixLockingStyle.  If nonsensical behavior is observed, it
> automatically falls back to whole-file locks.
>
>   - a
>
>
> Index: configure.ac
> ===
> RCS file: /sqlite/sqlite/configure.ac,v
> retrieving revision 1.26
> diff -B -u -b -r1.26 configure.ac
> --- configure.ac  3 Jun 2006 18:02:18 -   1.26
> +++ configure.ac  26 Dec 2006 01:25:40 -
> @@ -318,6 +318,21 @@
>  AC_SUBST(XTHREADCONNECT)
>  
>  ##
> +# Do we want to allow different locking styles?
> +#
> +AC_ARG_ENABLE(locking-style, 
> +AC_HELP_STRING([--enable-locking-style],[Enable different locking 
> styles]),,enable_lockingstyle=no)
> +AC_MSG_CHECKING([whether to allow connections to be shared across threads])
> +if test "$enable_lockingstyle" = "no"; then
> +  ENABLELOCKINGSTYLE=''
> +  AC_MSG_RESULT([no])
> +else
> +  ENABLELOCKINGSTYLE='-DSQLITE_ENABLE_LOCKING_STYLE=1'
> +  AC_MSG_RESULT([yes])
> +fi
> +AC_SUBST(ENABLELOCKINGSTYLE)
> +
> +##
>  # Do we want to set threadsOverrideEachOthersLocks variable to be 1 (true) by
>  # default. Normally, a test at runtime is performed to determine the
>  # appropriate value of this variable. Use this option only if you're sure 
> that
> @@ -673,7 +688,35 @@
>  # Redefine fdatasync as fsync on systems that lack fdatasync
>  #
>  
> +AC_CHECK_HEADER([sys/statfs.h], [TARGET_CFLAGS="$TARGET_CFLAGS 
> -DHAVE_SYS_STATFS_H=1"],)
> +
>  AC_CHECK_FUNC(fdatasync, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FDATASYNC=1"])
> +AC_CHECK_FUNC(fsctl, [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_FSCTL=1"])
> +
> +AC_CHECK_MEMBER(struct statfs.f_flags,
> +  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FLAGS=1"],,
> +  [
> +#include 
> +#include 
> +#include 
> +  ])
> +
> +AC_CHECK_MEMBER(struct statfs.f_type,
> +  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_TYPE=1"],,
> +  [
> +#include 
> +#include 
> +#include 
> +#include 
> +  ])
> +
> +AC_CHECK_MEMBER(struct statfs.f_fstypename,
> +  [TARGET_CFLAGS="$TARGET_CFLAGS -DHAVE_STATFS_F_FSTYPENAME=1"],,
> +  [
> +#include 
> +#include 
> +#include 
> +  ])
>  
>  #
>  # Put out accumulated miscellaneous LIBRARIES
> Index: Makefile.in
> ===
> RCS file: /sqlite/sqlite/Makefile.in,v
> retrieving revision 1.160
> diff -B -u -b -r1.160 Makefile.in
> --- Makefile.in   21 Dec 2006 22:38:23 -  1.160
> +++ Makefile.in   26 Dec 2006 01:25:40 -
> @@ -32,7 +32,7 @@
>  # Omitting the define will cause extra debugging code to be inserted and
>  # includes extra comments when "EXPLAIN stmt" is used.
>  #
> -TCC += @TARGET_DEBUG@ @XTHREADCONNECT@
> +TCC += @TARGET_DEBUG@ @XTHREADCONNECT@ @ENABLELOCKINGSTYLE@
>  
>  # Compiler options needed for programs that use the TCL library.
>  #
> Index: src/os_unix.c
> ===
> RCS file: /sqlite/sqlite/src/os_unix.c,v
> retrieving revision 1.114
> diff -B -u -b -r1.114 os_unix.c
> --- src/os_unix.c 21 Dec 2006 01:29:23 -  1.114
> +++ src/os_unix.c 26 Dec 2006 01:25:41 -
> @@ -52,6 +52,9 @@
>  #ifdef SQLITE_ENABLE_LOCKING_STYLE
>  #include 
>  #include 
> +#ifdef HAVE_SYS_STATFS_H
> +#include 
> +#endif /* HAVE_SYS_STATFS_H */
>  #include 
>  #endif /* SQLITE_ENABLE_LOCKING_STYLE */
>  
> @@ -479,6 +482,52 @@
>  #define fcntl lockTrace
>  #endif /* SQLITE_LOCK_TRACE */
>  
> +#ifdef SQLITE_ENABLE_LOCKING_STYLE
> +/**
> + *  Check to see if the OS fcntl() byte-range locking call will "lie"
> + *  to us and grant a lock that is not enforced.  This happens notably
> + *  with files in AFS (OpenAFS client <1.5.0, all OSes but Linux).
> + *
> + *  Returns zero if byte-range locks appear to work as expected.
> + */
> +static int 

[sqlite] Re: Creating a database from inside a program

[Igor Tandetnik]

Clay Dowling  wrote:

Open the database as per normal with sqlite3_open().  Then issue the
SQL
commands necessary to create your schema.  I have a nice little
utility I
wrote which will take an SQLite schema dump and convert it to an
array of
C strings that you can issue in sequence


Or simply build a single string with all statements separated by 
semicolons, and execute all of them with a single sqlite3_exec call.


Igor Tandetnik 



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

Re: [sqlite] sqlite performance, locking & thread

[Emerson Clarke]

Ken,

Yes you cannot have multiple threads within the same transaction, and
you cannot pass a connection between threads.

I think we have an undestanding about the performance situation, and
we are getting to the real heart of the issue, which why it is not
possible to have a single transaction, single connection and multiple
threads.

Provided the user synchronises the access to the connection, this
should offer the highest performance.  But there is some technical
reason why sqlite has this (in my opinion unusual behaviour).  Perhaps
DRH can explain this...

Emerson


On 12/29/06, Ken <[EMAIL PROTECTED]> wrote:

The test server.c code is an example that has a single thread that performs the 
DB interactions each client thread, communicates via a queue interface. Each 
client will get serialized into the  DBserver thread and get its work done. 
Thus eliminating any lower level locking and mutexing inside the application 
code.

 Your assumption regarding 1 thread/1 cache is pretty accurate. This is what 
the test_server.c code does, however each client thread does however get a 
database connection handle.

 If you have a single global transaction you can do insr/selec/upd/delete.. The 
thing is that the connection may not be passed around amongst threads.

 You cannot have multiple threads executing within the same transaction!!! To 
my understanding, that is not allowed. Please someone else correct me if I'm 
wrong...

 The difference is with other DB's they utilize a finer grain of locking 
internally, either page locking, row locking etc.. Sqlite uses Database 
LOCKING, which is full file. So its really only designed to be used by a single 
thread of execution. (again DRH please correct me here if I'm wrong).


Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,

Thanks.

Ok, i think i did miss the point with your test case.  I assumed that
it was always possible to perform multiple select, insert, delete, and
update statements within a single transaction anyway.  Which i guess
relates to you last point.  I dont see why if i have a single global
transaction i cant perform all the operations i want within that
transaction to maximise performance.  In fact i know i can. because
thats what i do with a single thread.

I have always assumed that the simplest and highest performance
solution would be to have multiple threads with one transaction and
one cache.  I will take a look at the test_server.c code and see if i
can understand what you are suggesting.

I dont see why writer starvation would be a problem, but again i
probably just dont understand what your suggesting there.  Anyway,
thanks for your help.  I suspect that you are right and the ultimate
solution will be to write my own synchronisation code, since that will
allow me to have a consistent api across multiple databases like
sqlite and mysql.

I could achieve this if i could have multiple threads executing
statements within the same transaction since that is the effective
behaviour i get from most other databases which are not in process.
In those cases it is the process boundary and the tcp/ip connections
which are effectivley serialising all interactions which is equivalent
of having only one thread when using sqlite.

Emerson


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

Re: [sqlite] sqlite performance, locking & threadi

[Ken]

The test server.c code is an example that has a single thread that performs the 
DB interactions each client thread, communicates via a queue interface. Each 
client will get serialized into the  DBserver thread and get its work done. 
Thus eliminating any lower level locking and mutexing inside the application 
code.
 
 Your assumption regarding 1 thread/1 cache is pretty accurate. This is what 
the test_server.c code does, however each client thread does however get a 
database connection handle.
 
 If you have a single global transaction you can do insr/selec/upd/delete.. The 
thing is that the connection may not be passed around amongst threads. 
 
 You cannot have multiple threads executing within the same transaction!!! To 
my understanding, that is not allowed. Please someone else correct me if I'm 
wrong...
 
 The difference is with other DB's they utilize a finer grain of locking 
internally, either page locking, row locking etc.. Sqlite uses Database 
LOCKING, which is full file. So its really only designed to be used by a single 
thread of execution. (again DRH please correct me here if I'm wrong).
 
 
Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,

Thanks.

Ok, i think i did miss the point with your test case.  I assumed that
it was always possible to perform multiple select, insert, delete, and
update statements within a single transaction anyway.  Which i guess
relates to you last point.  I dont see why if i have a single global
transaction i cant perform all the operations i want within that
transaction to maximise performance.  In fact i know i can. because
thats what i do with a single thread.

I have always assumed that the simplest and highest performance
solution would be to have multiple threads with one transaction and
one cache.  I will take a look at the test_server.c code and see if i
can understand what you are suggesting.

I dont see why writer starvation would be a problem, but again i
probably just dont understand what your suggesting there.  Anyway,
thanks for your help.  I suspect that you are right and the ultimate
solution will be to write my own synchronisation code, since that will
allow me to have a consistent api across multiple databases like
sqlite and mysql.

I could achieve this if i could have multiple threads executing
statements within the same transaction since that is the effective
behaviour i get from most other databases which are not in process.
In those cases it is the process boundary and the tcp/ip connections
which are effectivley serialising all interactions which is equivalent
of having only one thread when using sqlite.

Emerson

On 12/29/06, Ken  wrote:
>
>  Emerson,
>
> You just need to lock the entire transaction using a mutex before hand. That 
> means each thread will have its own sqlite cache. Reread the sqlite locking 
> and concurrency guide, you'll see that SQLITE is NOT a transaction based 
> system such as Postgress/mysql/ oracle. It locks the entire database file at 
> the point of writing, not at the first insert/update/delete. Its better to 
> think of sqlite as an easy way to perform data access against a FLAT file. 
> Rather than a transaction oriented system.
>
> Regarding my test case. You missed the point, there is a select statement in 
> the middle that each thread executes. The nice thing is that each and every 
> thread uses a shared cache and can execute the select statement concurrently 
> without a mutex. The locking when using a shared cache works at the table 
> level instead of file!
>
> Another thing you should look at: Create a master DB, then attach the 
> individual databases to the master. That way you only need one Cache. How do 
> you know you wouldn't benefit from merging your caches? Have you tried? Do 
> you have test cases to prove that it doesn't help?
>
> You stated that you want to put your owner locking mutex wrapper around the 
> database interactions so that there would be only 1 thread access the DB.. 
> That means that you need to release the lock after each 
> statement(insert/update/delete) and not perform a BEGIN transaction/commit... 
> Which means very slow performance. And besides only one thread regardless 
> could be accessing the DB. Whereas the test_server.c architecture (you need 
> to modify this for your own needs) will let you create multiple shared 
> connections to the database. And each thread can perform selects 
> concurrently. But all threads are serialized into the single thread for 
> writing. This takes care of all locking issues and is technically the same as 
> mutexing the writes across threads. And it addresses writer starvation, which 
> you have not addressed with your simple mutex locking.
>
> It seems to me you might want write your own locking implementation or even 
> abandoning sqlite if it doesn't fit your needs.
>
>
> Emerson Clarke  wrote: Ken,
>
> Thanks i understand your example well.
>
> What im looking for is the ability to have multiple threads perform a
> write 

Re: [sqlite] sqlite performance, locking & threadi

[Emerson Clarke]

OK, thanks Joe.

I will look up those peculiarities as you suggested, im interested in
understanding what they are.

I agree with you, DRH has done a great job.  Sqlite is a fantastic
piece of software and and outstanding example of the way open source
should be.  It is small, efficient and incredibly easy to use.

It is remarkable that it has so many capabilities and as such it has
rapidly become the critical component in many of the things i am
working on.

I was just a bit confused about the way the threading works, hence
this discussion.

Emerson

On 12/29/06, Emerson Clarke <[EMAIL PROTECTED]> wrote:

Ken,

Thanks.

Ok, i think i did miss the point with your test case.  I assumed that
it was always possible to perform multiple select, insert, delete, and
update statements within a single transaction anyway.  Which i guess
relates to you last point.  I dont see why if i have a single global
transaction i cant perform all the operations i want within that
transaction to maximise performance.  In fact i know i can. because
thats what i do with a single thread.

I have always assumed that the simplest and highest performance
solution would be to have multiple threads with one transaction and
one cache.  I will take a look at the test_server.c code and see if i
can understand what you are suggesting.

I dont see why writer starvation would be a problem, but again i
probably just dont understand what your suggesting there.  Anyway,
thanks for your help.  I suspect that you are right and the ultimate
solution will be to write my own synchronisation code, since that will
allow me to have a consistent api across multiple databases like
sqlite and mysql.

I could achieve this if i could have multiple threads executing
statements within the same transaction since that is the effective
behaviour i get from most other databases which are not in process.
In those cases it is the process boundary and the tcp/ip connections
which are effectivley serialising all interactions which is equivalent
of having only one thread when using sqlite.

Emerson

On 12/29/06, Ken <[EMAIL PROTECTED]> wrote:
>
>  Emerson,
>
>You just need to lock the entire transaction using a mutex before hand. 
That means each thread will have its own sqlite cache.  Reread the sqlite locking 
and concurrency guide, you'll see that SQLITE is NOT a transaction based system 
such as Postgress/mysql/ oracle. It locks the entire database file at the point of 
writing, not at the first insert/update/delete. Its better to think of sqlite as 
an easy way to perform data access against a FLAT file. Rather than a transaction 
oriented system.
>
>  Regarding my test case. You missed the point, there is a select statement in 
the middle that each thread executes. The nice thing is that each and every thread 
uses a shared cache and can execute the select statement concurrently without a 
mutex. The locking when using a shared cache works at the table level instead of 
file!
>
>  Another thing you should look at: Create a master DB, then attach the 
individual databases to the master. That way you only need one Cache. How do you 
know you wouldn't benefit from merging your caches? Have you tried? Do you have 
test cases to prove that it doesn't help?
>
>  You stated that you want to put your owner locking mutex wrapper around the 
database interactions so that there would be only 1 thread access the DB.. That 
means that you need to release the lock after each statement(insert/update/delete) 
and not perform a BEGIN transaction/commit... Which means very slow performance. 
And besides only one thread regardless could be accessing the DB. Whereas the 
test_server.c  architecture (you need to modify this for your own needs) will let 
you create multiple shared connections to the database. And each thread can 
perform selects concurrently. But all threads are serialized into the single 
thread for writing. This takes care of all locking issues and is technically the 
same as mutexing the writes across threads. And it addresses writer starvation, 
which you have not addressed with your simple mutex locking.
>
>  It seems to me you might want write your own locking implementation or even 
abandoning sqlite if it doesn't fit your needs.
>
>
> Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,
>
> Thanks i understand your example well.
>
> What im looking for is the ability to have multiple threads perform a
> write operation based on my mutex, not some internal sqlite write
> mutex.  If i am managing the concurrency and performing correct
> synchronisation, why can i not have multiple threads writing to the
> database at once and achive a better overall performance.
>
> Given that the indexing process i am referring too has several steps
> which it must perform in successive order, and not all of them are
> database writes, i am simply trying to use threads to emulate a
> situation where there is only one thread doing all the database
> writes, if that makes sense.

Re: [sqlite] Creating a database from inside a program

[Clay Dowling]

Open the database as per normal with sqlite3_open().  Then issue the SQL
commands necessary to create your schema.  I have a nice little utility I
wrote which will take an SQLite schema dump and convert it to an array of
C strings that you can issue in sequence (and thanks to Microsoft for the
technique).

I've included the utility which converts an SQL export to C code.  I'll
let you work out the details for the rest.

Clay Dowling

Rob Richardson said:
> Greetings!
>
>
>
> I need to be able to create a database with a known schema from inside a
> program.  I used sqlite3.exe's .schema command to export the SQL needed
> to create the schema for my new database.  I can create the database by
> the following steps:
>
>
>
> C:\: sqlite3 newdatabase.db
>
> Sqlite3> .read schemafile.txt
>
>
>
> At that point, my database is ready to go.
>
>
>
> But, sqlite3 is supposed to be able to accept commands on its command
> line.  For example, the schema file was generated using this command:
>
>
>
> C:\: sqlite3 existingdatabase.db .schema > schemafile.txt
>
>
>
> So, I thought I could just issue the following command from inside my
> program:
>
>
>
> Sqlite3 newdatabase.db .read schemafile.txt
>
>
>
> But, when I issue this command from the DOS prompt,  it gives me an
> error message saying that there is no command named "read".  (Note the
> lack of a leading period.)  If this won't work from a DOS prompt, I'm
> sure it won't work from my program.  So, what is the recommended way to
> create a new database and its schema from inside a program?  In case it
> matters, I'll be using Visual C# 2005 and the SQLite.net.dll file from
> SourceForge.
>
>
>
> Thank you very much!
>
>
>
> Rob Richardson
>
> RAD-CON INC.
>
>


-- 
Simple Content Management
http://www.ceamus.com/* This file (c) Copyright 2004, 2005, 2006 Lazarus Internet Development
 *
 * Permission is given to use this source code for personal or 
 * non-profit use free of charge, so long as this copyright is
 * maintained.  You may use this source code for commercial use
 * so long as you have obtained a license from Lazarus Internet
 * Development.  A license may be purchased by writing to
 * Clay Dowling <[EMAIL PROTECTED]>
 *
 * If you modify this source code and distribute it you must 
 * indicate such in this header, and provide all support for
 * your modified version.
 *
 * $Id: sqlmodule.c 142 2006-05-28 17:43:28Z clay $
 */

/* Create a C module which will return an array of strings, one for each
 * of the SQL statements in the passed file name
 *
 * This program runs on the SQL dumps/scripts produced by most SQL databases.
 * It's only requirement is that each statement ends with a semicolon (';').
 * This rules out SQL Server, which terminates a statement with GO on a line
 * by itself.  It should be a pretty easy adaptation though if such a feature
 * is needed.
 *
 * Comments and empty lines are ignored.
 * Lines with text are added to the array with a newline appended and
 * any single or double quotes escaped.
 * Lines which have a semi-colon cause a new array entry to be created
 */

#include 
#include 
#include 
#include 

#define LINE_SIZE 2048

void markcomment(char*);
const char* readline(FILE*);
void escapeline(char*);
void trim_trailing_space(char*);

int main(int argc, char** argv) {

  FILE* in;
  FILE* out;
  char* filename;
  char* base;
  char* outfile;
  const char* line;

  if (argc == 1) {
fprintf(stderr, "usage: %s file.sql\n", argv[0]);
return EXIT_FAILURE;
  }

  in = fopen(argv[1], "r");
  if (!in) {
perror(argv[1]);
return EXIT_FAILURE;
  }
  
  filename = (char*)calloc(1, strlen(argv[1]) + 1);
  strcpy(filename, argv[1]);
  base = strrchr(filename, '.');
  if (base) *base = '\0';
  outfile = (char*)calloc(1, strlen(filename) + 6);
  snprintf(outfile, strlen(filename) + 6, "mod%s.c", filename);

  out = fopen(outfile, "w");
  if (!out) {
perror(outfile);
return EXIT_FAILURE;
  }

  fprintf(out, "/* auto-generated SQL module */\n\n");
  fprintf(out, "char** getsql() {\n");
  fprintf(out, "  static char *sql[] = {\n");

  while((line = readline(in))) {
  	if (strlen(line) > 0) {
	  fprintf(out, "  \"%s\"", line);
  if (strchr(line, ';')) fprintf(out, ",\n");
	  fprintf(out, "\n");
  	}
  }
  fprintf(out, "  0};\n\n");
  fprintf(out, "  return sql;\n");
  fprintf(out, "}\n");

  fclose(in);
  fclose(out);
  
 
  return EXIT_SUCCESS;

}

const char* readline(FILE* in) {

static char line[LINE_SIZE];
memset(line, 0, LINE_SIZE);

if (fgets(line, LINE_SIZE/2, in)) {
  markcomment(line);
  trim_trailing_space(line);
  escapeline(line);
  return line;
}
else
  return NULL;

}

void markcomment(char* line) {

char* pos;

pos = strstr(line, "--");
if (pos)
	*pos = '\0';

}

void escapeline(char* line) {

static char work[LINE_SIZE];
char* dst;
char* src;

memset(work, 0, LINE_SIZE);
dst = work;
src = line;
while(*src) {
	

Re: [sqlite] sqlite performance, locking & threadi

[Emerson Clarke]

Ken,

Thanks.

Ok, i think i did miss the point with your test case.  I assumed that
it was always possible to perform multiple select, insert, delete, and
update statements within a single transaction anyway.  Which i guess
relates to you last point.  I dont see why if i have a single global
transaction i cant perform all the operations i want within that
transaction to maximise performance.  In fact i know i can. because
thats what i do with a single thread.

I have always assumed that the simplest and highest performance
solution would be to have multiple threads with one transaction and
one cache.  I will take a look at the test_server.c code and see if i
can understand what you are suggesting.

I dont see why writer starvation would be a problem, but again i
probably just dont understand what your suggesting there.  Anyway,
thanks for your help.  I suspect that you are right and the ultimate
solution will be to write my own synchronisation code, since that will
allow me to have a consistent api across multiple databases like
sqlite and mysql.

I could achieve this if i could have multiple threads executing
statements within the same transaction since that is the effective
behaviour i get from most other databases which are not in process.
In those cases it is the process boundary and the tcp/ip connections
which are effectivley serialising all interactions which is equivalent
of having only one thread when using sqlite.

Emerson

On 12/29/06, Ken <[EMAIL PROTECTED]> wrote:


 Emerson,

   You just need to lock the entire transaction using a mutex before hand. That 
means each thread will have its own sqlite cache.  Reread the sqlite locking 
and concurrency guide, you'll see that SQLITE is NOT a transaction based system 
such as Postgress/mysql/ oracle. It locks the entire database file at the point 
of writing, not at the first insert/update/delete. Its better to think of 
sqlite as an easy way to perform data access against a FLAT file. Rather than a 
transaction oriented system.

 Regarding my test case. You missed the point, there is a select statement in 
the middle that each thread executes. The nice thing is that each and every 
thread uses a shared cache and can execute the select statement concurrently 
without a mutex. The locking when using a shared cache works at the table level 
instead of file!

 Another thing you should look at: Create a master DB, then attach the 
individual databases to the master. That way you only need one Cache. How do 
you know you wouldn't benefit from merging your caches? Have you tried? Do you 
have test cases to prove that it doesn't help?

 You stated that you want to put your owner locking mutex wrapper around the 
database interactions so that there would be only 1 thread access the DB.. That 
means that you need to release the lock after each 
statement(insert/update/delete) and not perform a BEGIN transaction/commit... 
Which means very slow performance. And besides only one thread regardless could 
be accessing the DB. Whereas the test_server.c  architecture (you need to 
modify this for your own needs) will let you create multiple shared connections 
to the database. And each thread can perform selects concurrently. But all 
threads are serialized into the single thread for writing. This takes care of 
all locking issues and is technically the same as mutexing the writes across 
threads. And it addresses writer starvation, which you have not addressed with 
your simple mutex locking.

 It seems to me you might want write your own locking implementation or even 
abandoning sqlite if it doesn't fit your needs.


Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,

Thanks i understand your example well.

What im looking for is the ability to have multiple threads perform a
write operation based on my mutex, not some internal sqlite write
mutex.  If i am managing the concurrency and performing correct
synchronisation, why can i not have multiple threads writing to the
database at once and achive a better overall performance.

Given that the indexing process i am referring too has several steps
which it must perform in successive order, and not all of them are
database writes, i am simply trying to use threads to emulate a
situation where there is only one thread doing all the database
writes, if that makes sense.

So in this case, what ever synchronisation sqlite is doing internally
is actually getting in the way of what i am trying to do.

There were no gains in performance in your test case becuase there was
no reason to have multiple threads.  The only thing each thread was
doing was writing to the database, so of course the only thing you
will introduce by using multiple threads is overhead.

Emerson



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

Re: [sqlite] sqlite performance, locking & threading

[Joe Wilson]

This old mailing list thread better describes this proposed algorithm
to contain all open/close/lock/unlock activity in a single work thread:

  http://www.mail-archive.com/sqlite-users@sqlite.org/msg15852.html

--- Joe Wilson <[EMAIL PROTECTED]> wrote:
> As you already know, it's not just Linux - it's a POSIX thing.
> It's also true with FreeBSD and OpenBSD. (BSD fcntl man page below).
> 
> It would be great if SQLite could remove this last vestage of not 
> being able to reliably pass connections between threads on UNIX. 
> One way to accomplish that is to have all low level UNIX open() and close() 
> calls be performed from a single thread. Regardless of whatever thread 
> initiates 
> the sqlite3_open or sqlite3_close, SQLite could populate a threadsafe 
> work queue with the open/close information and wait on a condition variable 
> for its successful completion. The same "don't close() the file until the 
> file ref-count is zero" trick would still have to be employed behind
> the scenes.
> 
>  This interface follows the completely stupid semantics of System V and
>  IEEE Std 1003.1-1988 (``POSIX.1'') that require that all locks associated
>  with a file for a given process are removed when any file descriptor for
>  that file is closed by that process.  This semantic means that applica-
>  tions must be aware of any files that a subroutine library may access.
>  For example if an application for updating the password file locks the
>  password file database while making the update, and then calls
>  getpwnam(3) to retrieve a record, the lock will be lost because
>  getpwnam(3) opens, reads, and closes the password database.  The database
>  close will release all locks that the process has associated with the
>  database, even if the library routine never requested a lock on the data-
>  base.  Another minor semantic problem with this interface is that locks
>  are not inherited by a child process created using the fork(2) system
>  call.  The flock(2) interface has much more rational last close semantics
>  and allows locks to be inherited by child processes.  The flock(2) system
>  call is recommended for applications that want to ensure the integrity of
>  their locks when using library routines or wish to pass locks to their
>  children.


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

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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Richard,

First let me say thank you for your earilier suggestion regarding the
compile time options and overlaoding of the os routines.  It was
exactly the kind of information i was searching to for and i am
looking into using that strategy at the moment.

Sorry if ive managed to offend you, please excuse my enthusiasm. Its
just the way i write and it doesnt necessarily reflect my intentions.

I only meant to point out that it is very rare to encounter a library
which behaves in the way that sqlite does.  That is that the actual
pod types cannot be used across multiple threads.  I understand that
sqlite can be used with multiple threads, but only in what i percieve
to be a limited way.

I cannot use one connection with multiple threads.  So i cant take
advantage of the caching unless i use other options and pragma
directives.

I also cannot perform multiple operations within a single transaction
across multiple threads. This design is restrictive in that it forces
very granular transactions which then limit the overall performance.

Perhaps you could assist me in understanding why this design is better
than say, just allowing one connection to be shared amongst threads
and letting the user handle the synchronisation ?

At the moment there is no way which i can wrap the sqlite libraries in
an api which is shared with another database like mysql and have
consistent behaviour.  This is purely because of the restrictions on
the way sqlite handles threads and the fact that a transaction cannot
be used across multiple threads.  If sqlite behaved differently then
from the wrapper code there would be no difference between using
sqlite and mysql except for the sql syntax.  The fact that it is an
embedded file based database would be completely transparent.  At the
moment it is not.

Functions like qsort() are not only thread safe, but probably also
reentrant.  Im aware of the differences and i understand why things
are and arent thread safe. Of course there are several algorithms
other than quick sort which can be used to sort the same array in
multiple threads.

I dont wish to start a debate, merely to engange in a technical
discussion of the design which sqlite uses since i dont understand the
reasons behind it and i would very much like too.

Emerson

On 12/29/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Im not sure that the current situation of forced thread
> incompatibility is better than leaving it up to users to manage the
> threading.  Usually it is assumed that a library is thread unsafe
> unless otherwise specified.
>
> Developing multithreaded applications is difficult, i wouldnt dispute
> that.  But i do dispute the wisdom of actively making a library
> incompatible with threads.  Sqlite is the first example of such
> behaviour i have seen, it deliberately fails even if the user attempts
> to synchronise concurrent access.

You'll probably get more help in solving your problem if you
can refrain from sophomoric remarks such as the above.

>
> Surely it would be better to design the api so that it was either
> fully thread safe and blocked on every call to an sqlite_ function
> using its own mutexes, or to simply ensure that it was process safe
> using shared memory.  As i mentioned in a previous reply,
> synchronising sqlite would be no more or less difficult or error prone
> than synchonising a linked list.

The threading design of SQLite is very simple and intuitive.
Multiple threads can be using SQLite at the same time as long
as they are not using the same database connection at the same
time.  This is consistent with most other utility functions.
The qsort() function is threadsafe - you can have two different
threads in qsort() sorting different arrays at the same time.
But it does not work to sort the same array in two threads at
the same time.  In the same way, SQLite allows two different
threads to be working with different database connections at
the same time, but not the same database connection.

What about that is so difficult to understand?

On some older versions of Linux, SQLite is unable to pass
database connections from one thread to another.  But this
is a problem with the threading libraries used in those older
linux versions and is outside the control of SQLite.  I do not
think this issue comes into play here.

>
> I dont see what is gained by making it not work with threads, so i
> assume that it is becuase of some technical reason rather than a
> design decision.

SQLite does work with threads.  Your comment is based on a false
premise.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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




-
To unsubscribe, send email to [EMAIL PROTECTED]

Re: [sqlite] sqlite performance, locking & threadin

[Joe Wilson]

My last message cites some of the peculiarities of POSIX fcntl() locking. 
Search the SQLite mailing list archives for more detailed info as
it pertains to SQLite.

As for having a single unified (locking) model - SQLite already
employs such a strategy as best as is possible given the portable
nature of the library.  Trying to get all versions of UNIX and
Windows to play nice when it comes to file locking is no small chore.
DRH has done a great job.

--- Emerson Clarke <[EMAIL PROTECTED]> wrote:
> Im interested to know what those constraints are and why ?
> 
> The only reason i mentioned shared memory is because it provides a
> platform and filesystem agnostic way of handling ipc.  Obvioulsy i
> dont know the ins and outs of the locking process, but i just thought
> it would make sense to have a single unified model rather than
> different strategies on many platforms.
> 
> On 12/29/06, Joe Wilson <[EMAIL PROTECTED]> wrote:
> > --- Emerson Clarke <[EMAIL PROTECTED]> wrote:
> > > Developing multithreaded applications is difficult, i wouldnt dispute
> > > that.  But i do dispute the wisdom of actively making a library
> > > incompatible with threads.
> >
> > "Actively"?  That's a bit much.
> >
> > There are constraints on the ability to pass SQLite connections
> > between threads. To be safe, just use the connection on the
> > same thread it was created and you'll be fine.


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

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

RE: [sqlite] LEMON

[Downey, Shawn]

yes 


Shawn M. Downey
MPR Associates
10 Maxwell Drive, Suite 204
Clifton Park, New York 12065
518-371-3983 x113 (work)
860-508-5015 (cell)

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 29, 2006 11:47 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] LEMON

Lemon generates C files that could be compiled in Win or Linux Right?

On 29/12/06, Downey, Shawn <[EMAIL PROTECTED]> wrote:
>
> http://www.hwaci.com/sw/lemon/lemon.html
> Sorry if this link has already been pointed out to you.
>
>
> http://www.webdotdev.com/nvd/server-side/c/lemon-parser-generator-tuto
> ri
> al.html
> Looks OK but I have not looked at this site before.
>
> Shawn M. Downey
> MPR Associates
> 10 Maxwell Drive, Suite 204
> Clifton Park, New York 12065
> 518-371-3983 x113 (work)
> 860-508-5015 (cell)
>
> -Original Message-
> From: Cesar Rodas [mailto:[EMAIL PROTECTED]
> Sent: Friday, December 29, 2006 11:17 AM
> To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
> Subject: Re: [sqlite] LEMON
>
> Another URL where i can find a tutorial?
>
> On 29/12/06, Lloyd <[EMAIL PROTECTED]> wrote:
> >
> > Ethereal make use of Lemon
> >
> > On Fri, 2006-12-29 at 11:08 -0400, Cesar Rodas wrote:
> > > Where can i find a tutorial with examples of how to use LEMON
> parser...
> > > because i need to build interpreted language
> > >
> > > Thanks to all
> >
> >
> > __
> > Scanned and protected by Email scanner
> >
> >
> > 
> > --
> > --- To unsubscribe, send email to 
> > [EMAIL PROTECTED]
> >
> > 
> > --
> > ---
> >
> >
>
>
> --
> Cesar Rodas
> http://www.phpclasses.org/grank (A PHP implementation of PageRank)
>
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
>
> --
> ---
>
>


--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

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

[sqlite] Creating a database from inside a program

[Rob Richardson]

Greetings!

 

I need to be able to create a database with a known schema from inside a
program.  I used sqlite3.exe's .schema command to export the SQL needed
to create the schema for my new database.  I can create the database by
the following steps:

 

C:\: sqlite3 newdatabase.db

Sqlite3> .read schemafile.txt

 

At that point, my database is ready to go.  

 

But, sqlite3 is supposed to be able to accept commands on its command
line.  For example, the schema file was generated using this command:

 

C:\: sqlite3 existingdatabase.db .schema > schemafile.txt

 

So, I thought I could just issue the following command from inside my
program:

 

Sqlite3 newdatabase.db .read schemafile.txt

 

But, when I issue this command from the DOS prompt,  it gives me an
error message saying that there is no command named "read".  (Note the
lack of a leading period.)  If this won't work from a DOS prompt, I'm
sure it won't work from my program.  So, what is the recommended way to
create a new database and its schema from inside a program?  In case it
matters, I'll be using Visual C# 2005 and the SQLite.net.dll file from
SourceForge.

 

Thank you very much!

 

Rob Richardson

RAD-CON INC.

Re: [sqlite] sqlite performance, locking & threading

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> On some older versions of Linux, SQLite is unable to pass
> database connections from one thread to another.  But this
> is a problem with the threading libraries used in those older
> linux versions and is outside the control of SQLite.  I do not
> think this issue comes into play here.

As you already know, it's not just Linux - it's a POSIX thing.
It's also true with FreeBSD and OpenBSD. (BSD fcntl man page below).

It would be great if SQLite could remove this last vestage of not 
being able to reliably pass connections between threads on UNIX. 
One way to accomplish that is to have all low level UNIX open() and close() 
calls be performed from a single thread. Regardless of whatever thread 
initiates 
the sqlite3_open or sqlite3_close, SQLite could populate a threadsafe 
work queue with the open/close information and wait on a condition variable 
for its successful completion. The same "don't close() the file until the 
file ref-count is zero" trick would still have to be employed behind
the scenes.

 This interface follows the completely stupid semantics of System V and
 IEEE Std 1003.1-1988 (``POSIX.1'') that require that all locks associated
 with a file for a given process are removed when any file descriptor for
 that file is closed by that process.  This semantic means that applica-
 tions must be aware of any files that a subroutine library may access.
 For example if an application for updating the password file locks the
 password file database while making the update, and then calls
 getpwnam(3) to retrieve a record, the lock will be lost because
 getpwnam(3) opens, reads, and closes the password database.  The database
 close will release all locks that the process has associated with the
 database, even if the library routine never requested a lock on the data-
 base.  Another minor semantic problem with this interface is that locks
 are not inherited by a child process created using the fork(2) system
 call.  The flock(2) interface has much more rational last close semantics
 and allows locks to be inherited by child processes.  The flock(2) system
 call is recommended for applications that want to ensure the integrity of
 their locks when using library routines or wish to pass locks to their
 children.


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

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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[Michael Sizaki]

I went to implement this suggestion and quickly discovered
that SQLite already uses the FILE_ATTRIBUTE_TEMPORARY flag
on TEMP tables.  Or at least I think it does.  Can somebody
with a symbolic debugger that runs on windows please confirm
that the marked line of code in below (found in os_win.c) gets 
executed when using TEMP tables:


It gets called!

Michael

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

Re: [sqlite] sqlite performance, locking & threadin

[Emerson Clarke]

Joe,

Im interested to know what those constraints are and why ?

The only reason i mentioned shared memory is because it provides a
platform and filesystem agnostic way of handling ipc.  Obvioulsy i
dont know the ins and outs of the locking process, but i just thought
it would make sense to have a single unified model rather than
different strategies on many platforms.

On 12/29/06, Joe Wilson <[EMAIL PROTECTED]> wrote:

--- Emerson Clarke <[EMAIL PROTECTED]> wrote:
> Developing multithreaded applications is difficult, i wouldnt dispute
> that.  But i do dispute the wisdom of actively making a library
> incompatible with threads.

"Actively"?  That's a bit much.

There are constraints on the ability to pass SQLite connections
between threads. To be safe, just use the connection on the
same thread it was created and you'll be fine.

> Sqlite is the first example of such
> behaviour i have seen, it deliberately fails even if the user attempts
> to synchronise concurrent access.
>
> Surely it would be better to design the api so that it was either
> fully thread safe and blocked on every call to an sqlite_ function
> using its own mutexes, or to simply ensure that it was process safe
> using shared memory.  As i mentioned in a previous reply,
> synchronising sqlite would be no more or less difficult or error prone
> than synchonising a linked list.

You're free to serialize your calls to SQLite in your own code.
Take a look at the many wrappers for SQLite for ideas.

Shared memory is not the answer. It's a nightmare to manage and creates
more problems than it solves.


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

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




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

Re: [sqlite] sqlite performance, locking & threadin

[Ken]

 Emerson,
 
You just need to lock the entire transaction using a mutex before hand. 
That means each thread will have its own sqlite cache.  Reread the sqlite 
locking and concurrency guide, you'll see that SQLITE is NOT a transaction 
based system such as Postgress/mysql/ oracle. It locks the entire database file 
at the point of writing, not at the first insert/update/delete. Its better to 
think of sqlite as an easy way to perform data access against a FLAT file. 
Rather than a transaction oriented system.
 
 Regarding my test case. You missed the point, there is a select statement in 
the middle that each thread executes. The nice thing is that each and every 
thread uses a shared cache and can execute the select statement concurrently 
without a mutex. The locking when using a shared cache works at the table level 
instead of file!
 
 Another thing you should look at: Create a master DB, then attach the 
individual databases to the master. That way you only need one Cache. How do 
you know you wouldn't benefit from merging your caches? Have you tried? Do you 
have test cases to prove that it doesn't help?
 
 You stated that you want to put your owner locking mutex wrapper around the 
database interactions so that there would be only 1 thread access the DB.. That 
means that you need to release the lock after each 
statement(insert/update/delete) and not perform a BEGIN transaction/commit... 
Which means very slow performance. And besides only one thread regardless could 
be accessing the DB. Whereas the test_server.c  architecture (you need to 
modify this for your own needs) will let you create multiple shared connections 
to the database. And each thread can perform selects concurrently. But all 
threads are serialized into the single thread for writing. This takes care of 
all locking issues and is technically the same as mutexing the writes across 
threads. And it addresses writer starvation, which you have not addressed with 
your simple mutex locking.
 
 It seems to me you might want write your own locking implementation or even 
abandoning sqlite if it doesn't fit your needs. 
 
 
Emerson Clarke <[EMAIL PROTECTED]> wrote: Ken,

Thanks i understand your example well.

What im looking for is the ability to have multiple threads perform a
write operation based on my mutex, not some internal sqlite write
mutex.  If i am managing the concurrency and performing correct
synchronisation, why can i not have multiple threads writing to the
database at once and achive a better overall performance.

Given that the indexing process i am referring too has several steps
which it must perform in successive order, and not all of them are
database writes, i am simply trying to use threads to emulate a
situation where there is only one thread doing all the database
writes, if that makes sense.

So in this case, what ever synchronisation sqlite is doing internally
is actually getting in the way of what i am trying to do.

There were no gains in performance in your test case becuase there was
no reason to have multiple threads.  The only thing each thread was
doing was writing to the database, so of course the only thing you
will introduce by using multiple threads is overhead.

Emerson

On 12/28/06, Ken  wrote:
> Emerson,
>
> Is the Database on the same disk as the rest of the file operations? If so is 
> it possible that you are I/O bound and causing seek issues due to i/o access 
> patterns?
>
> Take a look at the test_server.c code in the sqlite/src directory. I used 
> that as a basis to build a custom library that opens a single DB and then 
> allows multiple threads to access. The nice thing about this architecture is 
> that the threads will all get to write and no writer starvation. But all 
> write operations an single threaded.
>
>  The test code I ran creates any number of threads and performs the following 
> in each thread:
>
>  outer loop 1- 10
>  begin txn
>  loop  1 -1000
>   insert record (using modulo for data so data is unique amongst 
> threads)
>  end loop
>  commit
>
>  prepare statement
>  loop 1 - 1000
>  Select data (using modulo)
>  end loop
>   close statement
>
>   begin transaction
>   loop 1 - 1000
>   delete data, using same modulo
>   end loop
>  end main loop
>
>  timinng (seconds)  Thread count
>  1.6651   (transaction 
> size is 1000)
>  1.6352   (transcaction 
> size is 500)
>  3.094   4( txn size is 
> 250 )
>  5.571   8(txn size is 
> 125 )
>  7.82216(txn size is 62.5)
>
> so as the number of threads increase the overall time it takes to 
> insert/select/delete a fixed set of data increases using this architecture. 
> This is because all 

[sqlite] Re: multiple selects in a single prepare

[A. Pagaltzis]

* Igor Tandetnik <[EMAIL PROTECTED]> [2006-12-29 17:35]:
> Why not
> 
> select state, count(*)
> where state in ('Normal', 'Critical')
> group by state;
> 
> ?

Clever!


* [EMAIL PROTECTED] <[EMAIL PROTECTED]> [2006-12-29 17:40]:
> SELECT count(state='Normal'), count(state='Critical') FROM tbl1;

How exactly does this work? I assume it involves data type
coercion, but what are the rules and effects?

Regards,
-- 
Aristotle Pagaltzis // 

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

[sqlite] Re: multiple selects in a single prepare

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

SELECT count(state='Normal'), count(state='Critical') FROM tbl1;


Wouldn't that just return the number of all rows in tbl1, twice? That 
probably should be


SELECT sum(state='Normal'), sum(state='Critical') FROM tbl1;

Igor Tandetnik 



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

Re: [sqlite] LEMON

[Cesar Rodas]

Lemon generates C files that could be compiled in Win or Linux Right?

On 29/12/06, Downey, Shawn <[EMAIL PROTECTED]> wrote:


http://www.hwaci.com/sw/lemon/lemon.html
Sorry if this link has already been pointed out to you.


http://www.webdotdev.com/nvd/server-side/c/lemon-parser-generator-tutori
al.html
Looks OK but I have not looked at this site before.

Shawn M. Downey
MPR Associates
10 Maxwell Drive, Suite 204
Clifton Park, New York 12065
518-371-3983 x113 (work)
860-508-5015 (cell)

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED]
Sent: Friday, December 29, 2006 11:17 AM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] LEMON

Another URL where i can find a tutorial?

On 29/12/06, Lloyd <[EMAIL PROTECTED]> wrote:
>
> Ethereal make use of Lemon
>
> On Fri, 2006-12-29 at 11:08 -0400, Cesar Rodas wrote:
> > Where can i find a tutorial with examples of how to use LEMON
parser...
> > because i need to build interpreted language
> >
> > Thanks to all
>
>
> __
> Scanned and protected by Email scanner
>
>
> --
> --- To unsubscribe, send email to
> [EMAIL PROTECTED]
>
> --
> ---
>
>


--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[Michael Sizaki]

[EMAIL PROTECTED] wrote:

Perhaps someone with more windows experience can correct
me if my assertion above is incorrect.  Are there some
special flags that SQLite could pass to CreateFileW() to
trick windows into doing a better job of caching temp
files?


It seems you've done it right:
  fileflags = FILE_FLAG_RANDOM_ACCESS;
#if !OS_WINCE
  if( delFlag ){
fileflags |= FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_DELETE_ON_CLOSE;
  }
#endif

I'm really puzzled why my system hits the disk so heavily


Michael

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

[sqlite] Re: multiple selects in a single prepare

[A. Pagaltzis]

* chetana bhargav <[EMAIL PROTECTED]> [2006-12-29 17:20]:
> Actually I am trying to retrieve values in a single step.
> 
> My queries need to be something like,
> 
> select count(*) from tbl1 where state='Normal';select count(*) from tbl1 
> where state='Critical'
> 
> I got to have these two as seperate, because if there's any
> critical need to display a diff icon, and also the sum of those
> results. So wondering how can I avoid two table scans, and
> instead try to retrieve them in a single statement.

Then you should ask how to do that, instead of just making up a
way you think it should work and then asking whether that’s
possible.

What you want can be done by using an expression that returns
some non-NULL value for rows you want to include in the count and
NULL value for those you’re not interested in, then counting the
rows you got.

SELECT
COUNT( CASE state WHEN 'Normal' THEN 1 ELSE NULL END ) AS num_normal,
COUNT( CASE state WHEN 'Critical' THEN 1 ELSE NULL END ) AS num_critical
FROM
tbl1

Regards,
-- 
Aristotle Pagaltzis // 

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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[drh]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] wrote:
> > Are there some
> > special flags that SQLite could pass to CreateFileW() to
> > trick windows into doing a better job of caching temp
> > files?
> 
> FILE_ATTRIBUTE_TEMPORARY
> A file is being used for temporary storage. File systems avoid writing 
> data back to mass storage if sufficient cache memory is available, 
> because an application deletes a temporary file after a handle is 
> closed. In that case, the system can entirely avoid writing the data. 
> Otherwise, the data is written after the handle is closed.
> 

I went to implement this suggestion and quickly discovered
that SQLite already uses the FILE_ATTRIBUTE_TEMPORARY flag
on TEMP tables.  Or at least I think it does.  Can somebody
with a symbolic debugger that runs on windows please confirm
that the marked line of code in below (found in os_win.c) gets 
executed when using TEMP tables:

int sqlite3WinOpenExclusive(const char *zFilename, OsFile **pId, int delFlag){
  winFile f;
  HANDLE h;
  DWORD fileflags;
  void *zConverted = convertUtf8Filename(zFilename);
  if( zConverted==0 ){
return SQLITE_NOMEM;
  }
  assert( *pId == 0 );
  fileflags = FILE_FLAG_RANDOM_ACCESS;
#if !OS_WINCE
  if( delFlag ){
/**  The following line should run when opening a TEMP table **/
fileflags |= FILE_ATTRIBUTE_TEMPORARY | FILE_FLAG_DELETE_ON_CLOSE;
  }
#endif

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] LEMON

[epankoke]

I'm not familiar with Lemon, so I don't know if this will help: 
http://linuxgazette.net/106/chirico.html.  Also, I'd highly recommend just 
doing a Google search for either "Lemon Parser" or "Lemon Tutorial".

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "Cesar Rodas" <[EMAIL PROTECTED]>
> Another URL where i can find a tutorial?
> 
> On 29/12/06, Lloyd <[EMAIL PROTECTED]> wrote:
> >
> > Ethereal make use of Lemon
> >
> > On Fri, 2006-12-29 at 11:08 -0400, Cesar Rodas wrote:
> > > Where can i find a tutorial with examples of how to use LEMON parser...
> > > because i need to build interpreted language
> > >
> > > Thanks to all
> >
> >
> > __
> > Scanned and protected by Email scanner
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >
> >
> 
> 
> -- 
> Cesar Rodas
> http://www.phpclasses.org/grank (A PHP implementation of PageRank)


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

RE: [sqlite] LEMON

[Downey, Shawn]

http://www.hwaci.com/sw/lemon/lemon.html
Sorry if this link has already been pointed out to you.


http://www.webdotdev.com/nvd/server-side/c/lemon-parser-generator-tutori
al.html
Looks OK but I have not looked at this site before.

Shawn M. Downey
MPR Associates
10 Maxwell Drive, Suite 204
Clifton Park, New York 12065
518-371-3983 x113 (work)
860-508-5015 (cell)

-Original Message-
From: Cesar Rodas [mailto:[EMAIL PROTECTED] 
Sent: Friday, December 29, 2006 11:17 AM
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] LEMON

Another URL where i can find a tutorial?

On 29/12/06, Lloyd <[EMAIL PROTECTED]> wrote:
>
> Ethereal make use of Lemon
>
> On Fri, 2006-12-29 at 11:08 -0400, Cesar Rodas wrote:
> > Where can i find a tutorial with examples of how to use LEMON
parser...
> > because i need to build interpreted language
> >
> > Thanks to all
>
>
> __
> Scanned and protected by Email scanner
>
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
>
> --
> ---
>
>


--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

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

Re: [sqlite] multiple selects in a single prepare

[drh]

chetana bhargav <[EMAIL PROTECTED]> wrote:
> Actually I am trying to retrieve values in a single step.
> 
> My queries need to be something like,
> 
> select count(*) from tbl1 where state='Normal';
> select count(*) from tbl1 where state='Critical'
> 
> I got to have these two as seperate, because if there's any critical 
> need to display a diff icon, and also the sum of those results. So 
> wondering how can I avoid two table scans, and instead try to 
> retrieve them in a single statement.
> 

SELECT count(state='Normal'), count(state='Critical') FROM tbl1;

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

[sqlite] Re: Re: multiple selects in a single prepare

[Igor Tandetnik]

chetana bhargav
 wrote: 

Actually I am trying to retrieve values in a single step.

My queries need to be something like,

select count(*) from tbl1 where state='Normal';select count(*) from
tbl1 where state='Critical' 


Why not

select state, count(*)
where state in ('Normal', 'Critical')
group by state;

?

Igor Tandetnik

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

[sqlite] Re: ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

Are there some
special flags that SQLite could pass to CreateFileW() to
trick windows into doing a better job of caching temp
files?


FILE_ATTRIBUTE_TEMPORARY
A file is being used for temporary storage. File systems avoid writing 
data back to mass storage if sufficient cache memory is available, 
because an application deletes a temporary file after a handle is 
closed. In that case, the system can entirely avoid writing the data. 
Otherwise, the data is written after the handle is closed.


Igor Tandetnik 



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

Re: [sqlite] LEMON

[Cesar Rodas]

Another URL where i can find a tutorial?

On 29/12/06, Lloyd <[EMAIL PROTECTED]> wrote:


Ethereal make use of Lemon

On Fri, 2006-12-29 at 11:08 -0400, Cesar Rodas wrote:
> Where can i find a tutorial with examples of how to use LEMON parser...
> because i need to build interpreted language
>
> Thanks to all


__
Scanned and protected by Email scanner


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

Re: [sqlite] Re: multiple selects in a single prepare

[chetana bhargav]

Actually I am trying to retrieve values in a single step.

My queries need to be something like,

select count(*) from tbl1 where state='Normal';select count(*) from tbl1 where 
state='Critical'

I got to have these two as seperate, because if there's any critical need to 
display a diff icon, and also the sum of those results. So wondering how can I 
avoid two table scans, and instead try to retrieve them in a single statement.

..
Chetana

- Original Message 
From: A. Pagaltzis <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, December 28, 2006 11:21:28 AM
Subject: [sqlite] Re: multiple selects in a single prepare


* chetana bhargav <[EMAIL PROTECTED]> [2006-12-28 06:00]:
> Just wanted to know can we have multiple quries in a single
> prepare statement seperated by semicolons.Something like,
> 
> Select count(*) from tbl where name="foo";select count(*) from tbl1 where 
> name = "bar"

Just how is that supposed to work?

Are you looking for the UNION operator, perchance?

Regards,
-- 
Aristotle Pagaltzis // 

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

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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[drh]

Michael Sizaki <[EMAIL PROTECTED]> wrote:
> 
> What surprises me, is that the temp file is not kept in
> cache. I have 2GB of memory and much bigger files can be
> kept in cache. Why is sqlite "hitting the disk"?  What is
> going on here? The maximum file cache needed would be 70 MB
> for the database + 75 MB for the temp table. 150MB is
> nothing on a 2GB system.
> 

When writing to TEMP files, SQLite uses ordinary disk I/O
operations.  And it never calls FlushFileBuffers().  So
it is not a question of SQLite hitting the disk but rather
your operating system.  Why isn't windows able to cache
a 150MB file when you have 2GB of RAM?  I don't know, but
I don't think it has anything to do with SQLite.

Perhaps someone with more windows experience can correct
me if my assertion above is incorrect.  Are there some
special flags that SQLite could pass to CreateFileW() to
trick windows into doing a better job of caching temp
files?

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[Michael Sizaki]

Here's the screenshot showing the resource usage of the slow query:

 >time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 20 ORDER BY 
size, name;"|wc
  19  204598 24676875

real4m49.947s
user0m18.386s
sys 0m13.318s

Peak memory 35 MB


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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Im not sure that the current situation of forced thread
> incompatibility is better than leaving it up to users to manage the
> threading.  Usually it is assumed that a library is thread unsafe
> unless otherwise specified.
> 
> Developing multithreaded applications is difficult, i wouldnt dispute
> that.  But i do dispute the wisdom of actively making a library
> incompatible with threads.  Sqlite is the first example of such
> behaviour i have seen, it deliberately fails even if the user attempts
> to synchronise concurrent access.

You'll probably get more help in solving your problem if you
can refrain from sophomoric remarks such as the above.

> 
> Surely it would be better to design the api so that it was either
> fully thread safe and blocked on every call to an sqlite_ function
> using its own mutexes, or to simply ensure that it was process safe
> using shared memory.  As i mentioned in a previous reply,
> synchronising sqlite would be no more or less difficult or error prone
> than synchonising a linked list.

The threading design of SQLite is very simple and intuitive.
Multiple threads can be using SQLite at the same time as long
as they are not using the same database connection at the same
time.  This is consistent with most other utility functions.
The qsort() function is threadsafe - you can have two different
threads in qsort() sorting different arrays at the same time.
But it does not work to sort the same array in two threads at
the same time.  In the same way, SQLite allows two different
threads to be working with different database connections at
the same time, but not the same database connection.

What about that is so difficult to understand?

On some older versions of Linux, SQLite is unable to pass
database connections from one thread to another.  But this
is a problem with the threading libraries used in those older
linux versions and is outside the control of SQLite.  I do not
think this issue comes into play here.

> 
> I dont see what is gained by making it not work with threads, so i
> assume that it is becuase of some technical reason rather than a
> design decision.

SQLite does work with threads.  Your comment is based on a false
premise.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[Michael Sizaki]

==> SUMMARY <==
==> There is indeed no difference between 3.3.7 and 3.3.8
==> However, sqlite hits the disk a lot in a temp file??!!
==> PRAGMA temp_store = MEMORY; helps
==> Why is sqlite hitting the disk with a 70MB database?

Further tests shows that there is no difference between
3.3.7 and 3.3.8.

The problem was, that I was using sqlite.exe interactively.
The in the 3.3.8 shell I have been running some tests that
created and deleted some temp tables before I did
performance tests.

It turns out that the query hits the disk when the table
exceeds a certain size. There's a certain size of my tables
when performance goes down dramatically. It takes 14 sec for
100,000 rows and 300 for 200,000. The CPU goes down to
almost 0 and the disk gets very active.

My database:
  pragma cache_size = 2;
  pragma page_size = 4096;
  Database file (after vacuum) 70MB with about 450,000 records

> time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 10 ORDER BY 
size, name;"|wc
  9  103445 11352384

real0m14.281s
user0m7.260s
sys 0m3.775s

Peak memory 35 MB

>time ./sqlite3.3.8.exe db.sqlite "SELECT * FROM files where id < 20 ORDER BY 
size, name;"|wc
 19  204598 24676875

real4m49.947s
user0m18.386s
sys 0m13.318s

Peak memory 35 MB

I captured the performance using sysinternals procexp:
  
http://www.microsoft.com/technet/sysinternals/SystemInformation/ProcessExplorer.mspx
See the attached screen shot. It's interesting that half of the memory is
allocated in the last seconds...

When I prepend the query with
  PRAGMA temp_store = MEMORY;
The queries are fast, but the process needs a lot of memory
(about 5 times the size of the .dump size of the result table)

> time ./sqlite3.3.8.exe db.sqlite "PRAGMA temp_store = MEMORY;SELECT * FROM where id 
< 10 ORDER BY size, name;"|wc
 9  103445 11352384

real0m8.262s
user0m6.659s
sys 0m0.210s

Peak memory 58 MB

> time ./sqlite3.3.8.exe db.sqlite "PRAGMA temp_store = MEMORY;SELECT * FROM where id 
< 20 ORDER BY size, name;"|wc
 19  204598 24676875

real0m13.329s
user0m12.187s
sys 0m0.310s

Peak memory 75 MB

What surprises me, is that the temp file is not kept in
cache. I have 2GB of memory and much bigger files can be
kept in cache. Why is sqlite "hitting the disk"?  What is
going on here? The maximum file cache needed would be 70 MB
for the database + 75 MB for the temp table. 150MB is
nothing on a 2GB system.

I thought maybe
  PRAGMA synchronous = OFF;
would help. But it does not.


Michael

[EMAIL PROTECTED] wrote:
> Michael Sizaki <[EMAIL PROTECTED]> wrote:
>> What has changed in 3.3.8 to make it so slow?
>>
>
> There were no changes to the query optimizer between 3.3.7
> and 3.3.8.  None.  Nada.  Zilch.

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

Re: [sqlite] LEMON

[Lloyd]

Ethereal make use of Lemon

On Fri, 2006-12-29 at 11:08 -0400, Cesar Rodas wrote:
> Where can i find a tutorial with examples of how to use LEMON parser...
> because i need to build interpreted language
> 
> Thanks to all


__
Scanned and protected by Email scanner

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

[sqlite] LEMON

[Cesar Rodas]

Where can i find a tutorial with examples of how to use LEMON parser...
because i need to build interpreted language

Thanks to all
--
Cesar Rodas
http://www.phpclasses.org/grank (A PHP implementation of PageRank)

[sqlite] coldfusion and sqlite

[Kurt Cleeren]

Does anyone know how I can connect to an sqlite db with coldfusion?

 

I was trying to do that with ODBC but it doesn't work like I want it to
work.

 

Does anyone have another id?

 

Greetz

Kurt

 

Re: [sqlite] sqlite performance, locking & threading

[Joe Wilson]

--- Emerson Clarke <[EMAIL PROTECTED]> wrote:
> Developing multithreaded applications is difficult, i wouldnt dispute
> that.  But i do dispute the wisdom of actively making a library
> incompatible with threads. 

"Actively"?  That's a bit much.

There are constraints on the ability to pass SQLite connections
between threads. To be safe, just use the connection on the
same thread it was created and you'll be fine.

> Sqlite is the first example of such
> behaviour i have seen, it deliberately fails even if the user attempts
> to synchronise concurrent access.
>
> Surely it would be better to design the api so that it was either
> fully thread safe and blocked on every call to an sqlite_ function
> using its own mutexes, or to simply ensure that it was process safe
> using shared memory.  As i mentioned in a previous reply,
> synchronising sqlite would be no more or less difficult or error prone
> than synchonising a linked list.

You're free to serialize your calls to SQLite in your own code.
Take a look at the many wrappers for SQLite for ideas.

Shared memory is not the answer. It's a nightmare to manage and creates
more problems than it solves.


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

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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

In general worker threads is not an efficient solution to the problem
even if parallelism is available.  There is nothing to be gained by
having one thread handing off A to another set of worker threads
because you have to synchronise on whatever queue/list/pipe you use to
transfer the information between threads anyway.  So you may as well
just have multiple threads all performing their A,B, and C operations
in parallel and they will naturally interleave their operations based
on the synchronisation that you use.

Typically worker threads are useful when you want something to be done
asynchronously in the background.   I dont think its a suitable design
for this task though because it does not do anything that multiple
threads with mutexes cant do in the foreground and only introduces
overhead and delay in the queueing process and context switching.

On the second point, i cant see any reason why the VDBE design would
be a problem for what i want to do.  Taking out a table wide lock is
fine, so long as i can make changes to that table from multiple
threads using the same connection and lock.

The only danger occurs if those threads are not synchronised and two
or more attempt to modify the table at once, but of course, thats what
mutexes are for.

Emerson

On 12/29/06, Roger Binns <[EMAIL PROTECTED]> wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Emerson Clarke wrote:
| For architectural reasons the above steps must be performed in that
| order. This means that operations cant be separated or queued up in
| the way that you suggested.  Each step is dependent on the previous
| step.

I was looking for parallelism expecting that indexing one document has
no dependencies on other documents.  Lets say that you have 3 documents
A, B and C.  Is it impossible to index B until after A's indexing is
done?  If that is the case then your indexing process is imposing
serialization and there is nothing SQLite can do about that.  If the
indexing of A, B & C can be done in parallel, then you can have the
indexing workers send updates to a single thread/process.  That can
aggregate information and then dump it all in a single transaction quickly.

| Of course i know the simple answer, which is that the current api does
| not support this.  But im wondering why, and if there are any other
| ways to achieve the desired performance.

It is actually the internal design of SQLite that doesn't support this.
~  (Look at VDBE instructions for queries sometime). SQLite locks the
whole database, rather than tables or rows.  But the locks are held for
the shortest time period possible.  There are other database systems out
there that have finer granularity locking, but that is more complicated.
~ There are also potential patent issues and SQLite deliberately uses old
techniques to ensure no patent violation.  SQLite's techniques have to
work without centrally coordinating mechanism and within the confines of
lowest common denominator functionality available on a variety of
operating systems.

Quite frankly if you cannot change the way you work with your data set
then you may want to try a different database system such as Postgres.
Of course that internally solves the problems by having a single process
and doesn't have to worry about any other process/thread stepping on its
toes.

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

iD8DBQFFlLG+mOOfHg372QQRAn+sAKCrImPgr0Ex1xo3XOXgWg0E7t3wWQCgtAAQ
ZB5bs3fFntT4u3rABIGcOik=
=c2pB
-END PGP SIGNATURE-

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




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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Im not sure that the current situation of forced thread
incompatibility is better than leaving it up to users to manage the
threading.  Usually it is assumed that a library is thread unsafe
unless otherwise specified.

Developing multithreaded applications is difficult, i wouldnt dispute
that.  But i do dispute the wisdom of actively making a library
incompatible with threads.  Sqlite is the first example of such
behaviour i have seen, it deliberately fails even if the user attempts
to synchronise concurrent access.

Surely it would be better to design the api so that it was either
fully thread safe and blocked on every call to an sqlite_ function
using its own mutexes, or to simply ensure that it was process safe
using shared memory.  As i mentioned in a previous reply,
synchronising sqlite would be no more or less difficult or error prone
than synchonising a linked list.

I dont see what is gained by making it not work with threads, so i
assume that it is becuase of some technical reason rather than a
design decision.

Anyway, as another user has pointed out.  You can compile the library
in such a way as to allow the thread synchronisation routines to be
overridden so i think i have a solution to the problem, but perhaps
not an answer as to why things are the way they are.


On 12/29/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:

On 12/27/06, Emerson Clarke <[EMAIL PROTECTED]> wrote:
> The first question is why database locking has been enabled on a per thread
> basis instead of per process so that the normal methods of thread
> synchronisation (mutexes, ciritcal sections) could not be used for
> maintaining consistency within the application.  The programmer can easily
> ensure that no two statements are executed at the same time just as they
> would ensure that no more than one operation on a linked list was performed
> at the same time.

Having watched a lot of people attempt to write thread safe code I would
say this is an incorrect assumption. I think it only appears simple at a casual
glance.

--
The PixAddixImage Collector suite:
http://groups-beta.google.com/group/pixaddix

SqliteImporter and SqliteReplicator: Command line utilities for Sqlite
http://www.reddawn.net/~jsprenkl/Sqlite

Cthulhu Bucks!
http://www.cthulhubucks.com

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




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

Re: [sqlite] ORDER BY is more than 10 times slower with 3.3.8 compared to 3.3.7

[drh]

Michael Sizaki <[EMAIL PROTECTED]> wrote:
> 
> What has changed in 3.3.8 to make it so slow?
> 

There were no changes to the query optimizer between 3.3.7
and 3.3.8.  None.  Nada.  Zilch.  

--
D. Richard Hipp  <[EMAIL PROTECTED]>


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