Introdução aos índices em bancos de dados e no SQL Server

Boa noite pessoal,

Finalizando o ano de 2012, estou aqui postando meu último artigo desse ano. Hoje finalizei a edição do último vídeo de um conjunto de quatro vídeos explicando o que são índices, para que servem, sua estrutura interna entre outros aspectos.

Iniciei a elaboração desses vídeos pensando na dificuldade que é ter explicar o que são índices em curto período de tempo nos momentos como MCT, no desafio que é explicar a diferença entre índices clustered e nonclustered em sucessivas dúvidas em fórum e principalmente para montar um material de referência que sirva para detalhar melhor algumas das explicações que vejo por aí além de desmitificar alguns equívocos que vejo por aí a citar:

“Imagine uma árvore, a organização dos indices é semelhante a um formato de árvore, na estrutura da árvore os dados das tabelas resídem no nível folha, ou seja, imagine os dados espalhados por todas as folhas da árvore, para facilitar a busca nestas folhas é que existem os índices.”

“O índice Clustered, geralmente é definido juntamente com a chave primária da tabela. Uma característica importante deste tipo de indice é que ele determina a ordem da gravação (ordenação física) dos registros.”

Independente de certas ou erradas, são afirmações que dizem muito pouco ou praticamente nada para quem ainda não tenha sido apresentado ao assunto. Assim sendo, para aqueles que desejam uma introdução mais sólida sobre o assunto de forma a montar uma boa base para aprofundá-lo e ter argumentos em discussões futuras eu recomendo dar uma olhada nos vídeos abaixo:

Índices – Fundamentos, Estruturas & Usabilidade (Parte 1)

Índices – Fundamentos, Estruturas & Usabilidade (Parte 2)

Índices – Fundamentos, Estruturas & Usabilidade (Parte 3)

Índices – Fundamentos, Estruturas & Usabilidade (Parte 4)

Elogios, sugestões e críticas são muito bem vindas. Não deixe de comentar para que eu possa evoluí-los. No resto mais, um ótimo 2013 a todos.

[ ]s,

Gustavo

Anúncios

Índices e Foreing Keys

Boa Noite Pessoal,

Hoje vi uma dúvida interessante no fórum de SQL Server (MSDN / TechNET). A dúvida em si não era complicada. Bastava apenas identificar quais foreign keys estavam com índices criados ou não. Postarei um script para fazer isso logo mais, mas antes de propriamente apresentá-lo parei um pouco para pensar a respeito dessa necessidade. Se alguém deseja identificar quais FKs não tem índice é porque certamente pretenderá criar índices sobre elas, o que me remete a pensar se indexar todas as FKs é de fato um bom negócio. Sim, vejo muitas recomendações para fazê-lo em vários livros de performance e modelagem de dados. De fato, costuma ser uma boa coisa em muitas situações, mas se fosse bom sempre, então porque o SQL Server, o ORACLE, o DB2 e todos os outros não criam a FKs indexadas por padrão ?

Por que criar índices sobre Foreign Keys ?

A partir do momento em que uma FK é criada sobre uma coluna ou um grupo de colunas, é impositivo que essa coluna ou esse grupo possui um relacionamento com uma coluna ou grupo de colunas de outra tabela que possui essa coluna ou grupo compondo a chave primária. Uma vez que esse relacionamento é construído por essas constraints, em algum momento será necessário identificá-lo para montar as consultas e as junções (JOINs), para recuperar um determinado resultado que combine dados de duas ou mais tabelas.

As operações de JOIN de um ponto de vista sintático são bem conhecidas (INNER, OUTER, FULL, NATURAL, etc), mas fisicamente falando, elas costumam obedecer três algoritmos básicos (Nested Loops, Merge Join e Hash Join). Não vou explicar os algoritmos hoje, mas limito a dizer que não existe um melhor ou pior que o outro (embora alguns jurem que HASH JOIN não é uma boa coisa), apenas que o otimizador normalmente sabe quando é melhor utilizar um ou outro algoritmo em uma determinada situação. O fato é que a presença de índices pode auxiliar o otimizador na escolha desse algoritmo e por consequência retornar os dados mais rapidamente. Vejamos um rápido exemplo:

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

— Cria as tabelas
CREATE TABLE Pessoas (ID INT, Nome VARCHAR(50))
CREATE TABLE Saldos (DataSaldo DATE, IDPessoa INT, Valor SMALLMONEY, HashRegistro UNIQUEIDENTIFIER)

— Cria as constraints
ALTER TABLE Pessoas ADD PRIMARY KEY (ID)
ALTER TABLE Saldos ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE Saldos ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)
ALTER TABLE Saldos ADD DEFAULT NewID() FOR HashRegistro

— Insere alguns registros de pessoas
INSERT INTO Pessoas VALUES
(1,‘Vinicius’),(2,‘Wagner’),(3,‘André’),(4,‘Gilson’),(5,‘Emerson’)

— Insere vários registros fictícios de saldos
;WITH Nums (Num) As (
    SELECT 0 As Num
    UNION ALL SELECT Num + 1 FROM Nums
    WHERE Num < 4500)

INSERT INTO Saldos (DataSaldo, IDPessoa, Valor)
SELECT DATEADD(d,N1.Num,‘2000-01-01’) As Data, N2.Num + 1 As IDPessoa, 1000 As Valor
FROM Nums As N1
CROSS JOIN Nums As N2
WHERE N2.Num <= 2
ORDER BY Data, IDPessoa
OPTION (MAXRECURSION 0)

— Insere um registro de saldo
INSERT INTO Saldos VALUES (‘2012-08-15’,4,5000, NEWID())

— Cria um índice sobre a coluna IDPessoa na tabela Saldos
CREATE INDEX IX_Pessoa ON Saldos (IDPessoa)

Agora que a tabela está criada, vejamos uma consulta que traga o nome e os registros de saldo do primeiro cliente:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 4

O plano de execução é exibido logo abaixo:

Podemos ver que primeiro o índice IX_Pessoa foi utilizado para pesquisar os registros na tabela de Saldo com IDPessoa = 4 retornando um ponteiro para o único registro existente na tabela de Saldos com essa condição. Posteriormente, após achar os ponteiros no índice, foi feita uma operação de Lookup contra a tabela Saldos e a junção do ponteiro do índice IX_Pessoa com os registros foram consolidadas em uma operação de junção através do algoritmo Nested Loop. Para juntar os registros da tabela de pessoas com a tabela de Saldos, foi feita uma outra operação de JOIN através do algoritmo Nested Loops e por fim o resultado foi devolvido (muito rapidamente por sinal). Podemos ver que nesse caso, o uso do índice foi muito benéfico para acelerar a pesquisa e o resultado corrobora com a tese de que indexar FKs é uma boa idéia.

Uma segunda opinião

E se mudássemos a consulta ? E se ao invés de escolher IDPessoa = 4 escolhéssemos IDPessoa = 1 ? Vejamos:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

O plano de execução é exibido logo abaixo:

Dessa vez os resultados foram um pouco diferentes da consulta anterior. Podemos ver que na tabela Pessoa, houve uma pesquisa (Seek) para recuperar a pessoa de ID = 1, mas na tabela Saldos, houve uma varredura (Scan) da tabela inteira (Clustered Index pq ela está clusterizada). O otimizador concluiu que era melhor ignorar o índice IX_Pessoa, pois, para favorecer a condição IDPessoa = 1, seria mais fácil ler a tabela inteira do que ir no índice e ir na tabela registro a registro. Se formos analisar por esse resultado, a criação de índices em FKs só tem a trazer desvantagens. O índice IX_Pessoa na tabela de saldos irá retardar as operações de escrita, irá ocupar mais espaço e não servirá para atender as consultas que utilizem a coluna IDPessoa. No entanto, será que o índice não serve mesmo para nada ?

SELECT P.Nome, COUNT(*) FROM Pessoas As P
INNER JOIN Saldos As S ON P.ID = S.IDPessoa
WHERE P.ID = 1
GROUP BY P.Nome

O plano de execução é exibido logo abaixo:

Embora o índice IX_Pessoa não seja útil para filtrar registros na tabela Saldos onde IDPessoa = 1, o fato de existir um índice  e o objetivo ser apenas uma contagem e não uma exibição das colunas, faz com que a consulta tenha de ser feita apenas sobre o índice e nesse caso, foi possível até fazer um seek e posteriormente uma agregação para posteriormente efetuar um JOIN com a tabela Pessoas. Podemos ver que o índice IX_Pessoa não é tão ruim afinal.

O papel do índice em relacionamentos fortes ou identificados

Um relacionamento é caracterizado pela presença de uma PK em sua tabela Pai e uma FK referenciado as colunas da PK em uma tabela filha. Diz-se que o relacionamento é forte ou identificado quando as colunas da FK também fazem parte da PK na tabela filha. Vejamos o papel que o índice pode desempenhar nesse caso.

— Efetua duas cópias da tabela de Saldos
SELECT IDPessoa, DataSaldo, Valor, HashRegistro
INTO Saldos2 FROM Saldos
ORDER BY IDPessoa, DataSaldo

SELECT IDPessoa, DataSaldo, Valor, HashRegistro
INTO Saldos3 FROM Saldos
ORDER BY IDPessoa, DataSaldo

— Cria as constraints (PK apenas em Saldos2)
ALTER TABLE Saldos2 ADD PRIMARY KEY (IDPessoa, DataSaldo)
ALTER TABLE Saldos2 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)
ALTER TABLE Saldos3 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)

— Cria um índice em Saldos3
CREATE INDEX IX_Pessoa ON Saldos3 (IDPessoa)

Agora vejamos o impacto nas consultas que fizemos anteriormente:

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos2 As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

SELECT P.Nome, S.* FROM Pessoas As P
INNER JOIN Saldos3 As S ON P.ID = S.IDPessoa
WHERE P.ID = 1

O plano de execução é exibido logo abaixo:

No primeiro caso, podemos ver que o fato da coluna IDPessoa ser a primeira coluna da chave primária (que por padrão é clusterizada) faz com que a leitura dos registros da tabela Saldos2 ainda seja uma pesquisa (Seek), pois, a chave primária composta por IDPessoa e DataSaldo impõe uma ordem dos registros por IDPessoa. Já no caso da tabela Saldos3, como não há nenhuma ordem e muitos registros para IDPessoa = 1, o índice é simplesmente ignorado e a tabela é completamente varrida (Table Scan). Proporcionalmente, podemos ver que a consulta em Saldos3 é duas vezes maior que em Saldos2 (67% vs 33% no mesmo Batch).

O papel do índice nas operações de exclusão

Até agora tivemos um foco muito grande no uso do índice nas FKs para consultas, mas ele tem papel nas operações de exclusão ou atualizações em cascata ?

— Efetua uma cópia da tabela de Saldos
SELECT * INTO Saldos4 FROM Saldos

— Cria as constraints
ALTER TABLE Saldos4 ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE Saldos4 ADD FOREIGN KEY (IDPessoa) REFERENCES Pessoas (ID)

DELETE FROM Pessoas WHERE ID = 1

O plano de execução é exibido logo abaixo:

Podemos ver que a exclusão ocorre na tabela Pessoas representada pela operação Clustered Index Delete. Também podemos ver que é feita uma verificação da existência de registros filhos nas tabelas Saldos, Saldos2, Saldos3 e Saldos4. Podemos ver que em todas as tabelas filhas é feita uma pesquisa para checagem da existência (algo muito parecido com a consulta que fez a contagem de registros nos exemplos anteriores). Especificamente na Saldos4, podemos ver que é feita uma varredura.

A varredura na tabela Saldos4 é feita porque não há como saber se existem registros para IDPessoa = 5 senão lendo a tabela inteira uma vez que não exista nenhum índice capaz de auxiliar nessa pesquisa. Em virtude da tabela ser pequena, o custo no plano é de apenas 13% (1% a mais do que as demais tabelas de Saldo). Agora imagine se essa tabela fosse muito grande e muito larga. Uma varredura poderia tornar a exclusão absurdamente lenta, além de impor bloqueios por muito tempo e muitas vezes desnecessários.

Algumas conclusões

A conclusão que chego é provavelmente a que os fabricantes já chegaram, ou seja, como não sabemos o comportamento da seletividade dos valores nas colunas da chave estrangeira, não há como afirmar se a criação de índices em chaves estrangeiras é ou não um bom negócio. Então nesse caso, é melhor assumir a posição de não criar índices nas colunas das chaves estrangeiras e deixar a cargo do implementador efetuar essa avaliação. Excetuando as operações de exclusão e atualizações em cascata, a criação de índices está muito mais voltada para a consulta em si do que o fato de existir um relacionamento. Ainda assim algumas recomendações:

1. Se você for efetuar exclusões nas tabelas pai com frequência e as tabelas filhas forem muito volumosas, indexe as FKs
2. Se você for efetuar JOINs com o objetivo apenas de contar registros ou verificar existência (EXISTs), indexe as FKs
3. Se você utilizar relacionamentos fortes ou identificados, indexe as FKs
4. Se as ocorrência dos valores nas colunas das FKs tiverem muita repetição, ou seja, forem pouco seletivas e as condições acima não forem verdadeiras, avalie se criar índices é um bom negócio (possivelmente não será)
5. Se os três primeiros itens não forem verdadeiros, olhe a criação do índice muito mais como um índice sobre um coluna comum, pois, nesse caso, a coluna ser ou não FK não fará diferença

E como descobrir quais FKs não estão indexadas ?

Seria simples utilizar o exemplo anterior que possui colunas simples, mas vou criar mais três tabelas com chaves compostas e assim podermos avaliar a eficácia do meu script. Embora as tabelas induzam um relacionamento identificado, deixarei algumas colunas de fora propositalmente

— Retira a nulabilidade
SET ANSI_NULL_DFLT_OFF ON

— Cria mais três tabelas
CREATE TABLE SaldosSuspeitos (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)
CREATE TABLE SaldosLimite (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)
CREATE TABLE SaldosDevedores (DataSaldo DATE, IDPessoa INT, DataInclusao DATE)

— Adiciona as Constraints
ALTER TABLE SaldosSuspeitos ADD PRIMARY KEY (DataSaldo, IDPessoa)
ALTER TABLE SaldosSuspeitos ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)
ALTER TABLE SaldosLimite ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)
ALTER TABLE SaldosDevedores ADD FOREIGN KEY (DataSaldo, IDPessoa) REFERENCES Saldos (DataSaldo, IDPessoa)

— Adiciona um índice na FK para a tabela SaldosDevedores
CREATE INDEX IX_Saldo ON SaldosDevedores (DataSaldo, IDPessoa)
CREATE INDEX IX_DataSaldo ON SaldosLimite (DataSaldo)

Visualmente sabemos que a tabela Saldo4 não tem índice na coluna IDPessoa e tem FK para essa coluna. Sabemos também que a tabela SaldosLimite tem a FK com as colunas DataSaldo e IDPessoa, mas possui um índice apenas sobre a coluna DataSaldo e portanto não tem sua FK indexada. Segue a consulta:

;WITH FKsNI (ObjectId, FKId) As (
SELECT DISTINCT
    parent_object_id, constraint_object_id
FROM sys.foreign_key_columns As FC
WHERE NOT EXISTS (
    SELECT * FROM sys.index_columns As IC
    WHERE
        FC.parent_object_id = IC.object_id AND
        FC.constraint_column_id = IC.index_column_id AND
        FC.parent_column_id = IC.column_id))

SELECT
    OBJECT_NAME(FKsNI.ObjectId) As Objeto,
    OBJECT_NAME(FKsNI.FKId) As FK,
    C.Name As Coluna,
    FC.constraint_column_id As Ordem
FROM FKsNI
INNER JOIN sys.foreign_key_columns As FC ON
    FKsNI.FKId = FC.constraint_object_id AND
    FKsNI.ObjectId = FC.parent_object_id
INNER JOIN sys.columns As C ON
    FC.parent_object_id = C.object_id AND
    FC.parent_column_id = C.column_id
ORDER BY Ordem

O resultado sai exatamente conforme o esperado:

Objeto	FK	Coluna	Ordem
Saldos4	FK__Saldos4__IDPesso__25869641	IDPessoa	1
SaldosLimite	FK__SaldosLimite__2B3F6F97	DataSaldo	1
SaldosLimite	FK__SaldosLimite__2B3F6F97	IDPessoa	2

Agora vem a pergunta ? Depois que você rodar a consulta e identificar as FKs que não estão indexadas, você vai criar os índices ? Depende de cada um.

[ ]s,

Gustavo

Funções Escalares – Resusabilidade, Formatação, Preguiça vs Desempenho

Bom Dia Pessoal,

Já faz algum tempo que queria escrever sobre funções escalares e recentemente com algumas threads no MSDN, me senti ainda mais motivado a escrever algumas palavrinhas a respeito. Vejo muita gente falando, usando e recomendando mesmo sem saber exatamente as penalidades envolvidas no uso de funções escalares.

O que são funções escalares ?

Função escalares são funções que recebem um ou mais valores retornam um valor. São exemplos de funções escalares naturais LTRIM, LEFT, CAST. A partir do SQL Server 2000 é possível a construção de funções escalares customizadas (User Defined Scalar Functions) obedecendo a mesma definição, ou seja, devem receber um conjunto de valores e retornar um valor. O uso das funções escalares está em consonância com um dos principais conceitos básicos da orientação, o encapsulamento. Uma vez que seja possível codificar uma lógica dentro da função (cálculo da taxa de juros, números de pedidos por empregado, ou ainda a formatação de uma data) é possível reaproveitá-la em vários trechos do códigos. O fato da função residir dentro do banco de dados também pode evitar a troca de contexto entre banco e aplicação.

Funções escalares e desempenho

Quando funções escalares customizadas são utilizadas em queries tendo como parâmetros de entradas valores das colunas referenciadas na instrução SELECT, normalmente há um alto custo de desempenho associado, pois, essas funções tem de ser chamadas uma vez para cada linha retornada (Row based) já que esse tipo de chamada não é otimizado pelo otimizador de consulta, pois, necessita de várias checagens adicionais (permissão, análise sintática, análise semântica, etc). Se uma função escalar necessita ser utilizada para conjuntos com poucos linhas, esse custo é imperceptível, mas quando grandes quantidade de linhas são necessárias, o custo da utilização das funções escalares torna-se cada mais visível.

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

Para demonstrar a perda de desempenho provocada pelo uso de funções escalares, o teste a seguir mostra duas consultas: uma com o uso de uma função escalar em conjunto com uma instrução SELECT e outra com a mesma lógica direta no SELECT sem a utilização de uma função:

— Cria uma tabela com 1 milhão de números
CREATE TABLE tNums (Num INT NOT NULL)

DECLARE @i INT
SET @i = 1

WHILE @i <= 1000000
BEGIN
    INSERT INTO tNums VALUES (@i)
    SET @i = @i + 1
END

— Marca a coluna Num como chave primária
ALTER TABLE tNums ADD CONSTRAINT PK_Num PRIMARY KEY (Num)

— Cria uma função que recebe um número e mostra o mesmo número mais um
CREATE FUNCTION fnAumenta1 (@Num INT)
RETURNS INT
As
BEGIN
    RETURN(@Num + 1)
END

— Retorna os 10 primeiros números com uso da função
SELECT Num, dbo.fnAumenta1(Num) FROM tNums
WHERE Num <= 10

— Retorna os 10 primeiros números seu o uso da função
SELECT Num, Num + 1 FROM tNums
WHERE Num <= 10

Se compararmos o plano de execução, as consultas são idênticas.

Entretanto, o Profiler nos revela algumas diferenças:

A execução do primeiro comando mostra que a função fnAumenta1 foi chamada 10 vezes (uma para cada linha do SELECT) e que a execução total teve de efetuar 7 leituras. A execução do segundo comando foi mais direta e resultou em apenas 3 leituras. Como a quantidade linhas envolvidas é bem pequena (apenas 10 registros), a recuperação dos dados foi praticamente instântanea em ambos os casos. A tabela abaixo mostra a evolução à medida que a quantidade de registros aumenta. Para essa tabela foi considerada apenas a execução do comando discartando-se os resultados (Tools, Options, Query Results, Results to Grid, Discard Results After Execution)

Função ?	Registros	CPU	Leitura	Tempo(ms)
Sim	100	0	5	3
Não	100	0	3	0
Sim	1.000	16	4	6
Não	1.000	0	4	2
Sim	10.000	31	20	53
Não	10.000	0	20	5
Sim	100.000	375	164	449
Não	100.000	31	164	31
Sim	1.000.000	4102	1615	4491
Não	1.000.000	219	1615	259

As primeiras diferenças começam a aparecer acima de 1.000 registros quando o uso da função começa a gastar ciclos de CPU mais significativos (em torno de 16).  Para a quantidade 10.000 registros percebe-se que a duração do comando com a função foi 10 vezes maior (53 /5). Para os volumes de 100.000 registros e 1.000.000 registros o processamento CPU com a solução baseada em function fica entre 10 a 20 vezes maior. O tempo de execução do comando também é aumentado nessa proporção.

Alguns irão argumentar que a diferença é imperceptível já que uma consulta normalmente retorna poucos registros. Sim, é verdade que para poucos registros, a diferença é mínima, mas é preciso lembrar que o uso da função não se aplica somente ao resultado da consulta. Se funções são utilizadas em tabelas com muitos registros, joins, etc, elas irão degradar o desempenho mesmo que o resultado final seja apenas algumas poucas linhas.

Funções Escalares e Formatação

Sempre defendi que a formatação dos dados para exibição não deve ficar no banco de dados. Qualquer aplicação corretamente modulada jamais deixaria as tarefas de formatação no banco de dados já que a camada de persistência tem como objetivo gravar e recuperar dados e a formatação fica para uma camada de exibição (ou no máximo de aplicação) e não em uma camada de persistência. Ainda assim, não é difícil encontrar consultas que formatam dados. Vejamos como o uso de funções escalares possibilita essa tarefa.

— Cria uma coluna de data
ALTER TABLE tNums ADD Data DATE NULL

— Atualiza a coluna de datas
UPDATE tNums SET Data = DATEADD(D,Num % 20,‘20110522’)

— Cria uma função para formatar a data
CREATE FUNCTION dbo.fnFormataData (@Data DATE)
RETURNS VARCHAR(30)
As
BEGIN
    DECLARE @DataRet VARCHAR(30)
    SET @DataRet = CONVERT(CHAR(10),@Data,103)
    SET @DataRet = @DataRet + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN ‘Terça-Feira’
            WHEN 3 THEN ‘Quarta-Feira’
            WHEN 4 THEN ‘Quinta-Feira’
            WHEN 5 THEN ‘Sexta-Feira’
            WHEN 6 THEN ‘Sábado’
            WHEN 7 THEN ‘Domingo’
        END + ‘ )’
    RETURN (@DataRet)
END

A função dbo.fnFormataData converte uma data para o formato 103 (dd/mm/yyyy) e em seguida adiciona um sufixo com o dia da semana. Vejamos o desempenho com o uso de uma consulta sem formatação, com formatação na consulta e com o uso da função de formatação:

— Retorna as datas distintamente
SELECT DISTINCT Data FROM tNums

— Retorna as datas distintamente com formatação
SELECT DISTINCT
        CONVERT(CHAR(10),Data,103) + ‘ (‘ +
        CASE @@DATEFIRST
            WHEN 1 THEN ‘Segunda-Feira’
            WHEN 2 THEN ‘Terça-Feira’
            WHEN 3 THEN ‘Quarta-Feira’
            WHEN 4 THEN ‘Quinta-Feira’
            WHEN 5 THEN ‘Sexta-Feira’
            WHEN 6 THEN ‘Sábado’
            WHEN 7 THEN ‘Domingo’
        END + ‘ )’ FROM tNums

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

Como podemos ver, só a consulta pura gastou 200ms. Se colocarmos a formatação na consulta, esse tempo vai para 809ms. Ao meu ver é um enorme desperdício formatar dados na camada de banco. Veja que a consulta formatada teve 609ms (809 – 200) só para formatar dados, ou seja, do seu tempo total de execução, 25% do tempo foi para recuperar os dados e 75% do tempo para formatá-los. O uso da CPU para essa formatação foi quase 5 vezes maior (e olha que ainda fizemos um SCAN em uma tabela com 1.000.000 de linhas que representam apenas 3256 blocos nesse exemplo). Se os dados fossem devolvidos para que a aplicação formatasse, teríamos gastado menos recursos do SGBD e mais recursos do servidor de aplicação. Não há como “sumir” com o processamento, mas convenhamos que é mais correto deixar a formatação onde ela deve realmente ficar e vale a pena lembrar que servidores de aplicação escalam mais facilmente que um servidor de banco de dados, pois, dispõe de facilidades como NLB, Web Farm, Web Garden, etc.

A última consulta que usa a função escalar para formatar nem merece muita apreciação. Gastou mais de 20 vezes o total de CPU e quase 50 vezes mais lenta que a consulta de dados sem formatação. Notoriamente um grande desperdício. É comum reproduzí-lo quando se usa o DISTINCT em conjunto com formatação. Se for realmente imprescindível o uso da formatação, opte por fazê-la após o DISTINCT e não antes.

— Efetua o DISTINCT antes, formata depois
;WITH DadosDistintos As (
    SELECT DISTINCT Data FROM tNums)
SELECT DISTINCT dbo.fnFormataData(Data) FROM DadosDistintos

— Retorna as datas distintamente com função
SELECT DISTINCT dbo.fnFormataData(Data) FROM tNums

 

Nesse caso, mesmo a UDF que era o pior dos casos teve seu desempenho muito reduzido tanto em termos de CPU quanto de duração do comando (algo em torno de 30 vezes mais rápido). Quando se usa DISTINCT, filtrar antes e formatar depois é uma excelente prática para ganhar desempenho (embora pouco conhecida e pouco utilizada).

Funções Escalares e Pregüiça

Alguns optam pelo uso de funções escalares para “escapar” de fazer um JOIN ou ainda “fugir” de um GROUP BY. Acredito que essa seja uma das piores utilizações para funções escalares.

— Cria uma função para contar quantas ocorrências existem por data
CREATE FUNCTION dbo.fnContaOcorrenciasPorData (@Data DATE)
RETURNS INT
As
BEGIN
    DECLARE @Total INT
    SET @Total = (
        SELECT COUNT(*) FROM tNums
        WHERE Data = @Data)
    RETURN(@Total)
END

— Cria uma tabela de eventos
CREATE TABLE Eventos (
    Data DATE,
    TipoEvento VARCHAR(50))
INSERT INTO Eventos VALUES (‘20110522’,‘Palestra Estagiários’)
INSERT INTO Eventos VALUES (‘20110523’,‘Café de Boas Vindas’)
INSERT INTO Eventos VALUES (‘20110524’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110525’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110526’,‘Oficina de Contabilidade’)
INSERT INTO Eventos VALUES (‘20110527’,‘Visita à Matriz’)
INSERT INTO Eventos VALUES (‘20110528’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110529’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110530’,‘Oficina de Risco Operacional’)
INSERT INTO Eventos VALUES (‘20110531’,‘Encerramento do Mês’)

— Usa a função para contar quantas ocorrências existem para cada evento
SELECT TipoEvento, dbo.fnContaOcorrenciasPorData(Data) FROM Eventos

— Faz a mesma consulta com um JOIN e um GROUP BY
SELECT TipoEvento, COUNT(Num) FROM Eventos
INNER JOIN tNums ON Eventos.Data = tNums.Data
GROUP BY TipoEvento

E novamente as funções escalares perdem no quesito desempenho. O uso da função gastou quase cinco vezes mais ciclos de CPU e a duração do comando foi quase dez vezes maior. O detalhe mais interessante está na coluna Reads. A tabela tNums possui 3256 blocos e a tabela Eventos possui apenas dois blocos. O JOIN faz um SCAN em ambas e por isso o total de leituras na implementação baseada em JOIN e GROUP BY foi de 3258 blocos. Já a função, como precisa fazer a contagem para cada linha, ela leu várias vezes o mesmo bloco fazendo com que a quantidade de leituras fosse dez vezes maior. Esse é o custo da “pregüiça”.

Mantendo a reusabilidade

Se apenas o desempenho for levado em consideração, provavelmente a recomendação geral seria não utilizar funções escalares e deixar a lógica na própria consulta ou ainda na aplicação. Entretanto, as funções escalares provêm o benefício da reusabilidade, pois, uma vez criadas, podem ser reaproveitadas em várias consultas sem a necessidade de remotar sua lógica em cada consulta. É visível que isso custa desempenho, mas é possível manter o benefício da reusabilidade reduzindo essa penalidade. Vejamos como fazê-lo.

— Cria uma tabela de cotações
CREATE TABLE Cotacoes (
    DataRef DATE NOT NULL,
    Taxa DECIMAL(7,4))

— Cadastra algumas taxas
INSERT INTO Cotacoes VALUES (‘20110501’,0.0142)
INSERT INTO Cotacoes VALUES (‘20110601’,0.0157)
INSERT INTO Cotacoes VALUES (‘20110701’,0.0167)
INSERT INTO Cotacoes VALUES (‘20110801’,0.0135)
INSERT INTO Cotacoes VALUES (‘20110901’,0.0103)
INSERT INTO Cotacoes VALUES (‘20111001’,0.0121)
INSERT INTO Cotacoes VALUES (‘20111101’,0.0158)
INSERT INTO Cotacoes VALUES (‘20111201’,0.0174)

— Monta um função de cálculo de montante final
CREATE FUNCTION dbo.FnRetornaRentabilidade (@DataInicio DATE, @Dias SMALLINT)
RETURNS DECIMAL(9,6)
As
BEGIN

— Inicializa a variável para calcular a taxa
DECLARE @Taxa DECIMAL(9,6)

— Calcula o mês de referência
DECLARE @DataRef DATE
SET @DataRef = (DATEADD(D,1-DAY(@DataInicio),@DataInicio))

— Calcula a taxa proporcional em relação ao início do mês de referência
DECLARE @DiasFimMes SMALLINT
SET @DiasFimMes = DATEDIFF(D,@DataInicio,DATEADD(M,1,@DataRef))

SET @Taxa = (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef)
SET @Taxa = POWER(1 + @Taxa,@DiasFimMes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4))) – 1

— Calcula o restante dos dias úteis
DECLARE @DiasRestantes SMALLINT
SET @DiasRestantes = @Dias – @DiasFimMes

— Aumenta a data de referência em um mês
SET @DataRef = DATEADD(M,1,@DataRef)

WHILE (@DiasRestantes > 0)
BEGIN
    — Verifica se irá fechar o próximo mês
    IF @DiasRestantes > DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
    BEGIN
        SET @Taxa = ((1 + @Taxa) * (1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef))) – 1
        SET @DiasRestantes = @DiasRestantes – DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef))
        SET @DataRef = DATEADD(M,1,@DataRef)
    END
    ELSE
    BEGIN
        SET @Taxa = (1 + @Taxa) * POWER(1 + (SELECT Taxa FROM Cotacoes WHERE DataRef = @DataRef),
        @DiasRestantes / CAST(DATEDIFF(D,@DataRef,DATEADD(M,1,@DataRef)) As Decimal(7,4)))
        SET @DiasRestantes = 0
    END
END

— Se for menos de 40 dias, não há rendimento
SET @Taxa = CASE WHEN @Dias < 40 THEN 0 ELSE @Taxa – 1 END

RETURN(@Taxa)

END

— Testa a função
SELECT dbo.FnRetornaRentabilidade(‘20110511’,40)

De acordo com as taxas oferecidas, se um investimento for feito 11/05/2011 e durar 40 dias, ele irá render 1,9592% que correspondem a 20 dias na rentabilidade de maio 1,42% e 20 dias na rentabilidade junho (1,57%). Não vou entrar nos detalhes desse cálculo (isso fica para uma outra oportunidade), mas ao contrário das abordagens anteriores, esse é o tipo de lógica que, embora possível de ser incorporada a uma consulta diretamente, normalmente ficará dentro de uma função escalar, pois, será bem oportuno que esse cálculo possa ser encapsulado e reutilizado. Será que é possível amenizar a perda de desempenho provocada pelo uso da função linha a linha ?

— Cria uma tabela de aplicações financeiras
CREATE TABLE AppFin (
    ClienteID INT NOT NULL,
    DataApp DATE NOT NULL,
    Valor MONEY NOT NULL,
    DiasCorridos SMALLINT NOT NULL)

— Restringe o investimento a no mínimo 40 dias
ALTER TABLE AppFin ADD CONSTRAINT CK_DiasCorridos CHECK (DiasCorridos >= 40)

— Insere alguns investimentos de forma aleatória
DECLARE @qtdInvestimentos INT
DECLARE @MaxClienteID INT
DECLARE @MaxDataApp TINYINT
DECLARE @MaxValor MONEY
DECLARE @MaxDiasCorridos TINYINT

SET @qtdInvestimentos = 500000
SET @MaxClienteID = 1000
SET @MaxDataApp = 5
SET @MaxValor = 1000000
SET @MaxDiasCorridos = 2

DECLARE @i INT
SET @i = 1

WHILE @i <= @qtdInvestimentos
BEGIN
    INSERT INTO AppFin VALUES (
        ABS(CHECKSUM(NEWID())) % @MaxClienteID,
        DATEADD(M,ABS(CHECKSUM(NEWID())) % 5,‘20110511’),
        ABS(CHECKSUM(NEWID())) % @MaxValor / 100.0000,
        ((ABS(CHECKSUM(NEWID())) % @MaxDiasCorridos + 1) * 30) + 10)
    SET @i = @i + 1
END

— Efetua a consulta para retornar a rentabilidade
SELECT
    ClienteID, DataApp, Valor, DiasCorridos,
    dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
FROM AppFin

Considerando que a tabela AppFin possui 1697 páginas, os resultados do cálculo da do foram expressivos. Para realizar a consulta em questão foram necessárias 3752278 páginas. Isso significa mais de 2.200 vezes o total de páginas da tabela além de um gasto de CPU e tempo muito significativos (quase 30 segundos). Será que há otimização possível ?

No exemplo, a rentabilidade é influenciada basicamente pelos parâmetros DataApp e DiasCorridos dispensado as demais colunas da tabela AppFin. Possivelmente haverá várias repetições dessa combinação. Vejamos quantas combinações distintas existem nessas duas colunas.

— Verifica quantas combinações distintas existem para o cálculo da rentabilidade
SELECT DISTINCT DataApp, DiasCorridos FROM AppFin

No meu exemplo foram retornadas apenas 10 ocorrências. Penso que calcular a rentabilidade para 10 ocorrências é possivelmente mais eficiente que calcular a rentabilidade para 500.000 ocorrências principalmente porque das 500.000 temos apenas dez tipos. Minha proposta seria efetuar o cálculo separadamente para essas dez e posteriormente efetuar um JOIN com as outras 500.000. Vejamos se essa abordagem funciona.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN RentabilidadeCalculada As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Embora as CTEs tenham me ajudado no exemplo da formatação, dessa vez elas me deixaram na mão. Os indicadores (CPU, tempo e leituras) são muito próximos com uma leve desvantagens para as CTEs. Ainda assim, a idéia de fazer 10 vezes e não 500.000 ainda me parece mais inteligente. Vejamos agora o resultado com uma tabela temporária.

— Calcula as taxas distintamente para reduzir o número de cálculos
— Posteriormente efetua um JOIN do Resultados
— Utiliza uma TMP para persistir os resultados temporariamente
;WITH
    AppDistintas (DataApp, DiasCorridos) As
        (SELECT DISTINCT DataApp, DiasCorridos FROM AppFin),
    RentabilidadeCalculada (DataApp, DiasCorridos, Rentabilidade) As
        (SELECT
            DataApp, DiasCorridos, dbo.FnRetornaRentabilidade(DataApp, DiasCorridos)
            FROM AppDistintas)

SELECT DataApp, DiasCorridos, Rentabilidade INTO #TMP FROM RentabilidadeCalculada

SELECT
    A.ClienteID, A.DataApp, A.Valor, A.DiasCorridos, R.Rentabilidade
FROM AppFin As A
INNER JOIN #TMP As R ON A.DataApp = R.DataApp AND A.DiasCorridos = R.DiasCorridos

Dessa vez os resultados foram bastante significativos, além de reduzir as consultas anteriores de 27 segundos para pouco mais de meio segundo, tivemos um ganho expressivo em termos de CPU (menos de 3% do consumo anterior) e uma economia de leituras na casa de 99% (3602 conta 3752276). Definitivamente, fico com essa implementação. Ela mantem a resusabilidade sem comprometer o desempenho. A lógica é simples, se a função precisa ser feita linha a linha, é melhor fazer o menor número de vezes possível.

Confesso que ainda não descobri o porquê das CTEs terem falhado nesse caso. Normalmente são alternativas mais interessantes que o uso de tabelas temporárias, mas enfim… Isso é assunto para uma nova pesquisa e aprendizado.

O uso de funções escalares customizadas não é ruim no SQL Server. Pode encapsular lógicas complexas, facilitar a manutenção do código, prover mecanismos de segurança entre várias outras utilidades. Entretanto, uma boa implementação não consiste no seu uso indiscriminado, mas sim na sua aplicação de forma planejada conhecendo suas vantagens, as penalidades associadas e a forma de lidar com elas.

[ ]s,

Gustavo

Consultas parametrizadas, ISNULL e SQL dinâmica

Bom Dia Pessoal,

Por várias vezes nos fóruns e em algumas situações que presencio na vida real, vejo implementações para execução de consultas parametrizadas com base em vários parâmetros. É sem dúvida uma necessidade muito comum, pois, se uma determinada entidade (tabela) possui várias características (colunas), é quase natural que se pesquise as instâncias (registros) dessa entidade com base em uma, ou duas, ou N características de forma individual ou simultânea. Não é errado necessitar de tal mecanismo de pesquisa, mas o que vejo é que muitas implementações o fazem de forma completamente equivocada e muitas vezes pouco performática. Como poucos registros em um ambiente de desenvolvimento e homologação a diferença é imperceptível, mas à medida em que o volume em produção aumenta, a dificuldade de escalar é nata e muitas vezes a solução não é aumentar a infraestrutura. Vejamos então o que as implementações de consultas parametrizadas devem e não devem fazer.

Inicialmente criarei uma tabela com várias colunas que são potenciais características de pesquisa.

— Cria uma tabela de Pessoas
CREATE TABLE Pessoas (
    ID INT, Nome VARCHAR(100), SobreNome VARCHAR(100),
    Filhos TINYINT, AnoNascimento SMALLINT,
    Cidade VARCHAR(100), UF CHAR(2))

— Insere quinze registros
INSERT INTO Pessoas VALUES (01,‘João’,‘Silva’,2,1950,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (02,‘João’,‘Gonçalves’,1,1990,‘Porto Alegre’,‘RS’)
INSERT INTO Pessoas VALUES (03,‘João’,‘Leite’,0,1992,‘Natal’,‘RN’)
INSERT INTO Pessoas VALUES (04,‘Daniel’,‘Antunes’,0,1986,‘Diadema’,‘SP’)
INSERT INTO Pessoas VALUES (05,‘Daniel’,‘Mendes’,1,1979,‘Manaus’,‘AM’)
INSERT INTO Pessoas VALUES (06,‘Daniela’,‘Petrovisk’,1,1976,‘Salvador’,‘BA’)
INSERT INTO Pessoas VALUES (07,‘Danilo’,‘Silva’,3,1965,‘Brasília’,‘DF’)
INSERT INTO Pessoas VALUES (08,‘Peter’,‘Parker’,0,1989,‘Fortaleza’,‘CE’)
INSERT INTO Pessoas VALUES (09,‘Isabela’,‘Costa’,2,1984,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (10,‘Regiane’,‘Meira’,5,1945,‘Recife’,‘PE’)
INSERT INTO Pessoas VALUES (11,‘Maíra’,‘Gonçalves’,4,1982,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (12,‘Nayara’,‘Silva’,2,1950,‘Brasília’,‘DF’)
INSERT INTO Pessoas VALUES (13,‘Patríca’,‘Gomides’,2,1950,‘São Paulo’,‘SP’)
INSERT INTO Pessoas VALUES (14,‘Natália’,‘Arruda’,2,1950,‘Rio de Janeiro’,‘RJ’)
INSERT INTO Pessoas VALUES (15,‘Márcia’,‘Alves’,0,1983,‘Brasília’,‘DF’)

— Completa até 10.000 de registros fixos de São Paulo
DECLARE @i INT
SET @i = 16

WHILE @i <= 10000
BEGIN
    INSERT INTO Pessoas VALUES (@i,‘Fulano’,‘Silva’,0,1969,‘São Paulo’,‘SP’)
    SET @i = @i + 1
END

— Insere 200 registros com sobrenome “Mendes”
— Insere 200 pessoas que nasceram em 1979
DECLARE @i INT
SET @i = 10001

WHILE @i <= 10200
BEGIN
    INSERT INTO Pessoas VALUES (@i,‘Osvaldo’,‘Mendes’,1,1987,‘Brasília’,‘DF’)
    INSERT INTO Pessoas VALUES (@i + 200,‘Fabiano’,‘Rocha’,0,1979,‘Rio de Janeiro’,‘RJ’)
    SET @i = @i + 1
END

— Cria índices nas colunas potencialmente pesquisáveis e com seletividade
CREATE INDEX IX_SobreNome ON Pessoas (SobreNome)
CREATE INDEX IX_Filhos ON Pessoas (Filhos)
CREATE INDEX IX_AnoNascimento ON Pessoas (AnoNascimento)
CREATE INDEX IX_Cidade ON Pessoas (Cidade)
CREATE INDEX IX_UF ON Pessoas (UF)

O exemplo não retrata 100% da vida real, pois, o cadastro não seria tão concentrado assim. Entretanto, meu objetivo não é bem reproduzir um cadastro, mas sim mostrar que a eficiência dos índices pode ser comprometida por uma determinada implementação (mesmo que ele possua seletividade). Vejamos algumas consultas que possam usufruir da seletividade desses índices:

— Verifica quem nasceu em 1965
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
SELECT * FROM Pessoas WHERE SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
SELECT * FROM Pessoas WHERE SobreNome = ‘Mendes’ AND AnoNascimento = 1979

— Verifica quem tem 1 filho e nasceu em Brasília
SELECT * FROM Pessoas WHERE Filhos = 1 AND Cidade = ‘Brasília’

Como é possível perceber, todas as consultas utilizaram os índices. Algumas precisaram combiná-los (Hash Match Inner Join), mas no geral, nenhuma fez nenhuma varredura.

Tradicionalmente, uma tabela de pessoas possui muitas características que servem de argumento de pesquisa e será bem comum que uma aplicação recebe parâmetros para uma, duas ou até todas as características. O comportamento esperado é bem simples. Se a característica for informada, ela deve ser considerada como filtro. Se a característica não for informada, então ela não deve ser considerada como filtro. A seguir uma implementação típica para atender essa necessidade.

— Cria uma procedure de pesquisa
CREATE PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    ID = ISNULL(@ID,ID) AND
    Nome = ISNULL(@Nome,Nome) AND
    SobreNome = ISNULL(@SobreNome,SobreNome) AND
    Filhos = ISNULL(@Filhos,Filhos) AND
    AnoNascimento = ISNULL(@AnoNascimento,AnoNascimento) AND
    Cidade = ISNULL(@Cidade,Cidade) AND
    UF = ISNULL(@UF,UF)

Essa é uma implementação típica de consultas parametrizadas. Simples de construir e muito fácil de manter. Basta colocar cada coluna com a função ISNULL e a comparação com o valor da variável correspondente. Caso um coluna seja alterada, retirada ou adicionada basta mudar a cláusula WHERE para refletir essa alteração. Vejamos agora se a eficiência na criação e manutenção se mantém no desempenho.

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

Como podemos ver, todas as execuções tem o mesmo plano

Ao contrário do que poderia parecer, nenhuma das consultas que anteriormente usavam índices e eram eficientes comportou-se da mesma forma dentro da Stored Procedure. Não é difícil imaginar o porquê disso acontecer. Já é conhecido que a utilização de funções sobre colunas força tende a mudar a execução para um SCAN. Ainda assim, vejamos a reescrita da procedure com uma semântica parecida, mas sem o ISNULL.

— Cria uma procedure de pesquisa
ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    (ID = @ID OR @ID IS NULL) AND
    (Nome = @Nome OR @Nome IS NULL) AND
    (SobreNome = @SobreNome OR @SobreNome IS NULL) AND
    (Filhos = @Filhos OR @Filhos IS NULL) AND
    (AnoNascimento = @AnoNascimento OR @AnoNascimento IS NULL) AND
    (Cidade = @Cidade OR @Cidade IS NULL) AND
    (UF = @UF OR @UF IS NULL)

A semântica dessa versão da procedure é exatamente igual à versão anterior, mas os resultados são um pouco diferentes:

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

De uma forma geral, todas as execuções tiveram o mesmo plano e dessa vez não foi um TABLE SCAN

A primeira execução utilizava como argumento de pesquisa a data de nascimento. O índice IX_AnoNascimento foi utilizado, mas ao contrário da consulta, a execução da SP fez um SCAN no índice e não um Seek no índice como feito anteriormente. Já a segunda execução utiliza como argumento de pesquisa o sobrenome e não o endereço, mas mesmo assim, a procedure mostrou o plano de execução com o índice IX_AnoNascimento. Isso deve-se ao fato de que a stored procedure compilou um plano de execução e o utilizou sucessivamente para cada uma das quatro execuções. Para uma consulta trivial, esse comportamento é até benéfico, mas para consultas parametrizadas, fica evidente que essa não é uma boa implementação. Para forçar com que a stored procedure produza um novo plano a cada execução devemos usar o WITH RECOMPILE. Normalmente essa é uma cláusula esquecida no momento de se construir uma procedure, mas veremos que ela pode fazer muita diferença.

— Cria uma procedure de pesquisa
ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
WITH RECOMPILE
As

— Recupera Pessoas
SELECT * FROM Pessoas
WHERE
    (ID = @ID OR @ID IS NULL) AND
    (Nome = @Nome OR @Nome IS NULL) AND
    (SobreNome = @SobreNome OR @SobreNome IS NULL) AND
    (Filhos = @Filhos OR @Filhos IS NULL) AND
    (AnoNascimento = @AnoNascimento OR @AnoNascimento IS NULL) AND
    (Cidade = @Cidade OR @Cidade IS NULL) AND
    (UF = @UF OR @UF IS NULL)

Agora que a stored procedure foi alterada, vamos a uma nova tentativa:

— Verifica quem nasceu em 1965
EXEC UspRecuperaPessoas @AnoNascimento = 1965

— Verifica quem tem o sobrenome Gonçalves
EXEC UspRecuperaPessoas @SobreNome = ‘Gonçalves’

— Verifica quem tem o sobrenome Mendes e nasceu em 1979
EXEC UspRecuperaPessoas @SobreNome = ‘Mendes’, @AnoNascimento = 1979

— Verifica quem tem 0 filhos e nasceu em Brasília
EXEC UspRecuperaPessoas @Filhos = 1, @Cidade = ‘Brasília’

Com exceção das duas últimas execuções que resultam em TABLE SCAN, parece que o RECOMPILE gerou um plano mais adequado para as duas primeiras execuções:

Ainda que os índices corretos tenham sido utilizados, eles ainda não foram usados de forma performática, pois, ao invés do Seek (pesquisa) utilizada nas consultas, a stored procedure preferiu fazer um Scan (varredura) tornando o resultado menos eficiente. Podemos ver isso muito claramente com o uso do SET STATISTICS IO.

— Ativa as estatísticas de IO
SET STATISTICS IO ON

— Faz a consulta via Query
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Executa a SP
EXEC UspRecuperaPessoas @AnoNascimento = 1965

Table ‘Pessoas’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Pessoas’. Scan count 1, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Os resultados mostram o quanto a stored procedure parametrizada foi menos eficiente. Na consulta são feitas três leituras lógicas, ou seja, três páginas de dados contra 24 páginas de dados no uso da stored procedure. O Seek no índice é 8 vezes mais eficiente em termos de I/O do que o SCAN. Vejamos agora o uso de uma procedure que usa SQL dinâmica.

ALTER PROCEDURE UspRecuperaPessoas
    @ID INT = NULL,
    @Nome VARCHAR(100) = NULL,
    @SobreNome VARCHAR(100) = NULL,
    @Filhos TINYINT = NULL,
    @AnoNascimento SMALLINT = NULL,
    @Cidade VARCHAR(100) = NULL,
    @UF CHAR(2) = NULL
As

DECLARE @cmdsql As NVARCHAR(4000)

SET @cmdsql = N’SELECT * FROM Pessoas’ +
                N’ WHERE 1 = 1′
      + CASE WHEN @ID IS NOT NULL THEN
          N’ AND ID = @pID’ ELSE N” END
      + CASE WHEN @Nome IS NOT NULL THEN
          N’ AND Nome = @pNome’ ELSE N” END
      + CASE WHEN @SobreNome IS NOT NULL THEN
          N’ AND SobreNome = @pSobreNome’ ELSE N” END
      + CASE WHEN @Filhos IS NOT NULL THEN
          N’ AND Filhos = @pFilhos’ ELSE N” END
      + CASE WHEN @AnoNascimento IS NOT NULL THEN
          N’ AND AnoNascimento = @pAnoNascimento’ ELSE N” END
      + CASE WHEN @Cidade IS NOT NULL THEN
          N’ AND Cidade = @pCidade’ ELSE N” END
      + CASE WHEN @UF IS NOT NULL THEN
          N’ AND UF = @pUF’ ELSE N” END;

EXEC sp_executesql @cmdsql,
N’@pID As INT, @pNome As VARCHAR(100), @pSobreNome VARCHAR(100), @pFilhos TINYINT, @pAnoNascimento SMALLINT, @pCidade VARCHAR(100), @pUF CHAR(2)’,
    @pID = @ID, @pNome = @Nome, @pSobreNome = @SobreNome,
    @pFilhos  = @Filhos, @pAnoNascimento = @AnoNascimento,
    @pCidade = @Cidade, @pUF = @UF

O uso da SQL dinâmica faz a montagem prévia do comando como um texto e posteriormente dispara sua execução através da instrução sp_executesql ou o EXEC(). Vejamos agora os resultados:

— Ativa as estatísticas de IO
SET STATISTICS IO ON

— Faz a consulta via Query
SELECT * FROM Pessoas WHERE AnoNascimento = 1965

— Executa a SP
EXEC UspRecuperaPessoas @AnoNascimento = 1965

Table ‘Pessoas’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table ‘Pessoas’. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver, dessa vez a consulta direta e a stored procedure tiveram exatamente o mesmo plano e comportaram-se de forma eficiente, ou seja, o índice foi utilizado e foi realizado um Seek e não um Scan demandando a leitura de apenas três páginas de dados. As demais execuções também terão os mesmos resultados das consultas. Isso ocorre porque a instrução é montada e executada dinamicamente e nessa montagem, a estrutura do comando é eficiente sem fazer uso de funções como o ISNULL ou operadores como o OR. O único efeito colateral dessa implementação é que o fato da montagem ser baseada em strings pode potencialmente introduzir riscos de SQL Injection e isso demandará do implementador a cautela em validar corretamente seus parâmetros de entrada bem alguns IFs para verificar se há alguma string maliciosa dentro do código. Acredito que seja um trabalho mínimo e realizado uma única vez em benefício das milhares de execuções de uma SP.

Após a demonstração dos cuidados que uma consulta parametrizada dentro de uma SP deva ter, indico as seguintes recomendações:

• Não utilize funções ISNULL contra colunas em uma cláusula WHERE
• Não utilize comparações do tipo Coluna = @Variavel OR @Variavel IS NULL
• Se sua procedure possibilita várias combinações, coloque o WITH RECOMPILE para não gerar planos inadequados
• Dê preferência a stored procedures dinâmicas nesse tipo de situação, mas previna-se contra o SQL Injection

Há muitas informações adicionais e uma explicação mais detalhada no livro do Itzik Ben Gan (Inside SQL Server 2005: T-SQL Programming). Há inclusive uma versão desse livro para SQL Server 2008. Recomendo a leitura.

[ ]s,

Gustavo

Algumas implementações multivaloradas com XML e Table Value Parameter – Parte II

Boa Noite Pessoal,

No artigo anterior, apresentei duas alternativas para lidar com implementações "Master Detail". A primeira foi mais trivial e envolveu várias iterações com o banco de dados. A segunda baseada em XML já optou por consolidar os dados e com uma única iteração com o banco de dados enviá-los em um formato XML. O próprio banco de dados efetuou o processo de Shredding convertendo os dados em XML para um formato tabular com linhas e colunas. Na parte II desse artigo demonstrarei uma terceira alternativa baseada no SQL Server 2008.

O SQL Server 2008 disponibilizou novas funcionalidades em relação ao Transact SQL. Uma dessas melhorias foi a evolução da variável do tipo TABLE para estruturas do conhecidas como User Defined Table Type & Table-Valued Parameters. Esse recurso permite que várias linhas sejam repassadas como uma única variável para stored procedures ou functions o que em outras palavras significa repassar uma tabela ou um resultset como parâmetro. O script a seguir exemplifica a criação de tipos TABLE:

— Muda o contexto de banco de dados
USE Pedidos;

— Cria um tipo tPedidos baseado na estrutura da tabela Pedidos
CREATE TYPE dbo.tPedidos As TABLE (
    DataPedido DATETIME2(0) NOT NULL, Frete SMALLMONEY NOT NULL,
    ClienteID INT NOT NULL, EnderecoID INT NOT NULL)

— Cria um tipo tPedidos baseado na estrutura da tabela Pedidos
CREATE TYPE dbo.tItens As TABLE (
    NumPedido INT NOT NULL, ProdutoID INT NOT NULL,
    Quantidade INT NOT NULL, PrecoUnitario SMALLMONEY NOT NULL)

A criação de dois tipos (tPedidos e tItens) permite sua plena reutilização em outras sessões e batch de comandos. A seguir são criadas e populadas duas variáveis baseadas nesses dois tipos.

— Declara uma variável do tipo tPedidos
DECLARE @Pedidos dbo.tPedidos

— Insere pedidos na variável @Pedidos
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:45:25’,45.39,1,2)
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:57:33’,95.33,3,1)

— Declara uma variável do tipo tItens
DECLARE @Itens dbo.tItens

— Insere itens na variável @Itens
INSERT INTO @Itens VALUES (1,11,21,15.02)
INSERT INTO @Itens VALUES (1,13,15,16.05)
INSERT INTO @Itens VALUES (1,12,33,29.55)
INSERT INTO @Itens VALUES (1,15,17,32.13)
INSERT INTO @Itens VALUES (1,16,23,29.99)
INSERT INTO @Itens VALUES (2,17,25,150.31)
INSERT INTO @Itens VALUES (2,11,20,290.00)
INSERT INTO @Itens VALUES (2,18,16,324.55)
INSERT INTO @Itens VALUES (2,13,34,456.12)
INSERT INTO @Itens VALUES (2,14,43,399.99)
INSERT INTO @Itens VALUES (2,10,19,238.45)

— Exibe os pedidos
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Exibe os itens
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM @Itens

DataPedido	Frete	ClienteID	EnderecoID
2010-05-17 20:45:25	45,39	1	2
2010-05-17 20:57:33	95,33	3	1

NumPedido	ProdutoID	Quantidade	PrecoUnitario
1	11	21	15,02
1	13	15	16,05
1	12	33	29,55
1	15	17	32,13
1	16	23	29,99
2	17	25	150,31
2	11	20	290,00
2	18	16	324,55
2	13	34	456,12
2	14	43	399,99
2	10	19	238,45

Com exceção da palavra "reusabilidade" não há absolutamente nenhuma diferença entre User Defined Table Type e variáveis do tipo TABLE. O script poderia ser substituído sem perda de desempenho ou diferenças no resultado final por variáveis do tipo TABLE. De fato variáveis do tipo TABLE e User Defined Table Type compartilham características em comum (não é à toa que alguns costumam dizer que User Defined Table Type são nada mais que variáveis do tipo TABLE "turbinadas"), mas vejamos algumas características mais particulares a estruturas User Defined Table Type.

— Cria uma função que recebe uma "tabela" do tipo tPedidos como parâmetro
CREATE FUNCTION dbo.ExibePedidos (@Peds dbo.tPedidos READONLY)
RETURNS TABLE
RETURN (SELECT DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos)

— Declara uma variável do tipo tPedidos
DECLARE @Pedidos dbo.tPedidos

— Insere pedidos na variável @Pedidos
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:45:25’,45.39,1,2)
INSERT INTO @Pedidos VALUES (‘2010-05-17 20:57:33’,95.33,3,1)

— Faz a chamada à função ExibePedidos
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM dbo.ExibePedidos(@Pedidos)

A possibilidade de passar uma tabela como parâmetro é uma característica exclusiva a User Defined Table Type. Quando essas estruturas são repassadas como parâmetros para stored procedures e functions elas são conhecidas como Table Value Parameters. O exemplo acima declarou uma variável @Pedidos do tipo tPedidos, populou-a com vários registros e repassou-a para uma função que retornou seu conteúdo. No exemplo utilizado até então, pode-se utilizar uma única stored procedure para efetuar o cadastro recebendo apenas dois parâmetros (um do tipo tPedidos e outro do tipo tItens). Algo parecido com o exemplo a seguir:

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE XXXXXX
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM @Itens

Da forma como está proposta, bastaria informar as variáveis @Pedidos e @Itens populadas para a stored procedure e ela faria a carga nas respectivas tabelas. Não faria nenhum sentido popular as variáveis com TSQL e informá-las para a stored procedure. Os parâmetros seriam populados pelo próprio ADO.NET com o uso de objetos do tipo SqlDbType.Structured. Uma pequena alteração deve ser realizada para capturar o numero do pedido gerado pela propriedade Identity.

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina a stored procedure UspInserePedidos
DROP PROCEDURE UspInserePedidos

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT SCOPE_IDENTITY(), ProdutoID, Quantidade, PrecoUnitario FROM @Itens

Dessa vez, ao invés de popular a aplicação a partir de um arquivo XML com vários pedidos e itens, farei a utilização de apenas um pedido e seus respectivos itens diretamente na aplicação (de forma a evitar a criação de um formulário para entrada de dados).

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP)");
            Console.ReadLine();

            // Cria um DataSet
            DataSet ds = new DataSet();

            // Adiciona duas DataTables (Pedido e Itens) ao DataSet
            ds.Tables.Add("Pedidos");
            ds.Tables.Add("Itens");

            // Adiciona as colunas de Pedidos
            ds.Tables["Pedidos"].Columns.Add("DataPedido", System.Type.GetType("System.DateTime"));
            ds.Tables["Pedidos"].Columns.Add("Frete", System.Type.GetType("System.Double"));
            ds.Tables["Pedidos"].Columns.Add("ClienteID", System.Type.GetType("System.Int32"));
            ds.Tables["Pedidos"].Columns.Add("EnderecoID", System.Type.GetType("System.Int32"));

            // Adiciona as colunas de Itens
            ds.Tables["Itens"].Columns.Add("NumPedido", System.Type.GetType("System.Int32"));
            ds.Tables["Itens"].Columns.Add("ProdutoID", System.Type.GetType("System.Int32"));
            ds.Tables["Itens"].Columns.Add("Quantidade", System.Type.GetType("System.Int32"));
            ds.Tables["Itens"].Columns.Add("PrecoUnitario", System.Type.GetType("System.Double"));

            // Adiciona um pedido
            DateTime DataPedido = DateTime.Parse("2010-05-17 20:45:25");
            Double Frete = 45.39;
            Int32 ClienteID = 1;
            Int32 EnderecoID = 2;

            ds.Tables["Pedidos"].Rows.Add(DataPedido, Frete, ClienteID, EnderecoID);

            // Adiciona os itens do pedido
            // Declara e inicializa os valores
            Int32 NumPedido = 0; // Será sobrescrito no banco de dados
            Int32 ProdutoID = 0;
            Int32 Quantidade = 0;
            Double PrecoUnitario = 0;

            // Insere o primeiro Item
            ProdutoID = 11;
            Quantidade = 21;
            PrecoUnitario = 15.02;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o segundo Item
            ProdutoID = 13;
            Quantidade = 15;
            PrecoUnitario = 16.05;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o terceiro Item
            ProdutoID = 12;
            Quantidade = 33;
            PrecoUnitario = 29.55;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o quarto Item
            ProdutoID = 15;
            Quantidade = 17;
            PrecoUnitario = 32.13;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Insere o quinto Item
            ProdutoID = 16;
            Quantidade = 23;
            PrecoUnitario = 29.99;

            ds.Tables["Itens"].Rows.Add(NumPedido, ProdutoID, Quantidade, PrecoUnitario);

            // Comita as novas linhas
            ds.AcceptChanges();

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetro Pedidos e Itens
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Structured);
            cmdPed.Parameters.Add("@Itens", SqlDbType.Structured);
            cmdPed.Parameters["@Pedidos"].Value = ds.Tables["Pedidos"];
            cmdPed.Parameters["@Itens"].Value = ds.Tables["Itens"];

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Os passos para a compilação do código são exatamente os mesmos utilizados na primeira parte do artigo. Da mesma forma que a versão em XML foi alterada, fiz apenas uma modificação no nome do arquivo .cs e no .exe para não conflitar com os arquivos anteriores.

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoTVP.exe C:\CadastraPedidoTVP.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedidoTVP.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP) Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99

Essa abordagem funciona perfeitamente para estruturas onde apenas um pedido é informado ou chaves naturais são utilizadas, pois nesse caso não há grandes dificuldades no mapeamento dos relacionamentos no banco de dados. Essa é a situação mais comum, pois, normalmente em um formulário de entrada de dados informa-se um pedido de cada vez. Se os dados do XML (dois pedidos e onze itens) fossem utilizados não seria tão fácil já que colunas do tipo Identity são utilizadas. Como obter os Identities gerados na tabela de pedidos e repassá-los para os itens se as inserções são realizadas em sequência ? Da forma como foi proposto, fatalmente os relacionamentos seriam perdidos, pois, não seria possível recuperar o número do pedido e repassá-lo antes da inserção dos itens.

Resolver esse pequeno detalhe não é algo trivial. Se na implementação com XML várias soluções estavam disponíveis, o mesmo não é verdade com o uso de Table Value Parameters, pois, a inserção é direta com pouca margem a manipulação. Ao meu ver o uso de chaves alternativas seria um caminho (no caso de chaves naturais não estarem disponíveis). Seria possível por exemplo replicar na tabela de Itens do DataSet, as colunas DataPedido e ClienteID para que elas fossem submetidas junto com os demais dados. A estrutura tItens poderia contemplar essas colunas e no momento da inserção em Itens, um JOIN seria realizado para recuperar o Numero do Pedido. Algo semelhante à stored procedure abaixo:

— Recria a Stored Procedure UspInserePedidos
CREATE PROCEDURE XXXXXX
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, ClienteID, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, PrecoUnitario
FROM @Itens As I
INNER JOIN Pedidos As P ON I.DataPedido = P.DataPedido AND I.ClienteID = I.ClienteID

Nos exemplos anteriores, os dados foram extraídos de um documento XML. Nesse caso, podemos utilizar as colunas de relacionamento que são criadas automaticamente quando o XML é importado para um DataSet e possui aninhamento. Essa combinação com chaves alternativas permite o cadastro sem a perda das referências.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP V2)");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetro Pedidos e Itens
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Structured);
            cmdPed.Parameters.Add("@Itens", SqlDbType.Structured);
            cmdPed.Parameters["@Pedidos"].Value = ds.Tables["Pedido"];
            cmdPed.Parameters["@Itens"].Value = ds.Tables["Item"];

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

No SQL Server, os tipos tPedidos e tItens foram alterados para ter exatamente o mesmo esquema que as tabelas do DataSet (inclusive a mesma ordem das colunas).

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina a stored procedure UspInserePedidos
DROP PROCEDURE UspInserePedidos

— Elimina a function ExibePedidos
DROP FUNCTION dbo.ExibePedidos

— Elimina os tipos Tipos tPedidos e tItens
DROP TYPE dbo.tPedidos
DROP TYPE dbo.tItens

— Recria os tipos tPedidos e tItens para refletir a estrutura do XML no DataSet
CREATE TYPE dbo.tPedidos As TABLE (Pedido_ID INT NOT NULL,
    DataPedido DATETIME2(0) NOT NULL, Cliente INT NOT NULL,
    EnderecoID INT NOT NULL, Frete SMALLMONEY NOT NULL)

CREATE TYPE dbo.tItens As TABLE (
    ProdutoID INT NOT NULL, Preco SMALLMONEY NOT NULL,
    Quantidade INT NOT NULL, Pedido_ID INT NOT NULL)

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos dbo.tPedidos READONLY,
    @Itens dbo.tItens READONLY
As

— Insere os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT DataPedido, Frete, Cliente, EnderecoID FROM @Pedidos

— Insere os Itens
INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
SELECT NumPedido, ProdutoID, Quantidade, Preco
FROM @Itens As I
INNER JOIN @Pedidos As P ON I.Pedido_ID = P.Pedido_ID
INNER JOIN Pedidos As Ped ON P.DataPedido = Ped.DataPedido AND P.Cliente = Ped.ClienteID

A compilação do código é idêntica às realizadas anteriormente. A única alteração que foi feita foi nos nomes dos arquivo cs e exe bem como a chamada do executável conforme o prompt abaixo:

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoTVP_v2.exe C:\CadastraPedidoTVP_v2.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedidoTVP_v2.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (TVP) Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

Uma breve consulta a partir do Management Studio mostra que o cadastro foi feito com sucesso:

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2
2	2010-05-17 20:57:33	95,33	3	1

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99
06	2	17	25	150,31
07	2	11	20	290,00
08	2	18	16	324,55
09	2	13	34	456,12
10	2	14	43	399,99
11	2	10	19	238,45

Como pode ser observado, a presença de colunas Identity pode complicar um pouco algumas implementações e nesse ponto chave naturais pode ser mais interessante. Felizmente a presença de chaves alternativas viabiliza o uso de Identities mesmo com um pouco mais de código. O uso do tipo XML e (ou) de Table Value Parameters são soluções robustas para implementações "Master Detail", pois, estão preparadas para qualquer cardinalidade, ou seja, funcionam bem independente da quantidade de registros "Details" e conseguem repassar os dados com uma única iteração.

[ ]s,

Gustavo

Algumas implementações multivaloradas com XML e Table Value Parameter – Parte I

Boa Tarde Pessoal,

Um dia desses, um aluno me perguntou se o SQL Server disponibiliza alguma forma de tratar implementações "Master Detail" em uma única iteração sem a necessidade de loops para os registros "Detail" evitando assim várias trocas de contexto entre a aplicação e o banco de dados. Achei a pergunta bastante interessante e felizmente há algumas alternativas para lidar com essas situações. Antes de apresentá-las é importante exemplificar as dificuldades comuns de cadastro. Para isso utilizarei uma pequena aplicação em .NET. Meus conhecimentos nessa plataforma são um pouco limitados (tirei apenas o MCAD no .NET Framework 1.1), mas vejamos a construção de um aplicativo em C# (VS 2008) para simular o cadastro de um pedido e seus Itens. Para evitar a construção de um formulário de entrada de dados, farei a importação de dados a partir de um arquivo XML e a gravação através de stored procedures.

Arquivo XML de Pedidos

<?xml version="1.0"?>
 <Pedidos>
  <Pedido DataPedido="2010-05-17T20:45:25" Cliente="1" EnderecoID="2" Frete="45,39">
   <Item ProdutoID="11" Preco="15,02" Quantidade="21"/>
   <Item ProdutoID="13" Preco="16,05" Quantidade="15"/>
   <Item ProdutoID="12" Preco="29,55" Quantidade="33"/>
   <Item ProdutoID="15" Preco="32,13" Quantidade="17"/>
   <Item ProdutoID="16" Preco="29,99" Quantidade="23"/>
  </Pedido>
  <Pedido DataPedido="2010-05-17T20:57:33" Cliente="3" EnderecoID="1" Frete="95,33">
   <Item ProdutoID="17" Preco="150,31" Quantidade="25"/>
   <Item ProdutoID="11" Preco="290,00" Quantidade="20"/>
   <Item ProdutoID="18" Preco="324,55" Quantidade="16"/>
   <Item ProdutoID="13" Preco="456,12" Quantidade="34"/>
   <Item ProdutoID="14" Preco="399,99" Quantidade="43"/>
   <Item ProdutoID="10" Preco="238,45" Quantidade="19"/>
  </Pedido>
 </Pedidos>

A estrutura do arquivo XML é bem intuitiva. Há um nó root (Pedidos) que lista todos os pedidos a serem processados. Os nós do tipo "Pedido" tem os dados do Pedido e o nós do tipo "Item" possuem os itens de cada pedidos. Em princípio o arquivo foi salvo como C:\Pedidos.xml podendo ser alterado caso necessários. Os scripts abaixo criam uma base de dados, as tabelas e stored procedures necessárias.

Criação do Banco de Dados

— Cria uma base de dados
CREATE DATABASE Pedidos

— Muda o contexto de banco de dados
USE Pedidos

— Cria as tabelas de Pedidos e Itens
CREATE TABLE Pedidos (NumPedido INT NOT NULL IDENTITY(1,1),
    DataPedido DATETIME2(0) NOT NULL, Frete SMALLMONEY NOT NULL,
    ClienteID INT NOT NULL, EnderecoID INT NOT NULL,
    CONSTRAINT PK_Pedidos PRIMARY KEY (NumPedido))

CREATE TABLE Itens (
    ItemID INT NOT NULL IDENTITY(1,1), NumPedido INT NOT NULL,
    ProdutoID INT NOT NULL, Quantidade INT NOT NULL,
    PrecoUnitario SMALLMONEY NOT NULL,
    CONSTRAINT FK_Itens_Pedidos FOREIGN KEY (NumPedido)
    REFERENCES Pedidos (NumPedido))

— Cria a Stored Procedure para Inserção de Pedidos
CREATE PROCEDURE UspInserePedidos
    @DataPedido DATETIME2(0),
    @Frete SMALLMONEY,
    @ClienteID INT,
    @EnderecoID INT,
    @NumPedido INT OUTPUT
AS
    — Faz a gravação na tabela de pedidos
    INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
    VALUES (@DataPedido, @Frete, @ClienteID, @EnderecoID)

    — Atribui o valor gerado pela coluna Identity na variável @NumPedido
    SET @NumPedido = SCOPE_IDENTITY()

— Cria a Stored Procedure para Inserção de Itens
CREATE PROCEDURE UspInsereItens
    @NumPedido INT,
    @ProdutoID INT,
    @Quantidade INT,
    @PrecoUnitario SMALLMONEY
AS
    — Faz a gravação na tabela de itens
    INSERT INTO Itens (NumPedido, ProdutoID, Quantidade, PrecoUnitario)
    VALUES (@NumPedido, @ProdutoID, @Quantidade, @PrecoUnitario)

Agora que o banco de dados está pronto, o próximo passo é criar uma aplicação para carregar o arquivo XML no banco de dados.

Console Application em C# para gravação de pedidos

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona os parâmetros necessários
            cmdPed.Parameters.Add("@DataPedido", SqlDbType.DateTime2);
            cmdPed.Parameters.Add("@Frete", SqlDbType.SmallMoney);
            cmdPed.Parameters.Add("@ClienteID", SqlDbType.Int);
            cmdPed.Parameters.Add("@EnderecoID", SqlDbType.Int);

            // Cria uma variável para capturar o ID do Pedido
            Int32 NumPedido = 0;
            cmdPed.Parameters.Add("@NumPedido", SqlDbType.Int);
            cmdPed.Parameters["@NumPedido"].Direction = ParameterDirection.Output;

            // Instancia um comando para utilizar a stored procedure UspInsereItens
            SqlCommand cmdItem = sc.CreateCommand();
            cmdItem.CommandType = CommandType.StoredProcedure;
            cmdItem.CommandText = "UspInsereItens";

            // Adiciona os parâmetros
            cmdItem.Parameters.Add("@NumPedido", SqlDbType.Int);
            cmdItem.Parameters.Add("@ProdutoID", SqlDbType.Int);
            cmdItem.Parameters.Add("@Quantidade", SqlDbType.Int);
            cmdItem.Parameters.Add("@PrecoUnitario", SqlDbType.SmallMoney);

            // Abre a conexão
            sc.Open();

            // Para cada Pedido, registre-o
            foreach (DataRow drPed in ds.Tables["Pedido"].Rows)
            {
                cmdPed.Parameters["@DataPedido"].Value = DateTime.Parse(drPed["DataPedido"].ToString());
                cmdPed.Parameters["@Frete"].Value = Double.Parse(drPed["Frete"].ToString());
                cmdPed.Parameters["@ClienteID"].Value = Int32.Parse(drPed["Cliente"].ToString());
                cmdPed.Parameters["@EnderecoID"].Value = Int32.Parse(drPed["EnderecoID"].ToString());

                // Executa SP
                cmdPed.ExecuteNonQuery();

                // Captura o ID
                NumPedido = Int32.Parse(cmdPed.Parameters["@NumPedido"].Value.ToString());

                // Para cada Item, registre-o
                foreach (DataRow drItem in drPed.GetChildRows(ds.Relations[0]))
                {
                    cmdItem.Parameters["@NumPedido"].Value = NumPedido;
                    cmdItem.Parameters["@ProdutoID"].Value = Int32.Parse(drItem["ProdutoID"].ToString());
                    cmdItem.Parameters["@Quantidade"].Value = Int32.Parse(drItem["Quantidade"].ToString());
                    cmdItem.Parameters["@PrecoUnitario"].Value = Double.Parse(drItem["Preco"].ToString());

                    // Executa a SP
                    cmdItem.ExecuteNonQuery();
                }
            }

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Antes de efetuar a compilação do código é necessário fazer as alterações para o caminho do arquivo XML (C:\Pedidos.xml) e o servidor utilizado em questão (utilizei a instância local default). Se os parâmetros utilizados não forem os mesmos é preciso fazer alterações no código. Os passos a seguir produzem o executável.

• Abra o Notepad
• Cole o código
• Salve o arquivo em algum local com a extensão .cs (Ex: C:\CadastraPedido.cs)
• Abra um prompt de comando
• Navegue até o diretório C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 (ou superior se houver .NET Framework mais recente instalado)
• Digite csc /out:C:\CadastraPedido.exe C:\CadastraPedido.cs e pressione ENTER (pressupondo que o arquivo seja C:\CadastraPedido.cs)

Após a compilação do código e geração do executável, basta abrir um prompt de comando e executá-lo:

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedido.exe C:\CadastraPedido.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedido.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

Uma breve consulta a partir do Management Studio mostra que o cadastro foi feito com sucesso:

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2
2	2010-05-17 20:57:33	95,33	3	1

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99
06	2	17	25	150,31
07	2	11	20	290,00
08	2	18	16	324,55
09	2	13	34	456,12
10	2	14	43	399,99
11	2	10	19	238,45

A aplicação fez o cadastro dos pedidos e dos respectivos itens. Normalmente essa situação pode-se utilizar do método Update de uma classe SqlAdapter passando-se um objeto do tipo DataSet como parâmetro. Entretanto a presença de valores autoincrementáveis pode trazer certa dificuldade para utilizar apenas esse método como atualização de tabelas em uma relação Master Detail. A Microsoft possui um artigo detalhando essas dificuldades e como contorná-las em: Retrieving Identity or Autonumber Values (ADO.NET), mas o mesmo mostra como recuperar colunas autoincrementáveis, mas não necessariamente como utilizar essas colunas e atualizá-las em tabelas Master Details. Existem outras alternativas com o DataSet, mas o fato é que dificilmente se escapa da necessidade de efetuar um loop para as tabelas filhas como foi feito no exemplo.

O uso do tipo XML

O uso de um tipo semiestruturado como o XML permite a representação dos dados em um formato hierárquico em oposição ao uso de tabelas "Pai" e "Filho" ligadas por um relacionamento (exatamente como demonstrado no documento XML no início do artigo). A passagem dos dados via XML permite que a aplicação envie os dados diretamente ao banco de dados com uma única passagem de parâmetro evitando assim os loops e trocas de contexto entre o banco e a aplicação. Caberá ao banco transformar o XML em um formato relacional (Shredding XML). Para isso é necessário efetuar algumas alterações no documento XML (trocar as vírgulas por pontos), nas stored procedures utilizadas e na aplicação.

<?xml version="1.0"?>
 <Pedidos>
  <Pedido DataPedido="2010-05-17T20:45:25" Cliente="1" EnderecoID="2" Frete="45.39">
   <Item ProdutoID="11" Preco="15.02" Quantidade="21"/>
   <Item ProdutoID="13" Preco="16.05" Quantidade="15"/>
   <Item ProdutoID="12" Preco="29.55" Quantidade="33"/>
   <Item ProdutoID="15" Preco="32.13" Quantidade="17"/>
   <Item ProdutoID="16" Preco="29.99" Quantidade="23"/>
  </Pedido>
  <Pedido DataPedido="2010-05-17T20:57:33" Cliente="3" EnderecoID="1" Frete="95.33">
   <Item ProdutoID="17" Preco="150.31" Quantidade="25"/>
   <Item ProdutoID="11" Preco="290.00" Quantidade="20"/>
   <Item ProdutoID="18" Preco="324.55" Quantidade="16"/>
   <Item ProdutoID="13" Preco="456.12" Quantidade="34"/>
   <Item ProdutoID="14" Preco="399.99" Quantidade="43"/>
   <Item ProdutoID="10" Preco="238.45" Quantidade="19"/>
  </Pedido>
 </Pedidos>

Os scripts a seguir excluem todos os itens e pedidos das tabelas e zera suas respectivas sequências. Posteriormente as stored procedures de inserção de pedidos e itens são excluídas e substituídas por uma única stored procedure que faz a inserção nas duas tabelas.

— Muda o contexto de banco de dados
USE Pedidos;

— Elimina todos os registros de ambas as tabelas e reinicia os Identities
DELETE FROM Itens
DELETE FROM Pedidos
DBCC CHECKIDENT(Itens,‘RESEED’,0)
DBCC CHECKIDENT(Pedidos,‘RESEED’,0)

— Elimina as duas stored procedures
DROP PROCEDURE UspInsereItens
DROP PROCEDURE UspInserePedidos

— Cria uma única Stored Procedure para inserção
CREATE PROCEDURE UspInserePedidos
    @Pedidos XML
AS

— Extrai e grava os pedidos
INSERT INTO Pedidos (DataPedido, Frete, ClienteID, EnderecoID)
SELECT
    P.p.value(‘@DataPedido’,‘DATETIME2(0)’) As DataPedido,
    P.p.value(‘@Frete’,‘SMALLMONEY’) As Frete,
    P.p.value(‘@Cliente’,‘INT’) As ClienteID,
    P.p.value(‘@EnderecoID’,‘INT’) As EnderecoID
FROM @Pedidos.nodes(‘/Pedidos/Pedido’) P(p)

— Insere e grava os itens
— Assume-se que um cliente jamais fará dois pedidos exatamente no mesmo momento
— A combinação DataPedido e ClienteID serve como chave alternativa para obter o ID do pedido
;WITH ItensColetados As (
SELECT
    P.i.value(‘../@DataPedido’,‘DATETIME2(0)’) As DataPedido,
    P.i.value(‘../@Cliente’,‘INT’) As ClienteID,
    P.i.value(‘@ProdutoID’,‘INT’) As ProdutoID,
    P.i.value(‘@Preco’,‘SMALLMONEY’) As PrecoUnitario,
    P.i.value(‘@Quantidade’,‘INT’) As Quantidade
FROM @Pedidos.nodes(‘/Pedidos/Pedido/Item’) P(i))

INSERT INTO Itens (NumPedido, ProdutoID, PrecoUnitario, Quantidade)
SELECT
    NumPedido, ProdutoID, PrecoUnitario, Quantidade
FROM ItensColetados As IC
INNER JOIN Pedidos As Ped ON
    IC.DataPedido = Ped.DataPedido AND IC.ClienteID = Ped.ClienteID

Agora que os objetos de persistência no banco de dados foram devidamente alterados, resta adaptar a aplicação para utilizá-los.

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;

namespace CadastraPedido
{
    class CP
    {
        static void Main(string[] args)
        {
            Console.WriteLine(@"Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (XML)");
            Console.ReadLine();

            // Instancia um documento XML
            XmlDocument xd = new XmlDocument();

            // Carrega o documento XML para o objeto xd
            xd.Load(@"C:\Pedidos.xml");

            // Converte o documento XML para um DataSet
            DataSet ds = new DataSet();
            ds.ReadXml(new XmlNodeReader(xd));

            // Converte o DataSet para XML
            String xmlPed = ds.GetXml().ToString();

            // Instancia uma conexão com o banco de dados para a base Pedidos no servidor Local
            SqlConnection sc = new SqlConnection(@"SERVER=LocalHost;Database=Pedidos;Integrated Security=true");

            // Instancia um comando para utilizar a stored procedure UspInserePedidos
            SqlCommand cmdPed = sc.CreateCommand();
            cmdPed.CommandType = CommandType.StoredProcedure;
            cmdPed.CommandText = "UspInserePedidos";

            // Adiciona o parâmetro XML
            cmdPed.Parameters.Add("@Pedidos", SqlDbType.Xml);
            cmdPed.Parameters["@Pedidos"].Value = xmlPed;

            // Abre a conexão
            sc.Open();

            // Executa a Stored Procedure de cadastro de pedidos
            cmdPed.ExecuteNonQuery();

            // Fecha a conexão
            sc.Close();

            // Elimina a conexão
            sc.Dispose();

            Console.WriteLine(@"Os pedidos e itens foram cadastrados com êxito");
            Console.WriteLine(@"Pressione qualquer tecla para fechar esse aplicativo");
            Console.ReadLine();
        }
    }
}

Há um passo desse código que gostaria de comentar. O início do código converte o arquivo XML para um DataSet e o passo seguinte converte o DataSet para XML. Não seria mais fácil então utilizar diretamente o XML a partir do arquivo ? Sim, não parece fazer sentido extrair dados em XML para o DataSet e posteriormente convertê-los para XML novamente, mas esses passos foram colocados propositalmente. O uso de um arquivo XML deve-se apenas para evitar a criação de um formulário de entrada de dados. Em uma aplicação real, provavelmente o uso Windows Form (ou Web Form) incluiria os dados em um DataSet e posteriormente faria a conversão para XML. Os passos para a compilação do código são exatamente os mesmos utilizados no início do artigo. Fiz apenas uma alteração no nome do arquivo .cs e no .exe para não conflitar com os arquivos anteriores. Os resultados são os mesmos da implementação anterior:

Command Prompt

C:\>cd C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>csc /out:C:\CadastraPedidoXML.exe C:\CadastraPedidoXML.cs Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.3053 for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727 Copyright (C) Microsoft Corporation 2001-2005. All rights reserved. C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>cd\ C:\>CadastraPedidoXML.exe Pressione qualquer tecla para fazer o cadastro dos pedidos e itens (XML) Os pedidos e itens foram cadastrados com êxito Pressione qualquer tecla para fechar esse aplicativo C:\>

USE Pedidos;

SELECT NumPedido, DataPedido, Frete, ClienteID, EnderecoID FROM Pedidos;
SELECT ItemID, NumPedido, ProdutoID, Quantidade, PrecoUnitario FROM Itens;

NumPedido	DataPedido	Frete	ClienteID	EnderecoID
1	2010-05-17 20:45:25	45,39	1	2
2	2010-05-17 20:57:33	95,33	3	1

ItemID	NumPedido	ProdutoID	Quantidade	PrecoUnitario
01	1	11	21	15,02
02	1	13	15	16,05
03	1	12	33	29,55
04	1	15	17	32,13
05	1	16	23	29,99
06	2	17	25	150,31
07	2	11	20	290,00
08	2	18	16	324,55
09	2	13	34	456,12
10	2	14	43	399,99
11	2	10	19	238,45

O uso XML mostrou-se eficaz no cadastro de pedidos e seus respectivos itens. Além de produzir o mesmo resultado, foi capaz de fazer todo o procedimento com uma dose menor de código na aplicação (só foi necessário identificar o nome da SP e do parâmetro) e o deixou bem versátil (ainda que um overhead adicional seja concentrado no banco de dados).

A construção da stored procedure utilizou uma chave alternativa para identificar o pedido como único através da combinação DataPedido e ClienteID. Esse artifício foi utilizado uma vez que a chave dos pedidos é artificial (Identity) e não seria possível capturá-la e vinculá-la aos itens. Existem outras alternativas baseadas em TSQL como confiar em uma recuperação com base em posição (os primeiros itens serão dos primeiros pedidos) ou ainda utilizar cursores para gravar de forma alternada os pedidos e os itens mas não julgo ser uma solução muito performática. Outras alternativas incluem um ID do pedido no próprio XML, sua transformação com XSLT ou ainda o uso do atributo Nested em uma Data Relation para que o XML veja aninhado e os relacionamentos identificados. Enfim, são algumas das possibilidades quando se usa esse formato.

De forma a não tornar o artigo mais extenso do que eu gostaria e não me deparar com limitações ao tamanho do post, a parte I desse artigo finaliza aqui. A parte II pode ser conferida logo a seguir.

[ ]s,

Gustavo

Criptografia, Autenticadores e Ataques de Inferência no SQL Server

Boa Noite Pessoal,

A primeira vez que fui apresentado aos recursos de criptografia do SQL Server em meados de 2006, ficou uma certa curiosidade sobre o parâmetro authenticator dos métodos EncryptByKey e EncryptByPassPhrase. As formas mais seguras de criptografia utilizam a combinação de chaves públicas e privadas e são representadas pelos métodos EncryptByAsymKey e EncryptByCert que por sua vez dispensam em sua assinatura o uso de autenticadores. Qual será então a utilidade desse parâmetro ? Se os métodos mais fortes o dispensam e os métodos mais fracos o utilizam como argumento opcional em que situação ele é necessário ?

Passei algum tempo sem encontrar a resposta, mas depois de estudar um pouco mais sobre segurança acabei descobrindo a sua real utilidade. Algumas formas de ataque como negação de serviço, sql injection, etc sempre me foram familiares, pois, já trabalhei muito para prevení-las e eventualmente já tive que ajudar algumas vítimas de ataques nessa modalidade. Entretanto, o termo "ataque de inferência" não é um dos que costumo observar no dia a dia e tampouco vejo mencionado nas bibliografias de SQL Server e nem sequer no Books OnLine. O uso de autenticadores nos métodos EncryptByKey e EncryptByPassPhrase serve justamente para prevenir esse tipo de ataque a dados criptografados.

Ao invés de me prender a explicações mais formais desse termo, vejamos em uma situação prática, porque apenas a criptografia sozinha talvez não seja eficaz na garantia da integridade e confidencialidade dos dados. O script a seguir cria uma pequena tabela de empregados com algumas colunas cadastrais clássicas e alguns dados mais sensitivos a exemplo da coluna salário.

— Cria uma tabela de empregados
CREATE TABLE Empregados (
    EmpregadoID INT IDENTITY(1,1) NOT NULL,
    CPF CHAR(11) NOT NULL,
    Nome VARCHAR(50) NOT NULL,
    Funcao VARCHAR(50) NOT NULL,
    Salario VARBINARY(256) NULL)

— Adiciona as constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)

— Insere três empregados
INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘70036543921’,‘Ademilda Souza Gonçalves’,‘Diretora de TI’, NULL)

INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘51034681233’,‘Alexandre Pereira Neto’,‘Analista Sênior’, NULL)

INSERT INTO Empregados (CPF, Nome, Funcao, Salario)
VALUES (‘25341677542’,‘Edmilson Alves Medeiros’,‘Desenvolvedor Júnior’, NULL)

Os dados cadastrais estão concluídos, agora resta informar os salários para cadastro. Podemos utilizar várias opções no próprio SQL Server como certificados, chaves simétricas, as funções pwdEncrypt e pwdCompare, etc. Não entrarei no mérito do método de criptografia mais forte. Para o propósito desse artigo uma criptografia simétrica baseada em frase já é suficiente.

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

— Declara strings para armazenar os salários (tem que ser texto para atender a criptografia)
DECLARE @Salario1 VARCHAR(8), @Salario2 VARCHAR(8), @Salario3 VARCHAR(8)
SET @Salario1 = ‘10256.78’ — Salário da Ademilda
SET @Salario2 = ‘5360.29’ — Salário do Alexandre
SET @Salario3 = ‘2230.55’ — Salário do Edmilson

— Declara strings para armazenar as senhas criptografadas
DECLARE @Salario1C VARBINARY(256), @Salario2C VARBINARY(256), @Salario3C VARBINARY(256)
SET @Salario1C = ENCRYPTBYPASSPHRASE(@pwd,@Salario1)
SET @Salario2C = ENCRYPTBYPASSPHRASE(@pwd,@Salario2)
SET @Salario3C = ENCRYPTBYPASSPHRASE(@pwd,@Salario3)

— Atualiza as senhas dos clientes
UPDATE Empregados SET Salario = @Salario1C WHERE EmpregadoID = 1
UPDATE Empregados SET Salario = @Salario2C WHERE EmpregadoID = 2
UPDATE Empregados SET Salario = @Salario3C WHERE EmpregadoID = 3

Edmilson é um desenvolvedor que conseguiu uma credencial para acesso a tabela de empregados. Essa credencial possui permissão de SELECT e UPDATE na tabela de Empregados. Ele não sabe exatamente o salário de Ademilda, mas a julgar pela sua posição na empresa, ele sabe que trata-se de um salário muito superior ao seu. Assim sendo, ele efetua um UPDATE para igualar o seu salário ao dela.

UPDATE Empregados SET Salario = (SELECT Salario FROM Empregados WHERE EmpregadoID = 1)
WHERE EmpregadoID = 3

— Verifica as colunas de nome e salário
SELECT Nome, Salario FROM Empregados

Após a execução do comando, o salário de Ademilda e de Edmilson realmente são iguais (ainda que criptografados). Os códigos HASH podem divergir, mas o importante é que os salários da Ademilda e do Edmilson são iguais.

Nome	Salário
Ademilda Souza Gonçalves	0x01000000CF13BEDF13CFC59006793E775C19BACA7605877816E31CB8D63DC548CCBDC56A
Alexandre Pereira Neto	0x010000001DCCD902BF968B61514AF6AF0DA3510D4DDD6F001A83D149
Edmilson Alves Medeiros	0x01000000CF13BEDF13CFC59006793E775C19BACA7605877816E31CB8D63DC548CCBDC56A

O que ocorreu foi uma inferência por parte do usuário Edmilson. Ele não sabe o salário da Ademilda, mas baseando-se na sua posição, ele consegue inferir que o salário é superior e portanto efetuou a atualização. No momento que ele visualizar o contracheque, ele conseguirá descobrir o salário da Ademilda. Podemos constatar que eles são iguais através da consulta abaixo:

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

SELECT Nome,
    CAST(
        CAST(DECRYPTBYPASSPHRASE(@pwd,Salario) As VARCHAR(8))
    As SMALLMONEY) As Salario
FROM Empregados

A consulta retorna os empregados e seus salários descriptografados.

Nome	Salário
Ademilda Souza Gonçalves	10256,78
Alexandre Pereira Neto	5360,29
Edmilson Alves Medeiros	10256,78

De fato, a consulta confirma que Edmilson "promoveu-se" aumentando o seu salário. Vejamos agora como o uso de autenticadores pode prevenir esse problema.

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

— Declara strings para armazenar os salários (tem que ser texto para atender a criptografia)
DECLARE @Salario1 VARCHAR(8), @Salario2 VARCHAR(8), @Salario3 VARCHAR(8)
SET @Salario1 = ‘10256.78’ — Salário da Ademilda
SET @Salario2 = ‘5360.29’ — Salário do Alexandre
SET @Salario3 = ‘2230.55’ — Salário do Edmilson

— Declara strings para armazenar as senhas criptografadas com o uso de autenticadores
DECLARE @Salario1C VARBINARY(256), @Salario2C VARBINARY(256), @Salario3C VARBINARY(256)
SET @Salario1C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario1,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 1)
SET @Salario2C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario2,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 2)
SET @Salario3C = (SELECT ENCRYPTBYPASSPHRASE(@pwd,@Salario3,1,CAST(EmpregadoID As SYSNAME)) FROM Empregados WHERE EmpregadoID = 3)

— Atualiza as senhas dos clientes
UPDATE Empregados SET Salario = @Salario1C WHERE EmpregadoID = 1
UPDATE Empregados SET Salario = @Salario2C WHERE EmpregadoID = 2
UPDATE Empregados SET Salario = @Salario3C WHERE EmpregadoID = 3

Supondo que Edmilson irá tentar novamente efetuar um ataque por inferência, vejamos a consulta abaixo:

UPDATE Empregados SET Salario = (SELECT Salario FROM Empregados WHERE EmpregadoID = 1)
WHERE EmpregadoID = 3

— Verifica as colunas de nome e salário
SELECT Nome, Salario FROM Empregados

A consulta retorna os empregados e seus salários criptografados (parte do resultado foi truncado na exibição)

Nome	Salário
Ademilda Souza Gonçalves	0x010000000D436D519FF7094F209CBA414C63D48C95DBCC868919A2AECEFDEBC53EC67…
Alexandre Pereira Neto	0x0100000041A7183124B568B47C5945C168C13382DBD8577476E3BE800F060F8CE220D…
Edmilson Alves Medeiros	0x010000000D436D519FF7094F209CBA414C63D48C95DBCC868919A2AECEFDEBC53EC67…

Realmente o salário de Ademilda e Edmilson continuam iguais na sua forma criptografada, agora vejamos se é possível descriptografá-los.

— Declara uma string para montar a chave simétrica
DECLARE @pwd NCHAR(8)
SET @pwd = N’ds-ospc!’

SELECT Nome,
    CAST(
        CAST(DECRYPTBYPASSPHRASE(@pwd,Salario,1,CAST(EmpregadoID As SYSNAME)) As VARCHAR(8))
    As SMALLMONEY) As Salario
FROM Empregados

A consulta mostra o resultado abaixo:.

Nome	Salário
Ademilda Souza Gonçalves	10256,78
Alexandre Pereira Neto	5360,29
Edmilson Alves Medeiros	NULL

De fato embora o salário criptografado seja exatamente o mesmo no momento de efetuar o retorno, o salário de Edmilson não pode ser descriptografado. Isso ocorreu porque embora a chave de criptografia utilizada seja exatamente a mesma (ds-ospc!), houve um autenticador a mais no processo de autenticação. Esse autenticador foi o EmpregadoID. O valor de EmpregadoID para Ademilda é 1 e para Edmilson é 2. Como ele usado como autenticador, não será possível que o salário de Edmilson seja corretamente descriptografado, pois, o EmpregadoID será diferente.

Para que o ataque tivesse êxito, seria necessário que Edmilson atualizasse também o seu valor para EmpregadoID, mas nesse caso, além dele não ter as permissões necessárias, a tabela possui uma unicidade em torno do ID que impediria que ele efetuasse a troca. Mesmo que ele conseguisse fazer isso, trocar o seu ID é semelhante a trocar sua identidade. Se esse ID for utilizado em outras tabelas ou for emitido no contracheque, ou ainda, for utilizado em outras tabelas, haverá vários indícios de fraude e certamente ele seria descoberto muito rapidamente. Imagine por exemplo que o ID seja utilizado para rastrear despesas históricas de viagem, reuniões de compromissos, etc. Nesse caso Edmilson teria que literalmente se responsabilizado por todas as atividades de Ademilda e esse tipo de fraude seria descoberto muito rapidamente.

Esse tipo de ataque não é exclusivo do SQL Server e tampouco de banco de dados. O fato de um dado ser criptografado não necessariamente significa que ele está protegido contra todos os tipos de violações de integridade e confiabilidade. O uso de autenticadores pode reduzir esse tipo de ameça embora não dispense uma boa gestão de permissões e guarda das chaves de criptografia utilizadas.

[ ]s,

Gustavo