Thiago Zavaschi R2 www.zavaschi.com

Entendendo as Common Table Expressions – CTE – Parte 3 (O Retorno)

Sabe quando você termina uma coisa, mas fica na cabeça de que esqueceu alguma coisa?

Poisé… alguns dos meus posts anteriores trataram de Common Table Expressions e eu não mencionei outra característica muito interessante.

Lembram que o result set da CTE só existe no escopo da query seguinte? Mas será que não é possivél eu ir juntando várias CTE’s e no final fazer uma query que manipule o conjunto de todas?

É sim! Vejam o seguinte exemplo:

;WITH cteA(a)
AS
(
    SELECT 1
    UNION ALL
    SELECT a + 1 FROM cteA WHERE a < 10
), cteB(b)
AS
(
    SELECT 1
    UNION ALL
    SELECT b + 1 FROM cteB WHERE b < 10
), cteC(c)
AS
(
    SELECT 5
)
SELECT ((SELECT SUM(b) from cteB) + (SELECT SUM(a) from cteA)) /
((SELECT COUNT(b) from cteB) + (SELECT COUNT(a) from cteA)) *
(SELECT c FROM cteC)

São 3 CTEs sendo executadas. O retorno das duas primeiras já nos é conhecido: é a contagem de 1 a 10 (visto no post anterior), e o retorno da última é 5, bastante trivial.

Esse post não serve apenas para demonstrar essa capacidade, mas também para alertar para os perigos que podem vir atrelados.

E o meu recado é: CUIDADO com a comodidade! Senão a legibilidade ganha com as CTEs de nada servirá. A explicação é facilmente observada através do plano de execução da query acima.

StmtText
----------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1026]=(([Expr1004]+[Expr1010])/([Expr1016]+[Expr1022]))*(5)))
       |--Nested Loops(Inner Join)
            |--Nested Loops(Inner Join)
            |    |--Nested Loops(Inner Join)
            |    |    |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1045]=(0) THEN NULL ELSE [Expr1046] END))
            |    |    |    |--Stream Aggregate(DEFINE:([Expr1045]=COUNT_BIG([Recr1003]), [Expr1046]=SUM([Recr1003])))
            |    |    |         |--Index Spool(WITH STACK)
            |    |    |              |--Concatenation
            |    |    |                   |--Compute Scalar(DEFINE:([Expr1041]=(0)))
            |    |    |                   |    |--Constant Scan(VALUES:(((1))))
            |    |    |                   |--Assert(WHERE:(CASE WHEN [Expr1043]>(100) THEN (0) ELSE NULL END))
            |    |    |                        |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1043], [Recr1001]))
            |    |    |                             |--Compute Scalar(DEFINE:([Expr1043]=[Expr1042]+(1)))
            |    |    |                             |    |--Table Spool(WITH STACK)
            |    |    |                             |--Compute Scalar(DEFINE:([Expr1002]=[Recr1001]+(1)))
            |    |    |                                  |--Filter(WHERE:(STARTUP EXPR([Recr1001]<(10))))
            |    |    |                                       |--Constant Scan
            |    |    |--Compute Scalar(DEFINE:([Expr1022]=CONVERT_IMPLICIT(int,[Expr1051],0)))
            |    |         |--Stream Aggregate(DEFINE:([Expr1051]=COUNT([Recr1021])))
            |    |              |--Index Spool(WITH STACK)
            |    |                   |--Concatenation
            |    |                        |--Compute Scalar(DEFINE:([Expr1047]=(0)))
            |    |                        |    |--Constant Scan(VALUES:(((1))))
            |    |                        |--Assert(WHERE:(CASE WHEN [Expr1049]>(100) THEN (0) ELSE NULL END))
            |    |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1049], [Recr1019]))
            |    |                                  |--Compute Scalar(DEFINE:([Expr1049]=[Expr1048]+(1)))
            |    |                                  |    |--Table Spool(WITH STACK)
            |    |                                  |--Compute Scalar(DEFINE:([Expr1020]=[Recr1019]+(1)))
            |    |                                       |--Filter(WHERE:(STARTUP EXPR([Recr1019]<(10))))
            |    |                                            |--Constant Scan
            |    |--Compute Scalar(DEFINE:([Expr1016]=CONVERT_IMPLICIT(int,[Expr1056],0)))
            |         |--Stream Aggregate(DEFINE:([Expr1056]=COUNT([Recr1015])))
            |              |--Index Spool(WITH STACK)
            |                   |--Concatenation
            |                        |--Compute Scalar(DEFINE:([Expr1052]=(0)))
            |                        |    |--Constant Scan(VALUES:(((1))))
            |                        |--Assert(WHERE:(CASE WHEN [Expr1054]>(100) THEN (0) ELSE NULL END))
            |                             |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1054], [Recr1013]))
            |                                  |--Compute Scalar(DEFINE:([Expr1054]=[Expr1053]+(1)))
            |                                  |    |--Table Spool(WITH STACK)
            |                                  |--Compute Scalar(DEFINE:([Expr1014]=[Recr1013]+(1)))
            |                                       |--Filter(WHERE:(STARTUP EXPR([Recr1013]<(10))))
            |                                            |--Constant Scan
            |--Compute Scalar(DEFINE:([Expr1010]=CASE WHEN [Expr1061]=(0) THEN NULL ELSE [Expr1062] END))
                 |--Stream Aggregate(DEFINE:([Expr1061]=COUNT_BIG([Recr1009]), [Expr1062]=SUM([Recr1009])))
                      |--Index Spool(WITH STACK)
                           |--Concatenation
                                |--Compute Scalar(DEFINE:([Expr1057]=(0)))
                                |    |--Constant Scan(VALUES:(((1))))
                                |--Assert(WHERE:(CASE WHEN [Expr1059]>(100) THEN (0) ELSE NULL END))
                                     |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1059], [Recr1007]))
                                          |--Compute Scalar(DEFINE:([Expr1059]=[Expr1058]+(1)))
                                          |    |--Table Spool(WITH STACK)
                                          |--Compute Scalar(DEFINE:([Expr1008]=[Recr1007]+(1)))
                                               |--Filter(WHERE:(STARTUP EXPR([Recr1007]<(10))))
                                                    |--Constant Scan

Poisé, é executado tudo separado. Se houverem processamento desnecessários nas CTEs, poderemos encontrar sérios gargalos nestas querys!

Então pessoal, era mais isso que eu queria mostrar deste fantástico recurso! Usem CTE’s, e para os que usarem, usem sabendo como funcionam! :)

E para quem não acompanhou, segue os links dos posts anteriores:

Entendendo as Common Table Expressions – CTE – Parte 1

http://thiagozavaschi.spaces.live.com/blog/cns!8DE5A8EFC1819ECA!390.entry

Entendendo as Common Table Expressions – CTE – Parte 2 (Final)

http://thiagozavaschi.spaces.live.com/blog/cns!8DE5A8EFC1819ECA!391.entry

Abraços,
Thiago Zavaschi