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
find some sort of sqlite front-end (I know SQLite has a CLI tool, but
I'm happier with front-ends), the VM crashed (for the second time)!
So I'm afraid self-helping by trying that branch isn't happening. If you
have the means to readily build one and send it to me off-list, feel
free to and I'll happily test it. If you can't trust the creator of
SQLite who can you trust? :-)
---
The good news is that Keith's suggestion of removing the "LEFT" from the
JOIN fixed the ORDER BY DESC issue. The database and query are the same
as the one's I provided you, just with a bunch of semi-random data in
the "urls" table.
---
However, - I've now inserted 100,000 semi-random entries into the
"lookups" table. If I run the same query again (which is unchanged
except removing the LEFT's from in front of the JOINs), it's going slow
again taking about 0.5s.
If I change the ORDER BY to ASC, it's also about 0.5s - so they're
consistent.
That's with this index added which seems to be its preference from the
numerous variants I created:
CREATE INDEX url_id_datetime_idx ON lookups (
url_id DESC,
retrieval_datetime
);
The things you're likely interested in though, and they may or may not
be addressed by your branch:
a) If I remove the ORDER BY and LIMIT, the query takes 15 (fifteen) seconds!
b) And if I add the LEFT back in front of the JOIN's, the ORDER BY ASC
query is back to being modestly speedy - 0.07s - and with no ORDER BY
it's the same as well.
c) But with the LEFT JOIN's the query takes about 1.1s for ORDER BY DESC
I can provide another copy of the database with the new data in if you
wish. Or test the fix if you have a dll you want to send me off list.
Thanks,
Jonathan
On 2018-03-21 17:58, Richard Hipp wrote:
On 3/21/18, Jonathan Moules <jonathan-li...@lightpear.com> 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 ability to compile SQLite from canonical sources? If
so, please try again with the tip of the join-strength-reduction
branch (https://www.sqlite.org/src/timeline?r=join-strength-reduction).
To compile on Windows:
(1) Download a tarball or ZIP archive (or SQLite Archive) and unpack it.
(2) Run "nmake /f makefile.msc sqlite3.c"
On unix:
(1) Download and unpack as before
(2) ./configure; make sqlite3.c
The only dependence for the above is having a "tclsh" somewhere on your $PATH.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
