Re: [sqlite] Progress callback and nested queries

On 11/03/2011 01:11 AM, Duquette, William H (318K) wrote: I'm pretty sure I know the answer to this. Sqlite3 allows you to define a "progress" callback, which will be called every so many byte-code instructions during a long-running query, so that you can update a progress bar or like that.

Re: [sqlite] problem using sqlite_prepare_v2

On 11/2/2011 2:46 PM, John Markavitch wrote: iRes = sqlite3_step(statement); if ((iRes == SQLITE_ROW) || (iRes == SQLITE_DONE)) { psItemPID = sqlite3_column_text (statement, 0); //

Re: [sqlite] Progress callback and nested queries

> I'm assuming that querying the same database using the same handle during a > "progress" callback would be a *bad* idea.  Am I mistaken? That would deadlock on handle's internal mutex or corrupt the handle if your SQLite is compiled without multi-threading support. Pavel On Wed, Nov 2,

Re: [sqlite] Slow INDEX

On Wed, Nov 2, 2011 at 2:13 PM, Fabian wrote: > 2011/11/2 Nico Williams >> But note that this can still fail you when the file is larger than >> available RAM.  In that case such a flag would be very bad.  And >> SQLite3 can't know how much RAM is

Re: [sqlite] Slow INDEX

2011/11/2 Nico Williams > > Incidentally, it pays to make the SQLite3 page size match the > filesystem preferred block size. > > I already have the page_size set to 4096, which should match the default NTFS cluster size. But note that this can still fail you when the file

[sqlite] problem using sqlite_prepare_v2

Good Afternoon: I am relatively new at sqlite and am having a problem with the sqlite_prepare_v2() statements. My sqlite version is 3.7.8 running under Windows 7. After using a sqlite_prepare_v2() statement, if I do a sqlite3_finalize(), all further sqlite_prepare_v2() calls return a

Re: [sqlite] Slow INDEX

On Wed, Nov 2, 2011 at 1:20 PM, Fabian wrote: > Linux will not read the whole file in, but Windows eventually does. The > inserts go progressively faster when they are reaching halfway, and Windows > reads very large pages from disk, even if you request only 10 bytes. So in

Re: [sqlite] Slow INDEX

2011/11/2 Nico Williams > > But that's NOT what SQLite3 is doing. SQLite3 is doing random I/O. > And the OS sees the random I/O pattern and concludes it's better to > not read the whole file in. So for those 10K inserts you pay -worst > case- 10K I/Os. At ~12ms per

[sqlite] Progress callback and nested queries

I'm pretty sure I know the answer to this. Sqlite3 allows you to define a "progress" callback, which will be called every so many byte-code instructions during a long-running query, so that you can update a progress bar or like that. I'm assuming that querying the same database using the same

Re: [sqlite] Slow INDEX

2011/11/2 Black, Michael (IS) > Then, when you restart the file is on system disk and it is flushing each > insert to system disk on the WAL file slowing things down dramaticalliy. > I do not use WAL, since I have turned 'journal_mode' off (to improve insert performance), and as far as I know

Re: [sqlite] Slow INDEX

On Wed, Nov 2, 2011 at 11:41 AM, Fabian wrote: > 2011/11/2 Mr. Puneet Kishor >> ahh, so you *are* getting expected behavior, just not what *you* expected. >> Did you have a different number in mind instead of a factor of 300? And, if >> so, why? > > To

Re: [sqlite] Slow INDEX

What happens if you run your test outside of VirtualBox? I'm thinking when you first write the file VirtualBox does it locally and then flushes the whole thing to system disk. Then, when you restart the file is on system disk and it is flushing each insert to system disk on the WAL file

Re: [sqlite] Slow INDEX

2011/11/2 Simon Slavin > > So compare your 'restart-and-INSERT' test with one where you restart, log > in, then do a few random operations for a minute: start your web browser > and load a page. Open and close a few documents or control panels. Sit > and do nothing for a

Re: [sqlite] Slow INDEX

On 2 Nov 2011, at 4:31pm, Fabian wrote: > 2011/11/2 Mr. Puneet Kishor > >> Others will have better answers, but methinks that when you reboot the >> computer, the operating system's caches are flushed out, which slows the >> operation. Try working with the db for a bit

Re: [sqlite] Slow INDEX

2011/11/2 Paul Corke > > A stateful antivirus that does lots of heavy processing when you first > open the file? > > I'm running these tests on a completey clean Win7 install, without any anti-virus (or other software) installed. I should note that it runs virtualized

Re: [sqlite] Slow INDEX

On 11/02/2011 12:31 PM, Fabian wrote: 2011/11/2 Mr. Puneet Kishor Others will have better answers, but methinks that when you reboot the computer, the operating system's caches are flushed out, which slows the operation. Try working with the db for a bit (SELECT, repeat

Re: [sqlite] Slow INDEX

On 02 November 2011 16:42, Fabian wrote: > Maybe there is a very simple explanation, I just can't think of any. A stateful antivirus that does lots of heavy processing when you first open the file? Have you tried: 1) Reboot 2) Wait 10 minutes (don't even touch the computer) 3) Test Paul.

Re: [sqlite] Slow INDEX

2011/11/2 Mr. Puneet Kishor > > ahh, so you *are* getting expected behavior, just not what *you* expected. > Did you have a different number in mind instead of a factor of 300? And, if > so, why? To read an (un-cached) 150mb file from disk, doesn't take 30 seconds. And

Re: [sqlite] Slow INDEX

On Nov 2, 2011, at 11:31 AM, Fabian wrote: > 2011/11/2 Mr. Puneet Kishor > >> >> Others will have better answers, but methinks that when you reboot the >> computer, the operating system's caches are flushed out, which slows the >> operation. Try working with the db for a

Re: [sqlite] Slow INDEX

I would like to stick my neck out over the chopping block and agree. My experience is the opposite, but appears to support Puneet's assertion. With me, it takes my C# application 12 seconds to pass 103,00 records and insert 98,000 rows into the db from it. The next time I run the application

Re: [sqlite] Slow INDEX

2011/11/2 Mr. Puneet Kishor > > Others will have better answers, but methinks that when you reboot the > computer, the operating system's caches are flushed out, which slows the > operation. Try working with the db for a bit (SELECT, repeat INSERTs, etc.) > and notice if the

Re: [sqlite] Slow INDEX

On Nov 2, 2011, at 11:24 AM, Fabian wrote: > Now if I re-open the database, I can add an additional 10.000 rows very > fast (<1 sec). But if I reboot the (Windows) PC, and insert an additional > 10.000 rows, it takes at least 30 secs, which seems very slow, if I can add > the first 1 million in

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

ChingChang Hsiao wrote: > I can't reply in my system, so I create the problem description again. > > I miss one source code line "char tempString[1024];"in the last email. The > code dump happened after 4 days' run in a test script not immediately. The > SQLITE statements seem to be ok. Could be

[sqlite] Slow INDEX

I asked a similar question before, but received no response, so maybe it's a very stupid question, but if so, feel free to say so. I create a database, create one table with a TEXT column, insert 1 million rows in 10 secs, create an index in 10 secs, VACUUM the database, and close the database.

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

[sqlite] Informal speaker in UK on SQLite

Hi I wondered if there were any savvy SQLite users in the UK that might be interested in speaking generally about the topic at the London Titanium Meetup http://www.meetup.com/London-Titanium group. Titanium Mobile leverages SQLIte for its database APIs and although we can and will be talking

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

Re: [sqlite] EXT : core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 3

On 02 November 2011 15:46, Black, Michael (IS) wrote: > You're corrupting the stack somewhere. Possibly, or it might be that libsqlite3.mgmt-crd.so just doesn't have all the necessary symbols. > You can enable stack checking if you're using gcc. Assuming the stack trace given was generated

Re: [sqlite] EXT : core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 3

You're corrupting the stack somewhere. You can enable stack checking if you're using gcc. -fstack-protector -fstack-protector-all Might help identify where it's actually happening. Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating

[sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 3

I can't reply in my system, so I create the problem description again. It seems it's not the problem of sprintf. If it comes from sprintf array size, the core dump will be like this " in __stack_chk_fail () from /lib/libc.so.6". The code dump happened after 4 days' run in a test script, not

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

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:

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,

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

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

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

Stephan Beal wrote: > On Tue, Nov 1, 2011 at 11:25 PM, Tal Tabakman wrote: > >> second,needless to say that I want to avoid this since it causes mem >> leaks.) >> > > Why would it leak? Are you intentionally NOT calling finalize()? > > >>sqlite3_prepare_v2(handle,

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

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 >

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

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

[sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond Take 2

I can't reply in my system, so I create the problem description again. I miss one source code line "char tempString[1024];"in the last email. The code dump happened after 4 days' run in a test script not immediately. The SQLITE statements seem to be ok. Could be a performance issue?

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

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

Re: [sqlite] core dump happened in sqlite3_step for 30 statements in BEGIN TRANSACTION executing every 10 Millisecond

On Wed, Nov 2, 2011 at 2:57 AM, Teg wrote: > What happens if you replace all the sprinfs with some simple inserts > inserted directly into vector? > > dbStatements.push_back("INSERT INTO..."); > Since you need integers and floats in your strings, you can use std::ostringstream

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

On Tue, Nov 1, 2011 at 11:25 PM, Tal Tabakman wrote: > second,needless to say that I want to avoid this since it causes mem > leaks.) > Why would it leak? Are you intentionally NOT calling finalize()? >sqlite3_prepare_v2(handle, query.str().c_str(), >