Como manipular grandes volumes de forma performática?
Quando comecei a minha carreira como DBA, muitas das consultas que desenvolvia e que precisava de manipular grandes massas de dados eram feitas com loops WHILE ou CURSOR. Apesar de não ser uma boa prática (muitos defendem que não sejam usadas), a curva de aprendizado é muito mais simples e não costuma demandar tanto tempo do desenvolvedor. Todavia, uma das grandes preocupações que todo administrador de banco de dados tem é com a performance das consultas que são executadas nas bases de dados à qual é responsável.
Depois que comecei a trabalhar na parte de Business Intelligence, percebi o impacto que pode ser causado para o negócio quando dados não são processados de forma rápida, seja para geração de um simples relatório ou então quando aquele processo da carga falha e você perde o dia inteiro tendo que reprocessar lentamente, já que a base não permite uma concorrência com os processos que são executados em produção.
Pois bem. Comecei a estudar e vi muitas informações sobre algo chamado SET-BASED. O que seria isto?
Não é nenhum conceito revolucionário! Na verdade, a ideia de um SGBD é exatamente trabalhar manipulando vários registros. Lembre-se que leituras em disco são muito caras (lentas) e podem fazer com que consultas gastem muito tempo para serem executadas (podemos falar desse tema em outro artigo). SET-BASED (baseado em conjunto) nada mais é do que processar um grande bloco de dados de uma única vez. Isto é útil em cenários que precisamos processar um registro e projetar dados baseado numa leitura prévia, manipular uma massa de informações etc.
Então, vamos lá. Apresentarei dois exemplos sobre como usar manipulação de dados em massa.
Antes de continuar a análise, preciso informar as configurações do ambiente que os testes foram realizados:
Processador: Intel Core i7 3610QM 4C/8T a 2.3 GHz
Memória: 8 GB de RAM DDR3 2.1 GHz
Disco rígido: Kingston Sata 3 5200 RPM
Sistema Operacional: Windows 10 Professional
Versão do SQL Server: 2017 (14.0.1000.169)
Antes de iniciar alguns testes, veja como a performance da máquina estava sendo exibida pelo gerenciador de tarefas do Windows.
1) COMO CARREGAR 5000000 VALORES SEM USO DE WHILE/CURSOR?
Vamos mostrar, primeiramente, a tradicional consulta utilizando um LOOP para carregar 5 milhões de registros numa tabela.
Versão 1 - Uso de loop WHILE
DECLARE @inicial INT = 1
DECLARE @maximo INT = 5000000
DROP TABLE IF EXISTS #Dados
CREATE TABLE #Dados
(
Valor INT
)
WHILE (@inicial < @maximo)
BEGIN
INSERT INTO #Dados
SELECT 1
SET @inicial = @inicial + 1
END
Durante a execução desta consulta, o consumo de CPU aumentou consideravelmente (cerca de quase 200%, de 1,20GH a 3,21GHz), uso de memória ficou constante e o uso de disco não teve muita oscilação, visto que esta operação não estava lendo ou gravando um grande volume de dados no armazenamento interno.
A execução desta rotina gastou 00:04:53 para ser executada por completo.
Será que é possível reduzir este tempo sem aumentar o custo do banco? Vamos analisar!
Versão 2 - Uso de Dados em Memória
DROP TABLE IF EXISTS #Dados
CREATE TABLE #Dados
(
Valor INT
)
;WITH Dados AS
(
SELECT 1 AS Id
UNION ALL
SELECT Id + 1 AS Id
FROM Dados
WHERE Id < 5000000
)
INSERT INTO #Dados
SELECT 1
FROM Dados
OPTION (MAXRECURSION 0)
Veja como ficou o uso dos recursos do sistema para processar estas informações:
Podemos perceber que o uso de CPU foi equivalente ao resultado da primeira querie, assim como uso de memória. Mas, e o resultado, quanto tempo gastou?
Como podemos ver, o SGBG levou 00:01:17 para registrar a mesma quantidade de registros!
Por que a segunda querie é mais rápida que a primeira?
Na primeira execução, os valores são gerados a partir da variável @inicial, que é incrementa de forma sequencial e, a cada vez que esta operação ocorre, um registro é inserido na tabela #Dados. Na segunda execução, o SGBD utiliza os dados já inseridos na CTE (Common Table Expression) para povoar a própria tabela (e como estes dados estão em memória, aqui está o tiro do gato!).
2) COMO PROJETAR 36 MESES DE PARCELAS FIXAS E O SALDO DEVEDOR DE CLIENTES DE CARTÕES DE CRÉDITO?
Suponha que desejamos traçar o parcelamento de cartão de crédito de usuários que dividiram seus gastos em 36 vezes (é um valor hipotético, apenas para testes). Como fazer isto de forma performática?
Temos como premissa uma base com 2000 clientes, gerados de forma randômica e sem nenhum uso de dados restritos.
DROP TABLE IF EXISTS #Dados_Cliente
CREATE TABLE #Dados_Cliente
(
IdCliente INT IDENTITY(1,1) NOT NULL,
Nome VARCHAR(40),
CPF VARCHAR(11),
DataNascimento DATE,
NumeroCartao VARCHAR(16),
ValorContrato NUMERIC(18,5)
)
DROP TABLE IF EXISTS #ProjetaParcela
CREATE TABLE #ProjetaParcela
(
IdPessoa INT,
DataParcela DATE,
NumeroCartao VARCHAR(16),
ValorContrato NUMERIC(18,5),
ValorParcela NUMERIC(18,5),
SaldoRestante NUMERIC(18,5)
)
GO
INSERT INTO #Dados_Cliente VALUES ('Victor', '01125564612', '1990-01-01', '4444555561618080', 12700.50)
GO 2000
DECLARE @dataInicioContrato DATE = '2018-08-01'
DECLARE @numeroParcelas FLOAT = 36.0
DECLARE @dataFimContato DATE = DATEADD(MONTH, CAST(@numeroParcelas AS INT), @dataInicioContrato)
DECLARE @idCliente INT = (SELECT MIN(IdCliente) FROM #Dados_Cliente)
DECLARE @saldoContrato NUMERIC(18,5)
DECLARE @valorContrato NUMERIC(18,5)
WHILE (@idCliente < (SELECT MAX(IdCliente) FROM #Dados_Cliente))
BEGIN
SET @dataInicioContrato = '2018-08-01'
SELECT @saldoContrato = (ValorContrato - (ValorContrato / @numeroParcelas)),
@valorContrato = (ValorContrato)
FROM #Dados_Cliente WHERE IdCliente = @idCliente
WHILE (@dataInicioContrato < @dataFimContato)
BEGIN
INSERT INTO #ProjetaParcela
SELECT @idCliente AS IdPessoa,
@dataInicioContrato,
NumeroCartao,
ValorContrato,
ValorContrato / @numeroParcelas AS Saldo,
@saldoContrato AS Saldo
FROM #Dados_Cliente
WHERE IdCliente = @idCliente
SET @saldoContrato = @saldoContrato - (@valorContrato / @numeroParcelas)
SET @dataInicioContrato = DATEADD(MONTH, 1, @dataInicioContrato)
END
SET @idCliente = @idCliente + 1
END
Para executar a instrução acima (todo o script), o SQL Server precisou de 00:00:44 para executar, conforme a imagem abaixo:
Como desenvolver o mesmo script sem usar LOOP WHILE?
DROP TABLE IF EXISTS #Dados_Cliente
CREATE TABLE #Dados_Cliente
(
IdCliente INT IDENTITY(1,1) NOT NULL,
Nome VARCHAR(40),
CPF VARCHAR(11),
DataNascimento DATE,
NumeroCartao VARCHAR(16),
ValorContrato NUMERIC(18,5)
)
DROP TABLE IF EXISTS #ProjetaParcela
CREATE TABLE #ProjetaParcela
(
IdPessoa INT,
DataParcela DATE,
NumeroCartao VARCHAR(16),
ValorContrato NUMERIC(18,5),
ValorParcela NUMERIC(18,5),
SaldoRestante NUMERIC(18,5)
)
GO
INSERT INTO #Dados_Cliente VALUES ('Victor', '01125564612', '1990-01-01', '4444555561618080', 12700.50)
GO 2000
DECLARE @dataInicioContrato DATE = '2018-08-01'
DECLARE @numeroParcelas FLOAT = 36.0
;WITH ProjetarDados AS
(
SELECT IdCliente,
@dataInicioContrato AS Data,
NumeroCartao,
ValorContrato,
ValorContrato / @numeroParcelas AS ValorParcela,
ValorContrato - (ValorContrato / @numeroParcelas) AS Saldo
FROM #Dados_Cliente
UNION ALL
SELECT IdCliente,
DATEADD(MONTH, 1,Data) AS Data,
NumeroCartao,
ValorContrato,
ValorParcela,
Saldo - SUM(ValorParcela) OVER (PARTITION BY IdCliente ORDER BY Data ROWS UNBOUNDED PRECEDING) AS Saldo
FROM ProjetarDados
WHERE Data < DATEADD(MONTH, CAST(@numeroParcelas AS INT) -1, @dataInicioContrato)
)
INSERT INTO #ProjetaParcela
SELECT *
FROM ProjetarDados
Para executar a consulta acima, o SQL Server precisou de 00:00:11 para processar a instrução!
Esta é apenas uma das maneiras de manipular grandes massas de dados. O exemplo é hipotético, mas é passível de ser desenvolvido de acordo com a necessidade do usuário e pode auxiliar (e muito) a trazer aquele volume de dados que demora demais para ser executado!
Então, é isto, pessoal. Estou a disposição para eventuais dúvidas e também sobre dicas de novas publicações.
Até logo!