Re: [sqlite] Performance question related to multiple processes using sqlite

[Richard Hipp]

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

[sqlite] Performance question related to multiple processes using sqlite

[Varadan, Yamini (SCR US) (EXT)]

Hello,
We use Qt with sqldriver Sqlite-4 in our application. (Windows XP 32 bit, 
Visual studio 2005)
We are facing a performance issue in the following scenario.

There are two processes A and B. A uses sqlite DB1 and keeps populating data.
Process B uses sqlite DB2 for writing and occasionally opens DB1 for reading.

What we notice is when A is writing into DB1, B hangs for a couple of seconds 
even though it is not performing any DB operations on DB1/DB2.

On opening Windows event viewer, we see that process B is making a lot of 
registry calls and is accessing disk a lot during the time that process A is 
writing to DB1 and process B really is not doing any DB operation at that time.

For now, we have not been able to identify if this is happenning inside Qt sql 
module or inside sqlite.

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?

Thanks in advance!

This message and any attachments are solely for the use of intended recipients. 
The information contained herein may include trade secrets, protected health or 
personal information, privileged or otherwise confidential information. 
Unauthorized review, forwarding, printing, copying, distributing, or using such 
information is strictly prohibited and may be unlawful. If you are not an 
intended recipient, you are hereby notified that you received this email in 
error, and that any review, dissemination, distribution or copying of this 
email and any attachment is strictly prohibited. If you have received this 
email in error, please contact the sender and delete the message and any 
attachment from your system. Thank you for your cooperation
___
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

[Guy Hindell]

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

[P Kishor]

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

[Guy Hindell]

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

[sqlite] performance question: SELECT max(rowid) - 1

[Guy Hindell]

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

RE: [sqlite] Performance Question

[Slater, Chad]

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

[Dennis Cote]

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

[sqlite] Performance Question

[Slater, Chad]

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


Regards,


Chad

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

AW: AW: [sqlite] Performance question

[Michael Wohlwend]

-Ursprüngliche Nachricht-
Von: Martin Pfeifle [mailto:[EMAIL PROTECTED] 
Gesendet: Dienstag, 26. September 2006 13:35
An: sqlite-users@sqlite.org
Betreff: AW: AW: [sqlite] Performance question


>Hi Michael,
>could you please (re)post the exact create inex statements +primary key you
used. For speeding up 
>your query, you need an index on x only but not on id,x. Best Martin

The table looks like:
(blobsize between 100 and 8000 bytes, 25 rows in the table)

Create table t1 (x integer, y integer, flag integer, data blob)
Create index idx on t1 (x,y,flag)
(it doesn't matter if is inlcuded in the index)

Takes 5ms on my pda, 100 of those need 500ms:
Select data from t1 where x=v1 and y=v1 and flag=f  
Takes 7sec(!) on pda for a rectangle with 60 blobs:
Select data from t1 where (x between xlow and xhigh) and (y between ylow and
yhigh) and flag=f
Lightning fast:
Adding a column xy set to (x << 16|y) and replacing idex with an idx on xy:
Select x,y,data from t1 where xy in (xy1,xy2,...)

Cheers,
 Michael

AW: AW: [sqlite] Performance question

[Martin Pfeifle]

Hi Michael,
could you please (re)post the exact create inex statements +primary key you 
used.
For speeding up your query, you need an index on x only but not on id,x.
Best Martin

- Ursprüngliche Mail 
Von: Michael Wohlwend <[EMAIL PROTECTED]>
An: "sqlite-users@sqlite.org" <sqlite-users@sqlite.org>
Gesendet: Dienstag, den 26. September 2006, 09:34:00 Uhr
Betreff: AW: [sqlite] Performance question


-Ursprüngliche Nachricht-
Von: Dennis Cote [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 22. September 2006 17:07
An: sqlite-users@sqlite.org
Betreff: 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.
>
>   

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

Ah, that was a typo, of course the query was "between (low and high)". I
changed this to
"x > low and x <= high ..." and i got the same result: 1 single query
(without bouds-check) takes 5ms, the query with the bounds-check takes ca.
7seconds (there are indices on x and y).
I changed the query to (select  ... where id in (v1,v2,...)) this was quite
fast again, even if the list of values got over 200 elements, but that's not
the way I wanted to do it. Maybe sqlite on arm cpus in the current
implementation isn't optimized enough. But I have no idea where this huge
slowdown comes from.

Cheers
Michael






___ 
Der frühe Vogel fängt den Wurm. Hier gelangen Sie zum neuen Yahoo! Mail: 
http://mail.yahoo.de

AW: [sqlite] Performance question

[Michael Wohlwend]

-Ursprüngliche Nachricht-
Von: Dennis Cote [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 22. September 2006 17:07
An: sqlite-users@sqlite.org
Betreff: 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.
>
>   

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

Ah, that was a typo, of course the query was "between (low and high)". I
changed this to
"x > low and x <= high ..." and i got the same result: 1 single query
(without bouds-check) takes 5ms, the query with the bounds-check takes ca.
7seconds (there are indices on x and y).
I changed the query to (select  ... where id in (v1,v2,...)) this was quite
fast again, even if the list of values got over 200 elements, but that's not
the way I wanted to do it. Maybe sqlite on arm cpus in the current
implementation isn't optimized enough. But I have no idea where this huge
slowdown comes from.

Cheers
 Michael

Re: [sqlite] Performance question

[Dennis Cote]

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

[Martin Jenkins]

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

AW: [sqlite] Performance question

[Michael Wohlwend]

-Ursprüngliche Nachricht-
Von: Gerald Dachs [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 22. September 2006 11:28
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Performance question



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

I think it is:
where (x >= low_x and x <= high_x) and (y >= low_y and y <= high_y), i.e. in
"between a and b", a should be lower or equal to b (you don't get a result
otherwise)

So it's without the "or" part. But I will test the other statement too.

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

Yep :-)


Cheers 
 Michael

Re: [sqlite] Performance question

[Gerald Dachs]

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

[sqlite] Performance question

[Michael Wohlwend]

Hi,


I made a database of little pictures, which includes x und y coordinates and
a blob (between 100 and 8000 bytes in size,  one blob, total db size 180MB).
If I do 
"select data from pictures where x=? And y=?" 
this works well, also on wince (measured myself: 1 such a select take 5
milliseconds on my wince). If I do this 100 times (to select all pictures in
a rectangle), the time it needs scales linear.
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.
Any idea where this long time comes from? 
The code is written in c++, without wrapper, page_size=512, cache=16MB and
runs on a 400MHz Arm cpu.

Thanks for answering,
 Michael

Re: [sqlite] Performance Question: Ordering of columns

[Dennis Cote]

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

[sqlite] Performance Question: Ordering of columns

[Slater, Chad]

Hello,

Does the ordering of columns in a table have any impact on performance? 

Chad

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

Re: [sqlite] Performance Question

[Rob Sciuk]

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

[drh]

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

[Rob Sciuk]

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

[Mario Frasca]

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

[drh]

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]

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

[Mario Frasca]

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

[Derrell . Lipman]

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

[Kurt Welgehausen]

[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

[drh]

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

[sqlite] Performance Question

[Slater, Chad]

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?


TIA,


Chad

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

RE: [sqlite] performance question

[Williams, Ken]

> 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

[godot]

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]