What is the average size of the text in the direction field? and what page size have you set for the database? If the size of a record is such that only a small handful fit into a page, or worse each record overflows (and your select includes the direction field) then this could impact performance.
Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 18 March 2017 at 05:48, Rob Willett <rob.sql...@robertwillett.com> wrote: > We've just implemented a covering index for the last step (is it really?) > in our quest to get the query execution time down. > > To summarise we have gone from 32 mins to 16 mins by updating an index so > it doesn't use collate, we took another six minutes off by removing extra > fields in the select we didn't need. > > We have just created a new index which 'covers' all the fields we use in > the select, this means (and I paraphrase) that we use the index to get all > the data and there is no need to read from the database. > > Well that was a bit of a surprise, the index creation took 45 mins, we ran > the program again and thought, rats, we've cocked it up, it only took 54 > secs, we got something wrong. So we checked it and checked again and we > hasn't got anything wrong. Our query has moved from 32 mins to 54 secs. > > We're quite happy with that performance increase. In fact we're delighted, > so thanks for all the help in getting us to this stage. > > We have kept copies of the query planner bytecode output if anybody is > interested. Gunter has had copies, but if anybody else would like them, > please ask. > > Many thanks again for all the help, > > Rob > > > On 17 Mar 2017, at 22:12, Rob Willett wrote: > > Dear all, >> >> We progress steadily forward. >> >> 1. We immediately halved our execution time by moving to an updated index >> that doesn't use COLLATE. Thats 32mins to 16mins. >> >> 2. We then shaved a further six minutes off the execution time by >> removing extraneous fields in the select statement, so instead of "select * >> ...", we identified which fields we used and directly selected those. So we >> are now down to 10 mins or 1/3 of when we started for, to be honest, >> virtually no extra work, merely being smarter, or rather you being smarter. >> >> 3. We have looked through all our indexes and can see that every index >> has a COLLATE against it, even if the column is an integer. We have raised >> a support call with Navicat. >> >> 4. The next step is to create a "covering index" to try and get the whole >> of the query into the index. However its 22:11 in London and I need to get >> home. >> >> Thanks very much for the help so far. Tomorrow is more tricky but I'll >> read up on covering indexes to see how to use them,. >> >> Rob >> >> On 17 Mar 2017, at 18:39, Simon Slavin wrote: >> >> On 17 Mar 2017, at 6:22pm, Rob Willett <rob.sql...@robertwillett.com> >>> wrote: >>> >>> 4. Work through returning just the columns we actually need from our >>>> queries. We have a recollection that if we can build an index with all the >>>> information necessary in it, we can do all the work in joins rather than >>>> paging out to disk. Is this what you are referring to? >>>> >>> >>> It works only where all the columns you need to read are in the same >>> table. The ideal form of a covering index is to have the columns listed in >>> this order: >>> >>> 1) columns needed for the WHERE clause >>> 2) columns needed for the ORDER BY clause which aren’t in (1) >>> 3) columns needed to be read which aren’t in (2) or (1) >>> >>> SQLite detects that all the information it needs for the SELECT is >>> available from the index, so it doesn’t bother to read the table at all. >>> This can lead to something like a doubling of speed. Of course, you >>> sacrifice filespace, and making changes to the table takes a little longer. >>> >>> 5. Sleep (not exactly sure when) and watch three international rugby >>>> games tomorrow. >>>> >>> >>> Sleep while waiting for indexes to be created and ANALYZE to work. May >>> you see skilled players, creative moves and dramatic play. >>> >>> Simon. >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@mailinglists.sqlite.org >>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >>> >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
