Re: [sqlite] Please help test the latest query planner changes
Op 16-apr-2010, om 19:51 heeft D. Richard Hipp het volgende geschreven: > > On Apr 16, 2010, at 11:52 AM, Max Vlasov wrote: >> >> SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date, >> StihiAuthCandidates.Num as Num FROM StihiAuthors >> INNER JOIN StihiAuthCandidates ON >> StihiAuthors.Id=StihiAuthCandidates.AuthorId >> LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND >> Num=StihiPoems.PoemNum >> WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null >> >> sqlite3-amalgamation-3_6_23_1.dll >> reported 747 milliseconds returning 22,642 rows >> >> sqlite3-20100415132938.dll >> reported 563 milliseconds > > Thanks for the report! > > It is reassuring to know that the recent changes actually did some > good! > > > D. Richard Hipp > d...@hwaci.com There are however cases where the change makes things slower. For instance, if a program does only fetch the first row from a query. Possibly this is also the case in the above example if you add a 'LIMIT 1' condition. It my test it was three times slower, comparing the two SQLite versions. But this likely also depends on the test data and indexes. The good news is that I observed a more spectacular improvement, when fetching all rows. This was four ot five times faster! Another query that appears slower is a mega-union like this SELECT ... UNION ALL SELECT ... UNION ALL SELECT .. UNION ALL SELECT .. where each select includes joins and where only one of the branches of the union yields a row. It looks like the join in each branch causes some extra overhead in the new version. This is at execution time, using pre-compiled SQL statements. The difference is relatively big, a factor 10 or so. Though the differences are relatively big, they are absolutely in the order of mili seconds. So this is likely not an issus. Still it may be in particular applications, when queries are repeated very often. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help test the latest query planner changes
On Apr 16, 2010, at 11:52 AM, Max Vlasov wrote: > > SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date, > StihiAuthCandidates.Num as Num FROM StihiAuthors > INNER JOIN StihiAuthCandidates ON > StihiAuthors.Id=StihiAuthCandidates.AuthorId > LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND > Num=StihiPoems.PoemNum > WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null > > sqlite3-amalgamation-3_6_23_1.dll > reported 747 milliseconds returning 22,642 rows > > sqlite3-20100415132938.dll > reported 563 milliseconds Thanks for the report! It is reassuring to know that the recent changes actually did some good! D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Please help test the latest query planner changes
> We are looking forward to your feedback, both positive and negative. > > Mr D. Richard Hipp found some time to compare this variant with the direct predecessor (3.6.23_1) Both dlls were compiled with Borland command-line compiler with identical options I tested a simple multiply lookup driven query (SELECT .. LEFT JOIN .. LEFT JOIN.. ) and did not find any significant difference. In another test from real program when I have a master-detail relation between authors and their poems, the query forms a pool of not yet read poems your new planner seems to show real improvements. With the query SELECT StihiAuthors.Id As AuthId, StihiAuthCandidates.Date as Date, StihiAuthCandidates.Num as Num FROM StihiAuthors INNER JOIN StihiAuthCandidates ON StihiAuthors.Id=StihiAuthCandidates.AuthorId LEFT JOIN StihiPoems ON Date=StihiPoems.PoemDate AND Num=StihiPoems.PoemNum WHERE StihiAuthors.IsFav=1 AND StihiPoems.rowid Is Null sqlite3-amalgamation-3_6_23_1.dll reported 747 milliseconds returning 22,642 rows sqlite3-20100415132938.dll reported 563 milliseconds Both measurements are made several times and for a db that at least once was used before in order to exclude windows system cache as a player. Max Vlasov, maxerist.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Please help test the latest query planner changes
We've been tweaking of the SQLite query planner in an effort to help joins run faster. But whenever you tweak the query planner, there is always a risk that some query plans might become significantly slower. Your help in identifying any performance regressions is greatly appreciated. Check-in f538d759beda67 of SQLite should be a drop-in replacement for SQLite versions 3.6.22 or 3.6.23. Please test out check-in f538d759beda67 in your applications as you are able and let us know if you see any issues. You can download the f538d759beda67 check-in directly from http://www.sqlite.org/src/info/f538d759be Or, you can get a prebuilt amalgamation at http://www.sqlite.org/sqlite3-20100415132938.zip We are looking forward to your feedback, both positive and negative. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
