Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Black, Michael (IS)]

Then how's about we update the docs?  I can understand the need for consistency 
but never would've guessed that it sped things up so much.  A hint in the docs 
would help.



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

Northrop Grumman Information Systems


From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Dan Kennedy [danielk1...@gmail.com]
Sent: Wednesday, November 02, 2011 11:05 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] does sqlite3_reset have a performance impact 
onqueries?

On 11/02/2011 08:13 PM, Black, Michael (IS) wrote:
> Maybe my memory is fading but this is the first time I've heard anybody say 
> the wrapping a BEBIN around a SELECT was needed.  I'd swear it was always 
> said it wasn't ever needed.
>
>
>
>> From the docs
>
> http://www.sqlite.org/lang_transaction.html
>
> basically, any SQL command other than 
> SELECT<http://www.sqlite.org/lang_select.html>) will automatically start a 
> transaction if one is not already in effect
>
>
>
> Now you're saying SELECT always starts a transaction?

There are two types of transactions - read-only transactions and
read/write transactions.

A read-only transaction contains only reads. All reads that occur
within a single read transaction see the same snapshot of the
database. Logically, they are useful when you have one or more
SELECT statements to run and you don't want anybody else modifying
the database between them. If you don't wrap a SELECT in a
BEGIN/COMMIT block, then it will open and close its own
read-transaction.

But, read-transactions can also speed things up in SQLite - since
you don't have to lock and unlock the database file (and do some
other stuff too) for every SELECT.

___
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] does sqlite3_reset have a performance impact onqueries?

[Dan Kennedy]

On 11/02/2011 08:13 PM, Black, Michael (IS) wrote:

Maybe my memory is fading but this is the first time I've heard anybody say the 
wrapping a BEBIN around a SELECT was needed.  I'd swear it was always said it 
wasn't ever needed.




From the docs


http://www.sqlite.org/lang_transaction.html

basically, any SQL command other than 
SELECT) will automatically start a 
transaction if one is not already in effect



Now you're saying SELECT always starts a transaction?


There are two types of transactions - read-only transactions and
read/write transactions.

A read-only transaction contains only reads. All reads that occur
within a single read transaction see the same snapshot of the
database. Logically, they are useful when you have one or more
SELECT statements to run and you don't want anybody else modifying
the database between them. If you don't wrap a SELECT in a
BEGIN/COMMIT block, then it will open and close its own
read-transaction.

But, read-transactions can also speed things up in SQLite - since
you don't have to lock and unlock the database file (and do some
other stuff too) for every SELECT.

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Yuriy Kaminskiy]

Teg wrote:
> I'd  like  this  clarified  too. I specifically don't use transactions
> when  I'm  selecting.  In  fact, I'll select, then start a transaction
> later  for  inserting the results.  Would I be better off wrapping the
> whole thing in a transaction?

Cannot be sure without looking at your code/database schema/..., but it looks
your usage pattern is wrong - there are race condition this way (that maybe
alleviated by implicit transaction start by [unfinished] SELECT, but anyway it
is fragile and wrong).

> Wednesday, November 2, 2011, 9:13:20 AM, you wrote:
> 
> BMI> Maybe my memory is fading but this is the first time I've heard
> BMI> anybody say the wrapping a BEBIN around a SELECT was needed.  I'd
> BMI> swear it was always said it wasn't ever needed.

BTW, what is "needed"? Nobody *force* you to use BEGIN/COMMIT around SELECT -
transaction will be started (and ended) implicitly; just if you issue multiple
statement, it is more efficient to take lock once, than take and release lock
(and lot more - check database schema, flush cache, etc) for each statement.

And, of course, if you need consistent database state across many sql
statements, you need to wrap all of them in single transaction.

>> >From the docs
> 
> BMI> http://www.sqlite.org/lang_transaction.html
> 
> BMI> basically, any SQL command other than
> BMI> SELECT) will
> BMI> automatically start a transaction if one is not already in effect
> 
> 
> 
> BMI> Now you're saying SELECT always starts a transaction?

PS
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing on usenet and in e-mail?

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Teg]

I'd  like  this  clarified  too. I specifically don't use transactions
when  I'm  selecting.  In  fact, I'll select, then start a transaction
later  for  inserting the results.  Would I be better off wrapping the
whole thing in a transaction?

Wednesday, November 2, 2011, 9:13:20 AM, you wrote:

BMI> Maybe my memory is fading but this is the first time I've heard
BMI> anybody say the wrapping a BEBIN around a SELECT was needed.  I'd
BMI> swear it was always said it wasn't ever needed.



>>From the docs

BMI> http://www.sqlite.org/lang_transaction.html

BMI> basically, any SQL command other than
BMI> SELECT) will
BMI> automatically start a transaction if one is not already in effect



BMI> Now you're saying SELECT always starts a transaction?



BMI> Michael D. Black

BMI> Senior Scientist

BMI> Advanced Analytics Directorate

BMI> Advanced GEOINT Solutions Operating Unit

BMI> Northrop Grumman Information Systems
BMI> ___
BMI> sqlite-users mailing list
BMI> sqlite-users@sqlite.org
BMI> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users




-- 
Best regards,
 Tegmailto:t...@djii.com

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Yuriy Kaminskiy]

Black, Michael (IS) wrote:
> Maybe my memory is fading but this is the first time I've heard anybody say
> the wrapping a BEBIN around a SELECT was needed.  I'd swear it was always
> said it wasn't ever needed.
> 
> 
> 
> From the docs
> 
> http://www.sqlite.org/lang_transaction.html

> basically, any SQL command other than 
> SELECT) will automatically start a 
> transaction if one is not already in effect

That's, of course, documentation bug. How can you get consistent results from
SELECT without taking (at least) SHARED lock?

Only difference, that UPDATE/INSERT/DELETE needs RESERVED lock (and then
EXCLUSIVE lock to (auto)commit), and SELECT only needs SHARED lock.

> Now you're saying SELECT always starts a transaction?

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Black, Michael (IS)]

Maybe my memory is fading but this is the first time I've heard anybody say the 
wrapping a BEBIN around a SELECT was needed.  I'd swear it was always said it 
wasn't ever needed.



>From the docs

http://www.sqlite.org/lang_transaction.html

basically, any SQL command other than 
SELECT) will automatically start a 
transaction if one is not already in effect



Now you're saying SELECT always starts a transaction?



Michael D. Black

Senior Scientist

Advanced Analytics Directorate

Advanced GEOINT Solutions Operating Unit

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

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Tal Tabakman]

Thanks,
FYI, while it was a bad code styling on my behalf (I just did it for
showing en example) this was not my problem .
creating 2 short strings does not takes 20 seconds :)
also note, that when avoiding sqlite3_reset and creating new sqlite3_stmt
every time (and still creating those redundant string), there is no
performance overhead.

the real problem is the fact that each time, a new transaction was created
(see Richard's reply).
by doing BEGIN and clutter all operations under one transaction, the
program fly,

On Wed, Nov 2, 2011 at 2:11 PM, Stephan Beal  wrote:

> On Wed, Nov 2, 2011 at 12:59 PM, Igor Tandetnik 
> wrote:
>
> > The behavior of this fragment is in fact well defined by the C++
> standard.
> > The temporary is destroyed at the end of the full expression - basically,
> > at the semicolon, after the function call
> >
>
> That was also my understanding until recently when a friend of mine asked
> me to debug some code of his which mysteriously crashed. It was caused by a
> Qt debug wrapper macro which did the equivalent of c_str() on a temporary
> string instance. i unfortunately don't have the docs handy, and to be
> honest i'm not 100% convinced that what i wrote about undefined behaviour
> is true in that case, but i am 100% convinced that i've seen that usage
> cause problems before. Perhaps it was the compiler in question  (one of the
> MSVC variants) which made the behaviour undefined, as opposed to be a
> violation of the standard.
>
> In any case (undefined or not), calling ostringstream::str() twice there is
> unnecessary, and downright inefficient if the user's STL does not use CoW
> (all of them do, AFAIK, but that's an implementation detail clients
> shouldn't count on).
>
> --
> - 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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] does sqlite3_reset have a performance impact onqueries?

[Stephan Beal]

On Wed, Nov 2, 2011 at 1:11 PM, Stephan Beal  wrote:

> about undefined behaviour is true in that case, but i am 100% convinced
> that i've seen that usage cause problems before. Perhaps it was the
> compiler in question  (one of the MSVC variants) which made the behaviour
> undefined, as opposed to be a violation of the standard.
>
>
After reading up on this a bit, and finding the old email thread, i'm 99%
certain that the behaviour is (as you say) well-defined vis-a-vis the
standard. The crash my friend was seeing at the time may have been
compiler-specific or a side-effect of the specific implementation of the
debug macro he was using in conjunction with the c_str() return value.

-- 
- 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] does sqlite3_reset have a performance impact onqueries?

[Stephan Beal]

On Wed, Nov 2, 2011 at 12:59 PM, Igor Tandetnik  wrote:

> The behavior of this fragment is in fact well defined by the C++ standard.
> The temporary is destroyed at the end of the full expression - basically,
> at the semicolon, after the function call
>

That was also my understanding until recently when a friend of mine asked
me to debug some code of his which mysteriously crashed. It was caused by a
Qt debug wrapper macro which did the equivalent of c_str() on a temporary
string instance. i unfortunately don't have the docs handy, and to be
honest i'm not 100% convinced that what i wrote about undefined behaviour
is true in that case, but i am 100% convinced that i've seen that usage
cause problems before. Perhaps it was the compiler in question  (one of the
MSVC variants) which made the behaviour undefined, as opposed to be a
violation of the standard.

In any case (undefined or not), calling ostringstream::str() twice there is
unnecessary, and downright inefficient if the user's STL does not use CoW
(all of them do, AFAIK, but that's an implementation detail clients
shouldn't count on).

-- 
- 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] does sqlite3_reset have a performance impact onqueries?

[Igor Tandetnik]

Stephan Beal  wrote:
> Also, because
> 'query' is-a ostringstream, you are possibly creating 2 unneeded
> std::string copies here and you are definitely invoking undefined behaviour
> with this part:
> 
>   sqlite3_prepare_v2(handle, query.str().c_str(),
> 
> The problem is that query.str() returns a COPY of the string, which you
> call c_str() on to get its bytes, and then the copy is destroyed.

The behavior of this fragment is in fact well defined by the C++ standard. The 
temporary is destroyed at the end of the full expression - basically, at the 
semicolon, after the function call.
-- 
Igor Tandetnik

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