Re: [sqlite] Performance question related to multiple processes using sqlite
On Wed, Sep 4, 2013 at 3:51 PM, Varadan, Yamini (SCR US) (EXT) < yamini.varadan@siemens.com> wrote: > > But would any one know if there is any kind of synchronization that is > done between different processes that connect to different sqlite databases > that might slow down one process when the other process is performing DB > operation on its sqlite DB? > No. Two processes connected to different databases operating completely independently of one another as far as SQLite is concerned. Even if two processes are talking to the same SQLite database, if one process is not actively using SQLite and does not have a transaction open, then there is no interaction with the other process. And even if there is "interaction", that interaction is limited to file locks and/or use of a small amount of shared memory in WAL mode. It is never the case that one process will block or signal another process. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] performance question: SELECT max(rowid) - 1
Trey Mack wrote: > I have a fairly large table (10million rows) with a simple INTEGER > PRIMARY KEY AUTOINCREMENT field. > > Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is > 'SELECT min(rowid) FROM MyTable'. > > However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow > (apparently accessing every row). Further, 'SELECT max(rowid) - 1 > FROM MyTable' is slow - in fact using any constant in this expression > (including 0) results in a slow query. > > Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. > Check out http://www.sqlite.org/php2004/slides-all.html Page 61 SELECT max(rowid) FROM MyTable and SELECT min(rowid) FROM MyTable are optimized to run without a full table scan. However SELECT max(rowid) - min(rowid) FROM MyTable is not, and will perform a full table scan. To achieve the same functionality with the optimizations, try: SELECT (SELECT max(rowid) FROM MyTable) - (SELECT min(rowid) FROM MyTable) SELECT (SELECT max(rowid) FROM MyTable) - 1 - Trey Thank for that link Trey (and Puneet) - it pretty much confirms what I'd arrived at for myself by trial and error. guy - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] performance question: SELECT max(rowid) - 1
someone else might give a more technical and scientific explanation, but my take is that "SELECT n FROM table" is just that -- a row returned for every row in the table because there is no WHERE clause constraining the results. "SELECT max() - 1 FROM table" on the other hand GROUPs the result before returning it, hence GROUPing acts as a constraint. By that logic, "SELECT (SELECT max(rowid) FROM MyTable') - 10" is very fast because it is SELECTing one record from a returned set of one record... basically, the external SELECT is just as superfluous as "SELECT SELECT (SELECT max(rowid) FROM MyTable') - 10" would be just as fast as well, and so on. On 6/14/07, Guy Hindell <[EMAIL PROTECTED]> wrote: Ah, OK, I see that doing 'SELECT 1 FROM MyTable' returns a 1 for every row, so I can see where the effort is probably going. However, 'SELECT max(rowid) - 1 FROM MyTable' still only produces one result row (obviously I'm experimenting with a much smaller database now). Still need an explanation rather than just relying on my own speculation. Cheers guy Guy Hindell wrote: > I have a fairly large table (10million rows) with a simple INTEGER > PRIMARY KEY AUTOINCREMENT field. > > Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is > 'SELECT min(rowid) FROM MyTable'. > > However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow > (apparently accessing every row). Further, 'SELECT max(rowid) - 1 > FROM MyTable' is slow - in fact using any constant in this expression > (including 0) results in a slow query. > > Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. > > I am curious and would be grateful if someone can explain what is > going on here. > > guy > > > - > > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > > - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] performance question: SELECT max(rowid) - 1
Ah, OK, I see that doing 'SELECT 1 FROM MyTable' returns a 1 for every row, so I can see where the effort is probably going. However, 'SELECT max(rowid) - 1 FROM MyTable' still only produces one result row (obviously I'm experimenting with a much smaller database now). Still need an explanation rather than just relying on my own speculation. Cheers guy Guy Hindell wrote: I have a fairly large table (10million rows) with a simple INTEGER PRIMARY KEY AUTOINCREMENT field. Executing 'SELECT max(rowid) FROM MyTable' is very fast, as is 'SELECT min(rowid) FROM MyTable'. However, 'SELECT max(rowid) - min(rowid) FROM MyTable' is slow (apparently accessing every row). Further, 'SELECT max(rowid) - 1 FROM MyTable' is slow - in fact using any constant in this expression (including 0) results in a slow query. Finally, 'SELECT (SELECT max(rowid) FROM MyTable') - 10' is very fast. I am curious and would be grateful if someone can explain what is going on here. guy - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance Question
I overly simplified my example. I'm actually selecting columns from table B and C which is why I had this in the where clause: AND ( JoinAToB.B_id = B.id ) AND ( JoinAToB.A_id = A.id ) ... Converting those to explicit JOIN clauses fixed the problem. Thanks for your help! -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, February 12, 2007 4:10 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Performance Question Slater, Chad wrote: > Hello, > > I'm having trouble with the performance of one of my queries and my "sql > kung fu" is limited. Any help with this problem would be greatly > appreciated > > Here's a stripped down version of the tables I'm dealing with: > > CREATE TABLE A ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ) > > CREATE TABLE B ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ) > > CREATE TABLE C ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ) > > CREATE TABLE JoinAToB ( > A_id INTEGER NOT NULL REFERENCES A ( id ), > B_id INTEGER NOT NULL REFERENCES B ( id ), > UNIQUE( A_id, B_id ) > ) > > CREATE TABLE JoinAToC ( > A_id INTEGER NOT NULL REFERENCES A ( id ), > C_id INTEGER NOT NULL REFERENCES C ( id ), > UNIQUE( A_id, C_id ) > ) > > The following query takes so long I end up killing the app before the > query returns: > > SELECT DISTINCT A.id > FROM A, >C, >B, >JoinAToB, >JoinAToC > WHERE >( ( ( JoinAToB.B_id IN ( 1 ) ) >AND ( JoinAToB.B_id = B.id ) >AND ( JoinAToB.A_id = A.id ) ) > > OR ( ( JoinAToC.C_id IN ( 1 ) ) >AND ( JoinAToC.C_id = C.id ) >AND ( JoinAToC.A_id = A.id ) ) ) ; > > > Table A has approx 13,000 rows > Table B has 15 rows > Table C has 5 row > JoinTableAToB has 11 rows > JoinTableAToC has approx 450 rows > > If I execute either of these queries separately they are very fast: > > SELECT DISTINCT A.id > FROM A, B, >JoinAToB > WHERE >JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id > = A.id ; > > > SELECT DISTINCT A.id > FROM A, >C, >JoinAToC > WHERE > JoinAToC.C_id IN ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id > = A.id ; > > > Adding the OR clause to combine the results seems to be the culprit but > I don't know why... > Chad, You seem to be overly complicating the matter. Your query SELECT DISTINCT A.id FROM A, B, JoinAToB WHERE JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id = A.id ; is the same as SELECT A_id FROM JoinAToB WHERE JoinAToB.B_id = 1; Your table JoinAToB relates some A ids to some B ids. You don't need to join this to the tables A and B to do a query on the ids in that table. Similarly arguments apply to your table JoinAToC. It looks like you are trying to get all the A ids that are referenced by these two tables where the B id is 1 or the C id is 1. In SQL this is: SELECT A_id FROM JoinAToB WHERE JoinAToB.B_id = 1 UNION SELECT A_id FROM JOINAToC WHERE JoinAToC.C_id = 1 HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Slater, Chad wrote: Hello, I'm having trouble with the performance of one of my queries and my "sql kung fu" is limited. Any help with this problem would be greatly appreciated Here's a stripped down version of the tables I'm dealing with: CREATE TABLE A ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ) CREATE TABLE B ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ) CREATE TABLE C ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT ) CREATE TABLE JoinAToB ( A_id INTEGER NOT NULL REFERENCES A ( id ), B_id INTEGER NOT NULL REFERENCES B ( id ), UNIQUE( A_id, B_id ) ) CREATE TABLE JoinAToC ( A_id INTEGER NOT NULL REFERENCES A ( id ), C_id INTEGER NOT NULL REFERENCES C ( id ), UNIQUE( A_id, C_id ) ) The following query takes so long I end up killing the app before the query returns: SELECT DISTINCT A.id FROM A, C, B, JoinAToB, JoinAToC WHERE ( ( ( JoinAToB.B_id IN ( 1 ) ) AND ( JoinAToB.B_id = B.id ) AND ( JoinAToB.A_id = A.id ) ) OR ( ( JoinAToC.C_id IN ( 1 ) ) AND ( JoinAToC.C_id = C.id ) AND ( JoinAToC.A_id = A.id ) ) ) ; Table A has approx 13,000 rows Table B has 15 rows Table C has 5 row JoinTableAToB has 11 rows JoinTableAToC has approx 450 rows If I execute either of these queries separately they are very fast: SELECT DISTINCT A.id FROM A, B, JoinAToB WHERE JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id = A.id ; SELECT DISTINCT A.id FROM A, C, JoinAToC WHERE JoinAToC.C_id IN ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id = A.id ; Adding the OR clause to combine the results seems to be the culprit but I don't know why... Chad, You seem to be overly complicating the matter. Your query SELECT DISTINCT A.id FROM A, B, JoinAToB WHERE JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id = A.id ; is the same as SELECT A_id FROM JoinAToB WHERE JoinAToB.B_id = 1; Your table JoinAToB relates some A ids to some B ids. You don't need to join this to the tables A and B to do a query on the ids in that table. Similarly arguments apply to your table JoinAToC. It looks like you are trying to get all the A ids that are referenced by these two tables where the B id is 1 or the C id is 1. In SQL this is: SELECT A_id FROM JoinAToB WHERE JoinAToB.B_id = 1 UNION SELECT A_id FROM JOINAToC WHERE JoinAToC.C_id = 1 HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance question
Michael Wohlwend wrote: But If I do "select data from pictures where (x between high_x and low_x) and (y between high_y and low_y) then this takes ca. 8 seconds (!) on wince. Michael, If you are really writing your between clauses as above with the high limit first, then they are not doing what you think. The low limit should always be given first. From the SQL:1999 standard: 8.3 Function Specify a range comparison. Format ::= [ NOT ] BETWEEN [ ASYMMETRIC | SYMMETRIC ] AND Syntax Rules 1) If neither SYMMETRIC nor ASYMMETRIC is specified, then ASYMMETRIC is implicit. 2) Let X, Y, and Z be the first, second, and third expression>s, respectively. 3) ‘‘X NOT BETWEEN SYMMETRIC Y AND Z’’ is equivalent to ‘‘NOT ( X BETWEEN SYMMETRIC Y AND Z )’’. 4) ‘‘X BETWEEN SYMMETRIC Y AND Z’’ is equivalent to ‘‘((X BETWEEN ASYMMETRIC Y AND Z) OR (X BETWEEN ASYMMETRIC Z AND Y))’’. 5) ‘‘X NOT BETWEEN ASYMMETRIC Y AND Z’’ is equivalent to ‘‘NOT ( X BETWEEN ASYMMETRIC Y AND Z )’’. 6) ‘‘X BETWEEN ASYMMETRIC Y AND Z’’ is equivalent to ‘‘X>=Y AND X<=Z’’. Access Rules None. General Rules one. Conformance Rules 1) Without Feature T461, ‘‘Symmetric ’’, conforming SQL language shall not specify SYMMETRIC or ASYMMETRIC. 2) Without Feature S024, ‘‘Enhanced structured types’’, no subfield of the declared type of a value expression> that is simply contained in a shall be of a structured type. SQLite does not support symmetric between predicates so conformance rule 1 applies. This means that Syntax rule 1 also applies and asymmetric is implied. This means that syntax rule 6 is used to translate the between predicate. In your case, this means your: x between high_x and low_x is equivalent to: x >= high_x and x <= low_x which will never be true. Note that standard SQL does not reorder the limits in the between clause if they are given in reverse order (even though it could). Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance question
Michael Wohlwend wrote: I made a database of little pictures, which includes x und y coordinates and Are x and y indexed? Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance question
> But If I do "select data from pictures where (x between high_x and low_x) > and (y between high_y and low_y) then this takes ca. 8 seconds (!) on > wince. My sql knowledge may be a little bit rusty and I have really no idea how sqlite is doing "between" querys. Anyway, once I have learned never to use between because your query is equivalent to: where ((x >= high_x and x <= low_x) or (x >= low_x and x <= high_x) and (y >= high_y and y <= low_y) or (y >= low_y and y <= high_y)) because of the or operators you will get a union of 4 selects. Maybe I am wrong but I would expect that "where x >= low_x and x <= high_x and y >= low_y and y <= high_y" should be faster and all what you need. You have indices on x and y, haven't you? Gerald - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question: Ordering of columns
Slater, Chad wrote: Does the ordering of columns in a table have any impact on performance? Chad, Not significantly if your rows have less than a couple of hundred bytes of data. If they are larger than that they will spill into overflow page(s). It takes longer to insert and select data from the columns that are on the overflow page(s). If you have wide rows (i.e. rows with long string fields or blobs) you should try to locate those after all the smaller fields. In particular, id fields and fields used for joins should be placed at the beginning of the row to ensure they are not located on overflow pages. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
On Wed, 30 Aug 2006 [EMAIL PROTECTED] wrote: > > I have to go along with Mario, here. This is a potential show stopper, > > Show stopper? Really? The bug has been there for years, literally, > and nobody has even noticed it until now - despite thousands of users > and millions and millions of deployments. > > There is a really simple work-around: Just add NOT NULL to your > PRIMARY KEY column declaration... Point taken. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Rob Sciuk <[EMAIL PROTECTED]> wrote: > On Wed, 30 Aug 2006, Mario Frasca wrote: > > On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote: > > > > >> To my surprise (perhaps "horror") I find that SQLite has > > >> for a very long time allowed NULL values in PRIMARY KEY > > >> columns. [...] > > > > I understand your concern about legacy programs, but most of us expect > > PRIMARY KEY to imply NOT NULL... don't we? what about looking for > > alternative good solutions? we could put the correction code in the > > source, conditionally compiled (not the default) and with the next major > > release reverse the condition (new 'corrected' source becomes default > > and old 'legacy' behaviour still available if desired) ... ? > > > > I have to go along with Mario, here. This is a potential show stopper, Show stopper? Really? The bug has been there for years, literally, and nobody has even noticed it until now - despite thousands of users and millions and millions of deployments. There is a really simple work-around: Just add NOT NULL to your PRIMARY KEY column declaration... -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
On Wed, 30 Aug 2006, Mario Frasca wrote: > On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote: > > >> To my surprise (perhaps "horror") I find that SQLite has > >> for a very long time allowed NULL values in PRIMARY KEY > >> columns. [...] > > I understand your concern about legacy programs, but most of us expect > PRIMARY KEY to imply NOT NULL... don't we? what about looking for > alternative good solutions? we could put the correction code in the > source, conditionally compiled (not the default) and with the next major > release reverse the condition (new 'corrected' source becomes default > and old 'legacy' behaviour still available if desired) ... ? > > maybe 'the best of both worlds', hope you agree. > > regards, > Mario I have to go along with Mario, here. This is a potential show stopper, and I would grab a "fixed" version of SQLite ASAP were it made available. Alternatively, one might simply use an ifdef to restore the old (legacy) behaviour, something like: #ifdef WEIRD_AND_UNEXPECTED_BEHAVIOURS_DESIRED ... #endif I'd suggest that this is one for the regression suite as well. Rob Sciuk - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
On 2006-0829 13:15:02, [EMAIL PROTECTED] wrote: To my surprise (perhaps "horror") I find that SQLite has for a very long time allowed NULL values in PRIMARY KEY columns. [...] I understand your concern about legacy programs, but most of us expect PRIMARY KEY to imply NOT NULL... don't we? what about looking for alternative good solutions? we could put the correction code in the source, conditionally compiled (not the default) and with the next major release reverse the condition (new 'corrected' source becomes default and old 'legacy' behaviour still available if desired) ... ? maybe 'the best of both worlds', hope you agree. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Kurt Welgehausen <[EMAIL PROTECTED]> wrote: > [EMAIL PROTECTED] wrote: > > > Saying NOT NULL on a PRIMARY KEY is redundant, by the way. > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > sqlite> insert into t (k, d) values (null, 'jkl'); > sqlite> select * from t; > k d > -- -- >> <> jkl > > > Am I missing something, or should I write a bug ticket > about a primary key accepting nulls? > To my surprise (perhaps "horror") I find that SQLite has for a very long time allowed NULL values in PRIMARY KEY columns. This is clearly incorrect. But the ability to do this has been in the code for so long that I fear changing it might break many legacy programs. So I have chosen to merely document the behavior for now - with a warning to developers that the behavior might be fixed in the future. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Mario Frasca wrote: Kurt Welgehausen wrote: [...] should I write a bug ticket about a primary key accepting nulls? there is already a ticket for that: 518. I reopened it three days ago. I have right now attached a patch for it. it is quite small and I hope it fits in the current style. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Kurt Welgehausen wrote: [EMAIL PROTECTED] wrote: Saying NOT NULL on a PRIMARY KEY is redundant, by the way. [...] Am I missing something, or should I write a bug ticket about a primary key accepting nulls? there is already a ticket for that: 518. I reopened it three days ago. regards, Mario - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
Kurt Welgehausen <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] wrote: > >> Saying NOT NULL on a PRIMARY KEY is redundant, by the way. >> -- >> D. Richard Hipp <[EMAIL PROTECTED]> > > **kaw<~/tdpsa>$ sqlite3 > Loading resources from /home/kaw/.sqliterc > SQLite version 3.3.7 > Enter ".help" for instructions > sqlite> .nullvalue '<>' > sqlite> create table t (k int primary key, d char); > sqlite> insert into t (k, d) values (1, 'abc'); > sqlite> insert into t (k, d) values (1, 'def'); > SQL error: column k is not unique > sqlite> insert into t (k, d) values (null, 'ghi'); > sqlite> insert into t (k, d) values (null, 'jkl'); > sqlite> select * from t; > k d > -- -- > 1 abc > <> ghi > <> jkl > > > Am I missing something, or should I write a bug ticket > about a primary key accepting nulls? Yup. "int primary key" is not the same as "integer primary key". Although I'm using an older version than you are, I got exactly the same results you did with "int primary key". SQLite version 3.2.1 Enter ".help" for instructions sqlite> .nullvalue '<>' sqlite> create table t (k integer primary key, d char); sqlite> insert into t (k, d) values (1, 'abc'); sqlite> insert into t (k, d) values (1, 'def'); SQL error: PRIMARY KEY must be unique sqlite> insert into t (k, d) values (null, 'ghi'); sqlite> insert into t (k, d) values (null, 'jkl'); sqlite> select * from t; 1|abc 2|ghi 3|jkl sqlite> Derrell - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
[EMAIL PROTECTED] wrote: > Saying NOT NULL on a PRIMARY KEY is redundant, by the way. > -- > D. Richard Hipp <[EMAIL PROTECTED]> **kaw<~/tdpsa>$ sqlite3 Loading resources from /home/kaw/.sqliterc SQLite version 3.3.7 Enter ".help" for instructions sqlite> .nullvalue '<>' sqlite> create table t (k int primary key, d char); sqlite> insert into t (k, d) values (1, 'abc'); sqlite> insert into t (k, d) values (1, 'def'); SQL error: column k is not unique sqlite> insert into t (k, d) values (null, 'ghi'); sqlite> insert into t (k, d) values (null, 'jkl'); sqlite> select * from t; k d -- -- 1 abc <> ghi <> jkl Am I missing something, or should I write a bug ticket about a primary key accepting nulls? Regards - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance Question
"Slater, Chad" <[EMAIL PROTECTED]> wrote: > Hello, > > Consider the following lookup table definition: > > CREATE TABLE foobar ( > id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, > table1_id INTEGER NOT NULL REFERENCES table1, > table2_id INTEGER NOT NULL REFERENCES table2 > ); > > The id primary key column is not necessary for anything in my > application. But I've heard that some database implementations recommend > the primary key for performance reasons. Is this true for sqlite? > > No. In fact, AUTOINCREMENT will slow things down since with AUTOINCREMENT, the table has to keep track of the largest primary key that has ever existed in the table in order to insure that no key is ever repeated over the entire life of the table. AUTOINCREMENT in SQLite does not work like MySQL. Saying NOT NULL on a PRIMARY KEY is redundant, by the way. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] performance question
> On my PC the following query requires about 53 seconds: > select * from TABG a, TABB b where (a.S='3' or a.S='12 or...) and > b.G=a.G order by a.G asc; > > (On Oracle with the same scheme and data it requires only 0.4 > seconds.) In my experience, even though SQLite has very low overhead and is pretty lightweight, performance can get hurt pretty badly for complicated queries (or even fairly simple ones like yours) when it chooses the wrong optimization paths. I've had situations where changing "SELECT ... FROM table1, table2 ..." to "SELECT ... FROM table2, table1 ..." makes an enormous difference in execution time, because when SQLite has more than one index to choose from, it seems to choose randomly. Sometimes it's wrong, and sometimes quite badly so. This is why many other DBMs put a lot of effort into developing things like cost-based optimizers, so these kinds of issues can be dealt with nicely. I'm not sure if SQLite plans to add such things, but I'm not sure it fits with the stated goals of simplicity. So I guess the moral is that when performance gets slow, you really have to scrutinize the execution plans in SQLite more than in other databases I'm used to, rather than just adding indexes you *think* should help and trusting the database to do the "right" thing. -Ken - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] performance question
Hi, > I have a question about the performance of my SQLite DB, where the > db-file has about 20MB and which I use in a Java application via the > Java wrapper. First, your timing figures look indeed slower than what I would expect (using a somewhat similar DB in type and size and a similar select even on an embedded system) The Java wrapper might be your first suspective. Did you try the command line program as a reference? > TABB has 14785 rows, TABG 7111 rows. > On my PC the following query requires about 53 seconds: > select * from TABG a, TABB b where (a.S='3' or a.S='12 or...) and > b.G=a.G order by a.G asc; Depending upon how many "or" conditions you have, you might try ot use the "in" keyword. (Although I would not expect much improvement) > The times are used only for the query, not connecting etc. I guess it > has something to do with building up the data structures for the first > query resp. caching. I do not think the behaviour you see is sqlite-internal, I would suspect the Java wrapper. How large is the output of your selection? Maybe it is just the transfer (socket, whatever) which takes so much time. Regards, Frank Baumgart - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]