Re: [sqlite] Strange performance problem

[Rado Rado]

I've tried that before but now I've found out I had empty "begin"
implementation in my wrapper, now it works, thank you.

On Mon, Sep 5, 2011 at 6:59 PM, Dan Kennedy  wrote:

> On 09/05/2011 10:47 PM, Rado Rado wrote:
>
>> I'm running simple prepared SELECT statement in loop ( about 3000 times ).
>> It is something like "SELECT value FROM t WHERE t_id=? AND name=?". For
>> most
>> calls the row does not exist, step() returns SQLITE_DONE so I call reset
>> after that(). The loop takes about 0.25 second and result seems to be
>> correct.
>>
>> When I execute any SELECT query (using different table, like SELECT * FROM
>> t2) which returns some row and I won't call reset() so it stays open, when
>> I
>> execute the loop described above after this, it is much faster (0.08
>> sec.).
>>
>> Is it because of some lock obtained for my process by opened statement? Or
>> am i doing something wrong?
>>
>
> It will be the overhead of obtaining a read lock. In the first case,
> you are obtaining and releasing a database read lock (a system call)
> 3000 times. In the second case, you are only doing it once.
>
> You could get the same effect by wrapping your loop in a BEGIN/COMMIT
> block.
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Simon Slavin]

On 5 Sep 2011, at 6:24pm, Stephan Beal wrote:

> On Mon, Sep 5, 2011 at 7:11 PM, Simon Slavin  wrote:
> 
>> It should be.  However, if you have a multi-user, multi-process or
>> multi-thread setup, please make absolutely certain that you handle all
>> SQLite result codes apart from SQLITE_OK correctly.  If you don't have to
>> worry about multi-anything, then you shouldn't see anything alarming.
> 
> Doh, i wasn't aware that an explicit rollback is treated as an error, but my
> quick tests with the JavaScript bindings show that it is.

I was surprised too, though having seen that it makes sense.  Even more sense 
when you remember they're called 'result codes' not 'error codes':

http://www.sqlite.org/c3ref/c_abort.html

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Stephan Beal]

On Mon, Sep 5, 2011 at 7:11 PM, Simon Slavin  wrote:

> It should be.  However, if you have a multi-user, multi-process or
> multi-thread setup, please make absolutely certain that you handle all
> SQLite result codes apart from SQLITE_OK correctly.  If you don't have to
> worry about multi-anything, then you shouldn't see anything alarming.
>

Doh, i wasn't aware that an explicit rollback is treated as an error, but my
quick tests with the JavaScript bindings show that it is.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Dan Kennedy]

On 09/06/2011 12:04 AM, Stephan Beal wrote:

On Mon, Sep 5, 2011 at 6:59 PM, Dan Kennedy  wrote:


You could get the same effect by wrapping your loop in a BEGIN/COMMIT
block.



Out of curiosity: would a BEGIN/ROLLBACK be equivalent for this case (where
only SELECTs are used)?


It would be. A ROLLBACK might use a trivial amount more CPU cycles.
Nothing you would notice though.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Simon Slavin]

On 5 Sep 2011, at 6:04pm, Stephan Beal wrote:

> On Mon, Sep 5, 2011 at 6:59 PM, Dan Kennedy  wrote:
> 
>> You could get the same effect by wrapping your loop in a BEGIN/COMMIT
>> block.
> 
> Out of curiosity: would a BEGIN/ROLLBACK be equivalent for this case (where
> only SELECTs are used)?

It should be.  However, if you have a multi-user, multi-process or multi-thread 
setup, please make absolutely certain that you handle all SQLite result codes 
apart from SQLITE_OK correctly.  If you don't have to worry about 
multi-anything, then you shouldn't see anything alarming.

Looking at your original SELECT, by the way ...

> "SELECT value FROM t WHERE t_id=? AND name=?"

I hope you have an index that can be used to look those two values up quickly.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Stephan Beal]

On Mon, Sep 5, 2011 at 6:59 PM, Dan Kennedy  wrote:

> You could get the same effect by wrapping your loop in a BEGIN/COMMIT
> block.


Out of curiosity: would a BEGIN/ROLLBACK be equivalent for this case (where
only SELECTs are used)?

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Dan Kennedy]

On 09/05/2011 10:47 PM, Rado Rado wrote:

I'm running simple prepared SELECT statement in loop ( about 3000 times ).
It is something like "SELECT value FROM t WHERE t_id=? AND name=?". For most
calls the row does not exist, step() returns SQLITE_DONE so I call reset
after that(). The loop takes about 0.25 second and result seems to be
correct.

When I execute any SELECT query (using different table, like SELECT * FROM
t2) which returns some row and I won't call reset() so it stays open, when I
execute the loop described above after this, it is much faster (0.08 sec.).

Is it because of some lock obtained for my process by opened statement? Or
am i doing something wrong?


It will be the overhead of obtaining a read lock. In the first case,
you are obtaining and releasing a database read lock (a system call)
3000 times. In the second case, you are only doing it once.

You could get the same effect by wrapping your loop in a BEGIN/COMMIT
block.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Stephan Beal]

On Mon, Sep 5, 2011 at 5:47 PM, Rado Rado  wrote:

> I'm running simple prepared SELECT statement in loop ( about 3000 times ).
> It is something like "SELECT value FROM t WHERE t_id=? AND name=?".

,,,
> When I execute any SELECT query (using different table, like SELECT * FROM
> t2) which returns some row and I won't call reset() so it stays open, when
> I
> execute the loop described above after this, it is much faster (0.08 sec.).
>

Remember that the WHERE clause in the first query takes time to evaluate,
whereas the second query has no conditions and can therefore (at least in
principal) respond with the first result more quickly.

Whether or not that explains the whole difference, i cannot say (only
profiling could), but some of the gurus on the list can probably give you
some ballpark numbers on the effect the WHERE clause has.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Strange performance problem

[Rado Rado]

I'm running simple prepared SELECT statement in loop ( about 3000 times ).
It is something like "SELECT value FROM t WHERE t_id=? AND name=?". For most
calls the row does not exist, step() returns SQLITE_DONE so I call reset
after that(). The loop takes about 0.25 second and result seems to be
correct.

When I execute any SELECT query (using different table, like SELECT * FROM
t2) which returns some row and I won't call reset() so it stays open, when I
execute the loop described above after this, it is much faster (0.08 sec.).

Is it because of some lock obtained for my process by opened statement? Or
am i doing something wrong?
Thank you.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Strange performance problem

[Khamis Abuelkomboz]

Hi Michael

I tried your example and have experienced the same results. Then I 
created the following two indices, now the two queries are same fast:


CN_execute "CREATE INDEX idx_t1_id ON t1 (id)"
CN_execute "CREATE INDEX idx_t2_id ON t2 (id)"

It seems that sqlite doesn't create an index for primary keys that are 
not "INTEGER" :-) But haven't verified it yet.


khamis

Michael Gross wrote:


Khamis Abuelkomboz wrote:


CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);


Doent not help.


whereas the following quere takes "no" time:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE 
t1.deleted<>1



This is fast, because no entries has been found to be joined.


This is not true - "deleted" hast the value 0 in all rows - so
"t1.deleted=0" and "t1.deleted<>1" brings the same result.


And now - check this out - the following query ist fast too:

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE NOT NOT
(t1.deleted=0)

Whereas

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE (t1.deleted=0)

is slow. And as far as I undestand this should be the same, or do I miss
sth?

thx
  Michael





--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

Re: [sqlite] Strange performance problem

[Michael Gross]

Khamis Abuelkomboz wrote:

CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);

Doent not help.


whereas the following quere takes "no" time:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted<>1


This is fast, because no entries has been found to be joined.

This is not true - "deleted" hast the value 0 in all rows - so
"t1.deleted=0" and "t1.deleted<>1" brings the same result.


And now - check this out - the following query ist fast too:

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE NOT NOT
(t1.deleted=0)

Whereas

SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE (t1.deleted=0)

is slow. And as far as I undestand this should be the same, or do I miss
sth?

thx
  Michael

Re: [sqlite] Strange performance problem

[Khamis Abuelkomboz]

Michael Gross wrote:


Hello
I use sqlite 3.2.2. I have a strange performance problem. I am able to 
solve the problem by a slight change in the query but I want to ask if 
somebody can explain this behavior to me:


I have two tables:
  CREATE TABLE t1 (id VARCHAR(40) NOT NULL PRIMARY KEY, deleted BIT);
  CREATE TABLE t2 (id VARCHAR(40) NOT NULL PRIMARY KEY, t1id 
VARCHAR(40), deleted BIT);

  CREATE INDEX idx_t1_deleted ON t1 (deleted);
  CREATE INDEX idx_t2_deleted ON t2 (deleted);
  CREATE INDEX idx_t2_t1id ON t2 (t1id);


try the following index

CREATE INDEX idx_t2_t1id ON t2 (t1id, deleted);


When I now insert 2500 times (%d is the incrementing variable):
  INSERT INTO t1 (id, deleted) VALUES ('%d', 0);
  INSERT INTO t2 (id, t1id, deleted) VALUES ('%d', '%d', 0);
  INSERT INTO t2 (id, t1id, deleted) VALUES ('xxx%d', '%d', 0);

The the following query takes about 1 minute:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted=0


This is slow, because the join is not indexed


whereas the following quere takes "no" time:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted<>1


This is fast, because no entries has been found to be joined.

Keep in mind, that only one index can be used for a query or subquery.



thx
  Michael





--
Try Code-Navigator on http://www.codenav.com
a source code navigating, analysis and developing tool.
It supports following languages:
   * C/C++
   * Java
   * .NET (including CSharp, VB.Net and other .NET components)
   * Classic Visual Basic
   * PHP, HTML, XML, ASP, CSS
   * Tcl/Tk,
   * Perl
   * Python
   * SQL,
   * m4 Preprocessor
   * Cobol

[sqlite] Strange performance problem

[Michael Gross]

Hello
I use sqlite 3.2.2. I have a strange performance problem. I am able to 
solve the problem by a slight change in the query but I want to ask if 
somebody can explain this behavior to me:


I have two tables:
  CREATE TABLE t1 (id VARCHAR(40) NOT NULL PRIMARY KEY, deleted BIT);
  CREATE TABLE t2 (id VARCHAR(40) NOT NULL PRIMARY KEY, t1id 
VARCHAR(40), deleted BIT);

  CREATE INDEX idx_t1_deleted ON t1 (deleted);
  CREATE INDEX idx_t2_deleted ON t2 (deleted);
  CREATE INDEX idx_t2_t1id ON t2 (t1id);

When I now insert 2500 times (%d is the incrementing variable):
  INSERT INTO t1 (id, deleted) VALUES ('%d', 0);
  INSERT INTO t2 (id, t1id, deleted) VALUES ('%d', '%d', 0);
  INSERT INTO t2 (id, t1id, deleted) VALUES ('xxx%d', '%d', 0);

The the following query takes about 1 minute:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted=0

whereas the following quere takes "no" time:
  SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t1.id=t2.id WHERE t1.deleted<>1


thx
  Michael