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

[Michael Sizaki]

Enjoy this video:

  http://channel9.msdn.com/ShowPost.aspx?PostID=59936


Nice!

The key sentence is "a lot of the assumptions that where made
15 years ago, don't hold true anymore..."


Michael

-
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]

Michael Sizaki wrote:

 It's strange that windows is not a bit more
clever on caching. I have 2Gb and most of the time I have
1Gb free. Windows could use this for temp files.


It is clever on caching - it was designed to operate on a machine with 
4MB of RAM.  Oneof the design changes in Vista was recognising that they 
don't need to run in 4MB anymore!


Enjoy this video:

  http://channel9.msdn.com/ShowPost.aspx?PostID=59936

Roger

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

RE: [sqlite] Using sqlite.exe

[Michael Hooker]

Thanks Fred -  I need something working by 1 January and don't have time to
mess about!   I have code already written to feed another command line
program which just needs a couple of parameters changed.

I shall try using a Delphi wrapper later on, DiSQLite3 will probably be the
one I will choose, but so far I'm struggling to understand the examples
because the author has chosen to use a maze of separate units and an
unfamiliar set of external third party components to illustrate what it does
instead of just showing in a straightforward manner how to get data into a
standard Delphi record structure,  stringlist,  string array or whatever.
The documentation is very comprehensive but starts half-way through the
film, as far as I'm concerned.   Another programmer, far more experienced
than I,  who is giving me some help with DiSQLite has advised against the
AduSoft components because he finds the documentation difficult to
understand - so it looks like I wouldn't stand much chance.  Which doesn't
mean I won't give it a go.   In fact, I believe the original commercial
software that I'm working with uses the AduSoft components to manage its
database - but they've been a bit short-sighted in the reporting facilities
provided - for example to get data for any particular day, you have to
switch it off and on again every midnight to get data for a 24 hour
"session" and then export the data for the particular session.  Yet a simple
SQLite query will extract data by the date/time it's recorded, whichever
session it was recorded in.

Michael Hooker

-Original Message-
From: Fred Williams [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 16:01
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: RE: [sqlite] Using sqlite.exe


Don't know why you are using the "backdoor" approach (Calling the
SQLite.exe module) accessing an SQLite database from Delphi, but you
might want to look at what these people have to offer:

www.aducom.com

I have been using their components for a while now and have been quite
happy with the results.  It would certainly prove to be much more
straight forward and most likely produce much better performance.

Fred

> -Original Message-
> From: Michael Hooker [mailto:[EMAIL PROTECTED]
> Sent: Saturday, December 30, 2006 9:22 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Using sqlite.exe
>
>
> Thank you Kees - this works!
>
> I have been running sqlite3.exe simply by clicking on its icon in My
> Computer and I wrongly assumed that doing this would
> automatically make the
> current directory the one where sqlite3.exe is found.  When I
> come to use it
> from within my Delphi program I shall ensure that I "cd" to
> the appropriate
> directory first.
>
> Just a suggestion for the programmers, but it would be helpful if
> sqlite3.exe came back with a response like "Database  opened" or
> "Database  created in directory "  - it would
> give those of us
> who don't often use command line programs a bit of a clue
> what's going on.
> I have forgotten a lot since my DOS days!
>
> Thanks once again - I can get on with my work now!
>
> Michael Hooker
>
> -Original Message-
> From: Kees Nuyt [mailto:[EMAIL PROTECTED]
> Sent: 30 December 2006 12:38
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Using sqlite.exe
>
>
>
> On Fri, 29 Dec 2006 18:46:58 -0600,  Michael Hooker wrote:
>
> >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.
>
> Make that
>  C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb
> or you will end up creating the database in what happens to be
> the current directory,
>
> and then execute
>   .tables
> or
>   .schema
> to check what's in there.
> --
>   (  Kees Nuyt
>   )
> c[_]
>
> --
> --
> -
> 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.16.0/609 - Release
> Date: 29/12/2006
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.16.0/609 - Release
> Date: 29/12/2006
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>



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

RE: [sqlite] Using sqlite.exe

[Michael Hooker]

>>because it only would be helpful the first time
one uses the sqlite commandline shell.<<

No, this is not true.  Some of us have very short memories!  :)

Michael Hooker

-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 17:07
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Using sqlite.exe



Hi Michael,

On Sat, 30 Dec 2006 15:22:13 -, you wrote:

> Thank you Kees - this works!

I'm glad it does.

>I have been running sqlite3.exe simply by clicking on its icon in My
>Computer and I wrongly assumed that doing this would automatically make the
>current directory the one where sqlite3.exe is found.  When I come to use
it
>from within my Delphi program I shall ensure that I "cd" to the appropriate
>directory first.
>
>Just a suggestion for the programmers, but it would be helpful if
>sqlite3.exe came back with a response like "Database  opened" or
>"Database  created in directory "  - it would give those of us
>who don't often use command line programs a bit of a clue what's going on.
>I have forgotten a lot since my DOS days!

Though it is a nice suggestion I doubt anyone would care to
write the patch, because it only would be helpful the first time
one uses the sqlite commandline shell. Once you've set up your
development environment, you'll never have to think about it
again.

If anyone wants to make an effort, just make sure to only output
those messages in the same way as the version information:
suppress if redirection is active.

>Thanks once again - I can get on with my work now!

You're welcome, good luck, have fun.

>Michael Hooker
>
>-Original Message-
>From: Kees Nuyt [mailto:[EMAIL PROTECTED]
>Sent: 30 December 2006 12:38
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Using sqlite.exe
>
>Make that
> C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb
>or you will end up creating the database in what happens to be
>the current directory,
>
>and then execute
>   .tables
>or
>   .schema
>to check what's in there.
>--
>  (  Kees Nuyt
--
  (  Kees Nuyt
  )
c[_]


-
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.16.0/609 - Release Date: 29/12/2006

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


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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> 
> "With that and subsequent versions, it is safe to move a connection
> handle across threads as long as the connection is not holding any
> fcntl() locks. You can safely assume that no locks are being held if
> no transaction is pending and all statements have been finalized."
> 
> The above statement regarding transactions was one of the things which
> led me to believe that it was not possible to have the single
> transaction, single connection and multiple thread arrangement.
> 

The statement is true in general.  It works even on the older,
broken versions of Linux.  If you are running on a newer version
of Linux or on OS-X or windows, then you can move a database
connection from one thread to another anytime you want.  But
if you write code that does that, it will not be portable to
older versions of linux with the fcntl() bug.

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


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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Richard,

Im getting some mixed signals here, on the one hand i am being told
that it should be ok to use a single transaction and connection across
multiple threads (assuming that it is correctly synchronised). But on
the other hand i am reading statements like that as part of a list of
things which may cause a sqlite misuse error.

"Trying to use the same SQLite database connection from two or more
threads at the same time."

Anyway, the sqlite_step() problem certainly explains one of the
crashes that i saw so that will save me some debugging.

Thanks to those who have suggested alternative designs and strategies.
I am currently adapting the single thread does all the database work
solution.

The documentation sources i have been looking at regarding threading
are (in addition to various discussions in this mailing list) here;

http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
http://www.sqlite.org/lang_transaction.html
http://www.sqlite.org/lockingv3.html
http://www.sqlite.org/faq.html#q8

"With that and subsequent versions, it is safe to move a connection
handle across threads as long as the connection is not holding any
fcntl() locks. You can safely assume that no locks are being held if
no transaction is pending and all statements have been finalized."

The above statement regarding transactions was one of the things which
led me to believe that it was not possible to have the single
transaction, single connection and multiple thread arrangement.


Emerson

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

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > SQLite allows you to have multiple queries running at the
> > same time in multiple threads, as long as no two threads are
> > running sqlite3_step() at the same moment.
>
> Do you mean "as long as no two threads are running sqlite3_step()
> _for the same sqlite3* connection_ at the same moment"?
>

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


-
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

[Michael Sizaki]

Thanks Roger!

I switched "Memory Usage" to "System Cache"
  http://www.techspot.com/tweaks/memory-winxp/
and my performance problems are gone.

I have to see how this setting influences my overall
performance. It's strange that windows is not a bit more
clever on caching. I have 2Gb and most of the time I have
1Gb free. Windows could use this for temp files.

Michael


-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]
- 






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

Re: [sqlite] sqlite performance, locking & threading

[drh]

Joe Wilson <[EMAIL PROTECTED]> wrote:
> --- [EMAIL PROTECTED] wrote:
> > SQLite allows you to have multiple queries running at the
> > same time in multiple threads, as long as no two threads are
> > running sqlite3_step() at the same moment.
> 
> Do you mean "as long as no two threads are running sqlite3_step() 
> _for the same sqlite3* connection_ at the same moment"?
> 

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


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

Re: [sqlite] sqlite performance, locking & threading

[Doug Currie]

On Saturday, December 30, 2006 [EMAIL PROTECTED] wrote: 

> Isn't there a list of possible causes for SQLITE_MISUSE somewhere.
> I seem to remember writing such a list one.  Does anybody know where 
> I put it?

This is a forwarded message

From:[EMAIL PROTECTED] <[EMAIL PROTECTED]>
To:  sqlite-users@sqlite.org
Cc:  
Date:Tuesday, October 31, 2006
Subject: [sqlite] What is the proper use of threads?

-8<- Original message text - 

=?ISO-8859-2?Q?Zimmer_Andr=E1s?= <[EMAIL PROTECTED]> wrote:
> 
> However, at random times I keep getting "library routine called out of 
> sequence" errors (in the feeder). 

This is the SQLITE_MISUSE error.  You can grep for SQLITE_MISUSE
in the source code to find all the cases where this error can be
generated.  SQLITE_MISUSE gets generated for things like this:

  *  Trying to use the same SQLite database connection from two
 or more threads at the same time.
  *  Trying to use an SQLite database connection from within a
 Linux signal handler.
  *  Calling sqlite3_close() on a database connection that has
 already been closed.
  *  Calling sqlite3_step() on a prepared statement that has
 already been finalized.

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


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


-8<- End of Original message text --

-- 
Doug Currie
Londonderry, NH, USA


-
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

[Joe Wilson]

Thanks, Roger. I had no idea such a setting existed.

Why Windows forces you to make a choice on your usage pattern up-front
seems odd to me. You'd think they'd use heuristics and/or statistics to
tune this dynamically on the fly.

--- Roger Binns <[EMAIL PROTECTED]> wrote:
> 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


__
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

[Joe Wilson]

--- [EMAIL PROTECTED] wrote:
> SQLite allows you to have multiple queries running at the
> same time in multiple threads, as long as no two threads are
> running sqlite3_step() at the same moment.

Do you mean "as long as no two threads are running sqlite3_step() 
_for the same sqlite3* connection_ at the same moment"?


__
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

[John Stanton]

Good advice.  If you want to experiment with a certain architecture, 
write the programs yourself rather than trying to get others to do it 
for you.  Sqlite is a well thought through solution for an embedded 
database with a wide range of applications, but if you want something 
more specialised, write it yourself.


Indexing and data storage methods are well documented so it is not a 
daunting task to roll your own and get all the performance you want.


As Roger points out, Sqlite is open source and is available to you as a 
model.  You will find that it is clearly and simply written and the code 
is very easy to follow.


Roger Binns wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Emerson Clarke wrote:
| 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.

I don't want to debate your index architecture either :-).  Quite simply
several times a month people post to this ideally wanting SQLite changed
to match how they want to structure things.  People on the list explore
with the poster how the items of data are related, and can suggest an
alternative way of doing things.  Usually the poster finds that simpler
than what they had first thought of and goes away happy.

Is this your question: I want SQLite to work differently than it
currently does so that it matches how I want to do things?

If that is the case, then the answer is you can go ahead and rewrite as
much of SQLite as you want to to do that.  The code is public domain so
there are no legal or technical hindrances standing in your way.  This
thread may as well end at that.

On the other hand, if you do want to work within the constraints of
SQLite then there are quite a few things that can be suggested.  But
that is only possible if more is known about the relationships of the data.

| 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.

I was trying to establish what has to be serialized.  In particular the
question was about if A and B had any relationships to each other.  If
they do, then that means they would have to be processed serially and I
don't see the relevance of threading etc.

If they can be processed at the same time, then that means some sort of
partitioning can happen.  In theory breaking the datasets into 10
partitions can give 10 times the performance, but in practise there will
need to be some coordination in order to make it look like there is one
database not multiple pieces.

| 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.

Have you considered just using plain DB/dbm/gdbm and then importing the
data on demand into SQLite?

Also a lot of the synchronisation is because SQLite makes damn sure it
doesn't lose your data.  If your documents are permanent (ie you can
access them later if need be), then you can loosen the constraints on
SQLite.  For example you could run with pragma synchronous=off and then
do a checkpoint every 100,000 documents where you close the database,
copy it to a permanent file, sync, and start again.  You could also use
a ram disk and copy to permanent storage as your checkpoint.

| 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.

You can (ab)use custom functions and virtual tables to help behind the
scenes.  For example they can be used to make data sets that are
partitioned appear to be a single whole.  Another example is if you have
your database in two pieces - one that is read only with "old" data and
new one with updates.  That can again appear to the rest of the code as
one database.  Finally you can also make the functions and virtual
tables have side effects even on what appear to be read only queries.

| The schema is extemely simple, and there is barely any logic too the
| indexing process at all.

Maybe not even indexing the documents at all would work?  If you used a
virtual table, you can make it grovel through the documents on demand.
You can even build indices (in the SQL sense) which are in your own
format and performance characteristics and use those for the virtual table.

| Unfortunately i cannot do this with sqlite at the moment...

Correct.  SQLite errs on the side of being a library with no controller,
working with multiple processes and only having the lowest common
denominator operating system locking functionality available.  There are
techniques that can be used to improve concurrency.  DRH has a polic

Re: [sqlite] Using sqlite.exe

[Kees Nuyt]

Hi Michael,

On Sat, 30 Dec 2006 15:22:13 -, you wrote:

> Thank you Kees - this works!

I'm glad it does.

>I have been running sqlite3.exe simply by clicking on its icon in My
>Computer and I wrongly assumed that doing this would automatically make the
>current directory the one where sqlite3.exe is found.  When I come to use it
>from within my Delphi program I shall ensure that I "cd" to the appropriate
>directory first.
>
>Just a suggestion for the programmers, but it would be helpful if
>sqlite3.exe came back with a response like "Database  opened" or
>"Database  created in directory "  - it would give those of us
>who don't often use command line programs a bit of a clue what's going on.
>I have forgotten a lot since my DOS days!

Though it is a nice suggestion I doubt anyone would care to
write the patch, because it only would be helpful the first time
one uses the sqlite commandline shell. Once you've set up your
development environment, you'll never have to think about it
again.

If anyone wants to make an effort, just make sure to only output
those messages in the same way as the version information:
suppress if redirection is active.

>Thanks once again - I can get on with my work now!

You're welcome, good luck, have fun.

>Michael Hooker
>
>-Original Message-
>From: Kees Nuyt [mailto:[EMAIL PROTECTED]
>Sent: 30 December 2006 12:38
>To: sqlite-users@sqlite.org
>Subject: Re: [sqlite] Using sqlite.exe
>
>Make that
> C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb
>or you will end up creating the database in what happens to be
>the current directory,
>
>and then execute
>   .tables
>or
>   .schema
>to check what's in there.
>--
>  (  Kees Nuyt
-- 
  (  Kees Nuyt
  )
c[_]

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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Michael Ruck" <[EMAIL PROTECTED]> wrote:
> Richard,
> 
> I believe his problem is this:
> 
> "Each query is allowed to complete before the other one starts, but each
> thread may have multiple statements or result sets open."
> 
> The open resultsets/multiple started statements are causing him =
> headaches.
> 

SQLite allows you to have multiple queries running at the
same time in multiple threads, as long as no two threads are
running sqlite3_step() at the same moment.

Isn't there a list of possible causes for SQLITE_MISUSE somewhere.
I seem to remember writing such a list one.  Does anybody know where 
I put it?

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


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

AW: [sqlite] sqlite performance, locking & threading

[Michael Ruck]

Richard,

I believe his problem is this:

"Each query is allowed to complete before the other one starts, but each
thread may have multiple statements or result sets open."

The open resultsets/multiple started statements are causing him headaches.

Mike

-Ursprüngliche Nachricht-
Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 30. Dezember 2006 17:32
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Richard,
> 
> Are you sure we are not just getting into semantic knots here ?
> 
> Do we have the same definition of "at the same time".  I mean 
> concurrently, so that both threads use the same sqlite3 * structure, 
> within mutexes. Each query is allowed to complete before the other one 
> starts, but each thread may have multiple statements or result sets 
> open.
> 
> When i try to do this, i get api called out of sequence errors...
> 

There are around 50 test cases for this kind of behavior in the regression
test files thread1.test and thread2.test.  They all seem to work for me.

Perhaps your mutexes are not working as you expect and you are in fact
trying to use the same database connection simultaneously in two or more
threads.  SQLite attempts to detect this situation and when it sees it it
return SQLITE_MISUSE which generates the "API called out of sequence" error.


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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-



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

AW: [sqlite] sqlite performance, locking & threading

[Michael Ruck]

I want to contribute my 0.02€ to this discussion. Basically I believe your 
(Emerson) design is flawed. I've been working for years with multithreaded and 
even multi-core systems. From my experience a design using threads for specific 
tasks is *always* performing better, than having multiple threads execute the 
same things in parallel. This experience is based on the facts about lock 
contention, thread starvation, caching and more. These always apply unless you 
have a realtime operating system, which guarantees you that every thread is 
scheduled and the scheduling order. On most systems this ain't the case AFAIK. 
At least not on Windows/Linux, if there's heavy threading and lock contention 
going on.

I really would suggest you to test out a design, where threads are assigned 
single tasks, such as retrieving data from db, writing data to db and queues, 
which provide these threads with work items. If you're really smart, you'll 
create queues using atomic operations so that no thread will take locks for 
these operations. 

To summarize my points:

- Create one Sqlite writer thread, one sqlite reader thread.
- Create queues for all worker threads, which provide them with work items.
- Do *not* take kernel locks on data structures or libraries, this *will* 
really hurt your performance.
- Use transactions coarse grained on the Sqlite writer thread. Either decide 
inside the thread, when to commit and start a new one or design a specific 
workitem to trigger this from the outside. This depends on having a consistent 
state in your data structures/the database.

But: Your limiting factor will always be the hard disk. Analyze your tasks to 
determine, what the bounding factor is: Is it the CPU? Is it the disk? Is it 
the network? Only then start changing something. Multithreading only makes 
sense if you can parallelize heavily and are not bound by disk/network, but by 
CPU and have multiple (unused) cores available.

And: Having multiple statements executing concurrently is only possible with 
multiple Sqlite connections. A connection can only keep one resultset open or a 
statement executing (unless that has changed recently.) 

Mike

-Ursprüngliche Nachricht-
Von: Emerson Clarke [mailto:[EMAIL PROTECTED] 
Gesendet: Samstag, 30. Dezember 2006 17:08
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] sqlite performance, locking & threading

Richard,

Are you sure we are not just getting into semantic knots here ?

Do we have the same definition of "at the same time".  I mean concurrently, so 
that both threads use the same sqlite3 * structure, within mutexes. Each query 
is allowed to complete before the other one starts, but each thread may have 
multiple statements or result sets open.

When i try to do this, i get api called out of sequence errors...

On 12/30/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> "Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> > But why then can i not have a single transaction wrapping a single 
> > connection which is used within multiple threads, obvioulsy not at 
> > the same time.
>
> You can.  What makes you think you can't?
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
>
>
> --
> --- To unsubscribe, send email to 
> [EMAIL PROTECTED]
> --
> ---
>
>

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



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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Richard,
> 
> Are you sure we are not just getting into semantic knots here ?
> 
> Do we have the same definition of "at the same time".  I mean
> concurrently, so that both threads use the same sqlite3 * structure,
> within mutexes. Each query is allowed to complete before the other one
> starts, but each thread may have multiple statements or result sets
> open.
> 
> When i try to do this, i get api called out of sequence errors...
> 

There are around 50 test cases for this kind of behavior in the
regression test files thread1.test and thread2.test.  They all
seem to work for me.

Perhaps your mutexes are not working as you expect and you are
in fact trying to use the same database connection simultaneously
in two or more threads.  SQLite attempts to detect this situation
and when it sees it it return SQLITE_MISUSE which generates the
"API called out of sequence" error. 

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


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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> 
> I have code which creates a transaction on a connection in the parent
> thread, then creates several child threads which attempt to use the
> same connection and transaction in a synchronised mannor.  It does not
> work, and by all the documentation that i have read on sqlite, it
> should not work.
> 

This should work fine on SQLite version 3.3.1 and later on
newer Linux machines or on any windows or OS-X machine.  And I
think the documentation is clear on that point.

Where in the documentation does it suggest otherwise?

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


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

RE: [sqlite] sqlite performance, locking & threading

[Fred Williams]

Been following this a while...  You have access to the source, and
apparently are a "threading genius."  Please make the required minor
changes and post a link here so we can all benefit.

Fred

> -Original Message-
> From: Emerson Clarke [mailto:[EMAIL PROTECTED]
> Sent: Saturday, December 30, 2006 9:34 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] sqlite performance, locking & threading
>
>
> Roger,
>
> I think sqlite suffers somewhat from a bit of an identity crisis.
> Whilst it is both a library and a piece of code which you embed in a
> project it is often talked about as though it is some external
> component.
>
> Technically sqlite is not thread safe.  Just because the library has
> explicitly disallowed using the same sqlite3 * structure in multiple
> threads on some platforms (i understand this requirement has been
> relaxed on others) does not make it thread safe.  Even on the
> platforms where a single sqlite3 * structure can be used on multiple
> threads (provided it is not at the same time), it is not possible to
> have a transaction which works across these threads.  So even if the
> connection is thread safe, the transactions are not.
>
> By the usual definition, something which is thread safe can be safely
> used across multiple threads, usually with the aid of synchronisation
> but sometimes not.  For instance collections are often considdered
> thread safe only when they manage their own mutexes internally so that
> the user doesnt have to.  But either way, you can use them accross
> multiple threads.  You cannot do this with sqlite, so it is quite
> confusing to say that sqlite is thread safe...
>
> I think a better definition would be that sqlite can be safely used in
> a multithreaded program, but is not thread safe.
>
> I agree that multithreaded programming can be difficult, but its not
> magic and i think that a few simple rules can overcome most of the
> problems.  It certainly is not luck that multithreaded systems work,
> usually its the result of careful design and hard work.
>
> Emerson
>
> On 12/30/06, Roger Binns <[EMAIL PROTECTED]> wrote:
> > Emerson Clarke wrote:
> > > If i have a linked list, i can use it across threads if i want to,
> > > provided that i synchronise operations in such a way that the list
> > > does not get corrupted.
> >
> > And of course you also have to know about memory barriers
> and compiler
> > re-ordering.  That is highly dependent on the libraries
> and/or compiler
> > you are using, as well as underlying hardware
> implementation.  Most of
> > the time, developers just get lucky.
> >
> >   http://en.wikipedia.org/wiki/Memory_barrier
> >
> > > Likewise for most other data structures and libraries.
> >
> > Arguably that is by luck and not design!  Look at the
> effort that to go
> > in an add _r suffixed versions of several functions in the standard
> > libraries.  And many GUI libraries have long had a
> restriction that you
> > can only use them in one thread.
> >
> > > Sqlite does not follow these rules, as something created
> in one thread
> > > does not work in another thread regardless of
> synchronisation and it
> > > is out of my control.
> >
> > SQLite's design was not "luck".  The design expects you to
> create unique
> > sqlite3 objects in each thread.  Effort and thought was put
> into that!
> >
> > http://www.sqlite.org/cvstrac/wiki?p=MultiThreading
> >
> > It was loosened a bit in 3.3.x:
> >
> >   http://www.sqlite.org/faq.html#q8
> >
> > What isn't allowed is multiple statements executing at the
> same time in
> > multiple threads against the same sqlite3* db object.  In order to
> > support that, SQLite would have to have extensive code
> protecting the
> > various internal data structures as well as ensuring concurrency.
> >
> > > This is not a situation that i would expect anyone to purposefully
> > > design becuase it makes multithreaded programming difficult,
> >
> > The purposeful design is that you make sqlite3 objects per
> thread.  That
> > way there is absolutely no danger of corruption or other bad issues.
> >
> > Roger
> >
> >
> >
> --
> ---
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> --
> ---
> >
> >
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Richard,

Are you sure we are not just getting into semantic knots here ?

Do we have the same definition of "at the same time".  I mean
concurrently, so that both threads use the same sqlite3 * structure,
within mutexes. Each query is allowed to complete before the other one
starts, but each thread may have multiple statements or result sets
open.

When i try to do this, i get api called out of sequence errors...

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

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> But why then can i not have a single transaction wrapping a single
> connection which is used within multiple threads, obvioulsy not at the
> same time.

You can.  What makes you think you can't?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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




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

RE: [sqlite] Using sqlite.exe

[Fred Williams]

Don't know why you are using the "backdoor" approach (Calling the
SQLite.exe module) accessing an SQLite database from Delphi, but you
might want to look at what these people have to offer:

www.aducom.com

I have been using their components for a while now and have been quite
happy with the results.  It would certainly prove to be much more
straight forward and most likely produce much better performance.

Fred

> -Original Message-
> From: Michael Hooker [mailto:[EMAIL PROTECTED]
> Sent: Saturday, December 30, 2006 9:22 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Using sqlite.exe
>
>
> Thank you Kees - this works!
>
> I have been running sqlite3.exe simply by clicking on its icon in My
> Computer and I wrongly assumed that doing this would
> automatically make the
> current directory the one where sqlite3.exe is found.  When I
> come to use it
> from within my Delphi program I shall ensure that I "cd" to
> the appropriate
> directory first.
>
> Just a suggestion for the programmers, but it would be helpful if
> sqlite3.exe came back with a response like "Database  opened" or
> "Database  created in directory "  - it would
> give those of us
> who don't often use command line programs a bit of a clue
> what's going on.
> I have forgotten a lot since my DOS days!
>
> Thanks once again - I can get on with my work now!
>
> Michael Hooker
>
> -Original Message-
> From: Kees Nuyt [mailto:[EMAIL PROTECTED]
> Sent: 30 December 2006 12:38
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Using sqlite.exe
>
>
>
> On Fri, 29 Dec 2006 18:46:58 -0600,  Michael Hooker wrote:
>
> >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.
>
> Make that
>  C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb
> or you will end up creating the database in what happens to be
> the current directory,
>
> and then execute
>   .tables
> or
>   .schema
> to check what's in there.
> --
>   (  Kees Nuyt
>   )
> c[_]
>
> --
> --
> -
> 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.16.0/609 - Release
> Date: 29/12/2006
>
> --
> No virus found in this outgoing message.
> Checked by AVG Free Edition.
> Version: 7.1.409 / Virus Database: 268.16.0/609 - Release
> Date: 29/12/2006
>
>
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
>


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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Richard,

Well, that has been the whole point of this discussion.

I have code which creates a transaction on a connection in the parent
thread, then creates several child threads which attempt to use the
same connection and transaction in a synchronised mannor.  It does not
work, and by all the documentation that i have read on sqlite, it
should not work.

Ive also tried allocating a separate transaction to each child thread,
but of course then they all lock each other out.  So the only workable
alternative has been to either use much slower fine grained
transactions within each child thread, or to use an in process server
which performs all operations on one thread (at what i presume to be a
considerable hit to performance).



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

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Even on the
> platforms where a single sqlite3 * structure can be used on multiple
> threads (provided it is not at the same time), it is not possible to
> have a transaction which works across these threads.

I beg to differ.  What makes you think this does not work?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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




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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> But why then can i not have a single transaction wrapping a single
> connection which is used within multiple threads, obvioulsy not at the
> same time. 

You can.  What makes you think you can't?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Richard,

Ok, im pretty clear on the file locking being the cause of the
problems with the sqlite3 * structures, but thanks for confirming it.
I understand that on platforms that dont have this issue its not a
problem.

But why then can i not have a single transaction wrapping a single
connection which is used within multiple threads, obvioulsy not at the
same time.  Its rare that anything can be used at the same time by
multiple threads, hence the need for atomic operations (which *nix
doesnt have great standard support for).

To clarify, i have never been talking about using something
simultaneously, only within the confines of synchronisation.  So when
i talk about using something in multiple threads, i mean in a
syhcnronised way, but still it would be the same exact piece of
memory.


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

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Richard,
>
> My complaint, if you want to call it that.  Was simply that there are
> seemingly artificial constraints on what you can and cant do accross
> threads.
>
> If i have a linked list, i can use it across threads if i want to,
> provided that i synchronise operations in such a way that the list
> does not get corrupted.
>
> Likewise for most other data structures and libraries.  The default
> behaviour is that if i create an object on the stack or the heap, i
> can use it between multiple threads provided that i synchronise access
> to that object.
>
> Sqlite does not follow these rules, as something created in one thread
> does not work in another thread regardless of synchronisation and it
> is out of my control.
>

I think it was already explained to you that the reason for this
behavior is to work around bugs in file locking in older versions
of Linux.  If you are using a newer version of Linux or OS-X or
windows, those constraints do not apply to you. You can move
database connections across threads freely.

What you cannot do is to use the same database connection in two
or more threads at the same time.  This is the usual case with most
libraries, threadsafe or not - you can use separate instances of
an object in different threads at the same time, but you cannot use
the same object simultaneously in multiple threads.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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




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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Even on the
> platforms where a single sqlite3 * structure can be used on multiple
> threads (provided it is not at the same time), it is not possible to
> have a transaction which works across these threads.  

I beg to differ.  What makes you think this does not work?
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Roger,

I think sqlite suffers somewhat from a bit of an identity crisis.
Whilst it is both a library and a piece of code which you embed in a
project it is often talked about as though it is some external
component.

Technically sqlite is not thread safe.  Just because the library has
explicitly disallowed using the same sqlite3 * structure in multiple
threads on some platforms (i understand this requirement has been
relaxed on others) does not make it thread safe.  Even on the
platforms where a single sqlite3 * structure can be used on multiple
threads (provided it is not at the same time), it is not possible to
have a transaction which works across these threads.  So even if the
connection is thread safe, the transactions are not.

By the usual definition, something which is thread safe can be safely
used across multiple threads, usually with the aid of synchronisation
but sometimes not.  For instance collections are often considdered
thread safe only when they manage their own mutexes internally so that
the user doesnt have to.  But either way, you can use them accross
multiple threads.  You cannot do this with sqlite, so it is quite
confusing to say that sqlite is thread safe...

I think a better definition would be that sqlite can be safely used in
a multithreaded program, but is not thread safe.

I agree that multithreaded programming can be difficult, but its not
magic and i think that a few simple rules can overcome most of the
problems.  It certainly is not luck that multithreaded systems work,
usually its the result of careful design and hard work.

Emerson

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

Emerson Clarke wrote:
> If i have a linked list, i can use it across threads if i want to,
> provided that i synchronise operations in such a way that the list
> does not get corrupted.

And of course you also have to know about memory barriers and compiler
re-ordering.  That is highly dependent on the libraries and/or compiler
you are using, as well as underlying hardware implementation.  Most of
the time, developers just get lucky.

  http://en.wikipedia.org/wiki/Memory_barrier

> Likewise for most other data structures and libraries.

Arguably that is by luck and not design!  Look at the effort that to go
in an add _r suffixed versions of several functions in the standard
libraries.  And many GUI libraries have long had a restriction that you
can only use them in one thread.

> Sqlite does not follow these rules, as something created in one thread
> does not work in another thread regardless of synchronisation and it
> is out of my control.

SQLite's design was not "luck".  The design expects you to create unique
sqlite3 objects in each thread.  Effort and thought was put into that!

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

It was loosened a bit in 3.3.x:

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

What isn't allowed is multiple statements executing at the same time in
multiple threads against the same sqlite3* db object.  In order to
support that, SQLite would have to have extensive code protecting the
various internal data structures as well as ensuring concurrency.

> This is not a situation that i would expect anyone to purposefully
> design becuase it makes multithreaded programming difficult,

The purposeful design is that you make sqlite3 objects per thread.  That
way there is absolutely no danger of corruption or other bad issues.

Roger


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




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

[sqlite] Re: sqlite performance, locking & threading

[A. Pagaltzis]

* Emerson Clarke <[EMAIL PROTECTED]> [2006-12-30 15:15]:
> My complaint, if you want to call it that. Was simply that
> there are seemingly artificial constraints on what you can and
> cant do accross threads.

They are not artificial. The options for making the API of a
library with complex internal data structures thread-safe are:

• Expose all implementation details that will require
  synchronisation, including internals that will be changing
  frequently, so that you can punt the issue to client code.

• Add a lot of hard-to-debug code with a myriad checks to
  synchronise access to internal data structures behind the API
  facade, including measures like adding indirections to do
  queuing where necessary, and the like. If you don’t take great
  pains to get this design right, you are likely to create
  locking dependencies that will compromise concurrency and
  degrade performance. Strange bugs are likely to plague you
  whenever users try to deploy the code in new environments.

• Tell API clients they can’t do certain things.

Which of these seems sensible to you?

> If i have a linked list, i can use it across threads if i want
> to, provided that i synchronise operations in such a way that
> the list does not get corrupted.

That scenario is meaningless as an analogy. The right analogy
would be if the linked list were an internal datastructure that
is part of the implementation of a library but not of its public
API.

Regards,
-- 
Aristotle Pagaltzis // 

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

RE: [sqlite] Analyze before or after setting indices?

[RB Smissaert]

Thanks; I obviously missed something very essential there.

RBS


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Sent: 30 December 2006 14:56
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Analyze before or after setting indices?

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Doing some performance tweaking and wondering if there are any logical
> reasons why it would be better to analyze a table before or after setting
> the indices. I could figure it out by timing, but maybe somebody could
tell
> me already why one is better than the other.
> 
> RBS

The ANALYZE command examines indices, not the table itself.  So
you need to create your indices before running ANALYZE in order for
it to do any good.
--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] Using sqlite.exe

[Michael Hooker]

Thank you Kees - this works!

I have been running sqlite3.exe simply by clicking on its icon in My
Computer and I wrongly assumed that doing this would automatically make the
current directory the one where sqlite3.exe is found.  When I come to use it
from within my Delphi program I shall ensure that I "cd" to the appropriate
directory first.

Just a suggestion for the programmers, but it would be helpful if
sqlite3.exe came back with a response like "Database  opened" or
"Database  created in directory "  - it would give those of us
who don't often use command line programs a bit of a clue what's going on.
I have forgotten a lot since my DOS days!

Thanks once again - I can get on with my work now!

Michael Hooker

-Original Message-
From: Kees Nuyt [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 12:38
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Using sqlite.exe



On Fri, 29 Dec 2006 18:46:58 -0600,  Michael Hooker wrote:

>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.

Make that
 C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb
or you will end up creating the database in what happens to be
the current directory,

and then execute
.tables
or
.schema
to check what's in there.
--
  (  Kees Nuyt
  )
c[_]


-
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.16.0/609 - Release Date: 29/12/2006

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


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

Re: [sqlite] Analyze before or after setting indices?

[drh]

"RB Smissaert" <[EMAIL PROTECTED]> wrote:
> Doing some performance tweaking and wondering if there are any logical
> reasons why it would be better to analyze a table before or after setting
> the indices. I could figure it out by timing, but maybe somebody could tell
> me already why one is better than the other.
> 
> RBS

The ANALYZE command examines indices, not the table itself.  So
you need to create your indices before running ANALYZE in order for
it to do any good.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
> Richard,
> 
> My complaint, if you want to call it that.  Was simply that there are
> seemingly artificial constraints on what you can and cant do accross
> threads.
> 
> If i have a linked list, i can use it across threads if i want to,
> provided that i synchronise operations in such a way that the list
> does not get corrupted.
> 
> Likewise for most other data structures and libraries.  The default
> behaviour is that if i create an object on the stack or the heap, i
> can use it between multiple threads provided that i synchronise access
> to that object.
> 
> Sqlite does not follow these rules, as something created in one thread
> does not work in another thread regardless of synchronisation and it
> is out of my control.
> 

I think it was already explained to you that the reason for this
behavior is to work around bugs in file locking in older versions
of Linux.  If you are using a newer version of Linux or OS-X or 
windows, those constraints do not apply to you. You can move 
database connections across threads freely.

What you cannot do is to use the same database connection in two
or more threads at the same time.  This is the usual case with most
libraries, threadsafe or not - you can use separate instances of
an object in different threads at the same time, but you cannot use
the same object simultaneously in multiple threads.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

RE: [sqlite] Using sqlite.exe

[RB Smissaert]

OK, yes, I can see from your post now.
Can't think of anything else.

RBS


-Original Message-
From: Michael Hooker [mailto:[EMAIL PROTECTED] 
Sent: 30 December 2006 14:42
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Using sqlite.exe

Yes I know.  But the database was definitely made with SQLite3. That's why I
asked specifically about SQLite3 databases!

Michael Hooker

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 12:22
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: RE: [sqlite] Using sqlite.exe


I think if your database was made with SQLite 2 then you can't open it with
SQLite 3. In that case you will need SQLite 2, which I think is still
available.

RBS

-Original Message-
From: Michael Hooker [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 11:18
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Using sqlite.exe


Thank you.  Alas, that is exactly what I thought I should be doing (as the
help instructs), but I still get told that the table does not exist, when it
does exist.  The same happens when I try to open any other table within the
database (it's quite a complex one).

Are there some types of sqlite3 database which simply won't work with
sqlite3.exe, but do work with SQLiteSpy?  I have heard on the grapevine that
this database will not open in the freeware version of DisqLite 3 but does
in the commercial product, so it would seem that there is something odd
about it.

Michael Hooker





-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P
Kishor
Sent: 30 December 2006 00:47
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Using sqlite.exe


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
=
--
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.16.0/609 - Release Date: 29/12/2006



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
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.16.0/609 - Release Date: 29/12/2006

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



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




-
To unsubscrib

Re: [sqlite] sqlite performance, locking & threading

[Roger Binns]

Emerson Clarke wrote:

If i have a linked list, i can use it across threads if i want to,
provided that i synchronise operations in such a way that the list
does not get corrupted.


And of course you also have to know about memory barriers and compiler 
re-ordering.  That is highly dependent on the libraries and/or compiler 
you are using, as well as underlying hardware implementation.  Most of 
the time, developers just get lucky.


  http://en.wikipedia.org/wiki/Memory_barrier

Likewise for most other data structures and libraries.  


Arguably that is by luck and not design!  Look at the effort that to go 
in an add _r suffixed versions of several functions in the standard 
libraries.  And many GUI libraries have long had a restriction that you 
can only use them in one thread.



Sqlite does not follow these rules, as something created in one thread
does not work in another thread regardless of synchronisation and it
is out of my control.


SQLite's design was not "luck".  The design expects you to create unique 
sqlite3 objects in each thread.  Effort and thought was put into that!


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

It was loosened a bit in 3.3.x:

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

What isn't allowed is multiple statements executing at the same time in 
multiple threads against the same sqlite3* db object.  In order to 
support that, SQLite would have to have extensive code protecting the 
various internal data structures as well as ensuring concurrency.



This is not a situation that i would expect anyone to purposefully
design becuase it makes multithreaded programming difficult,


The purposeful design is that you make sqlite3 objects per thread.  That 
way there is absolutely no danger of corruption or other bad issues.


Roger


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

RE: [sqlite] Using sqlite.exe

[Michael Hooker]

Yes I know.  But the database was definitely made with SQLite3. That's why I
asked specifically about SQLite3 databases!

Michael Hooker

-Original Message-
From: RB Smissaert [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 12:22
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: RE: [sqlite] Using sqlite.exe


I think if your database was made with SQLite 2 then you can't open it with
SQLite 3. In that case you will need SQLite 2, which I think is still
available.

RBS

-Original Message-
From: Michael Hooker [mailto:[EMAIL PROTECTED]
Sent: 30 December 2006 11:18
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Using sqlite.exe


Thank you.  Alas, that is exactly what I thought I should be doing (as the
help instructs), but I still get told that the table does not exist, when it
does exist.  The same happens when I try to open any other table within the
database (it's quite a complex one).

Are there some types of sqlite3 database which simply won't work with
sqlite3.exe, but do work with SQLiteSpy?  I have heard on the grapevine that
this database will not open in the freeware version of DisqLite 3 but does
in the commercial product, so it would seem that there is something odd
about it.

Michael Hooker





-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P
Kishor
Sent: 30 December 2006 00:47
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Using sqlite.exe


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
=
--
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.16.0/609 - Release Date: 29/12/2006



-
To unsubscribe, send email to [EMAIL PROTECTED]

-





-
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.16.0/609 - Release Date: 29/12/2006

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


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

[sqlite] Analyze before or after setting indices?

[RB Smissaert]

Doing some performance tweaking and wondering if there are any logical
reasons why it would be better to analyze a table before or after setting
the indices. I could figure it out by timing, but maybe somebody could tell
me already why one is better than the other.

RBS



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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Richard,

My complaint, if you want to call it that.  Was simply that there are
seemingly artificial constraints on what you can and cant do accross
threads.

If i have a linked list, i can use it across threads if i want to,
provided that i synchronise operations in such a way that the list
does not get corrupted.

Likewise for most other data structures and libraries.  The default
behaviour is that if i create an object on the stack or the heap, i
can use it between multiple threads provided that i synchronise access
to that object.

Sqlite does not follow these rules, as something created in one thread
does not work in another thread regardless of synchronisation and it
is out of my control.

This is not a situation that i would expect anyone to purposefully
design becuase it makes multithreaded programming difficult,
particularly when most things obey the first paradigm (allowing things
to be used across threads).

So i really dont understand why sqlite has this thread behaviour, and
i assume that there is a technical limitation rather than design
decision behind it, though no one has as yet confirmed this...

Emerson

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

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
>>
> It seemed to me that making a library which only functioned on a per
> thread basis was something that you would have to do deliberately and
> by design.

I'm still trying to understand what your complaint is.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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




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

Re: [sqlite] sqlite performance, locking & threading

[drh]

"Emerson Clarke" <[EMAIL PROTECTED]> wrote:
>> 
> It seemed to me that making a library which only functioned on a per
> thread basis was something that you would have to do deliberately and
> by design. 

I'm still trying to understand what your complaint is.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


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

Re: [sqlite] sqlite performance, locking & threading

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Emerson Clarke wrote:
| I am left to assume that all other locking mechanisms like ipc and
| files have already been tried and been found wanting.  I also assume
| that priority has been given to making sqlite operate across network
| boundaries rather than process to process (where the locking
| requirements would be quite different)

Your assumptions are not quite correct.  SQLite was always designed to
be a library used within an optionally threaded process.  Its sweet spot
is low end to mid range.  You wouldn't run the New York Stock Exchange
on it, but it will work well on an MP3 player with memory measured in
kilobytes.  It has no external requirements like a daemon for
coordination and control.  IPC is difficult to work with when processes
are unrelated.  It does use file locking - see the various os_*.c files
in the source.  SQLite knows absolutely nothing about networks.  In
theory the file locking will work on files on a networked file server,
but in practise the various network file protocols and their
implementations often have problems getting their native locking
functionality correct.  See http://www.sqlite.org/faq.html#q7

Some people have proposed a networked version of SQLite and some even
sell products.  None of these are a standard part of SQLite.

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

| Im not familiar with DB/dbm/gdbm, are any of those under a similar
| license to sqlite and or as easy to use and simple ? :)

http://en.wikipedia.org/wiki/Gdbm
http://en.wikipedia.org/wiki/Berkeley_DB

They effectively use fixed size records and have no SQL layer.  The
Berkeley DB variant should allow the concurrency you want.  Version 1 of
SQLite was actually just a SQL layer over the GNU variant, gdbm.
http://www.sqlite.org/cvstrac/wiki?p=SqliteHistory

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

iD8DBQFFlmGomOOfHg372QQRAkKTAKDTOAt+jz/bEG0BmwJ9ZrOajOB3ZQCgjKUi
qghWqZISU+h6i+S3nAr2R6E=
=8BoZ
-END PGP SIGNATURE-

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

Re: [sqlite] Using sqlite.exe

[Kees Nuyt]

On Fri, 29 Dec 2006 18:46:58 -0600,  Michael Hooker wrote:

>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.

Make that
 C:\sqlite3\sqlite3.exe C:\sqlite3\BaseStation.sqb
or you will end up creating the database in what happens to be
the current directory,

and then execute
.tables
or
.schema
to check what's in there.
-- 
  (  Kees Nuyt
  )
c[_]

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

RE: [sqlite] Using sqlite.exe

[RB Smissaert]

I think if your database was made with SQLite 2 then you can't open it with
SQLite 3. In that case you will need SQLite 2, which I think is still
available.

RBS

-Original Message-
From: Michael Hooker [mailto:[EMAIL PROTECTED] 
Sent: 30 December 2006 11:18
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Using sqlite.exe


Thank you.  Alas, that is exactly what I thought I should be doing (as the
help instructs), but I still get told that the table does not exist, when it
does exist.  The same happens when I try to open any other table within the
database (it's quite a complex one).

Are there some types of sqlite3 database which simply won't work with
sqlite3.exe, but do work with SQLiteSpy?  I have heard on the grapevine that
this database will not open in the freeware version of DisqLite 3 but does
in the commercial product, so it would seem that there is something odd
about it.

Michael Hooker





-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P
Kishor
Sent: 30 December 2006 00:47
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Using sqlite.exe


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
=
--
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.16.0/609 - Release Date: 29/12/2006



-
To unsubscribe, send email to [EMAIL PROTECTED]

-




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

RE: [sqlite] Using sqlite.exe

[Michael Hooker]

Thank you.  Alas, that is exactly what I thought I should be doing (as the
help instructs), but I still get told that the table does not exist, when it
does exist.  The same happens when I try to open any other table within the
database (it's quite a complex one).

Are there some types of sqlite3 database which simply won't work with
sqlite3.exe, but do work with SQLiteSpy?  I have heard on the grapevine that
this database will not open in the freeware version of DisqLite 3 but does
in the commercial product, so it would seem that there is something odd
about it.

Michael Hooker





-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P
Kishor
Sent: 30 December 2006 00:47
To: sqlite-users@sqlite.org; [EMAIL PROTECTED]
Subject: Re: [sqlite] Using sqlite.exe


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
=
--
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.16.0/609 - Release Date: 29/12/2006


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

Re: [sqlite] sqlite performance, locking & threading

[Emerson Clarke]

Roger,

My original question was in fact not a statement.  I did not want
sqlite to work differently.  Rather the opposite, sqlite already works
differently to the way i, and probably a lot of users assume that it
would.  So all i wanted to know was why that is the case.

It seemed to me that making a library which only functioned on a per
thread basis was something that you would have to do deliberately and
by design.  But i am still vague as to what the reasons were behind
this design, aside from some mention of file locks being based on
threads not processes.

I am left to assume that all other locking mechanisms like ipc and
files have already been tried and been found wanting.  I also assume
that priority has been given to making sqlite operate across network
boundaries rather than process to process (where the locking
requirements would be quite different)

But thank you for your many suggestions and explanations of the
virtual tables functionality.  It seems quite powerful, and if i didnt
want to try to keep this as database agnostic as possible i would
probably look into that.  I especcially liked the notion that you
could keep the index as files but have it appear as a table since that
is in part what i am doing anyway in a slightly more abstract way.  I
am already using options like "synchronous=off".

I think as other users have suggested the two best options for me are
to write custom locking handlers for os.c (although i am still unsure
as to wether this will fix the problem, essentially i want to turn all
file locking off completely since the database will never be accessed
by more than one process) or to simply implement an in process server
which handles all of the sqlite functionality using message passing to
a single thread so that there is only ever one lock and one cache.

The second option is probably the one i will try first, since its more
transparent and for the cost of a single thread lets me emulate the
exact same behaviour that other databases have with regards to
transactions and threads.

Im not familiar with DB/dbm/gdbm, are any of those under a similar
license to sqlite and or as easy to use and simple ? :)

Emerson






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

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Emerson Clarke wrote:
| 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.

I don't want to debate your index architecture either :-).  Quite simply
several times a month people post to this ideally wanting SQLite changed
to match how they want to structure things.  People on the list explore
with the poster how the items of data are related, and can suggest an
alternative way of doing things.  Usually the poster finds that simpler
than what they had first thought of and goes away happy.

Is this your question: I want SQLite to work differently than it
currently does so that it matches how I want to do things?

If that is the case, then the answer is you can go ahead and rewrite as
much of SQLite as you want to to do that.  The code is public domain so
there are no legal or technical hindrances standing in your way.  This
thread may as well end at that.

On the other hand, if you do want to work within the constraints of
SQLite then there are quite a few things that can be suggested.  But
that is only possible if more is known about the relationships of the data.

| 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.

I was trying to establish what has to be serialized.  In particular the
question was about if A and B had any relationships to each other.  If
they do, then that means they would have to be processed serially and I
don't see the relevance of threading etc.

If they can be processed at the same time, then that means some sort of
partitioning can happen.  In theory breaking the datasets into 10
partitions can give 10 times the performance, but in practise there will
need to be some coordination in order to make it look like there is one
database not multiple pieces.

| 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.

Have you considered just using plain DB/dbm/gdbm and then importing the
data on demand into SQLite?

Also a lot of the synchronisation is because SQLite makes damn sure it
doesn't lose your data.  If your documents are permanent (ie you can
access them later if need be), then you can loosen the constraints on
SQLite.  For example you could run with pragma synchronous=off and then
do a checkpoint e

Re: [sqlite] sqlite performance, locking & threading

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Emerson Clarke wrote:
| 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.

I don't want to debate your index architecture either :-).  Quite simply
several times a month people post to this ideally wanting SQLite changed
to match how they want to structure things.  People on the list explore
with the poster how the items of data are related, and can suggest an
alternative way of doing things.  Usually the poster finds that simpler
than what they had first thought of and goes away happy.

Is this your question: I want SQLite to work differently than it
currently does so that it matches how I want to do things?

If that is the case, then the answer is you can go ahead and rewrite as
much of SQLite as you want to to do that.  The code is public domain so
there are no legal or technical hindrances standing in your way.  This
thread may as well end at that.

On the other hand, if you do want to work within the constraints of
SQLite then there are quite a few things that can be suggested.  But
that is only possible if more is known about the relationships of the data.

| 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.

I was trying to establish what has to be serialized.  In particular the
question was about if A and B had any relationships to each other.  If
they do, then that means they would have to be processed serially and I
don't see the relevance of threading etc.

If they can be processed at the same time, then that means some sort of
partitioning can happen.  In theory breaking the datasets into 10
partitions can give 10 times the performance, but in practise there will
need to be some coordination in order to make it look like there is one
database not multiple pieces.

| 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.

Have you considered just using plain DB/dbm/gdbm and then importing the
data on demand into SQLite?

Also a lot of the synchronisation is because SQLite makes damn sure it
doesn't lose your data.  If your documents are permanent (ie you can
access them later if need be), then you can loosen the constraints on
SQLite.  For example you could run with pragma synchronous=off and then
do a checkpoint every 100,000 documents where you close the database,
copy it to a permanent file, sync, and start again.  You could also use
a ram disk and copy to permanent storage as your checkpoint.

| 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.

You can (ab)use custom functions and virtual tables to help behind the
scenes.  For example they can be used to make data sets that are
partitioned appear to be a single whole.  Another example is if you have
your database in two pieces - one that is read only with "old" data and
new one with updates.  That can again appear to the rest of the code as
one database.  Finally you can also make the functions and virtual
tables have side effects even on what appear to be read only queries.

| The schema is extemely simple, and there is barely any logic too the
| indexing process at all.

Maybe not even indexing the documents at all would work?  If you used a
virtual table, you can make it grovel through the documents on demand.
You can even build indices (in the SQL sense) which are in your own
format and performance characteristics and use those for the virtual table.

| Unfortunately i cannot do this with sqlite at the moment...

Correct.  SQLite errs on the side of being a library with no controller,
working with multiple processes and only having the lowest common
denominator operating system locking functionality available.  There are
techniques that can be used to improve concurrency.  DRH has a policy of
only using those that are at least 17 years old, otherwise there are
likely to be patent implications.  See this page for example:

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

In summary, you can do one or more of the following:

- - Use some other database
- - Rewrite SQLite bits yourself
- - Use some sort of partitioning mechanism
- - ... which can be hidden using custom functions and virtual tables
- - Use a different storage mechanism (eg db/gdbm) with SQLite giving you
a front end (virtual tables)
- - Relax synchronisation and use a checkpointing mechanism

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

iD8