[sqlite] Same db, same query, very slow query performance in sqlite 3.7.0.1

Hi all, I have noticed a performance regression of 10 times in my db changing from version 3.6.32.1 to the 3.7.0.1.. It takes now 30 seconds against 3 seconds and query plans are different. Considering that table: customers have 80 records catalogues 6 records cronocatalogues 260

Re: [sqlite] Same db, same query, very slow query performance in sqlite 3.7.0.1

On Mon, Aug 16, 2010 at 4:46 AM, L L wrote: > Hi all, > I have noticed a performance regression of 10 times in my db changing from > version 3.6.32.1 to the 3.7.0.1.. It takes now 30 seconds against 3 seconds > and query plans are different. > Please try again with the

Re: [sqlite] Same db, same query, very slow query performance in sqlite 3.7.0.1

> And let us know if the problem persists. Perhaps this has been fixed by > >http://www.sqlite.org/src/info/e4b8a2ba6e > > Richard, I tried to investigate the problem L L posted in parallel. I think L L will report his results. From my tests in seem the snapshot has fixed the problem, but it

Re: [sqlite] Same db, same query, very slow query performance in sqlite 3.7.0.1

Yes, with the new source code of the snapshot, performance is very similar (about 3 seconds), even if query plan is different. --- On Mon, 8/16/10, Richard Hipp wrote: > From: Richard Hipp > Subject: Re: [sqlite] Same db, same query, very slow query

Re: [sqlite] Same db, same query, very slow query performance in sqlite 3.7.0.1

Sorry for my mistake, not only performance but also query plan of the snapshot are now the same of 3.6.23.1 --- On Mon, 8/16/10, Richard Hipp wrote: > From: Richard Hipp > Subject: Re: [sqlite] Same db, same query, very slow query performance in > sqlite

[sqlite] Record corruption on Mac OS X 10.6 (Snow Leopard)

Hello everyone. I have developed a conversion tool to generate a SQLite database from a MySQL one. The tool is a simple C Cocoa application in which I have statically compiled the amalgamation source of SQLite. The tool has a reading thread that pulls records from MySQL and a writing thread that

Re: [sqlite] Record corruption on Mac OS X 10.6 (Snow Leopard)

On Mon, Aug 16, 2010 at 9:33 AM, Dario Napolitano wrote: > Hello everyone. > I have developed a conversion tool to generate a SQLite database from a > MySQL one. The tool is a simple C Cocoa application in which I have > statically compiled the amalgamation source

[sqlite] .mode works for .import as well?

Hi, According to the man page, .mode works only for output mode. .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values .. However, it seems that it works for .import as well (see my

Re: [sqlite] EXTERNAL: Record corruption on Mac OS X 10.6 (Snow Leopard)

Can you post your code? In particular the place where it fails and you retry? Michael D. Black Senior Scientist Advanced Analytics Directorate Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org on behalf of Dario Napolitano Sent: Mon

[sqlite] Why the deadlock?

Hello, The script below fails with Deadlock detected when executing 'DELETE FROM foo WHERE id=2' What I think should be happening instead is this: - When executing statement 1, the main thread obtains a SHARED lock. - When executing statement 2, the main thread briefly obtains an

Re: [sqlite] Why the deadlock?

On Aug 17, 2010, at 1:48 AM, Nikolaus Rath wrote: > Hello, > > The script below fails with > > Deadlock detected when executing 'DELETE FROM foo WHERE id=2' > > What I think should be happening instead is this: > > - When executing statement 1, the main thread obtains a SHARED lock. > > - When

Re: [sqlite] EXTERNAL: Record corruption on Mac OS X 10.6 (Snow Leopard)

On 16 Aug 2010, at 5:54pm, Black, Michael (IS) wrote: > The SQLite > connection is created on the main thread and then handed off to the > writing thread which is the only one to use it. Where is the sqlite3_open command used ? Is it in the main thread or the writing thread ? Does the

Re: [sqlite] Why the deadlock?

Dan Kennedy writes: > On Aug 17, 2010, at 1:48 AM, Nikolaus Rath wrote: > >> Hello, >> >> The script below fails with >> >> Deadlock detected when executing 'DELETE FROM foo WHERE id=2' >> >> What I think should be happening instead is this: >>

Re: [sqlite] EXTERNAL: Why the deadlock?

I don't know if this is of any use...but it appears that there are some RD/WRLCKs that are not released. In particular 825 and 826 get read/write locks but I don't see them ever UNLCK them. They appear to be related to paging and I'm completely unsure as to how relevan this is. I

Re: [sqlite] .mode works for .import as well?

On Mon, Aug 16, 2010 at 11:53 AM, Peng Yu wrote: > Hi, > > According to the man page, .mode works only for output mode. > >       .mode MODE ?TABLE?     Set output mode where MODE is one of: >                                csv      Comma-separated values >                    

Re: [sqlite] .mode works for .import as well?

On 16 Aug 2010, at 9:44pm, Peng Yu wrote: > I guess that I misunderstood .mode csv. It seems that .import don't > treat double quote specially. So .import only can read file that has > some character (set by .separator) separates different fields? Correct. '.import' pays attention to

[sqlite] help with huge bulk inserts

Hello, I'm using sqlite as a data storage backend for my log parsing application. I have around 7 milion - equals to 1GB of binary log (up to 35 mln.) records to insert at once, I'm using prepared statments, huge transactions, and optimised (I hope) pragma settings: PRAGMA journal_mode = OFF;

Re: [sqlite] Why the deadlock?

Nikolaus Rath wrote: > What I think should be happening instead is this: > > - When executing statement 1, the main thread obtains a SHARED lock. > > - When executing statement 2, the main thread briefly obtains an > EXCLUSIVE lock. After statement 2 is executed, the

Re: [sqlite] Why the deadlock?

"Igor Tandetnik" writes: > Nikolaus Rath wrote: >> What I think should be happening instead is this: >> >> - When executing statement 1, the main thread obtains a SHARED lock. >> >> - When executing statement 2,

Re: [sqlite] [PATCH] cache preloading

ping Could you take a look at the original Chromium patch: http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup? Is it something you'd like to include in SQLite? If so, does the patch need any adjustments before that's possible? On Wed,

Re: [sqlite] [PATCH] cache preloading

On 17 Aug 2010, at 1:13am, Paweł Hajdan, Jr. wrote: > Could you take a look at the original Chromium patch: > http://src.chromium.org/viewvc/chrome/trunk/src/third_party/sqlite/preload-cache.patch?revision=26596=markup? > > Is it something you'd like to include in SQLite? If so, does the patch

Re: [sqlite] help with huge bulk inserts

On 17 Aug 2010, at 12:16am, Maciej Kurczewski wrote: > I'm using sqlite as a data storage backend for my log parsing application. > I have around 7 milion - equals to 1GB of binary log (up to 35 mln.) > records to insert at once, I'm using prepared statments, huge > transactions, and optimised