I reported a regression in SQLite query performance between 3.46.1 and 3.47 in this forum post(https://sqlite.org/forum/forumpost/1e25669426). I have done some more testing, and the regression seems to impact specifically queries where there are multiple inner joins with tables (that have indexes on the joined columns), and views (which obviously don't have indexes).

If you look at the example database provided earlier (https://drive.google.com/file/d/1mEEK6tBC9UkbQYJ89p5tJo3NktpR1CkZ/view?usp=sharing), there is a view called TripDetailsRaw, which is simply a bunch of inner joins involving various tables and views.

3.46.1 runs this query in well under a second. 3.47.2 takes about 45 seconds to run this query.

I have verified that all the individual views and tables involved in the joins can be selected from in microseconds, so it is only the final join between all of them that seems to have become much slower because of some change between 3.46.1 and 3.47.

Is there anything else you can suggest to make this easier to investigate? Thank you.

Random thought, but, considering other recent threads regarding views in the last couple of days:

Does tossing a random MATERIALIZE on any/all of the views help in any way?

Is there a way to materialize views? I thought the MATERIALIZED keyword was only for CTEs. The syntax diagram for creating a view does not have any mention of an option to materialize the view (https://sqlite.org/lang_createview.html).

I'm sorry, I was wrong.

I conflated the recent forum discussion regarding CTE performance issues and my own unrelated research at the same time on VIEWS.

Still, if I understand things correctly, both VIEWs and CTEs are "simply" ways of making it easier to write complex or reusable queries. And the recent discussion demonstrated that use of the MATERIALIZE keyword "hint" for CTEs can impact performance (both positively and negatively).

So, while it may not work on VIEWs directly, maybe some experiments with rewriting it as a CTE and playing with MATERIALIZE might yield some interesting information?

It appears that version 3.46.1 is the only version that runs the query quickly. All prior versions (3.46.0 and before) are slow and all subsequent versions (3.47.0 and later) are slow. Would I be correct to guess that you originally developed this query using SQLite 3.46.1 and that you never tested it on any other version of SQLite prior to upgrading to 3.47.0?

Not the OP, but I'd like to point out that the query [select * from TripDetailsRaw] runs quickly in 3.39.4, 3.43.0 (randomly chosen dlls) so probably there are other versions. The same is for the narrowed body of the TripDetailsRaw view I posted in another reply.

Some additional information as I see it with the OP original base

What I call a quick query maybe depends on the media (SSD vs rotating) and caching, anyway it's when it is executed from 1 second to 5 seconds. The slow one is the one that takes more than a minute to execute.

My bisects from the public versions point to 3.47.0 as the slowing culprit (3.46.1 is the quick one). The view, the body of the view and many queries with dimension tables lead to the slow one. The release page of the version is impressive but I'd say that it may have something to do with "Adjustments to the query planner so that it produces better plans for star queries with a large number of dimension tables." because many experiments I described earlier affected the execution time when the number of joining tables were around 10-11

So do you get good performance if you port that change to the latest trunk check-in? I do not. Did I do something wrong?

Note also that making that change speeds up OP's query, but slows down a bunch of others. I need a fix that makes all queries fast.

I followed my own instructions from the past, but the configure script complains about "C:/msys64/mingw32/bin/tclsh is not a file" when there's actual "C:msys64mingw32bintclsh.exe" file present

That's an over-zealous check which will be fixed and checked in momentarily. Here's a patch in case you want to work around that without having to wait on the next release:

Index: auto.def
==================================================================
--- auto.def
+++ auto.def
@@ -613,13 +613,11 @@
 
   set doConfigLookup 1 ; # set to 0 to test the tclConfig.sh-not-found cases
   if {"" ne $with_tclsh} {
     # --with-tclsh was provided or found above. Validate it and use it
     # to trump any value passed via --with-tcl=DIR.
-    if {![file isfile $with_tclsh]} {
-      proj-fatal "TCL shell $with_tclsh is not a file"
-    } elseif {![file-isexec $with_tclsh]} {
+    if {![file-isexec $with_tclsh]} {
       proj-fatal "TCL shell $with_tclsh is not executable"
     } else {
       define TCLSH_CMD $with_tclsh
       #msg-result "Using tclsh: $with_tclsh"
     }

The removed part does not account for implicit ".exe" extensions on Windows but the "file-isexec" check does.

(The patch for trunk is functionally the same but in a different file - that content has been moved around since the 3.48 release.)

Thanks, partially worked, but when I applied the patch to sqlite-config.tcl (for auto.def everything was different, no such texts, line numbers, etc). Probably what I meant by "different file". The script advanced but stopped at

WARNING: C:/msys64/mingw32/bin/tclsh is unable to recommend a tclConfig.sh
Error: couldn't execute "C:\msys64\home\user\sqlite\tool\tclConfigShToAutoDef.sh": no such file or
directory

but tclConfigShToAutoDef.sh is there

Probably what I meant by "different file".

Exactly - that stuff was moved yesterday in prep for an upcoming change.

but tclConfigShToAutoDef.sh is there

That... is a mystery and don't currently have a Windows machine to explore it. Just coincidentally, as of today we're working on getting me access to one, so i should be able to reproduce and resolve this sometime within the next week or so.

Hypothetically, though...

What happens if you change this part:

  eval [exec "$srcdir/tool/tclConfigShToAutoDef.sh" "$cfg"]

to:

  eval [exec /bin/sh "$srcdir/tool/tclConfigShToAutoDef.sh" "$cfg"]

My suspicion is that that will work (it does here in Unix-land but the /bin/sh is superfluous here).

Thanks, Stephan, somehow it helped to make things better with your variant slightly modified

  eval [exec sh "$srcdir/tool/tclConfigShToAutoDef.sh" "$cfg"]

configure finished. But now make sqlite3.c

eval 
C:/msys64/home/user/sqlite/tool/mksqlite3c.tcl:92: Error: format string too long or invalid time
Traceback (most recent call last):
  File "C:/msys64/home/user/sqlite/tool/mksqlite3c.tcl", line 92, in fconfigure
    clock format -129 -format {%Y-%m-%d %H:%M:%S UTC} -gmt 1
make: *** [C:/msys64/home/user/sqlite/main.mk:1080: sqlite3.c] Error

Nevermind, fixed with truncating to {%Y-%m-%d %H:%M:%S}

As for the original question from Drh , the trunk is also falls into (slow without the patch/quick with the patch);

somehow it helped to make things better with your variant slightly modified

Great, thank you. That's now patched in the trunk and 3.48 branch.

Nevermind, fixed with truncating to {%Y-%m-%d %H:%M:%S}

Can you tell us which tclsh version is choking on that part?

i'm unable to reproduce this with either jimtcl (which comes embedded in the source tree), tcl8.6, 8.7, or 9.0, but don't have 8.5 handy to test with.

The valuie -129 is, according to the code, coming from your system clock, and is the final 3 minutes of 1969.

Can you tell us which tclsh version is choking on that part?

$ echo 'puts $tcl_version;exit 0' | tclsh
8.6

$ pacman -Ss mingw-w64-i686-tcl
mingw32/mingw-w64-i686-tcl 8.6.12-2 [installed]

I suppose it's not the oldest (8.6), but the updated (8.6.12-2). I don't know whether $tcl_version should full version number.

I have been using sqlite for the last at least 5 years and I have not had any speed issues in previous versions.

I had a similar problem recently here -
https://sqlite.org/forum/forumpost/0f527f061f

For me, the problem was that the table-valued JSON functions were being demoted below indexed tables in the query planner in SQLite > 3.23.3.
At least as a diagnostic, perhaps try EXPLAIN QUERY PLAN in each version, check the join order, and try using CROSS JOIN to force the recursion order.

If indexing/analysis doesn't fix it but CROSS JOIN does, that's a regression according to the next-gen query planner documentation -
https://sqlite.org/queryplanner-ng.html#howtofix