CTE x SubSelect ? Qual é melhor?

Fala pessoal, estou iniciando uma série de posts que irão trazer algumas dicas de performance de querys.

É muito comum no dia a dia de um DBA se deparar com querys que consomem muito processamento, muita memória, querys que realizam muitas leituras. Um dos fatores que deixam as querys nessa situação é o uso de subselects, são necessários em muitas situações mas será que são de fato a única saída?

É o que vamos descobrir agora!

Inicialmente precisamos entender o conceito.

Temos duas tabelas:

--PEDIDO

CREATE TABLE TB_PEDIDO(

COD_PEDIDO INT IDENTITY(1,1)NOT NULL PRIMARY KEY,

COD_CLIENTE INT NOT NULL,

DATA_PEDIDO SMALLDATETIME NOT NULL,

VALOR_BRUTO DECIMAL(14,2) NOT NULL,

VALOR_DESCONTO DECIMAL(14,2) NOT NULL,

VALOR_TOTAL DECIMAL(14,2) NOT NULL

)

-- ITEM PEDIDO

CREATE TABLE TB_PEDIDO_ITEM(

ID_ITEM INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

COD_PEDIDO INT NOT NULL,

COD_PRODUTO INT NOT NULL,

QUANTIDADE INT NOT NULL,

PESO DECIMAL(10,2) NOT NULL,

VALOR_UNITARIO DECIMAL(14,2) NOT NULL

)

Existem situações em que precisamos retornar em apenas uma linha informações de um pedido e os totais dos itens.

COD_PEDIDO COD_CLIENTE DATA_PEDIDO QUANTIDADE PESO

------------------------- --------------------------- -------------------------- ------------------------ -------------------

1001 111 10/02/2019 40 109,5

....

Conseguimos retornar esses dados utilizando um subselect para cada uma das colunas que correspondem aos campos da tabela TB_PEDIDO_ITEM

SELECT

PED.COD_PEDIDO,

PED.COD_CLIENTE,

PED.DATA_PEDIDO,

(SELECT SUM(QUANTIDADE) FROM TB_PEDIDO_ITEM ITEM WHERE PED.COD_PEDIDO = ITEM.COD_PEDIDO) AS QUANTIDADE,

(SELECT SUM(PESO) FROM TB_PEDIDO_ITEM ITEM WHERE PED.COD_PEDIDO = ITEM.COD_PEDIDO) AS PESO

FROM TB_PEDIDO PED

Importante saber que para cada linha do select principal, ou seja, para cada pedido existente na tabela TB_PEDIDO serão realizados outros dois selects na tabela TB_PEDIDO_ITEM (para trazer as somas de quantidade e peso). Pensando em uma base pequena, essa solução é aceitável, porém se pensarmos em uma base muito grande (estamos falando de milhões de linhas nas tabelas), teremos um aumento muito grande de leituras, aumento do consumo de CPU e claro, no tempo de execução da query.(Quanto mais subselects tivermos pior ficará)

Uma alternativa para essa situação seria o uso do CTE (Common table expression - "Obrigado Microsoft, rs").

Esse conceito diminui o número de selects "extras" que a query fará para nos trazer o resultado desejado

Veja:

WITH ITEM_PEDIDO (QUANTIDADE, PESO) AS

( SELECT COD_PEDIDO,SUM(QUANTIDADE),SUM(PESO) FROM TB_PEDIDO_ITEM GROUP BY COD_PEDIDO)

SELECT

PED.COD_PEDIDO,

PED.COD_CLIENTE,

PED.DATA_PEDIDO,

ITEM.QUANTIDADE,

ITEM.PESO

FROM TB_PEDIDO PED

INNER JOIN ITEM_PEDIDO ITEM ON PED.COD_PEDIDO = ITEM.COD_PEDIDO

Como podemos observar o select na tabela de Itens será feito agora apenas uma vez (e não 2 vezes como no subselect). Assim, o resultado final são os mesmos, porém, o consumo e o tempo levado para o retorno da query é muito menor.

Se houver a necessidade de retornar mais um campo, soma do valor unitário por exemplo, basta adicionar no CTE, evitando mais um subselect.

A COMMON TABLE EXPRESSION(CTE) ESTA DISPONÍVEL DESDE O SQL 2005

É muito importante avaliar cada situação, cada ambiente, tabelas e ponderar até que ponto vale a pena usar um subselect ou partir direto para o CTE. Não existe um "certo ou errado", apenas maneiras diferentes de retornar a mesma informação. Quando são muitas colunas a se retornar na maioria dos casos o CTE fica melhor, em outras situações onde é apenas uma coluna para um registro da tabela principal (apenas um pedido em nosso exemplo) o subselect atende tranquilamente.

Gostou dessa dica? Fique ligado para ver as próximas =]

#CGQuery #CGTunning