Re: [sqlite] Recommended tool to read SQLITE btree?

On 17 Feb 2018, at 12:34am, Deon Brewis wrote: > Anybody have a recommendation for a tool that can read/show/interpret a > SQLITE file at the BTREE level? You probably want one of these (listed in alphabetical order):

[sqlite] Recommended tool to read SQLITE btree?

Anybody have a recommendation for a tool that can read/show/interpret a SQLITE file at the BTREE level? Want to be able to decode the links between pages, figure out what all the data mean etc. And should be able to work on a corrupted file. - Deon

Re: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

On 02/16/2018 06:31 AM, David Pitchford wrote: I've almost finished debugging an issue in which a certain query was taking drastically longer for some versions of a database (a music library file) than for other, similarly sized versions. I have been using SQLite 3.8.2 for this since I don't

Re: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

On 15 Feb 2018, at 11:31pm, David Pitchford wrote: > So I'm wondering not just for this query, but in general, how can adding an > index cause a query plan to change even if the new query plan doesn't make > use of the new index? After creating indexes and

Re: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

On Fri, Feb 16, 2018 at 6:07 PM, David Raymond wrote: > Remember that the usefulness of an index depends on the ordering of the > fields. An index on (b, a) isn't useful if you're looking for a, it's only useful > if you're looking for b. > Sometimes it is. See

Re: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

A little hard to debug without the actual schema. You're giving us the query with a, b, c etc but the query plan with the real names. The slow plan is using an automatic index on CoreCache (ModelID), ie it's making a new index when it runs and then dumping the index at the end of the query.

Re: [sqlite] Question about threadsafe

THREADSAFE is about entrance requirements and has nothing whatsoever to do with reading/writing or transactions. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users

Re: [sqlite] Need some tips on using FTS5 with SQLite

On Wed, 14 Feb 2018 14:26:21 + Wout Mertens wrote: > I too am interested in this answer, I still have to start using fts5. > > What would be interesting is to see the `EXPLAIN QUERY PLAN [query]` for > each of your queries, so as to see what causes the slowness. >

Re: [sqlite] Question about threadsafe

Yes. If you are starting a transaction in which you know you are going to write use BEGIN IMMEDIATE. Plain BEGIN (BEGIN DEFERRED) starts a READ transaction and only attempts to upgrade to a write transaction when you try to write. Since a read transaction in WAL works with a version of the

[sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index?

I've almost finished debugging an issue in which a certain query was taking drastically longer for some versions of a database (a music library file) than for other, similarly sized versions. I have been using SQLite 3.8.2 for this since I don't feel up to try replacing the version that came with

Re: [sqlite] [Legacy Email] Re: Static sqlite3 library for Linux

Greetings Reid and thank you for your comment! I have followed this procedure by running: ./configure --prefix=/usr/local/sqlite --enable-static --enable-fts5 --enable-json1 CFLAGS="-g -O2 -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_RTREE=1” make make install Unfortunately,

Re: [sqlite] [Legacy Email] Re: Static sqlite3 library for Linux

Greetings Reid and thank you for your comment! I have followed this procedure by running: ./configure --prefix=/usr/local/sqlite --enable-static --enable-fts5 --enable-json1 CFLAGS="-g -O2 -DSQLITE_ENABLE_FTS3=1 -DSQLITE_ENABLE_FTS4=1 -DSQLITE_ENABLE_RTREE=1” make make install Unfortunately,

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

Hello, On 2018-02-16 13:00, Dominique Devienne wrote: While you're technically right, I think of SQL as a declarative language, and as such I'd say giving this information to SQLite is a best practice IMHO. Unlikely in this case, but perhaps one day SQLite might be able to optimize "something"

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

I suppose ‘select * from (original select with limit clause) limit :lim’ gets round it. From: Dominique Devienne Sent: 16 February 2018 12:21 To: SQLite mailing list Subject: Re: [sqlite] LIMIT versus sqlite3_step LIMIT

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 1:11 PM, x wrote: > Thanks for the replies. For my purpose it was about avoiding the > possibility of having to apply a limit to a query that might already have a > limit clause. Good point. I tried, and indeed that's an issue. I really really

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

Thanks for the replies. For my purpose it was about avoiding the possibility of having to apply a limit to a query that might already have a limit clause. From: Dominique Devienne Sent: 16 February 2018 12:00 To: SQLite mailing

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

On Fri, Feb 16, 2018 at 12:33 PM, Cezary H. Noweta wrote: > On 2018-02-16 11:18, x wrote: > >> If a query is sorted on an index is there any advantage to including >> LIMIT in the stmt as opposed to omitting it and stepping through the result >> set LIMIT times? >> > > No --

Re: [sqlite] LIMIT versus sqlite3_step LIMIT times

Hello, On 2018-02-16 11:18, x wrote: If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times? No -- LIMIT appends an additional opcode to check the number of rows and introduces an

[sqlite] LIMIT versus sqlite3_step LIMIT times

If a query is sorted on an index is there any advantage to including LIMIT in the stmt as opposed to omitting it and stepping through the result set LIMIT times? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org

Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

I am not familiar with System.Data.SQLite or what the pooling parameters make it do. Starting a transaction and then going away for a cup of coffee will definitely make any busy timeout expire. This should be avoided. -Ursprüngliche Nachricht- Von: sqlite-users

Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

Thank you for your answer. I am currently using connection time pooling using " Pooling=True; Min Pool Size=1; Max Pool Size=100;". The timeout is set to 30 seconds and none of the operations should take more than a second.. It is confusing as in a demo project I do get an exception showing

Re: [sqlite] [EXTERNAL] "Database is locked" in diagnostic tools C#

The "database is locked" message means that the transaction was unable to complete within the designated busy timeout and was therefore rolled back. Getting this message implies that you are running multiple processes. Depending on the journal mode, a writer process may block both readers and

[sqlite] "Database is locked" in diagnostic tools C#

Hi everyone, I have managed to successfully replace SQLCE with System.Data.SQLite in a mid-size application. Everything works fine but now that I started optimizing the old code and replaced own connection pooling with SQLite connection pooling I am getting an occasional Program Output