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

By | 29/09/2009

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

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios são marcados com *