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

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

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

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

RE: [sqlite] Using sqlite.exe

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

RE: [sqlite] Using sqlite.exe

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

Re: [sqlite] sqlite performance, locking & threading

"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

Re: [sqlite] sqlite performance, locking & threading

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

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

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

Re: [sqlite] sqlite performance, locking & threading

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

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

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

Re: [sqlite] sqlite performance, locking & threading

--- [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*

Re: [sqlite] sqlite performance, locking & threading

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,

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

Re: [sqlite] sqlite performance, locking & threading

"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 =

AW: [sqlite] sqlite performance, locking & threading

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:

AW: [sqlite] sqlite performance, locking & threading

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

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

Re: [sqlite] sqlite performance, locking & threading

"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

RE: [sqlite] sqlite performance, locking & threading

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,

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

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

Re: [sqlite] sqlite performance, locking & threading

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

Re: [sqlite] sqlite performance, locking & threading

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

Re: [sqlite] sqlite performance, locking & threading

"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

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

[sqlite] Re: sqlite performance, locking & threading

* 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

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

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

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

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

Re: [sqlite] sqlite performance, locking & threading

"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

RE: [sqlite] Using sqlite.exe

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

Re: [sqlite] sqlite performance, locking & threading

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

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] Analyze before or after setting indices?

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

Re: [sqlite] sqlite performance, locking & threading

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

Re: [sqlite] sqlite performance, locking & threading

"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

Re: [sqlite] sqlite performance, locking & threading

-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

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

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

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

Re: [sqlite] sqlite performance, locking & threading

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

Re: [sqlite] sqlite performance, locking & threading

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