[sqlite] WAL file growth concern

> Are you *sure* you aren't accidentally holding a read transaction open > somewhere? > Do you have any other clues on how we can isolate the problem? A test case > that will we can run here, perhaps? A am pretty certain that I am not accidentally holding a read transaction open. I have my

Re: [sqlite] WAL file growth concern

On Thu, Oct 21, 2010 at 6:55 PM, Bob Smith wrote: > I have been using WAL mode for a few months and have been quite happy with > the write performance increases. > > I might possibly have found an issue/concern with the way sqlite handles > doing new writes to the WAL file

Re: [sqlite] [BUG] 1200x slow-down running query in 3.7.3

On Mon, Oct 18, 2010 at 9:18 AM, Peter wrote: > I have a query which takes 17 minutes to run with 3.7.3 against 800ms > with 3.7.2 > > explain query plan with 3.7.3: > 0 0 TABLE sheep AS s > 1 1 TABLE flock_owner AS prev WITH INDEX >

[sqlite] WAL file growth concern

I have been using WAL mode for a few months and have been quite happy with the write performance increases. I might possibly have found an issue/concern with the way sqlite handles doing new writes to the WAL file during a time that checkpoints are unable to checkpoint data from the WAL file

Re: [sqlite] programmatic way of determining fragmentation?

On Thu, Oct 21, 2010 at 1:27 PM, Dustin Sallings wrote: > > On Oct 21, 2010, at 10:05, Pavel Ivanov wrote: > > > I think it's not related to fragmentation, but to fill percentage of > > b-tree pages. I guess your reconstructed table is much less in total > > size than your

Re: [sqlite] programmatic way of determining fragmentation?

On Oct 21, 2010, at 10:05, Pavel Ivanov wrote: > I think it's not related to fragmentation, but to fill percentage of > b-tree pages. I guess your reconstructed table is much less in total > size than your initial one. Also does changing cache_size changes > above numbers? Interesting.

Re: [sqlite] programmatic way of determining fragmentation?

>        Select * from a table took just slightly under three hours. >        Select * from a reconstructed table (insert into select from) in a new > database took 57 seconds. I think it's not related to fragmentation, but to fill percentage of b-tree pages. I guess your reconstructed table is

Re: [sqlite] programmatic way of determining fragmentation?

On 21 Oct 2010, at 5:38pm, Dustin Sallings wrote: > On Oct 21, 2010, at 9:27, Simon Slavin wrote: > >> Have you actually demonstrated this ? In other words do you have an >> operation that's really 'too slow', but after a VACUUM it's fast enough ? > > Yes. > > Select * from a

Re: [sqlite] programmatic way of determining fragmentation?

On Oct 21, 2010, at 9:27, Simon Slavin wrote: > Have you actually demonstrated this ? In other words do you have an > operation that's really 'too slow', but after a VACUUM it's fast enough ? Yes. Select * from a table took just slightly under three hours. Select *

Re: [sqlite] Detach says database is locked

This type of thing works with SQLite 3.6.18. I have a similar issue with 3.7. I believe the difference is that in 3.6.18, if you do a select on a main database that doesn't involve an attached database, the attached database isn't locked. In 3.7, if you do a select on the main database that

Re: [sqlite] programmatic way of determining fragmentation?

On 21 Oct 2010, at 5:21pm, Dustin Sallings wrote: > Those provide some info, but not the specific info I'm having problems > with right now. I have too many non-sequential pages and it's making my > application run a couple of orders of magnitude slower than a fresh DB. Have you

Re: [sqlite] programmatic way of determining fragmentation?

On Oct 21, 2010, at 7:52, Roger Binns wrote: > You'll need to read the docs on the file format: > > http://www.sqlite.org/fileformat.html > http://www.sqlite.org/fileformat2.html > > - From that you can determine a measure of how bad the fragmentation is, and > your code can be quick and

Re: [sqlite] programmatic way of determining fragmentation?

On Oct 21, 2010, at 1:00, Kees Nuyt wrote: > PRAGMA page_count; and PRAGMA freelist_count; will give you > some info, but not as much as sqlite3_analyzer. > It might be enough in your case. Those provide some info, but not the specific info I'm having problems with right now. I have

Re: [sqlite] Detach says database is locked

On 21/10/10 00:35, Igor Tandetnik wrote: > Alan Chandler wrote: >> Further to my other post related to attaching to databases with PHP PDO, >> I have now managed to ATTACH OK >> >> However, when I come to DETACH, I am getting a Database is locked error >> when I try

Re: [sqlite] programmatic way of determining fragmentation?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 10/21/2010 12:32 AM, Dustin Sallings wrote: > Mostly, I want to have an idea how fragmented I am. You'll need to read the docs on the file format: http://www.sqlite.org/fileformat.html http://www.sqlite.org/fileformat2.html - From that

Re: [sqlite] (no subject)

On 21 Oct 2010, at 2:42pm, "" wrote: > please remove my name jbh...@bluefrog.com from the mailing list. Please click on the link included at the end of every message on this list. Simon. ___ sqlite-users

[sqlite] (no subject)

please remove my name jbh...@bluefrog.com from the mailing list. Thank you. 79 AE5IL John Houston www.rebuildinglostchurches.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Difference between times.

Thanks Simon, GREAT solution!!! A very interesting sintax. 2010/10/21 Simon Davies > On 21 October 2010 11:58, Danilo Cicerone wrote: > > Thanks Simon, but I've the following situation: > > > . > . > . > > The SQL query should be something like

Re: [sqlite] Difference between times.

On 21 October 2010 11:58, Danilo Cicerone wrote: > Thanks Simon, but I've the following situation: > . . . > The SQL query should be something like that: > > select case when dateStart <= '2010-01-21 00:00:00' and dateEnd >= > '2010-01-21 > 00:00:00' then > strftime('%s',

Re: [sqlite] Difference between times.

Thanks Simon, but I've the following situation: BEGIN TRANSACTION; CREATE TABLE t1( id integer primary key, dateStart text, -- '-MM-DD HH:MM:SS' dateEnd text -- '-MM-DD HH:MM:SS' ); INSERT INTO "t1" VALUES(1,'2010-01-20 18:00:00','2010-01-21 02:00:00'); COMMIT; and I'd like to know how

Re: [sqlite] Difference between times.

On 21 October 2010 11:03, Danilo Cicerone wrote: > Hi to all, > I'd like to calculate hours and minutes having the following situation where > A and B are query, B and C are the data stored in a table: ? B is query and data? > > ->Time > >  A          

[sqlite] Difference between times.

Hi to all, I'd like to calculate hours and minutes having the following situation where A and B are query, B and C are the data stored in a table: ->Time A B | | C D | | A = '2010-01-20 09:00:00' B =

Re: [sqlite] programmatic way of determining fragmentation?

On Thu, 21 Oct 2010 00:32:28 -0700, Dustin Sallings wrote: > > I realize sqlite3_analyzer will give me some good > data for general use, but I'd like to be able to > do this from within my app. Does anyone have a > lib-like thing I can use, or an internal sqlite API > that can

[sqlite] programmatic way of determining fragmentation?

I realize sqlite3_analyzer will give me some good data for general use, but I'd like to be able to do this from within my app. Does anyone have a lib-like thing I can use, or an internal sqlite API that can help me out here? Mostly, I want to have an idea how fragmented I am.