.png)
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 =]