Re: [sqlite] Query plan gone haywire lays waste to my library's performance

oʎ ɟı > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Simon Slavin > Sent: Friday, 28 April, 2017 15:00 > To: SQLite mailing list > Subject: Re: [sqlite] Query plan gone haywire lays waste to my library's > perfor

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On 4/28/2017 4:37 PM, Jens Alfke wrote: CREATE TABLE docs (doc_id INTEGER PRIMARY KEY, docid TEXT UNIQUE NOT NULL, expiry_timestamp INTEGER); CREATE INDEX docs_docid ON docs(docid); For the record, this index is redundant. There's already an automatically created index on docs(docid), thanks

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On 28 Apr 2017, at 10:23pm, Jens Alfke wrote: > On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > >> What indexes do you have on these two tables ? I can’t recommend one >> without knowing which columns belong to which tables. > > I showed them at the end of my first message. Sorry, I mis

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

> On Apr 28, 2017, at 2:00 PM, Simon Slavin wrote: > > What indexes do you have on these two tables ? I can’t recommend one without > knowing which columns belong to which tables. I showed them at the end of my first message. > First query rewritten for clarity: > […] Should be more like >

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

On 28 Apr 2017, at 9:49pm, Jens Alfke wrote: > SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs, docs > WHERE sequence>? AND current!=0 AND deleted=0 AND revs.doc_id = docs.doc_id > ORDER BY revs.doc_id, deleted, revid DESC > 0 0 0 SCAN TABLE revs USING INDEX revs_by_docid_re

Re: [sqlite] Query plan gone haywire lays waste to my library's performance

Sorry for the quick follow-up, but I’ve just run a quick test that does an EXPLAIN QUERY PLAN for every statement that gets compiled, in an empty database, and confirmed that the query plan comes out wrong right from the start: SELECT revs.doc_id, sequence, docid, revid, json, deleted FROM revs

[sqlite] Query plan gone haywire lays waste to my library's performance

I’ve got an urgent-to-me issue wherein a customer says that our library has slowed down by several orders of magnitude in iOS 10.3 compared to iOS 9. I ran a test case they provided and found that some queries that should be fast are taking a very long time (~500ms) and generating huge numbers o

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

G.)" , "Natanam, Karthigeyan (K.)" Subject: Re: [sqlite] QUERY: Usage of malloc and free in SQLite Hi Dan, Custom memory management APIs registered are being used by SQLite. We suspect SQLite, in certain cases is freeing allocated memory, not using the registered API to free. A

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

Anthrathodiyil, Sabeel (S.) Gesendet: Freitag, 24. März 2017 12:19 An: SQLite mailing list Cc: Subramaniyan, Ganesan (G.) ; Natanam, Karthigeyan (K.) Betreff: Re: [sqlite] QUERY: Usage of malloc and free in SQLite Hi Dan, Custom memory management APIs registered are being used by SQLite. We suspect

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

@mailinglists.sqlite.org Subject: Re: [sqlite] QUERY: Usage of malloc and free in SQLite On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote: > Hi, > We are facing dynamic memory pool corruption issue while using SQLite. Based > on our investigation we suspect SQLite freeing memory bypassing t

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

On Thu, Mar 23, 2017 at 9:01 AM, Dan Kennedy wrote: > On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote: > >> Hi, >> We are facing dynamic memory pool corruption issue while using SQLite. >> Based on our investigation we suspect SQLite freeing memory bypassing the >> memory management API'

Re: [sqlite] QUERY: Usage of malloc and free in SQLite

On 03/23/2017 10:00 PM, Subramaniyan, Ganesan (G.) wrote: Hi, We are facing dynamic memory pool corruption issue while using SQLite. Based on our investigation we suspect SQLite freeing memory bypassing the memory management API'S registered as below. We have registered the memory allocation r

[sqlite] QUERY: Usage of malloc and free in SQLite

Hi, We are facing dynamic memory pool corruption issue while using SQLite. Based on our investigation we suspect SQLite freeing memory bypassing the memory management API'S registered as below. We have registered the memory allocation routines as below. static const sqlite3_mem_methods mqxmem =

Re: [sqlite] Query default lookaside pool size

On Wed, Mar 8, 2017 at 2:41 PM, Simon Slavin wrote: > > On 8 Mar 2017, at 11:09am, Kim Gräsman wrote: > >> Is there a way to query SQLite build parameters at runtime, more >> specifically SQLITE_DEFAULT_LOOKASIDE? > > Ah. Love it, thank

Re: [sqlite] Query default lookaside pool size

On 8 Mar 2017, at 11:09am, Kim Gräsman wrote: > Is there a way to query SQLite build parameters at runtime, more > specifically SQLITE_DEFAULT_LOOKASIDE? Simon. ___ sqlite-users mailing list

[sqlite] Query default lookaside pool size

Hi all, Is there a way to query SQLite build parameters at runtime, more specifically SQLITE_DEFAULT_LOOKASIDE? Thanks, - Kim ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqli

Re: [sqlite] SQLite Query truncating String column at 255 chars long

On Fri, Jan 27, 2017 at 2:51 PM, Warren Young wrote: > > > There’s no obfuscated Perl contest because it’s pointless. > > Perl is not pointless: > ​True. I love it. But most Perl code is, by design, already rather obscure. Some of the "idioms​" used (like slurping input and then doing a single r

Re: [sqlite] SQLite Query truncating String column at 255 chars long

On 2017/01/27 10:51 PM, Warren Young wrote: Perl is not pointless: http://search.cpan.org/~markov/Geo-Point-0.96/lib/Geo/Point.pod Without Geometry Life is pointless. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http

Re: [sqlite] SQLite Query truncating String column at 255 chars long

On Jan 27, 2017, at 12:09 PM, John McKown wrote: > > On Fri, Jan 27, 2017 at 12:58 PM, Antonio Carlos Jorge Patricio < > antonio...@gmail.com> wrote: > >> In my tests, they got almost all chopped at 255 chars long >> > ​I'm guessing this is a problem with SQLite.NET I don’t see any 255 or 256

Re: [sqlite] SQLite Query truncating String column at 255 chars long

> On Jan 27, 2017, at 11:09 AM, John McKown > wrote: > > ​I'm guessing this is a problem with SQLite.NET , or .NET > itself, which is > not supported here. …or with Visual BASIC. (It looks like the code the OP posted is BASIC, judging by the ‘Dim’ statement, which I dim-l

Re: [sqlite] SQLite Query truncating String column at 255 chars long

On Fri, Jan 27, 2017 at 12:58 PM, Antonio Carlos Jorge Patricio < antonio...@gmail.com> wrote: > I have a table in a SQLite 3 database file (.db3), which has a TEXT column > whose fields often can be 1024 chars long or more. > > My application connects to this file using SQLite.NET library (from N

[sqlite] SQLite Query truncating String column at 255 chars long

I have a table in a SQLite 3 database file (.db3), which has a TEXT column whose fields often can be 1024 chars long or more. My application connects to this file using SQLite.NET library (from Nuget) and runs a simple "SELECT * FROM tabRxBinder;" command to populate a DataTable object. Inside th

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> Why don't you just explicitly sort by bar.foo? > > > sqlite> EXPLAIN QUERY PLAN SELECT bar.foo as id, foo.baz FROM bar CROSS JOIN > foo ON bar.foo = foo.id ORDER BY bar.foo LIMIT 10, 10; > 0|0|0|SCAN TABLE bar > 0|1|1|SEARCH TABLE foo USING INTEGER PRIMARY KEY (rowid=?) > sqlite> I have expla

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Dimitris From: sqlite-users on behalf of Nico Williams Sent: Thursday, November 17, 2016 4:32 PM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On Thu, Nov 17, 2016 at 04:48:2

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 04:48:20PM +0200, Paul wrote: > Replacing JOIN does not help either: > > sqlite> EXPLAIN QUERY PLAN SELECT foo.id FROM bar JOIN foo ON bar.foo = > foo.id ORDER BY id DESC LIMIT 0, 40; > selectidorder fromdetail >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On Thu, Nov 17, 2016 at 09:54:01AM -0500, Richard Hipp wrote: > Standard SQL requires an implied NOT NULL on all PRIMARY KEY columns. > But due to a coding error, early versions of SQLite did not enforce > that, and so we have taken care not to enforce it on all subsequent > versions of SQLite to p

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Paul wrote: > > > >> On 11/17/16, Richard Hipp wrote: > >> > On 11/17/16, Paul wrote: > >> >> That's why there was a LEFT JOIN in the first place, but as it seems, > >> >> it > >> >> wasn't that good idea. > >> > > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. Th

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Yeah.. I know that. Missed the PRIMARY KEY. :( Back to lurking. ;) -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:54 AM To: SQLite mailing list Subject: Re: [sqlite] Query

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Paul wrote: > >> On 11/17/16, Richard Hipp wrote: >> > On 11/17/16, Paul wrote: >> >> That's why there was a LEFT JOIN in the first place, but as it seems, >> >> it >> >> wasn't that good idea. >> > >> > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query >> > planner

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Richard Hipp wrote: > > On 11/17/16, Paul wrote: > >> That's why there was a LEFT JOIN in the first place, but as it seems, it > >> wasn't that good idea. > > > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > > planner in SQLite will not reorder a CROSS JOI

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Richard Hipp wrote: > On 11/17/16, Paul wrote: >> That's why there was a LEFT JOIN in the first place, but as it seems, it >> wasn't that good idea. > > Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query > planner in SQLite will not reorder a CROSS JOIN. But, as it t

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Paul wrote: > That's why there was a LEFT JOIN in the first place, but as it seems, it > wasn't that good idea. Try using CROSS JOIN instead of just JOIN or LEFT JOIN. The query planner in SQLite will not reorder a CROSS JOIN. -- D. Richard Hipp d...@sqlite.org ___

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

t; To: SQLite mailing list > Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy > when '=' condition gives a strong hint > > On 11/17/16, Marc L. Allen wrote: >> Maybe I'm missing something, but... >> >> ORDER BY id >> >

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

> On 11/17/16, Marc L. Allen wrote: > > Maybe I'm missing something, but... > > > > ORDER BY id > > > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > > to find enough records where the LEFT

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

Behalf Of Richard Hipp Sent: Thursday, November 17, 2016 9:32 AM To: SQLite mailing list Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint On 11/17/16, Marc L. Allen wrote: > Maybe I'm missing something, but... > &

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

On 11/17/16, Marc L. Allen wrote: > Maybe I'm missing something, but... > > ORDER BY id > > Is ordering by the ID the right-hand side of a LEFT join. As such, it > depends on how NULL factors into an ORDER BY. If NULL comes first, it has > to find enough records where the LEFT join fails. > > Ye

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

something. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Thursday, November 17, 2016 8:53 AM To: 'SQLite mailing list' Subject: Re: [sqlite] Query Planner fails to recognise efficient strategy when '='

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

operators > > -Ursprüngliche Nachricht- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > Auftrag von Paul > Gesendet: Donnerstag, 17. November 2016 13:58 > An: General Discussion of SQLite Database > Betreff: [sqlite] Query Planner fails to recognis

Re: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Paul Gesendet: Donnerstag, 17. November 2016 13:58 An: General Discussion of SQLite Database Betreff: [sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

[sqlite] Query Planner fails to recognise efficient strategy when '=' condition gives a strong hint

These are the queries: CREATE TABLE foo( idINTEGER, baz INTEGER, PRIMARY KEY(id) ); CREATE TABLE bar( foo INTEGER, PRIMARY KEY(foo), FOREIGN KEY(foo) REFERENCES foo(id) ON DELETE CASCADE ); EXPLAIN QUERY PLAN SELECT foo.id, baz FROM bar LEFT JOIN foo ON

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Laura, On Thu, Sep 8, 2016 at 5:22 AM, Laura BERGOENS wrote: > Hi everyone, > > So I've put indexes myself on the most used tables in my program, then > ANALYZE the db, and now it flies. > The automatic creation of index probably took a lot of time, and it affects > even more performance when it'

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi everyone, So I've put indexes myself on the most used tables in my program, then ANALYZE the db, and now it flies. The automatic creation of index probably took a lot of time, and it affects even more performance when it's always same queries on same tables that are repeated over and over again

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 8 Sep 2016, at 7:27am, Laura BERGOENS wrote: > I took notes of everything you guys said, and I'll spend my morning doing > this : > > Running ANALYZE once per DB I'mUsing, Create indexes myself and contact DB > Browser to share them the thing. Do those two steps the other way around. ANALY

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On > Behalf Of Chris Locke > Sent: Wednesday, September 7, 2016 12:12 PM > To: SQLite mailing list > Subject: Re: [sqlite] Query time execution difference between my > application > and SQLiteBrowser > > &

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Chris Locke Sent: Wednesday, September 7, 2016 12:12 PM To: SQLite mailing list Subject: Re: [sqlite] Query time execution difference

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Please reply if you sent this. Thanks. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Laura BERGOENS Sent: Wednesday, September 7, 2016 11:15 AM To: SQLite mailing list Subject: Re: [sqlite] Query time execution difference

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

>First of all, I'll check all the pragmas and stuff, plus the version of > SQliteBrowser (former DB Browser for SQlite indeed) I'm using, etc. Just to confirm (as it seems to be overlooked) that SQLite Browser (actually now called DB Browser for SQLite, rather than formerly...) is a 3rd party tool

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Ok I didn't know abount ANALYZE existence, sorry for that. I just run ANALYZE then the query again in sqlite3.exe and this time it took like half a second. Should I close and re open the DB, cause maybe this result was influenced with some cache system or something? Plus, I ran PRAGMA compile_opt

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

ists.sqlite.org] On Behalf Of Laura BERGOENS Sent: Wednesday, September 07, 2016 1:09 PM To: SQLite mailing list Subject: Re: [sqlite] Query time execution difference between my application and SQLiteBrowser quote:"Now, you need to create a primary/foreign keys and indexes on the tables you ar

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

I haven't gone through the previous emails to see whether anyone has proposed this, but it seems possible to me that the GUI tool (oriented as it is to interaction) is not waiting for the query to finish before displaying the first results. On Wednesday, September 7, 2016 7:21 AM, Laura BE

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 4:36pm, Laura BERGOENS wrote: > I've put ANALYZE in my code before running this SELECT query, I have no > change, and now on sqlite3.exe it runs kind of fast every time, even if i > close and re open the DB, without ANALYZE statement. Good. If should now run quickly in your o

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

quote : "Is there a reason why you can't create a copy of the database and work with a copy?" I never said I couldn't, and if I misled you I'm sorry. Of course I can play with the DB, since I'm in a dev environment, and I'm open to any alteration on the tables. I conceed I'm not giving away a lot

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi, Laura, On Wed, Sep 7, 2016 at 12:25 PM, Laura BERGOENS wrote: > Hi Dominique, > > I guess it's not, but I'm not quite sure on what I can share, I'm a newbie > in a professional environment. I would be more confortable if I ask my boss > first. In the meantime, all I can say is that there are

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Dominique, I guess it's not, but I'm not quite sure on what I can share, I'm a newbie in a professional environment. I would be more confortable if I ask my boss first. In the meantime, all I can say is that there are no primary keys nor foreign keys in the table, and no indexes at all. I activ

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On Wed, Sep 7, 2016 at 6:00 PM, Laura BERGOENS wrote: > Then I create real tables in the in-memory clone that contain the content > of the views : INSERT INTO tableA SELECT * from viewA > What matters is how you create the tables, and in particular what primary key and indexes you use on them.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Igor, I'm working on providing those data, meanwhile I have to emphasize on how those tables are created, maybe there is something here : The idea is that i run the application using a given database. I want this database to be untouched as for its data, so i'm making a clone of it in memory.

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi, Laura, On Wed, Sep 7, 2016 at 10:48 AM, Laura BERGOENS wrote: > Note : I had some real queries that use to take 100 seconds to execute, and > I optimized them myself. > It looks like this : > > I have 4 tables: tableA, tableLink, tableC and tableD > tableA, Link and C have no more than 10k r

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

I've put ANALYZE in my code before running this SELECT query, I have no change, and now on sqlite3.exe it runs kind of fast every time, even if i close and re open the DB, without ANALYZE statement. 2016-09-07 17:15 GMT+02:00 Laura BERGOENS : > Ok I didn't know abount ANALYZE existence, sorry for

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 3:48pm, Laura BERGOENS wrote: > This query takes 100 seconds approx. Once your tables have some convincing data in (does not need to be final data, just something useful to see how the values are distributed), run "ANALYZE", just once. It might speed up later SELECTs. It m

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Note : I had some real queries that use to take 100 seconds to execute, and I optimized them myself. It looks like this : I have 4 tables: tableA, tableLink, tableC and tableD tableA, Link and C have no more than 10k rows in it, and tableD around 50k (which is not big at all right?) The query goe

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Smith, Quote :"(though, SQLite successive iterations usually become faster, not slower, apart from compile-time options that may be different)." That's what concerns me as well, but since I'm a recent user of SQlite type database and sqlite3 I prefer blaming me for those differences. Sinc

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 2016/09/07 4:20 PM, Laura BERGOENS wrote: Hi Mr. Slavin, As for why the query takes so long, I do know the answer ! Long story short, my application does a lot of calculation and things, I don't want to get into the details here, but queries are built and auto-generated piece by piece. There

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 2016/09/07 2:53 PM, Laura BERGOENS wrote: Hello everyone, I tried to get some answers using the mailing list archives, but you guys have been communicating so much on this, it's hard to run through everything that was written ;) Basically, I'm using sqlite3 in my C application. I believe t

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Slavin, As for why the query takes so long, I do know the answer ! Long story short, my application does a lot of calculation and things, I don't want to get into the details here, but queries are built and auto-generated piece by piece. Therefore, sometimes the queries aren't optimized at

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 9/7/16, Simon Slavin wrote: > > On 7 Sep 2016, at 3:10pm, Laura BERGOENS wrote: > >> I guess I don't have further question regarding my issue, since I have the >> same time execution as with the sqlite3.exe tool, so there are no problems >> with my pragmas or whatsoever. > > Nevertheless, 100

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 7 Sep 2016, at 3:10pm, Laura BERGOENS wrote: > I guess I don't have further question regarding my issue, since I have the > same time execution as with the sqlite3.exe tool, so there are no problems > with my pragmas or whatsoever. Nevertheless, 100 seconds is a very long time for a query, a

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

Hi Mr. Hipp, First of all thanks for replying. As you recommend, I downloaded the sqlite3 executable (I'm on Debian). Just out of curiosity, I've ran both the "SELECT sqlite_source_id()" query and a long query i have on 3 systems, SQliteBrowser, sqlite3.exe and my app, here is what i get, it's... i

Re: [sqlite] Query time execution difference between my application and SQLiteBrowser

On 9/7/16, Laura BERGOENS wrote: > Hello everyone, > > I tried to get some answers using the mailing list archives, but you guys > have been communicating so much on this, it's hard to run through > everything that was written ;) > > Basically, I'm using sqlite3 in my C application. I believe the

[sqlite] Query time execution difference between my application and SQLiteBrowser

Hello everyone, I tried to get some answers using the mailing list archives, but you guys have been communicating so much on this, it's hard to run through everything that was written ;) Basically, I'm using sqlite3 in my C application. I believe the tool SQLiteBrowser is using it as well. I not

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Clemens Regards, 2. Sorting the entries before LIMIT is applied. 1. Sorting the entries before group_concat() is applied; or Chris Locke wrote: > Whats the benefit of getting a sorted query and then sorting that query > again? > > On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski > wrote:

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Whats the benefit of getting a sorted query and then sorting that query again? On Tue, Jul 12, 2016 at 12:45 AM, Stephen Chrzanowski wrote: > Simons + My answer; > > select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp > DESC LIMIT 2) a order by date_time_stamp; > > On Mon

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Thanks for all of the responses. Duplicates are OK, the date time stamps are part of a log file that I am trying to develop a query for. I couldn't get the LIMIT/OFFSET part of the query right after several attempts so I thought I'd ask the experts here. I appreciate your replies and suggestions.

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

SELECT DISTINCT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2 isn't it simply to use DISTINCT? On Mon, Jul 11, 2016 at 4:25 PM, Keith Christian wrote: > A table has a column of dates and times that look like this: > > 2015-10-02 07:55:02 > 2015-10-02 07:55:02 > 2015-10-02 1

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

On 2016/07/12 1:25 AM, Keith Christian wrote: A table has a column of dates and times that look like this: 2015-10-02 07:55:02 2015-10-02 07:55:02 2015-10-02 10:00:03 2015-10-02 10:05:02 2015-10-02 10:10:02 Schema: CREATE TABLE general ( id integer primary key autoincrement, server text, dat

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

Simons + My answer; select * from (SELECT date_time_stamp FROM general ORDER BY date_time_stamp DESC LIMIT 2) a order by date_time_stamp; On Mon, Jul 11, 2016 at 7:33 PM, Simon Slavin wrote: > > On 12 Jul 2016, at 12:25am, Keith Christian > wrote: > > > A table has a column of dates and times

Re: [sqlite] Query question: order by ascending, return the two largest values in ascending order

On 12 Jul 2016, at 12:25am, Keith Christian wrote: > A table has a column of dates and times that look like this: > > 2015-10-02 07:55:02 > 2015-10-02 07:55:02 > 2015-10-02 10:00:03 > 2015-10-02 10:05:02 > 2015-10-02 10:10:02 > > > Schema: > CREATE TABLE general ( id integer primary key autoi

[sqlite] Query question: order by ascending, return the two largest values in ascending order

A table has a column of dates and times that look like this: 2015-10-02 07:55:02 2015-10-02 07:55:02 2015-10-02 10:00:03 2015-10-02 10:05:02 2015-10-02 10:10:02 Schema: CREATE TABLE general ( id integer primary key autoincrement, server text, date_time_stamp text); Would like to get the latest

Re: [sqlite] Query Flattener vs. result-set column names

Simon Slavin wrote: > On 7 Jul 2016, at 3:37pm, Josef Kučera wrote: >> Imagine a query like "SELECT A.F1, B1.F FROM A JOIN (SELECT F2 AS F FROM B >> WHERE F3=0) B1". > > your natural JOIN is a little dangerous since it can collapse if you > change column names or definitions. A natural join woul

Re: [sqlite] Query Flattener vs. result-set column names

-boun...@mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Josef Kucera Gesendet: Donnerstag, 07. Juli 2016 17:42 An: SQLite mailing list Betreff: Re: [sqlite] Query Flattener vs. result-set column names - Original Message - From: "Simon S

Re: [sqlite] Query Flattener vs. result-set column names

- Original Message - From: "Simon Slavin" To: "SQLite mailing list" Sent: Thursday, July 07, 2016 5:11 PM Subject: Re: [sqlite] Query Flattener vs. result-set column names On 7 Jul 2016, at 3:37pm, Josef Kučera wrote: Imagine a query like "SELECT A.F1,

Re: [sqlite] Query Flattener vs. result-set column names

On 7 Jul 2016, at 3:37pm, Josef Kučera wrote: > Imagine a query like "SELECT A.F1, B1.F FROM A JOIN (SELECT F2 AS F FROM B > WHERE F3=0) B1". If the query flattener is active the result-set has columns > "A.F1" and "B1.F". If it is disabled the result-set columns are "F1" and "F". > The "shor

[sqlite] Query Flattener vs. result-set column names

Hello, today I have discovered a strange side-effect of a query flattener. Imagine a query like "SELECT A.F1, B1.F FROM A JOIN (SELECT F2 AS F FROM B WHERE F3=0) B1". If the query flattener is active the result-set has columns "A.F1" and "B1.F". If it is disabled the result-set columns are "F1"

[sqlite] Query optimizer not doing so well with my queries

Hi, I'm a big fan of SQLite, but I've been having some terrible speed problems when doing joins between subqueries. Strangely, if I join twice (first to the main table and then to its subquery), I can get the same answer in tenths of seconds and not minutes - or one thousand times faster. For ex

[sqlite] Query optimizer not doing so well with my queries

Richard Warburton wrote: > For example, this takes minutes: CPU Time: user 263.759513 sys 2.237379 > SELECT s.Id, s.BookingId, s.At, s.DateIn, s.DateOut, s.RoomUID, > s.RatePlanUID FROM StayV s > WHERE NOT EXISTS (SELECT NULL FROM InvoiceItemV i WHERE i.PriceUID IS NULL > AND i.StayUID=s.UID); > >

[sqlite] Sqlite Query Builder

Hi Does anybody know of a visual query builder which can be used on Linux to create syntactically-correct views/queries for a Sqlite database? I'm thinking of something along the lines of the query builder in MS Access or LibreOffice Base. Thanks Nige

[sqlite] query Benchmark

On Mon, 15 Feb 2016 10:57:29 +0100 Michele Pradella wrote: > Sorry you are right, the test I did was with % not with a *just a > cut and paste error > Anyway it's tricky because I have to tell sqlite which index to use > in LIKE to fast search but I do not have to tell the index if start > w

[sqlite] query Benchmark

Anyway thank you Simon the point of view it's clear now Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.pradella at selea.com* *http://www.selea

[sqlite] query Benchmark

Sorry you are right, the test I did was with % not with a *just a cut and paste error Anyway it's tricky because I have to tell sqlite which index to use in LIKE to fast search but I do not have to tell the index if start with % or _ because otherwise the query is not executed.. I'll handle

[sqlite] query Benchmark

Probably is documented so I'm going to read carefully, anyway if you do this CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName VARCHAR(255),Plate VARCHAR(255)); CREATE INDEX car_plates_plate on car_plates(Plate); PRAGMA case_sensitive_like=ON; explain query plan SELECT * FRO

[sqlite] query Benchmark

Hello MP> Anyway it's tricky because I have to tell sqlite which index to use in MP> LIKE to fast search but I do not have to tell the index if start with MP> % or _ because otherwise the query is not executed.. I'll handle it... I'm not sure if other people have suggested it already, but you can

[sqlite] query Benchmark

On 15 Feb 2016, at 9:42am, Michele Pradella wrote: > CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,FileName > VARCHAR(255),Plate VARCHAR(255)); > CREATE INDEX car_plates_plate on car_plates(Plate); > PRAGMA case_sensitive_like=ON; > > explain query plan SELECT * FROM car_plate

[sqlite] query Benchmark

On 2016/02/12 6:38 PM, Michele Pradella wrote: > Already solved with UNION of SELECT > It isn't solved, it is circumvented by trial and error without understanding why. I'm sure that works ok for you in this case, but the point is if you do study those documents a bit more, you may grasp the

[sqlite] query Benchmark

etween two > queries(example cache)? One way to look at performance of an SQLite query is to say that it is influenced by three factors: the amount of IO, the number and size of malloc() calls made and the CPU consumed by the library to run the query. Adding the -stats option to the shell tool c

[sqlite] query Benchmark

-users at mailinglists.sqlite.org> ??: Re: [sqlite] query Benchmark I'm already using my software. The scope of test my query with sqlite shell is to have a test environment without other interaction, just to test witch is the fast version of my query to use in my software. Another question is, if I

[sqlite] query Benchmark

Already solved with UNION of SELECT Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.pradella at selea.com* *http://www.selea.com* Il 12/02/2016

[sqlite] query Benchmark

Splitting query in 2 SELECT using UNION let me use car_plates_plate index without problemvery strange...but I found a walkaround Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.p

[sqlite] query Benchmark

Why this query PRAGMA case_sensitive_like=ON;EXPLAIN QUERY PLAN SELECT * FROM car_plates INDEXED BY car_plates_plate WHERE ((CarPlateType!=-1)AND((Plate LIKE 'AA00O%')OR(Plate LIKE 'AA0O0%')))OR((CarPlateType==-1)AND((Plate LIKE '~A00O%'))) give me "Error: no query solution" if I remove the sec

[sqlite] query Benchmark

the strange thing is that if you create CREATE TABLE car_plates (Id INTEGER PRIMARY KEY AUTOINCREMENT,DateTime BIGINT,StringKey VARCHAR(255) UNIQUE,Plate VARCHAR(255),Type BIGINT,CameraName VARCHAR(255),CameraIP VARCHAR(255),CameraMAC VARCHAR(255),FileName VARCHAR(255),Country VARCHAR(255),Conf

[sqlite] query Benchmark

No...I do not understand, try dump file and re-import but the result it's always the same even if I use PRAGMA case_sensitive_like=ON; the query PRAGMA case_sensitive_like=ON; EXPLAIN QUERY PLAN SELECT DateTime,FileName,Plate,Type,CameraName,Id,Country,Reason,CarPlateType,VehicleType,GPS FROM

[sqlite] query Benchmark

ok, assume casr_sensitive_like=OFF (default), according the point 6 of LIKE optimization: http://www.sqlite.org/optoverview.html should be the same to have my table definition and CREATE INDEX IF NOT EXISTS car_plates_plate ON car_plates(Plate COLLATE NOCASE); Correct? because that way LIKE do n

<    1   2   3   4   5   6   7   8   9   10   >