"ve3meo" <holden_fam...@sympatico.ca> wrote in message news:hiivpn$7f...@ger.gmane.org... > > "Simon Slavin" <slav...@bigfraud.org> wrote in > message > news:ad11c649-d23a-46ff-8545-85ffb5219...@bigfraud.org... >> >> The database structure has not changed. It is optimisation of a >> particular kind of query which seems to be working differently -- and not >> as well. Unfortunately, the example supplied is extremely complicated >> and it's not easy to tell which aspect of the query is at fault. > > Sorry. What I meant: is it possible that it is just the particular > structure of my database that is susceptible to this performance > regression? > > I agree that the optimisation strategy has changed for the better between > 3.6.17 and 3.6.20 for the unANALYZED unINDEXED query. > > I postulate that the optimisation strategy is defective for the ANALYZED, > INDEXED query for both 3.6.17 and 3.6.20 while 3.5.4 throws up a false > syntax error. > [snip] > Thanks for any follow-up. Is this the right way to flag a problem for the > SQlite developers? > After much playing about, I have determined that it is necessary to violate the guidelines on not using INDEXED BY and NOT INDEXED in an attempt to optimise performance. This would seem to be the case in a database where there are tables being queried that have one or more CREATEd INDEXes that could lead the SQLite query optimiser to choose an inappropriate index. The optimiser could thus direct the query to be processed extremely sub-optimally, as has been demonstrated by my database and queries where a 1000:1 ratio in execution time has been experienced. The longest times were for those queries in which the optimiser had total freedom to pick the order that tables were processed and which indexes were used. The shortest were those in which:
a) at least some explicit INDEXED BY clauses were added and the optimiser lucked out on the rest BEFORE ANALYZE was run, or b) AFTER ANALYZE was run, NOT INDEXED clauses were added to prevent the optimiser from using anything other than the PRIMARY KEY, and more INDEXED BY clauses were added to override the optimiser's now inappropriate selections. Post 3.6.17, it is clear that the query optimiser degraded performance extremely sub-optimally for this database in its ante-ANALYZED state. For the ANALYZED state, the query optimiser degraded the performance of queries with partial INDEX control for 3.6.17 and post 3.6.17. That says to me, "THE QUERY OPTIMISER IS NOT RELIABLE". 3.5.4 returned errors near "INDEXED" because INDEXED BY and NOT INDEXED extensions were only added at 3.6.3 in late 2008. Tom _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
