Convert sqlnode to relRoot cost 14s

[过 冰峰 <bi...@outlook.com>]

Dear calcite developer community：

       I am a user of Apache Calcitite，Recently I discovered that it took nearly 14 seconds for a simple query to convert sqlnode to relRoot. The reason was that my query contained about 100 unions. Then I added a relnode cache in it, and cost time reduced to 300ms. level. Can I open an issue to contribute this cache back to calcite to deal with poor performance when there are many unions.


thanks

Re: Convert sqlnode to relRoot cost 14s

[Julian Hyde <jh...@apache.org>]

I am always skeptical of caches. They make some things better, and
some things worse, and they make the whole system more complicated.

There are other cases where we were converting expressions to
left-deep or right-deep trees, which resulted in a cartesian (O(N^2))
algorithm, and we were able to convert them to O(N log N) by treating
the lists as balanced binary trees. Sometimes we can flatten, because
UNION allows more than 2 arguments.

I recently fixed https://issues.apache.org/jira/browse/CALCITE-4383,
which will help a lot if the inputs to your union are constant rows.

So, please log a JIRA case with a test case that reproduces the
problem, and let's all figure out the best solution.

Julian

On Sun, Dec 6, 2020 at 10:03 PM Vladimir Sitnikov
<si...@gmail.com> wrote:
>
> Hi,
>
> It would definitely help if you contribute the reproducer.
>
> Have you profiled the case? Could the conversion be tuned?
>
> Frankly speaking, it looks like sqltorel cache is not something we would
> like to add. SQL parsing should be cached at PreparedStatement level or
> something like that. In other words, if you happen to see the same SqlNode,
> then you are likely to see the same SQL text.
>
> Vladimir

Re: Convert sqlnode to relRoot cost 14s

[Vladimir Sitnikov <si...@gmail.com>]

Hi,

It would definitely help if you contribute the reproducer.

Have you profiled the case? Could the conversion be tuned?

Frankly speaking, it looks like sqltorel cache is not something we would
like to add. SQL parsing should be cached at PreparedStatement level or
something like that. In other words, if you happen to see the same SqlNode,
then you are likely to see the same SQL text.

Vladimir