Archive

Posts Tagged ‘Performance’

Conhecendo Indices Step-By-Step

Olá Galera, boa noite.

Desta vez escolhi um assunto extremamente importante e que vejo muita gente falando por ai. Porém, muitas pessoas ainda não entendem perfeitamente como funciona perfeitamente. Meu objetivo neste post é fazer com que o leitor entende como o SQL Server armazena e lê os dados. Isso mesmo, irei falar sobre índices. Esse artigo será divido em diversas partes, por ser um assunto com muito conteúdo e de certa forma complexo.  Já cheguei a falar sobre este assunto no blog, porém, de uma forma introdutória. Este post é dedicado a Andressa A. Martins, ela é uma pessoa que tem me ajudado muito na minha mais nova empreitada e me instruiu como os meus posts podem ter maior visibilidade na comunidade SQL Server.

Obs: Nos posts eu não vou traduzir nonclustered indexes, clustered indexes e alguns termos conhecido na documentação, pelo simples motivo de ficar ZUADO a tradução ao pé da letra.

Introdução:

Os índices são criados em colunas de uma tabela ou em view. O índice oferece uma maneira rápida de procurar dados com base nos valores dentro dessas colunas. Por exemplo, se você criar um índice para a chave primária e em seguida, procure uma linha de dados baseado em um dos valores de chave primária, SQL Server primeiro verifica que o valor no índice, e depois usa o índice para localizar rapidamente toda a linha de dados. Sem o índice, uma verificação de tabela teria de ser realizada a fim de localizar a linha, que pode ter um efeito significativo no desempenho.

Você pode criar índices na maioria das colunas de uma tabela ou em uma view. As exceções são principalmente as colunas configuradas com tipos de dados, tais como imagem, texto, e varchar (max) conhecidos como LOB’s. Você também pode criar índices em colunas XML, mas esses índices são ligeiramente diferentes do índice básico.

B-Tree:

No SQL Server, índices são organizados em B-Trees (O B é de Balanced e não de Binário). Cada página de um index em uma B-Tree é chamada de Nó (Node). O topo do nó da da B-Tree é chamado de Nó Root(Root Node). O ultimo nível desta estrutura é chamado de Nivel Folha (Leaf nodes). Qualquer nível entre o nível folha e o root é conhecido como Nível Intermediário.  As páginas de cada nível do índice estão relacionadas em uma lista duplamente ligada. Tendo como a função de um ponteiro de página para página, porém, isso não ocorre apenas em um sentido. Esses “ponteiros” existem em ambos os sentidos das páginas de dados, cada página tem referência para a página anterior (Last Page) e para a próxima página (Next Page).

Organização de uma Tabela:

A tabela contém uma ou mais partições e cada partição contém linhas de uma heap ou clustered index. As páginas de uma heap ou clustered index são gerenciadas em uma ou mais unidades de alocações (allocation unit) dependendo do tipo de dados. Como pode ser visto, podemos ter três diferentes tipos de unidade de alocação que gerenciam este armazenamento, como: Data, LOB e Row-Overflow.

Para verificar as unidades de alocações usadas pelo seu banco de dados, consulte a DMV: select* from sys.allocation_units

No meu caso eu estou mostrando quais as unidades de alocação que estão sendo utilizadas:

Partições:

Tabelas e páginas de índices são contidas em uma ou mais partições. Por padrão, uma tabela ou índice tem somente uma partição que contém toda a tabela ou as páginas de índice. Uma partição reside em apenas um único FileGroup. Quando temos uma tabela ou índice em múltiplas partições, os dados são particionados horizontalmente e um determinado grupo de linhas que é mapeado dentro de uma partição individual. Esse comportamento pode ser verificado em um artigo que falo sobre particiona mento: https://tcalencar.wordpress.com/2012/03/06/particionando-tabelas/. Para verificar as partições do seu banco de dados, consulte a DMV: select* from sys.partitions

E os níveis da B-Tree que você falou, onde estão? Vamos procurar a nossa resposta. O script cria uma tabela, adiciona um clustered index e realiza algumas inserções.

USE tempdb

GO

IF OBJECT_ID(‘dbo.TabelaFramentada ‘) IS NOT NULL

BEGIN

      DROP TABLE dbo.TabelaFramentada

END

 

CREATE TABLE dbo.TabelaFramentada (ID CHAR(800),

Nome CHAR(2100),

UltimoNome CHAR(2500),

Cidade CHAR(2200))

GO

 

CREATE CLUSTERED INDEX [IXNC_FragTable_ID] ON dbo.TabelaFramentada

(

[ID] ASC

) ON [PRIMARY]

GO

 

INSERT INTO dbo.TabelaFramentada (ID,Nome,UltimoNome,Cidade)

SELECT TOP 100 ROW_NUMBER() OVER (ORDER BY a.name) RowID,

‘Thiago’,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN ‘Bianca’

ELSE ‘Andressa Martin’ END,

CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN ‘Pinho’

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN ‘TT Maia’

WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN ‘Luiz Henrique’

ELSE ‘Bruno Catapano’ END

FROM sys.all_objects a

CROSS JOIN sys.all_objects b

Para verificar os níveis do índice, executaremos a seguinte query:

SELECT OBJECT_NAME(object_id)

,index_level

,record_count

FROM sys.dm_db_index_physical_stats(DB_ID(‘TempDb’),OBJECT_ID(‘dbo.TabelaFramentada’),NULL,NULL,’DETAILED’)

Comparando o resultado da query com o primeiro print deste post (Estrutura B-Tree) podemos afirmar que temos os seguintes níveis: Um nível folha, dois níveis intermediários e o Nível Root.

No próximo post iremos entender como o SQL Server armazena e lê os dados através de Clustered Indexes, NonClustered Indexes e  Heaps.

 

Referências:

http://blog.sqlauthority.com/2010/07/04/sql-server-index-levels-page-count-record-count-and-dmv-%C2%A0sys-dm_db_index_physical_stats/

http://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Books  On Line

Espero ter ajudado!

[] Thiago Carlos [TC] de Alencar

Performance começa na modelagem II

Boa noite galera! Como falei no meu post anterior, “apareci” novamente aqui para falar sobre modelagem. Vou mostrar alguns descuidos que podem causar futuro problemas de performance. Ou até mesmo causado por falta de conhecimento. E é aqui que nós profissionais de SQL Server entramos, pra ajudar os amigos de profissão e tirar dúvidas sempre quando possível.

Agora apresentarei as definições das formas normais:

1º Forma normal – Requer que todos os valores de colunas em uma tabela, sejam atômicos (ex., um número é um átomo, enquanto uma lista ou um conjunto não o são). Por exemplo, a normalização elimina grupos repetidos pondo-os cada um em uma tabela separada, conectando-os com uma chave primária ou estrangeira.

2º Forma normal –  Requer que não haja dependência funcional não-trivial de um atributo que não seja a chave, em parte da chave candidata.

3º Forma normal – Requer não haver dependências funcionais não-triviais de atributos que não sejam chave, em qualquer coisa exceto um superconjunto de uma chave candidata.

Referência: http://pt.wikipedia.org/wiki/Normaliza%C3%A7%C3%A3o_de_dados

Agora imagine que o programador ou o analista de sistemas recebeu a seguinte especificação:

Criar uma sistema de call center que recebe um lote de informações de clientes que devem receber ligações para venda de cartões. Porém, as informações são recebidas em um arquivo de texto, sendo que o nome do cliente, CPF do cliente, telefone residencial do cliente e telefone celular do cliente fazem parte de um único registro. Como em terra de cego que tem um olho é rei. Nosso ilustre amigo criou uma única tabela para receber os dados e assim facilitando o seu trabalho. A tabela ficou da seguinte forma:

USE MCITP
GO
IF OBJECT_ID(‘dbo.TB_CLIENTE’) IS NOT NULL
BEGIN
DROP TABLE dbo.TB_CLIENTE
END
GO
CREATE TABLE dbo.TB_CLIENTE
(
COD_CLI INT IDENTITY NOT NULL PRIMARY KEY,
CPF_CLI CHAR(11) NOT NULL ,
DD_TEL_RES CHAR(2) NOT NULL,
TEL_RES_CLI CHAR(8) NOT NULL,
DD_CEL_CLI CHAR(2) NOT NULL,
TEL_CEL_CLI CHAR(8) NOT NULL
)

Até aqui tudo parece perfeito, porém, a equipe gerencial deseja que uma tela de consulta seja criada e que seja possível realizar consulta por cpf, telefone residencial e telefone celular. Na tela todas os valores serão opcionais exceto cpf, teoricamente, deveria existir um índice para cada coluna, isso iria garantir que o SQL Server usasse o índice requisitado para a coluna escolhida como parâmetro.

O objetivo deste post não é falar sobre indexação ou estratégia de indexação. Esse assunto será abordado em um outro post com maiores detalhes e maiores abordagens. O que está sendo apresentando aqui acontece por ai no mercado, pois, esse é uma situação a qual eu já presenciei.

Voltando ao nosso assunto como todo mundo sabe que índice é “bom” para consultas, nosso amigo criou um índice para a coluna da cpf, telefone residencial e telefone celular.

USE MCITP
GO
CREATE NONCLUSTERED INDEX IX_CPF_CLI ON dbo.TB_CLIENTE (CPF_CLI)
GO
CREATE NONCLUSTERED INDEX IX_TEL_CLI ON dbo.TB_CLIENTE (DD_TEL_RES,TEL_RES_CLI)
GO
CREATE NONCLUSTERED INDEX IX_CEL_CLI ON dbo.TB_CLIENTE (DD_CEL_CLI,TEL_CEL_CLI)

Vamos aos fatos, um índice é uma faca de dois gumes. Em um ambiente OLTP temos muita leitura e criar índices ajuda a devolver os dados de forma mais eficaz e faz com que o SQL Server crie planos de execução mais eficientes. Por outro lado, quando você cria muito índice é possível que tenha baixa performance em instruções INSERT, UPDATE e DELETE. É correto afirmar que: O SQL Server organiza os índices em uma estrutura B-Tree (Esse assunto não será abordado neste post). E quando um índice clustered é atualizado ele deve manter os índices non-clustereds atualizados também. Já conseguiram sacar onde está o nosso possível overhead nas instruções que modificam os dados? Não! Então vamos lá!

Podemos ver que na consulta abaixo é possível verificar quais os índices que foram criados. Temos quatro índices. Um índice clustered para a coluna COD_CLI que é a nossa primary key  e os demais índices NONCLUSTEREDs que críamos anteriormente.

select i.name,i.type_desc from sys.indexes i
where i.object_id = OBJECT_ID(‘dbo.TB_CLIENTE’)

Como não realizamos nenhuma instrução sobre a nossa tabela TB_CLIENTE, podemos perceber que o nível folha e o nível intermediário da B-Tree não sofreu nenhuma modificação:

SELECT i.name,a.leaf_insert_count,a.nonleaf_insert_count
,a.leaf_delete_count,a.nonleaf_delete_count
,a.leaf_update_count,a.nonleaf_update_count
,a.leaf_allocation_count,a.page_lock_count
FROM sys.dm_db_index_operational_stats(DB_ID(‘mcitp’),OBJECT_ID(‘dbo.TB_CLIENTE’),NULL,NULL) as a
join sys.indexes i
on i.index_id = a.index_id
and i.object_id = a.object_id

Agora iremos realizar alguns inserts em nossa tabela e veremos o que acontece.

INSERT INTO dbo.TB_CLIENTE(CPF_CLI,DD_TEL_RES,TEL_RES_CLI,DD_CEL_CLI,TEL_CEL_CLI)
VALUES(‘7893652′,’11’,’24985236′,’11’,’78964521′)
,(‘7896325236′,’11’,’78965230′,’11’,’69365212′)
,(‘33209376325′,’11’,’78785225′,’11’,’80756312′)
,(‘33369863201′,’11’,’96785421′,’11’,’56365425′)

Executaremos novamente a query que traz as modificações feitas no nível folha e no nível intermediário para ver como a nossa estrutura está após os inserts:

Thiago! Continuo sem entender! Então vamos lá? O que aconteceu!? Como temos quatro índices na nossa tabela e realizamos quatro inserções, que dizer que: O SQL Server também atualizou todos os nossos índices nonclustereds no nível folha da nossa estrutura B-Tree como eu havia explicado anteriormente. Podemos ver que, muito índice em uma tabela que recebe alto nível de inserções pode prejudicar a performance. Imagine que eu precisasse importar 2000 linhas. O SQL Server escreveria 2000 linhas para o índice clustered e 2000 para cada índice nonclustered.

Mais e a atualização de registros, como ficaria ? Vejamos irei atualizar a coluna CPF_CLI para o cliente com o código 1.

UPDATE dbo.TB_CLIENTE SET CPF_CLI = ‘123654789’ WHERE COD_CLI = 1

Pronto! Agora com a atualização na coluna CPF_CLI podemos ver um aumento de valor na coluna leaf_insert_count para 5 no indice da coluna CPF. O registro também foi modificado no índice clustered, isso pode ser visto na coluna leaf_update_count que tem o valor de 1.  Mas porque no índice clustered? O índice clustered são os dados propriamente ditos, quando fizemos o update na coluna de cpf o SQL Server

Espero ter ajudado. Até o próximo post.

Performance começa na modelagem

Desempenho começa na Modelagem.

Bom dia, Querido blog, tempo que não escrevo em você….rs. Hoje estou aqui apenas para falar  aos leitores sobre algumas pequenas dicas de performance  que as vezes passam despercebidos aos nossos olhos.

Vamos à análise.
Uma página de dados no SQL Server ocupa 8192 bytes, sendo 8060 bytes para os dados e o restante para o header da página. É correto afirmar que: criar um índice em uma coluna do tipo de dados inteiro que possui 2015 linhas vai ter uma página de dados completamente preenchida:

Calculo: Tipo de dados * Qtde de Linhas
Calculo: Int 4 Bytes * 2015 Linhas = 8060 bytes.

Thiago não estou entendendo aonde você que chegar!? Simples, meu caro blog! Será que preciso de uma coluna do tipo de dados int para a minha tabela. Desenvolvedores, analistas, programadores e estagiários é aqui que a gente entra.

Quando modelar alguma necessidade de negócio, sempre temos que nos atentar a que tipo de dados vamos usar e se é realmente necessário este  tipo de dados para determinada coluna. Já vi em muitos casos tabelas de domínio que apenas guarda poucos valores com o tipo de dados inteiro. Vamos a um exemplo:

YODA deve desenvolver um sistema de venda de produtos e foi especificado que os produtos a serem vendidos, seriam divididos em categorias. YODA criou o seguinte script para a tabela de categoria:

USE tempdb
go
IF OBJECT_ID(‘dbo.CategoriaProduto’) IS NOT NULL
BEGIN
DROP TABLE dbo.CategoriaProduto
END
CREATE TABLE dbo.CategoriaProduto (COD INT IDENTITY, DESCRICAO VARCHAR(50))

GO

Agora vem a pergunta! Até quantos registros posso armazenar em um tipo de dados inteiro? Segundo a definição do meu melhor amigo (Books On line), podemos armazenar -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). Será que preciso armazenar todas estas categorias? Se tivermos apenas 50 ou ate 250 categorias, poderíamos usar o tipo de dados tinyint. Com isso apenas usaríamos um byte de armazenamento. Mas preciso mesmo me preocupar com esses detalhes? Será que essa diferença em bytes pode afetar minha performance ? A resposta é SIM!. O usuário final ou até mesmo o desenvolvedor não pode sentir essa perda, porém, isso para o SQL Server é extremamente importante. Uma das questões é: Tomando esse devido cuidado com tipo de dados você pode economizar espaço em disco e cache. Pode acreditar o buffer pool agradece.

Como o SQL Server ler os dados?

Todos nós sabemos que a leitura em memória é mais rápida do que a leitura em disco. O SQL Server também sabe! Quando uma query é enviada ao SQL Server ele verifica se os dados estão em memória (Buffer Pool), antes de verificar se os dados do disco. Como os dados estão em memória o SQL Server apenas realiza leitura lógica (Não Física). Thiago! E o que tudo isso tem a ver com o que foi escrito anteriormente? SIMPLES. Quanto menor for o tipo de dados usado em nossas colunas, menor a quantidade de espaço iremos armazenar e mais informações teremos dentro do buffer pool, sendo assim, diminuindo o acesso ao disco e aumentando a quantidade de informações em memória.

Pessoal modelar é uma tarefa muito importante, então, por favor. Prestem atenção. Estarei criado mais posts referente a estes assuntos futuramente.

Clausula TOP em Subqueries

Ola Galera! Vou postar uma coisa que hoje estava avaliando junto a um amigo desenvolvedor aqui na nossa fábrica. Marcos Meskelis…Um dos melhores que ja conheci…dono de uma super lógica. Já me ajudou a sair de várias, mas, vamos aos fatos e ao SQL Server (a melhor parte). Tinhamos uma query que criava uma derived table e que dentro dela havia duas outras quries simples…por um tempo que conversavamos sobre o “Brasileirão” e tal… Veio a pergunta! O que o SQL Server faz se dentro da subquery uma das consultas tiver uma clausula TOP e a consulta “externa” que gera a Derived Table também tiver uma Clausula TOP que retorna uma menor quantidade de linhas da Clausula TOP interna? Será que o SQL Server irá ler as duas tabelas da subquery pra aplicar o filtro no TOP da query externa no output das  subqueries? A melhor resposta que tivemos de imediato foi. Vamos Testar!  Crio duas tabelas uma de cliente e uma outra tabela de fornecedor. Na tabela TB_CLIENTE e na tabela TB_FORNECEDOR e realizo a inserção de seis registros para cada.

use tempdb
go
IF OBJECT_ID(‘dbo.TB_CLIENTE’) IS NOT NULL
DROP TABLE dbo.TB_CLIENTE ;
IF OBJECT_ID(‘dbo.TB_FORNECEDOR’) IS NOT NULL
DROP TABLE dbo.TB_FORNECEDOR ;

CREATE TABLE dbo.TB_CLIENTE
(
COD INT IDENTITY
,NOME VARCHAR(50)
,CPF CHAR(20)
,RG CHAR(25)
CONSTRAINT PK_TB_CLIENTE PRIMARY KEY CLUSTERED(COD)
)

CREATE TABLE dbo.TB_FORNECEDOR
(
COD INT IDENTITY
,RAZAO_SOCIAL VARCHAR(50)
,NOME_FANTASIA VARCHAR(80)
,CNPJ CHAR(20)
CONSTRAINT PK_TB_FORNECEDOR PRIMARY KEY CLUSTERED(COD)
)

INSERT INTO dbo.TB_CLIENTE(NOME,CPF,RG) 
VALUES (‘THIAGO ALENCAR’,’89636978925′,’78452362X’) 
,(‘EMANOEL ALENCAR’,’79636978925′,’88452362X’) 
,(‘CAIO LACRAIA’,’99636978925′,’784523625′) ;

INSERT INTO dbo.TB_CLIENTE(NOME,CPF,RG) 
VALUES (‘ALENCAR’,’89636978925′,’78452362X’) 
,(‘EMANOEL ‘,’79636978925′,’88452362X’) 
,(‘CAIO ‘,’99636978925′,’784523625’) ;
INSERT INTO dbo.TB_FORNECEDOR(RAZAO_SOCIAL,NOME_FANTASIA,CNPJ)
VALUES (‘D2 DA SILVA’,’MISIFIO LTDA’,’01.222.333/0001-12′)
,(‘KARATE / ARTES ‘,’JULIAO LTDA’,’01.222.333/0001-00′)
,(‘T. BUENO LTDA’,’TB GUARULHOS’,’01.222.333/0001-00′)
INSERT INTO dbo.TB_FORNECEDOR(RAZAO_SOCIAL,NOME_FANTASIA,CNPJ)
VALUES (‘D2′,’MISIFIO LTDA’,’01.222.333/0001-12′)
,(‘ARTES ‘,’JULIAO LTDA’,’01.222.333/0001-00′)
,(‘T. BUENO LTDA’,’GUARULHOS’,’01.222.333/0001-00′)

Vamos testar…rs! Habilite o plano de execução (Ctrl+M) e execute a query abaixo:

SET STATISTICS PROFILE ON
SELECT 
TOP 6 * 
FROM 
(
SELECT  COD, NOME,CPF FROM dbo.TB_CLIENTE 
UNION ALL
SELECT COD, NOME_FANTASIA,CNPJ FROM DBO.TB_FORNECEDOR 
) AS A
OPTION(RECOMPILE)
SET STATISTICS PROFILE OFF
A tabela de TB_CLIENTE contém 6 registros, assim como a de fornecedor. O SQL Server realiza um clustered index scan nas duas tabelas, porém, não é retornado nenhum registro da tabela de fornecedor. Pois o SQL Server  sabe que existem 6 registros na tabela de cliente e o TOP da consulta “externa” solicita o retorno de 6 registros.  Esse comportamento pode ser visto no plano de execução.
Obs: O SQL Server é completamente inteligente para não realizar a leitura em outro indice de uma  outra tabela. Já que o resultado do TOP quer apenas 6 registros. Vale lembrar que esse é apenas  um cenário para ver como as coisas funionam e qual caminho o SQL Server optou usar. Quando o TOP  é maior que 100 o SQL Server realiza um outro algoritmo. Isso pode mudar o plano de execução
 apresentado aqui.

Cuidado ao escrever queries

Post dedicado ao meu irmão gêmeo Emanoel Carlos de Alencar, que está se esforçando pra entrar na área de banco. Infelizmente foi pro Oracle, quem sabe consigo puxá-lo para o “mundo” SQL Server.
Obs: Apenas uma brincadeira, os dois produtos são bons. Sempre temos que avaliar qual a real necessidade do cliente para poder sugerir uma solução que o atenda e que “entre” no seu bolso.

Ola galera. blz? Em um dos meus artigos anteriores eu mostrei como trocar o uso do NOT IN para o  Left JOIN(Link), porém, porque fizemos isso? Qual Ganho de performance ?
Se você já executou as duas queries do artigo anterior com o plano de execução habilitado, você verá que o plano e o custo estimado de ambos são iguais. Vamos ver?

SELECT c.cod,c.nome FROM dbo.TB_CLIENTE c WHERE c.cod NOT IN(SELECT p.cod_cli FROM dbo.tb_pedido p) OPTION(RECOMPILE)

SELECT c.cod,c.nome, p.COD,p.COD_CLI FROM dbo.TB_CLIENTE c LEFT JOIN dbo.tb_pedido p ON c.cod = p.cod_cli
WHERE p.COD_CLI is null OPTION(RECOMPILE)

Thiago Que dizer que não ganhamos nada fazendo essa troca!?Não é assim! O que faz com que o SQL Server escolha quais operadores irão ser usados para acessar os dados, são as estatisticas das suas tabelas e também os indices. Neste post não irei entrar em detalhes, mas , se quiserem saber mais umas das otimas referências que temos no cenário brasileiro é o Fabiano Neves Amorim(Sabe tudo de QO). O objetivo deste post é mostrar que quando temos duas tabelas com quantidade insignificantes de registros, o SQL Server, pode escolher o mesmo plano, realizar um clustered index scan para obter o mesmo resultado e devolve-lo ao usuário. Isso foi visto no print anterior que com queries diferentes o SQL Server montou o mesmo plano. Mas se as tabelas existissem milhões de linhas e de pagina de dados,  será que o SQL Server iria usar os mesmos operadores para devolver os dados? Vamos ver isso na pratica? O que iremos fazer é:  atualizar as estatisticas do SQL Serve e executar novamente as duas queries.

–Atualiza as estatisticas da tabela TB_CLIENTE
UPDATE STATISTICS dbo.TB_CLIENTE WITH ROWCOUNT = 500000, PAGECOUNT = 200
–Atualiza as estatisticas da tabela TB_PEDIDO
UPDATE STATISTICS TB_PEDIDO WITH ROWCOUNT = 500000, PAGECOUNT = 200

Obs: Na verdade nos comandos acima estamos atualizando as estatisticas apenas para propositos de testes. Isso apenas mostra como é importante deixar as estatisticas sempre atualizadas dos nossos banco de dados.

SELECT c.cod,c.nome FROM dbo.TB_CLIENTE c WHERE c.cod NOT IN(SELECT p.cod_cli FROM dbo.tb_pedido p) OPTION(RECOMPILE)

SELECT c.cod,c.nome, p.COD,p.COD_CLI FROM dbo.TB_CLIENTE c LEFT JOIN dbo.tb_pedido p ON c.cod = p.cod_cli
WHERE p.COD_CLI is null OPTION(RECOMPILE)

Executando novamente as queries, podemos ver que a diferença entre o custo usado pelo plano de execução da query que utiliza o NOT IN é superior ao custo da query que usa o operador LEFT JOIN. Quando você está trabalhando com otimização de queries, sempre tente imaginar um cenário com muitas linhas, pois, banco de dados crescem. Uma vez ouvi o seguinte de um certo gestor que tive: “Essa aplicação, funcionava no começo! Porque agora está apresentando lentidão?”. Simples o banco de dados no inicio da aplicação tinha 20 MB de base de dados, hoje tem 130 GB. Então fica a dica, queries bem escritas + estatisticas atualizadas + indices corretos = Vida com Menos Stress

Espero ter ajudado

Calculo SQL Server com Windows Function

Ola galera, bom dia(Agora já é tarde, tive que parar pra resolver umas “buchinhas”, voltando após almoço e globo esporte). Blz ?
Hoje iremos fazer uma query usando as windows Function do SQL Server.Esse post  foi originado de uma query que tivemos que criar aqui na empresa. E a maneira mais “elegante” de escrever a  query foi usando as Windows Function.
Tendo as seguintes informações em um arquivo .xls, que me foi passado por um amigo desenvolvedor:
TabelaA

 

TabelaB

E o mesmo deseja o seguinte resultado as informações:

 

Vou explicar os calculos:

primeira Coluna “SomaProdutos” é a soma dos produtos das duas tabelas por IdPeriodo
Ex: A tabela A e a Tabela B armazena a informação de Valor para o periodo  1.
A soma dos valores 77 + 55 é  igual a 132 e assim sucessivamente.
A coluna “DiasUteis” é uma informação que esta atrelada a cada IdPeriodo. Em um periodo existe uma
certa quantidade de dias uteis para aquele periodos. No meu caso trasnformei essas    informações em
uma tabela de dias uteis e o IdPeriodo.
A coluna “Multiplicação” é a multiplicação dos DiasUteis por SomaProdutos.
A coluna “SomaMultiplicacao” é a soma total da coluna “Multiplicacao”.
A coluna “SomaDias” é a soma da coluna DiasUteis.
E a ultima coluna “Resultado” é a Divisão da coluna  “SomaMultiplicacao” por “SomaDias”.

Uma das vantagens da Windows Function é que podemos ter acesso aos detalhes de uma informação agrupada.
Mais informações Fabiano Amorim: http://blogs.solidq.com/fabianosqlserver/Post.aspx?ID=58

Vamos aos código (A parte boa….rs).
Primeiro crio as tabelas temporarias conforme arquivo .xls (É possivel fazer apenas com uma tabela de periodo,mas, depois da fejuca. Vamos que vamos). Crio a tabela de dias uteis e insiro as informações do meu arquivo .xls.
use tempdb
go
create table #tmp_produto_um(id_periodo int, id_produto int, valor decimal(19,2))
create table #tmp_produto_dois(id_periodo int, id_produto int, valor decimal(19,2))
create table #tmp_dias_uteis(id_periodo int, qtdeDias int)

insert into #tmp_produto_um(id_periodo,id_produto,valor) values(1,18,55.00),(2,18,44.00),(3,18,33.00),(4,18,22.00)
insert into #tmp_produto_dois(id_periodo,id_produto,valor) values(1,19,77.00),(2,19,66.00),(3,19,44.00),(4,19,33.00)
insert into #tmp_dias_uteis(id_periodo,qtdeDias)values(1,22),(2,21),(3,22),(4,23)

SELECT * FROM #tmp_produto_um
SELECT * FROM #tmp_produto_dois
SELECT * FROM #tmp_dias_uteis

 

 

 

Crio uma CTE com a soma dos Valores que no .xls é: SomaProdutos.A multiplicação que no .xls é:DiasUteis * SomaProdutos.

;WITH cte AS
(
SELECT    p.id_periodo                                AS IdPeriodo
        ,SUM(d.valor) + SUM(p.valor)                 AS ValorTotal
        ,u.qtdeDias                                    AS QtdeDias
        ,(SUM(d.valor) + SUM(p.valor)) * u.qtdeDias    AS ProdutVsDiasUteis
        FROM #tmp_produto_um p
        JOIN #tmp_produto_dois d
        ON p.id_periodo = d.id_periodo
        JOIN #tmp_dias_uteis u
        ON u.id_periodo = p.id_periodo
        GROUP BY p.id_periodo,u.qtdeDias
)
Abaixo realizo a query chamando a CTE e usando o SUM com o OVER() para que possa ser realizado a soma das colunas, sem a necessidade de agregação. E dentro da SubQuery crio a soma para ser usada como a coluna resultado. Código total abaixo:

;WITH cte AS
(
SELECT    p.id_periodo                                AS IdPeriodo
        ,SUM(d.valor) + SUM(p.valor)                 AS ValorTotal
        ,u.qtdeDias                                    AS QtdeDias
        ,(SUM(d.valor) + SUM(p.valor)) * u.qtdeDias    AS ProdutVsDiasUteis
        FROM #tmp_produto_um p
        JOIN #tmp_produto_dois d
        ON p.id_periodo = d.id_periodo
        JOIN #tmp_dias_uteis u
        ON u.id_periodo = p.id_periodo
        GROUP BY p.id_periodo,u.qtdeDias
)
SELECT  
        c.IdPeriodo,
        c.ValorTotal,
        c.QtdeDias,
        c.ProdutVsDiasUteis
        ,SUM(c.ProdutVsDiasUteis)        OVER()    AS SomaProdutVsDiasUteis
        ,SUM(c.QtdeDias)                OVER()    AS SomaTotalDias
        ,ROUND(D.SomaDiasUteis / D.SomaDias,2)  AS GeralCalculado
        FROM cte c
        JOIN (    SELECT    a.IdPeriodo
                        ,SUM(a.ProdutVsDiasUteis)        OVER()    AS SomaDiasUteis
                        ,SUM(a.QtdeDias)                OVER()    AS SomaDias
                FROM  cte a
                ) as D
        ON c.IdPeriodo = D.IdPeriodo

Como pode ser visto o resultado do SQLServer é o mesmo que o arquivo .xls que foi colocada no print anteriormente.
Resultado SQL Server:


Espero ter ajudado.
Abs a todos…e tenham uma ótima semana.


Funções de Ranking

No SQL Server 2005 apareceram as funes de ranqueamento. Essas funções nos ajudam a realizar algumas tarefas com mais facilidade que anteriormente(SQL 2000) .Você pode criar um numero incremental em uma consulta(simular a propriedade identity) ,criar funcionalidade de paginação e etc. Aqui mostrarei alguns exemplos onde essas funções possam ser aplicadas: A função ROW_NUMBER() acrescenta um inteiro incremental em uma consulta. Imagine que você precise montar uma consulta com o valor incremental e a nossa tabela não existe uma coluna identity(auto incremento), você pode usar a função ROW_NUMBER() para simular esse comportamento.

use tempdb
GO
if OBJECT_ID(‘dbo.tb_incrementa’,’U’)isnot null
drop table   dbo.tb_incrementa ;
create table dbo.tb_incrementa(cliente intnotnull,nome varchar(30)notnull)
INSERT INTO dbo.tb_incrementa(cliente,nome)
VALUES (10,’Kalen Delaney’),(15,’Paul Randal’),
(16,’Kimberly L. Tripp’),(19,’Itzik Ben Gan’),
(20,’Bill Gates’),(23,’Pinal’)

O select traz as informações dos clientes que tem código e nome,  o codigo do mesmo no incremental se nós quisessemos realizar uma consulta dando o valor incremental para os valores usaremos o ROW_NUMBER().

SELECT *  FROM dbo.tb_incrementa


Com a query abaixo possível criar um valor incremental para cada registro:
SELECT nome ASNomeCliente , ROW_NUMBER()OVER(ORDERBY cliente ASC)AS ValorInrementalFROM dbo.tb_incrementa:

Usando RANK e DENSE_RANK
Abaixo crio uma tabela chamada tb_corrida que armazena o nome de alguns pilotos e o tempo que os mesmos realizaram asprovas em uma competição.

if OBJECT_ID(‘dbo.tb_corrida’,’U’)is not  null
drop  table dbo.tb_corrida ;
create table dbo.tb_corrida
(idCorrida  intidentitynotnull, nomeCorredor varchar(30)notnull,
dataCorrida datenotnull, tempoCorrida timenotnull )

INSERT INTO dbo.tb_corrida(nomeCorredor,dataCorrida,tempoCorrida)
VALUES (‘Sebastian Vettel’,CURRENT_TIMESTAMP,’01:35:12:333′ ),
(‘Lewis Hamilton’,CURRENT_TIMESTAMP,’01:45:01:123′),
(‘Mark Webber’,CURRENT_TIMESTAMP,’01:45:00:128′),
(‘Felipe Massa’,CURRENT_TIMESTAMP,’01:59:15:123′),
(‘Rubens Barrichello’,CURRENT_TIMESTAMP,’01:59:15:123′),
(‘Vitaly Petrov’,CURRENT_TIMESTAMP,’02:15:16′),
(‘Sebastian Buemi’,CURRENT_TIMESTAMP,’02:15:16′),
(‘Jenson Button’,CURRENT_TIMESTAMP,’02:30:00′),
(‘Fernando Alonso’,CURRENT_TIMESTAMP,’02:31:56′)

Imagine que precisaremos criar uma query com o ranking de chegada dos corredores baseando-se no menor tempo de corrida poderiamos usar a função RANK() ou DENSE_RANK(). A única diferença entre elas que a RANK deixa um “buraco” para as informações que tem o mesmo valor. Você pode observar que Felipe Massa e Rubinho tem o mesmo tempo de conclusão da prova, fazendo os dois ficarem na mesma posição. A próxima classifição que deveria ser o “5” foi ignorado para o SQL Server. Quando a função DENSE_RANK() é utilizada esse “gap” desaparece. Como pode ser visto o SQL Server realiza o Rank em cima do tempo de concluso da prova.

SELECT nomeCorredor AS NomePiloto, tempoCorrida AS Tempo, RANK()OVER(ORDERBY tempoCorrida ASC)AS PosicaoRank, DENSE_RANK()OVER(ORDERBY tempoCorrida ASC)AS PosicaoDenseRank FROM dbo.tb_corrida

Um outro exemplo que pode ser feito montar o rank mundial dos corredores tendo como  parâmetro o número de vitórias/pontos do corredor. O corredor que tiver mais vitórias/ponto,  ser “top” no pódio. Esse exemplo também poderia também ser aplicado para criar a  tabela do campeonato brasileiro por exemplo. Abaixo crio a tabela tb_podio e populo com algumas informações ficticias.

if OBJECT_ID(‘dbo.tb_podio’,’U’)isnotnull
droptable dbo.tb_podio ;
createtable dbo.tb_podio(
idCorrida intidentitynotnull,
nomePiloto varchar(30)notnull,
Pais varchar(15)notnull,
totalPontos int,
Equipe varchar(40),
numeroVitorias int )
INSERT INTO dbo.tb_podio(nomePiloto,Pais,totalPontos,Equipe,numeroVitorias)
VALUES (‘Sebastian Vettel’,’ALE’,148,’RBR-Renault’, 4),
(‘Lewis Hamilton’,’ING’,85,’McLaren-Mercedes’,1),
(‘Mark Webber’,’AUS’,79,’RBR-Renault’,0),
(‘Rubens Barrichello’,’BRA’,2,’Williams-Cosworth’,0),
(‘Vitaly Petrov’,’ING’,21,’Renault-Lotus’,0),
(‘Sebastian Buemi’,’SUI’,7,’STR-Ferrari’,0),
(‘Jenson Button’,’ING’,76,’McLaren-Mercedes’,0),
(‘Fernando Alonso’,’ESP’,69,’Ferrari’,0 )

Abaixo a query e feita pelo o total de pontos acumulados pelos corredores. Montando o rank mundial por pontos.A query também poderia ser feita pelo numero de vitórias.

SELECT Equipe,nomePiloto as Piloto, numeroVitorias,TotalPontos ,
DENSE_RANK()OVER(ORDERBY totalPontos DESC)As Classificacao
FROM dbo.tb_podioORDERBYClassificacao

Usando o NTILE
O NTILE() usado para dividir um conjunto de resultados em grupos aproximadamente iguais. Imagine que você tem uma tabela de pedidos com o valor total dos pedidos. O departamento de negócios da empresa solicitou que você criasse uma query que trouxesse os produtos em grupo por valor.
O NTILE() recebe um inteiro como parâmetro, esse numero é usado para quebrar o total de registros em grupos. Quando o total de registros é divido em grupos pelo valor do parâmetros de entrada  do NTILE() o SQL Server incrementa a quantidade de registros da divisao(que sobraram) para o primeiros grupos. Por exemplo: Se temos 32 registros e dividimos ele em 3 grupos, o SQL Server ir criar 3 grupos com 10 registros. Com os dois registros restantes o SQL Server ir colocar um no primeiro grupo e outro no segundo. A query abaixo cria tr categorias para os produtos tendo como base o valor dos mesmos, so elas: Baixo, Medio e Alto.

if OBJECT_ID(‘dbo.tb_produtos’) is not null
 drop table dbo.tb_produtos ;
create table dbo.tb_produtos
(
codigo int identity not null,
descricao varchar(50) not null,
valor money not null
)
GO
INSERT INTO dbo.tb_produtos(descricao,valor)
VALUES (‘Lapis’,1.00), (‘Caneta’,1.50),(‘Celular’,130.00)
  ,(‘Livros PMO’,175.00), (‘Monitor’,450.00),(‘TV’,1200.00)
  ,(‘DVD’,450.00),(‘MP4’,250.00),(‘New Civic’,60000.00)
  ,(‘NoteBook’,2500.00),(‘Fogao’,176.00)
go
SELECT
  descricao AS Produto,
  valor  AS Valor,
 CASE NTILE(3) OVER(ORDER BY valor)
    WHEN 1 THEN ‘Baixo’
    WHEN 2 THEN ‘Médio’
    WHEN 3 THEN ‘Alto’
    ELSE ‘Sem Categoria’
 END AS DescricaoTitulo
FROM dbo.tb_produtos
ORDER BY valor;

Abs Galera. Por enquanto é só.