Re: [sqlite] does sqlite3_reset have a performance impact onqueries?
Stephan Beal sgb...@googlemail.com 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
Re: [sqlite] does sqlite3_reset have a performance impact onqueries?
On Wed, Nov 2, 2011 at 12:59 PM, Igor Tandetnik itandet...@mvps.org 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?
On Wed, Nov 2, 2011 at 1:11 PM, Stephan Beal sgb...@googlemail.com 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?
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 sgb...@googlemail.com wrote: On Wed, Nov 2, 2011 at 12:59 PM, Igor Tandetnik itandet...@mvps.org 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?
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 SELECThttp://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? 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?
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 SELECThttp://www.sqlite.org/lang_select.html) 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?
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 SELECThttp://www.sqlite.org/lang_select.html) 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?
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 SELECThttp://www.sqlite.org/lang_select.html) 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?
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 SELECThttp://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
Re: [sqlite] does sqlite3_reset have a performance impact onqueries?
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 SELECThttp://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
