[yunqa.de] Re: Another issue with latest Sqlite
- From: Tom Holden <ve3meo@xxxxxxxxx>
- To: "yunqa@xxxxxxxxxxxxx" <yunqa@xxxxxxxxxxxxx>
- Date: Wed, 4 Sep 2013 07:39:00 -0400
Confidential info posted to the Internet. :(
Try EXPLAIN QUERY PLAN on the query in both versions to see how the revised
query optimizer differs in its treatment.
Try ANALYZE to see if it makes much difference on subsequent query speed.
Tom
On 2013-09-04, at 3:29 AM, "Hennekens, Stephan" <stephan.hennekens@xxxxxx>
wrote:
> Hi Ralf,
>
> I found another issue with the latest build of SQLite which can easily be
> demonstrated with SQLiteSpy version 3.8.0.1 and the older version 3.7.8
> In the new version the query takes 93 seconds to produce a result set,
> whereas the older version does the job in less than a second. The database is
> quite big but can be downloaded from here:
> xxxxxxxxxxxx The db contains confidential information!
>
> Kind regards,
> Stephan
>
> SELECT SpeciesObservations.PlotObservationID FROM SpeciesObservations
> INNER JOIN Taxa ON SpeciesObservations.TaxonID = Taxa.ID
> INNER JOIN CoverCodes ON SpeciesObservations.CoverCodeID = CoverCodes.ID
> INNER JOIN TaxonLists ON Taxa.TaxonListID = TaxonLists.ID
> INNER JOIN SbsTaxaCrosswalk ON SbsTaxaCrosswalk.sbsTaxonlistID =
> TaxonLists.SbsTaxonlistID AND SbsTaxaCrosswalk.TV2SpeciesID =
> Taxa.TV2SpeciesID
> WHERE (sbsTaxaCrosswalk.sbsSpeciesID=22485 AND covercodes.percentage_mean>=25
> AND covercodes.percentage_mean<=100)
>
>
Other related posts:
