Re: [sqlite] How to optimise a somewhat-recursive query?

On 2018-03-22 12:03, Richard Hipp wrote: On 3/21/18, Jonathan Moules wrote: I've spent the last ~90 minutes trying to build this but to no avail The query planner enhancements are now available in the pre-release snapshot on the https://sqlite.org/download.html

Re: [sqlite] How to optimise a somewhat-recursive query?

On 3/21/18, Jonathan Moules wrote: > I've spent the last ~90 minutes trying to build this but to no avail The query planner enhancements are now available in the pre-release snapshot on the https://sqlite.org/download.html page. -- D. Richard Hipp d...@sqlite.org

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 10:58pm, Jonathan Moules wrote: > I know SQLite has a CLI tool, but I'm happier with front-ends You can use the CLI tool (which does still work under Windows 7) to open the database you prepared in your preferred environment and execute just

Re: [sqlite] How to optimise a somewhat-recursive query?

Hi Richard, I've spent the last ~90 minutes trying to build this but to no avail I'm afraid. I'm unable to find a version of nmake for Windows 7 (It seems to be a VS thing and that in turn is Windows 8/10 only). Then I tried inside a VM of Mint, managed to build it, and whilst I was trying to

Re: [sqlite] How to optimise a somewhat-recursive query?

a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Keith Medcalf >Sent: Wednesday, 21 March, 2018 12:31 >To: SQLite mailing list >Subject: Re: [sqlite] How to optimise a somewh

Re: [sqlite] How to optimise a somewhat-recursive query?

bject: Re: [sqlite] How to optimise a somewhat-recursive query? > >Hi Richard, Simon >Re: Compiling - I'm afraid I wouldn't really know where to begin. A >quick google finds >https://superuser.com/questions/146577/where-do-i-find-nmake-for- >windows-7-x64 >- but the answers/links t

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 6:17pm, Jonathan Moules wrote: > Simon - I suspected the ORDER BY thing was wrong but wanted to check first > rather than simply come out with "SQLite is broken!". This may be related to > the 3.22 regression I brought up a couple of days ago

Re: [sqlite] How to optimise a somewhat-recursive query?

ing when it executes the statement "blah" -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Jonathan Moules Sent: Wednesday, March 21, 2018 2:17 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] How to optimise a

Re: [sqlite] How to optimise a somewhat-recursive query?

Hi Richard, Simon Re: Compiling - I'm afraid I wouldn't really know where to begin. A quick google finds https://superuser.com/questions/146577/where-do-i-find-nmake-for-windows-7-x64 - but the answers/links there don't seem to work. I've got to go-out now but can take another look later and

Re: [sqlite] How to optimise a somewhat-recursive query?

On Wed, 21 Mar 2018 17:39:45 + Jonathan Moules escribió: > So, I'm back to being stuck on this. > I have inserted 500,000 random urls (no extra lookups - still just > 1000), and now the query (as per the below reply) is back to being > somewhat slow (I'm using

Re: [sqlite] How to optimise a somewhat-recursive query?

On 21 Mar 2018, at 5:39pm, Jonathan Moules wrote: > After a couple of hours of investigation, it's only slow when there is either > no ORDER BY, or if I use DESC (which is what I need). If I use ORDER BY u.url > ASC - it's near instantaneous. You have a query

Re: [sqlite] How to optimise a somewhat-recursive query?

On 3/21/18, Jonathan Moules wrote: > So, I'm back to being stuck on this. > I have inserted 500,000 random urls (no extra lookups - still just > 1000), and now the query (as per the below reply) is back to being > somewhat slow (I'm using sqlite 3.15) at about 0.6s

Re: [sqlite] How to optimise a somewhat-recursive query?

So, I'm back to being stuck on this. I have inserted 500,000 random urls (no extra lookups - still just 1000), and now the query (as per the below reply) is back to being somewhat slow (I'm using sqlite 3.15) at about 0.6s per request. After a couple of hours of investigation, it's only slow

Re: [sqlite] How to optimise a somewhat-recursive query?

Thanks Simon and Quan. I'm not sure it's the view itself per-se - It takes 0.000s (time too small to measure) for just the full View to be run on this dataset. It turns out the problem is simpler than that and no data changes are needed. I did consider Quan Yong Zhai's option and gave it a

Re: [sqlite] How to optimise a somewhat-recursive query?

On 18 Mar 2018, at 11:13pm, Jonathan Moules wrote: > Given there's such a small amount of data at this point, I suspect the issue > is more related to the recursion. I've tried creating these two indexes to > facilicate that Nice idea but I can see why it's not

Re: [sqlite] How to optimise a somewhat-recursive query?

Hi Both, Thanks for your thoughts. > SQLite has no DATETIME or BOOLEAN datatype I'm aware that DATETIME (and BOOLEAN) isn't an actual datatype in SQLite, but it functions exactly as you'd expect - I've used them extensively in the past and never had a problem - I'm actually storing ISO8601

Re: [sqlite] How to optimise a somewhat-recursive query?

Drop the view, add a new column “last_retrieval_datetime” to the table urls, and whenever fetch a page, update this column 发自我的 iPhone > 在 2018年3月19日，06:15，Clemens Ladisch 写道： > > I have not looked at the schema and queries in detail. > But at a first glance: > >> CREATE

Re: [sqlite] How to optimise a somewhat-recursive query?

On 18 Mar 2018, at 21:48, Jonathan Moules wrote: > CREATE TABLE lookups ( > url_id INTEGER REFERENCES urls (url_id), > retrieval_datetime DATETIME, > error_code INTEGER, > is_generic_flag BOOLEAN -- about one in

Re: [sqlite] How to optimise a somewhat-recursive query?

I have not looked at the schema and queries in detail. But at a first glance: > CREATE VIEW v_most_recent_lookup_per_url AS > ... > ORDER BY url_id; Drop the ORDER BY; it is useless in a view used in another query, and just might slow things down. > 011SEARCH TABLE lookups

[sqlite] How to optimise a somewhat-recursive query?

Hi List, I have a query that's quite slow on a small sample of data and I'd like to optimise it before larger datasets get in there. With just 1000 rows in each table I'm currently seeing query times of ~0.2 seconds. My attempts to optimise it so far by putting in carefully thought out