Common table expressions

[Thomas Kellerer]

Hi,

in the last version "Experimental support for recursive queries" (=
common table expressions) was added, which is very cool!

As far as I can tell this currently *only* works for recursive queries
- at least I could not get a non-recursive CTE to work.

Is it planned to extend this to a complete implementation of common
table expressions (non-recursive and recursive)?

Regards
Thomas

[Dario Fassi]

Hi,
This is very good news.

Common table expressions has a big impact on sql code quality, readability and portability.
I think that CTE it's a more important feature for itself than recursive queries that are rarely used.

DB2 for example take full advantage and does deep optimization on CTE code (query rewritten, caching, parallelization, etc).

Regards,
Dario


El 26/07/10 09:58, Thomas Kellerer escribió:

[Thomas Mueller]

Hi,

> As far as I can tell this currently *only* works for recursive queries
> - at least I could not get a non-recursive CTE to work.

Yes, that's possible. I only tested the recursive case. I think that's
the main reason to use CTE.

> Is it planned to extend this to a complete implementation of common
> table expressions (non-recursive and recursive)?

Yes, but it doesn't have a high priority for me. What is your use
case, and why can't you use regular views / subqueries?

Regards,
Thomas

[Thomas Kellerer]

On Jul 28, 8:26 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

Well, (non-recursive) CTE can be used to optimize statements where the
same sub-select is used more than once but with slightly different
WHERE conditions.

Something like:

SELECT *
FROM ...
WHERE col_a IN (<complicated sub-select> WHERE <common-condition> AND
col_xyz = 42)
OR col_b IN (<complicated sub-select> WHERE <common-condition> AND
col_xyz = 24)

In that case "<complicated sub-select> WHERE <common-condition>" can
be moved into a CTE which makes the SQL more readable, and gives the
optimizer more choices. With Oracle this can drastically reduce the
runtime, especially when the inner select is complicated and returns
only a moderate number of rows.

I have queries that use 5 or 6 CTEs that depend on each other and do
different levels of aggregation (often using analytical functions),
that are then combined into the final result:

with CTE_1 as (
),
CTE_2 as (
...
JOIN CTE_1 ON ...
) ,
CTE_3 as (
....
JOIN cte_1 ON ...
WHERE not exists (select .. FROM cte_2)
)
SELECT *
FROM cte_3

doing something like that without CTEs is extremely hard to read (and
debug)

[Thomas Mueller]

Hi,

> Well, (non-recursive) CTE can be used to optimize statements where the
> same sub-select is used more than once but with slightly different
> WHERE conditions.

You can also use views, right?

Regards,
Thomas

[Dario Fassi]

Right, we can, but I have cases of complex queries with more than 20 CTEs with multiple an cross usage in one query.
Try to write an sql like that with sub-selects is almost impossible and completely unintelligible , I know that this cases can be more a BI task than an analytic query but happen.
Think in CTEs as temporary views in the scope of a single query.

regards,
Dario

[Thomas Mueller]

Hi,

> I have cases of complex queries with more than 20 CTEs with multiple an cross usage in one query.

It sounds like you should change your application. You have a serious
complexity problem here. I don't think that this is related to the
database you use.

Regards,
Thomas

[Dario Fassi]

Noop, there aren't application ,only the pain of a DBA ;  no one would write a query like that if have another option.
That extreme cases come mostly from ETL queries over data warehouses with "many " tables. (~1000) or from end users Reporting tools that need to be optimized.

Anyway that's not the point, all I wish to remark it's how useful can be CTEs to deal with complex queries and to write better sql code in queries of any size.

Can be found tons of articles talking about this matter:
http://dotnet.sys-con.com/node/46999
http://searchsqlserver.techtarget.com/tip/Simplify-queries-with-SQL-Server-2005-common-table-expressions-CTEs
http://www.4guysfromrolla.com/webtech/071906-1.shtml

[Thomas Kellerer]

On Aug 2, 2:31 pm, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> You have a serious complexity problem here.

No I don't ;)

These are aggregating reports that do some pretty nifty aggregation &
calculation on a daily basis. And in order to re-use the intermediate
results CTEs are very useful.
Views are not really an option as each of those CTEs is passed a
parameter (the calculation runs in a stored procedure). Using the
parameters directly in the CTE is a bit faster than applying the
corresponding WHERE clause to a generalized view.

But as Dario says: that's not the point. CTEs *are* useful, and it
would be nice if H2 supported them as well.

Thomas

[Wes Clark]

Here at Guidewire we are considering using Common Table Expressions, which are now supported in SQL Server and Oracle.  I know this is on the to-do list already.  Any work going on in this area?

[Noel Grandin]

On 2013-04-12 18:38, Wes Clark wrote:
> Here at Guidewire we are considering using Common Table Expressions,
> which are now supported in SQL Server and Oracle. I know this is on
> the to-do list already. Any work going on in this area?
>

Not at the moment, but patches are always welcome :-)