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

2018-03-22 Thread Jonathan Moules
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 page. Well, after a lot of fid

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

2018-03-22 Thread Richard Hipp
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?

2018-03-21 Thread Simon Slavin
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 the statement you're interested

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

2018-03-21 Thread Jonathan Moules
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?

2018-03-21 Thread Keith Medcalf
ays 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 s

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

2018-03-21 Thread Keith Medcalf
gt;Subject: 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

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

2018-03-21 Thread Simon Slavin
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 (and why I'm using > 3.15) - pro

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

2018-03-21 Thread David Raymond
t's doing 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] Ho

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

2018-03-21 Thread Jonathan Moules
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 s

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

2018-03-21 Thread Eduardo
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 sqlite 3.15) at about 0.6s per

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

2018-03-21 Thread Simon Slavin
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 which gets faster when you add an

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

2018-03-21 Thread Richard Hipp
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 per request. Do you have the ab

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

2018-03-21 Thread Jonathan Moules
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 w

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

2018-03-18 Thread Jonathan Moules
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 try

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

2018-03-18 Thread Simon Slavin
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 working. You have an underlyin

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

2018-03-18 Thread Jonathan Moules
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 st

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

2018-03-18 Thread Quan Yong Zhai
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 VIEW v_most_recent_lo

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

2018-03-18 Thread Tim Streater
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 100 will have this > flagged >

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

2018-03-18 Thread Clemens Ladisch
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?

2018-03-18 Thread Jonathan Moules
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 indexes