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
