Hi Julian, Thanks for reaching out. I appreciate the sanity check that "I'm just not using the API correctly" or missing something trivial about Calcite, it really helps narrow down what I need to look at. I plan on investigating the hot spots and I'll open up a ticket if I find anything interesting.
Thanks, Logan On Wed, Sep 4, 2024 at 11:49 AM Julian Hyde <[email protected]> wrote: > Like many performance issues, you are probably running into this because > you have set <scaleFactor> to a higher value than anyone has previously > done. Quite often there is a straightforward solution to these problems. > Create a test that varies on <scaleFactor>, set <scaleFactor> to the > smallest value where performance becomes noticeably bad, identify the > hot-spot (often something simple like a cartesian loop or hash-key > collision), fix the hot-spot, check in the fix and test with a moderately > high value of <scaleFactor>. > > In your case, <scaleFactor> seems to be the number of CTEs. You should log > a jira case with a simple, repetitive query that has N CTEs and noticeably > bad performance. > > > On Sep 1, 2024, at 11:22 PM, JinxTheKid <[email protected]> wrote: > > > > Hi community, > > > > I have a unique use case where I have very large queries that have lots > of > > interconnected CTEs (90KB+, 100+ CTEs). When I run some of these queries > > through Calcite, I end up with very long compile times, anywhere from 8s > to > > 40s. I cannot share the queries, but for context manually optimizing the > > query is not really an option at the moment even though it would likely > > solve the problems I'm encountering. > > > > I've narrowed down the culprit of the slow compile times to > > *SqlToRelConverter's *method convertQueryRecursive*. *For large > > interconnected CTEs, this class ends up executing convertQueryRecursive > many > > times over the course of converting a query. This scenario sounded like a > > great use case for memoization to improve the conversion performance, > but I > > found that adapting this class was challenging. There is some internal > > state of *SqlToRelConverter's *that does not allow me to simply memoize > > convertQueryRecursive unfortunately. It appears correlated variables are > > (one of) the issue but I'm not certain. Has anyone else in the community > > run into similar issues, and if so what did you do to address this? Is > this > > an area the community has looked into? > > > > Thanks, > > Logan > >
