Could you please provide us with the database schema, or perhaps even a short script that demonstrates your problem, so that we can try to debug it?
On 9/29/18, Stephen F. Booth <m...@sbooth.org> wrote: > A query that ran fine under SQLite 3.24 is substantially slower in 3.25: > > SQLite version 3.24.0 2018-06-04 19:24:41 >> .timer on >> select a.id from a join c on a.id = case when c.b_a_name is not null then > c.b_a_id else c.a_id end where a.id in (select a_fts.rowid from a_fts where > a_fts match 'r*' order by rank) group by a.id; > Run Time: real 0.037 user 0.019868 sys 0.016376 > > SQLite version 3.25.0 2018-09-15 04:01:47 >> .timer on >> select a.id from a join c on a.id = case when c.b_a_name is not null then > c.b_a_id else c.a_id end where a.id in (select a_fts.rowid from a_fts where > a_fts match 'r*' order by rank) group by a.id; > Run Time: real 4.525 user 2.055779 sys 2.466143 > > Performance in 3.25.1 and 3.25.2 is similar to 3.25.0. > > The bottleneck seems to be in the CASE portion of the query. I am not sure > why. > > To try and make a valid comparison I compiled the versions identically > using the following flags: > > % gcc shell.c sqlite3.c -DSQLITE_THREADSAFE=0 -DSQLITE_DEFAULT_MEMSTATUS=0 > -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS > -DSQLITE_MAX_EXPR_DEPTH=0 -DSQLITE_OMIT_DECLTYPE -DSQLITE_OMIT_DEPRECATED > -DSQLITE_OMIT_PROGRESS_CALLBACK -DSQLITE_OMIT_SHARED_CACHE > -DSQLITE_USE_ALLOCA=1 -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_RTREE > -DSQLITE_ENABLE_STAT4 -DHAVE_READLINE -DHAVE_USLEEP -lreadline -Os > > Here is the compiler info: > Apple LLVM version 10.0.0 (clang-1000.11.45.2) > Target: x86_64-apple-darwin18.2.0 > Thread model: posix > > What could be causing this performance hit in 3.25? > > Thanks, > Stephen > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
