Archive

Archive for August, 2011

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.
Advertisements

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

Concatenando Valores na Mesma Linha – T-SQL

Galera, boa tarde. Este post é dedicado ao meu amigo Caio Vinicius. Ele só tem um problema: Programa em Java…rs.Mais o mano é firmeza. Vamos ao post! Colocando a mão na massa.

Estou trazendo hoje uma solução que apliquei em uma procedure hoje aqui na empresa. “Teoricamente” o problema poderia ser resolvido por um loop. Essa foi a primeira idéia do desenvolvedor,mas , pedi para ele que tivesse paciência para ambos pensarmos em uma solução em lote(Além de performática é mais elegante). Uma vez li que: Qualquer instrução que é feito linha-a-linha pode ser modificada para uma instrução “set-based” By Itzik.

Vamos Ao Cenário:
Em um ambiente OLTP, você tem uma tabela PAI e uma tabela FILHO em um relacionamento “1:N”. Um pai pode ter um ou mais filhos. O print abaixo mostra a estrutura das tabelas.

Como pode ser visto a Id 1(“X”) é Pai dos registros 1,2 e 3(A,B e C) na tabela filho (Contorno preto). E o Id 2 (y) é pai dos registros 4 e 5 (D e E) na tabela filho(Contorno Vermelho).

Um amigo desenvolvedor gostaria de realizar uma query deveria trazer o registro Pai em uma linha juntamente com o nome dos filhos concatenados na mesma linha. O resultado seria algo mais o menos assim:


Sabiamos que para não realizar um loop, poderíamos usar CTEs para resolver nosso problema, então fomos navegar na net até que achamos os caminhos das pedras (Referências no final do artigo).

Vamos ao código:
Primeiramente crio as tabelas #pai e #filho com os registros que foram ilustrados no print das estruturas da tabela.

Use tempdb
go

create table #pai(id int, descricao varchar(50))
create table #filho(id int, descricao varchar(50),id_pai int)

insert into #pai(id,descricao)values(1,’X’),(2,’Y’)
insert into #filho(id,descricao,id_pai)values(1,’A’,1),(2,’B’,1),(3,’C’,1)
insert into #filho(id,descricao,id_pai)values(4,’D’,2),(5,’E’,2)

Neste exeplo foram criadas três CTEs para resolver o problema.

;WITH RangeTabelaFilho ( PaiID, rnk, Filho )
AS (
      SELECT    f.id_pai as IdPai,
                ROW_NUMBER() OVER(PARTITION BY f.id_pai ORDER BY f.id_pai ) as IncrementoPorIdPai,
                CAST(f.descricao AS VARCHAR(8000))
         FROM #filho f
         join #pai p
         on f.id_pai = p.id
    )

Dentro desta CTE é feito um select da tabela #filho e da tabela #pai. A função ROW_NUMBER com a claúsula  Partition By diz ao SQL Server o seguinte: Cria um valor incremental inteiro e quando o idPai “mudar” recomeça a contagem. Esse comportamento pode ser visto nas linhas contornadas do print anterior.

2º – A segunda CTE chamada “AncoraDoRank” faz uma consulta na primeira CTE apenas filtrando os membros ancoras que são os registros com o rnk = 1. Perceba que as ancoras da consulta são os registros contornados no
primeiro print:

,AncoraDoRank (PaiID, rnk, Filho)
AS
(
            SELECT PaiID,rnk,Filho
            FROM RangeTabelaFilho        WHERE rnk = 1
)

3º – A terceira CTE chamada “RecursoRanqueamento” é uma CTE recursiva que é feita entre as duas primeiras CTEs já citadas anteriormente. A CTE recursiva usa a CTE “AncoraDoRank” para pegar os membros ancoras. E faz a recursividade com a CTE “RangeTabelaFilho” e a própria “RecursoRanqueamento”. O resultado é o seguinte:

,RecursoRanqueamento (PaiID, rnk, Filho)
AS
(

        SELECT PaiID , rnk, Filho
        FROM AncoraDoRank
        UNION ALL
        SELECT    RangeTabelaFilho.PaiID, RangeTabelaFilho.rnk,
                        RecursoRanqueamento.Filho + ‘, ‘ + RangeTabelaFilho.Filho
                  FROM RangeTabelaFilho
            INNER JOIN RecursoRanqueamento
            ON RangeTabelaFilho.PaiID = RecursoRanqueamento.PaiID
            AND RangeTabelaFilho.rnk = RecursoRanqueamento.rnk + 1
 )

Perceba que foram retornados cinco registros. Para cada “nivel” de rank o SQL Server criou a concatenação para cada leitura que ele passou dentro da recursividade.

Ex: Rank 1,2 e 3 do IdPai de numero 1,o resultado foi: Rnk 1 = A, Rnk 2 = A,B e Rnk 3 = A,B,C.
No final apenas faço um SELECT na CTE trazendo PaiID e realizando um MAX na coluna nome dos Filho, para que o resultado seja apenas duas linhas (PaiID 1 e 2). O resultado atende a necessidade anterior sem que seja necessário
o uso de um loop, cursor.  Abaixo Segue o script geral da solução:

Use tempdb
go

create table #pai(id int, descricao varchar(50))
create table #filho(id int, descricao varchar(50),id_pai int)

insert into #pai(id,descricao)values(1,’X’),(2,’Y’)
insert into #filho(id,descricao,id_pai)values(1,’A’,1),(2,’B’,1),(3,’C’,1)
insert into #filho(id,descricao,id_pai)values(4,’D’,2),(5,’E’,2)

;WITH RangeTabelaFilho ( PaiID, rnk, Filho )
AS (
      SELECT    f.id_pai as IdPai,
                ROW_NUMBER() OVER(PARTITION BY f.id_pai ORDER BY f.id_pai ) as IncrementoPorIdPai,
                CAST(f.descricao AS VARCHAR(8000))
         FROM #filho f
         join #pai p
         on f.id_pai = p.id
    )
,AncoraDoRank (PaiID, rnk, Filho)
AS
(
            SELECT PaiID,rnk,Filho
            FROM RangeTabelaFilho        WHERE rnk = 1
)
,RecursoRanqueamento (PaiID, rnk, Filho)
AS
(

        SELECT PaiID , rnk, Filho
        FROM AncoraDoRank
        UNION ALL
        SELECT    RangeTabelaFilho.PaiID, RangeTabelaFilho.rnk,
                        RecursoRanqueamento.Filho + ‘, ‘ + RangeTabelaFilho.Filho
                  FROM RangeTabelaFilho
            INNER JOIN RecursoRanqueamento
            ON RangeTabelaFilho.PaiID = RecursoRanqueamento.PaiID
            AND RangeTabelaFilho.rnk = RecursoRanqueamento.rnk + 1
 )

SELECT PaiID, MAX( Filho ) AS NomeFilhos FROM RecursoRanqueamento  GROUP BY PaiID;
DROP TABLE #filho
DROP TABLE #pai

Espero ter ajudado.

Referências:
http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=79050
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Retorna dados Aleatórios

Ola galera, blz ?
Hoje irei postar uma dica bem rápida aqui. Nesta manhã um desenvolvedor me perguntou:  Thiago como você faria para
criar uma query usando um top 10 que sempre retorne valores aleatórios?
Suspense no AR ………….EURECA!
Se desejo ordenar os valores em ordem aleatórios,  a coluna pela qual devo criar um order by deve ser uma coluna que modifique a toda execução. Isso! Usei a coluna  NEW_ID() para ordernar, vamos ao Exemplo:

USE AdventureWorks
go
SELECT TOP 10 *
    FROM AdventureWorks.Sales.SalesOrderDetail
    ORDER BY NEWID()

Dica rápida – Removendo NOT IN

Ola Galera, blz? Tô passando pra realizar um post na “velocidade da luz” devido ao curto espaço de tempo pra postar. Porém, essa duvida é meio que corriqueira no ambiente de desenvolvimento que passei. Quando temos um NOT IN, como podemos reescrever uma query? As vezes se soubermos como reescrever uma query de uma outra maneira e ela traz o mesmo resultado podemos obter ganho de performance. Mas, isso deve sempre ser testado para verificar se o ganho é verdadeiro(Esse assunto fica pra um outro post).

O Gustavo Maia escreveu um ótimo artigo sobre o assunto: http://gustavomaiaaguiar.wordpress.com/2009/01/18/piores-praticas-utilizacao-do-operador-not-in/

Evite usar o operador NOT IN para verificar inexistência de dados em tabelas. Se o uso deste operador for mesmo necessário, ele pode ser modificado por um left join, conforme Exemplo:

USE tempdb
go

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(‘[dbo].[TB_CLIENTE]’) and type=’U’)

DROP TABLE [dbo].[TB_CLIENTE]

go

CREATE TABLE [dbo].[TB_CLIENTE] (COD int identity, NOME varchar(max))

go

INSERT INTO [dbo].[TB_CLIENTE](NOME)VALUES(‘Minha CONSULTORIA’)

INSERT INTO [dbo].[TB_CLIENTE](NOME)VALUES(‘Minha’)

INSERT INTO [dbo].[TB_CLIENTE](NOME)VALUES(‘Consultoria’)

GO

IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(‘[dbo].[TB_PEDIDO]’) and type=’U’)

DROP TABLE [dbo].[TB_PEDIDO]

go

CREATE TABLE [dbo].[TB_PEDIDO] (COD int identity, PRODUTO varchar(50), COD_CLI INT)

go

INSERT INTO [dbo].[TB_PEDIDO] (PRODUTO,COD_CLI)VALUES(‘NOTE BOOK’,1)

INSERT INTO [dbo].[TB_PEDIDO] (PRODUTO,COD_CLI)VALUES(‘CADERNO ESCOLAR’,1)

INSERT INTO [dbo].[TB_PEDIDO] (PRODUTO,COD_CLI)VALUES(‘REVISTA’,1)

INSERT INTO [dbo].[TB_PEDIDO] (PRODUTO,COD_CLI)VALUES(‘BICLICLETA’,2)

INSERT INTO [dbo].[TB_PEDIDO] (PRODUTO,COD_CLI)VALUES(‘TELEFONE CELULAR’,2)

INSERT INTO [dbo].[TB_PEDIDO] (PRODUTO,COD_CLI)VALUES(‘BOLA DE FUTEBOL’,2)

O script acima cria uma tabela de cliente e de pedidos. Imagine que você precisa retornar uma query que traz os clientes que NÃO tem pedidos. O pensamento inicial seria usar o NOT IN:

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

O resultado seria:

A query poderia ser facilmente modificada para usar um left join obtendo o mesmo resultado.

 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

Na velocidade da luz….rs.

Espero ter ajudado

@@IDENTITY / SCOPE_IDENTITY() / IDENT_CURRENT

Quando for necessário resgatar o valor de uma coluna auto incremento no SQL Server, é recomendado que use a opção SCOPE_IDENTITY(). Abaixo segue o comportamento das opções existem para esta função.

@@IDENTITY: Retorna o ultimo valor de identidade gerado para qualquer tabela na sessão atual dentro de qualquer escopo.

SCOPE_IDENTITY():Retorna o ultimo valor de identidade gerado para qualquer tabela na sessão atual para o comando e escopo atual.

IDENT_CURRENT: Retorna o ultimo valor de identidade gerado para uma tabela especifica em qualquer sessão e em qualquer escopo.

Ex:

USE tempdb;

GO

if exists(select name from sys.objects where object_id= OBJECT_ID(‘dbo.t6’) and type = ‘U’)

begin

DROP TABLE dbo.t6;

end

if exists(select name from sys.objects where object_id=OBJECT_ID(‘dbo.t7′) and type=’U’)

begin

DROP TABLE dbo.t7;

end

GO

CREATE TABLE dbo.t6(id int IDENTITY);

CREATE TABLE dbo.t7(id int IDENTITY(100,1));

GO

CREATE TRIGGER t6ins ON t6 FOR INSERT

AS

BEGIN

INSERT dbo.t7 DEFAULT VALUES

END;

GO

SELECT * FROM dbo.t6;

–id é branco

SELECT * FROM dbo.t7;

–id é branco

–Executar este codigo na sessao 1(janela do SSMS)

INSERT dbo.t6 DEFAULT VALUES;

SELECT @@IDENTITY;

/*Retorna o valor 100. Que foi inserido pela trigger.*/

SELECT SCOPE_IDENTITY();

/* Retorna o valor 1. Esta valor foi inserido por uma instrução INSERT duas instruções antes desta query*/

SELECT IDENT_CURRENT(‘dbo.t7’);

/* Retorna o valor dentro de t7, que foi inserido pela trigger. Fora do escopo atual.*/

SELECT IDENT_CURRENT(‘dbo.t6’);

/* Returns value inserted into t6. Esta valor foi inserido por uma instrução INSERT quatro instruções antes desta query.*/

DROP TRIGGER t6ins

— Executar este codigo na sessao 2 (uma segunda janela no SSMS)

SELECT @@IDENTITY;

/* Retorna NULO, porque nenhuma instrução de INSERT foi gerada neste ponto para esta sessão*/

SELECT SCOPE_IDENTITY();

/* Retorna NULO, porque nenhuma instrução de INSERT foi gerada neste escopo para esta sessão*/

SELECT IDENT_CURRENT(‘t7’);

/* Retorna o ultimo valor inserido dentro de t7 = 100*/

Referência:

http://msdn.microsoft.com/pt-br/library/ms190315.aspx

Espero ter ajudado!