sábado, 19 de julho de 2008

Novos tipos de dados do SQL 2008: HIERARCHYID (Parte 2)

Depois de um logo período sem publicar nada no Blog (fiquei enrolado no trabalho...), finalmente vou finalizar o post sobre o tipo de dados hierárquico. A primeira parte está no endereço: http://sqlserver-brasil.blogspot.com/2008/05/novos-tipos-de-dados-do-sql-2008_14.html.

No último post mostrei como implementar hierarquia no SQL Server 2005, veremos agora as novidades do SQL Server 2008. Vou utilizar a mesma hierarquia do post anterior, segue abaixo a figura:



O novo tipo de dados hierarchyid representa uma hierarquia, estando disponível uma série de métodos para sua manipulação: GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write. Vou utilizar alguns destes no exemplo abaixo.

Primeiro vamos criar a tabela abaixo contendo o novo tipo de dados hierarchyid:

CREATE TABLE FuncionarioHierarchies(
FuncionarioID int not null primary key,
Nome varchar(10) not null,
Cargo varchar(12) not null,
Hierarquia hierarchyid null)

Para incluir o ROOT da hierarquia (Presidente Jose) vamos utilizar o método GetRoot, que retorno o ROOT da hierarquia:

INSERT FuncionarioHierarchies VALUES
(1,'Jose','Presidente',hierarchyid::GetRoot())

SELECT * FROM FuncionarioHierarchies

where Hierarquia = hierarchyid::GetRoot()

Reparem que para consultar o ROOT, basta utilizar o mesmo método da inclusão!

Vamos incluir agora os Diretores Maria e Pedro, utilizando o método GetDescendant:

-- Inclui Maria
DECLARE @Pai hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
INSERT FuncionarioHierarchies VALUES

(2,'Maria','Diretor',@Pai.GetDescendant(NULL,NULL))

-- Inclui Pedro
DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = hierarchyid::GetRoot() FROM FuncionarioHierarchies
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES

(3,'Pedro','Diretor',@Pai.GetDescendant(@PrimeiroFilho,NULL))

A tabela abaixo mostra como utilizar os dois parâmetros do método pai.GetDescendant (filho1,filho2):



Para completar nosso organograma utilize o script abaixo:

DECLARE @Pai hierarchyid
DECLARE @PrimeiroFilho hierarchyid
SELECT @Pai = Hierarquia FROM FuncionarioHierarchies

where FuncionarioID = 2
INSERT FuncionarioHierarchies VALUES

(4,'Ana','Gerente',@Pai.GetDescendant(NULL,NULL))
SELECT @PrimeiroFilho =@Pai.GetDescendant(NULL,NULL)
INSERT FuncionarioHierarchies VALUES

(5,'Lucia','Gerente',@Pai.GetDescendant(@PrimeiroFilho,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 3
INSERT FuncionarioHierarchies VALUES

(6,'Ronaldo','Gerente',@Pai.GetDescendant(NULL,NULL))

SELECT @Pai = Hierarquia FROM FuncionarioHierarchies where FuncionarioID = 5
INSERT FuncionarioHierarchies VALUES

(7,'Marcio','Coordenador',@Pai.GetDescendant(NULL,NULL))
go


Agora vamos retornar a hierarquia utilizando o SELECT abaixo com o método GetAncestor:

SELECT FuncionarioID,Nome,Cargo,Hierarquia.GetLevel() as Nivel,
(Select FuncionarioID FROM FuncionarioHierarchies
Where Hierarquia = e.Hierarquia.GetAncestor(1)) As Chefe
FROM FuncionarioHierarchies e

Até o próximo post,
Landry.

quinta-feira, 19 de junho de 2008

Melhorando o Desempenho das Consultas com INCLUDE

Estava preparado para finalizar o artigo sobre o novo tipo de dado hierárquico do SQL Server 2008, quando ao tentar entrar no SQL Profiler recebi a mensagem indicando que o SQL 2008 CTP havia expirado (ainda não instalei o CTP de Fevereiro 2008 no notebook)! Como estou ministrando o curso 2784 (tuning de consultas), resolvi escrever sobre um recurso novo no SQL Server 2005, a cláusula INCLUDE do CREATE INDEX.

Vamos criar uma tabela a partir da VIEW vIndividualCustomer do banco de dados AdventureWorks para utilizar nos exemplos deste artigo.

use TEMPDB
go
select CustomerID,FirstName,MiddleName,Lastname,
Phone,EmailAddress,AddressLine1 as Address,'RJ' as Region,
dateadd(d,-CustomerID,getdate()) DataCadastro
into customer
from AdventureWorks.Sales.vIndividualCustomer

Quando criamos um índice nonclustered o SQL Server constrói uma Árvore B preenchendo todos os níveis (raiz, intermediário e folha) com as colunas que compõe e chave. Para atender a consulta abaixo, vamos criar um índice nonclustered com chave composta FirstName, LastName e Phone, evitando a necessidade de acessar a tabela:

create nonclustered index IXCustomer_FirstName on
Customer(FirstName,LastName,Phone)

Agora vamos executar a consulta abaixo, habilitando o plano de execução gráfico (menu query opção “Include actual execution plan”) e as estatísticas de I/O:

set statistics io on
go
select FirstName,LastName,Phone from Customer
where FirstName = 'David'
-- Table 'customer'. Scan count 1, logical reads 4

Veja no comentário do script que foram lidas quatro páginas para navegar no índice e retornar o resultado! A busca binária foi feita utilizando o filtro WHERE na coluna FirstName, já as colunas LastName e Phone serviram apenas para compor o resultado final. Para esta consulta, as colunas LastName e Phone não precisam estar no nível raiz e intermediário do índice, já que não foi feita pesquisa binária no índice com elas.

A partir do SQL Server 2005 você poderá reduzir I/O de consultas utilizando a cláusula INCLUDE, retirando as colunas que não serão utilizadas na busca binária do nível raiz e intermediário do índice, gerando um índice menor, com conseqüente melhora no desempenho. As colunas definidas na cláusula INCLUDE são acrescentadas ao nível folha do índice.

Vamos agora alterar o índice movendo as colunas LastName e Phone da chave para a cláusula INCLUDE:

create index IXCustomer_FirstName_LastName_Phone
on Customer(FirstName) INCLUDE (LastName,Phone)
with drop_existing

select FirstName,LastName,Phone from Customer
where FirstName = 'David'
-- Table 'customer'. Scan count 1, logical reads 2

Repare que o I/O da consulta foi reduzido pela metade!!!

Outra vantagem da cláusula INCLUDE é o limite de 1023 colunas, diferente da chave que contém um limite muito menor de 16 colunas ou 900 bytes! Alguns tipos de dados que não podem fazer parte da chave, como VARCHAR(MAX), mas podem compor o INCLUDE.

Ate o próximo post,

Landry.

segunda-feira, 9 de junho de 2008

Classificando as Transformações no Data Flow do Integration Service (SSIS)

Como não estou com o notebook que contém a VM de SQL Server 2008, não tenho como completar a série de artigos que fala sobre o tipo de dados hierárquico. Já que estou ministrando o curso 2795 sobre Design de ETL, vou escrever sobre o Integration Service (SSIS).

No DTS do SQL Server 2000 transformar dados significa escrever um VB Script executado linha a linha durante a importação, o que obviamente gerava sérios problemas de desempenho. Devido a esta limitação no DTS, para médios e grandes volumes de dados, se utilizava uma área intermediária chamada “Staging” dividindo o processo de importação em duas fases: Origem – Staging e Staging – Destino. Na primeira fase (Origem – Staging) carregavam-se os dados sem transformar, centralizando em uma única base intermediária no SQL Server destino. Na segunda fase (Staging – Destino) instruções T-SQL eram utilizadas para transformar os dados, obtendo melhor desempenho. Devido a esta prática muito comum no DTS, este não era classificado como uma ferramenta de ETL (Extract Transform Loan) e sim como ELT (Extract Loan Transform).

A área Data Flow representa a principal novidade no SSIS, onde construímos um fluxo de dados com objetivo de importar realizando transformações com desempenho, classificando o SSIS como uma real ferramenta de ETL. Mas para se obter o máximo de desempenho durante o Data Flow é fundamentar compreender alguns conceitos como buffers e a classificação das transformações.

No Data Flow Buffer é a coleção de colunas que flui de uma transformação para a próxima, sendo classificado como de entrada ou de saída:
- Source: contém apenas buffer de saída.
- Transformations: contém buffer de entrada e saída.
- Destination: contém apenas buffer de entrada.

As transformações podem ser classificadas de dois modos:

1) Síncrona X Assíncrona
Uma transformação é considerada Síncrona quando ela utiliza o mesmo buffer que a transformação anterior. Isto é possível porque o buffer de entrada e saída possui sempre a mesma quantidade de linhas. Já uma transformação Assíncrona possui buffer de entrada com quantidade de linhas diferente do buffer de saída, criando um novo buffer na sua saída.

Fica claro que transformações Síncronas proporcionam melhor desempenho e redução no consumo de recursos.

2) Sem Bloqueio, Semi Bloqueio e Com Bloqueio
Uma transformação Sem Bloqueio é aquela que não retém as linhas para executar o algoritmo, liberando a linha imediatamente. Como Exemplo a transformação “Derived Column” aplica o algoritmo linha a linha sem retenção.
Transformações de Semi Bloqueio retém parcialmente o fluxo devido a funcionalidade implementada pelo algoritmo. A transformação “Merge Join” é um exemplo de retenção parcial, pois implementa o algoritmo balance line para realizar o join de dois fluxos de dados.
Já as transformações Com Bloqueio retêm o fluxo todo, liberando as linhas no final da execução do algoritmo.

Segue abaixo tabela resumindo as principais características das transformações:




Um bom desempenho no Data Flow está diretamente relacionado ao uso de transformações Sem Bloqueio, além de evitar ao máximo as transformações de Semi Bloqueio e com Bloqueio. Segue abaixo a relação das tarefas:



Até o próximo post onde irei retomar a série dos novos tipos de dados.
Landry.

quarta-feira, 14 de maio de 2008

Novos tipos de dados do SQL 2008: HIERARCHYID (Parte 1)

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Minha esposa acabou de entrar no Teatro com meus dois filhos (Luaninha e Erick) e fiquei no restaurante para trabalhar em um projeto que está começando a atrasar.... é um projeto de BI e a fase atual de documentação do ETL é a mais chata!!! Após a primeira garrafa de Bohemia (muiiiito gelada!), resolvi escrever um post, só para relaxar.

Vou escrever sobre o novo tipo de dados hierárquico em dois posts, no primeiro veremos como criar e manipular hierarquias no SQL Server 2005, no próximo veremos as novidades no SQL Server 2008, OK?

Hierarquia no SQL Server 2005
Vamos utilizar como exemplo a hierarquia da figura abaixo:




A abordagem tradicional para lidar com hierarquia utiliza auto-relacionamento, veja o script abaixo que cria no SQL Server 2005 a hierarquia da figura acima:

USE tempdb
go

CREATE TABLE Funcionario(
FuncionarioID int not null primary key,
Nome varchar(40) not null,
Cargo varchar(20) not null,
ChefeID int null)
go

INSERT Funcionario VALUES (1,'Jose','Presidente',null)
INSERT Funcionario VALUES (2,'Maria','Diretor',1)
INSERT Funcionario VALUES (3,'Pedro','Diretor',1)
INSERT Funcionario VALUES (4,'Ana','Gerente',2)
INSERT Funcionario VALUES (5,'Lucia','Gerente',2)
INSERT Funcionario VALUES (6,'Ronaldo','Gerente',3)
INSERT Funcionario VALUES (7,'Marcio','Coordenador',5)
go




Algumas operações comuns em hierarquia podem ser bem complexas no SQL Server 2005, como retornar todos os subordinados de um funcionário. Na consulta abaixo vamos retornar todos os subordinados da Diretora Maria, utilizando CTE com recursividade:

WITH Organograma (FuncionarioID, Nome, Cargo, ChefeID)
AS (
-- Cria a ancora para estabelecer ponto inicial
SELECT FuncionarioID, Nome, Cargo, ChefeID FROM Funcionario
WHERE Nome = 'Maria'
UNION ALL
-- Cria recursividade
SELECT f.FuncionarioID, f.Nome, f.Cargo, f.ChefeID
FROM Funcionario f JOIN Organograma o
ON f.ChefeID = o.FuncionarioID)

SELECT * FROM Organograma
go




Para incluir um novo funcionário Gerente no lugar de Ana, deslocando-a para Coordenadora, será necessário fazer vários UPDATEs nos seus subordinados.

No próximo post veremos como ficou mais fácil manipular hierarquias no SQL Server 2008, Até lá.
Landry.

quarta-feira, 7 de maio de 2008

Novos tipos de dados do SQL 2008: DATETIMEOFFSET

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

Nos dois últimos posts (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html e http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008.html), escrevi sobre três tipos de dados novos para Data e Hora (DATE, TIME e DATETIME2). Neste post irei completar a série destinada aos novos tipos de dado Date e Time, escrevendo sobre suporte a Time Zone com o novo DATETIMEOFFSET.

DATETIMEOFFSET
Este tipo de dados é similar ao DATETIME2 onde é informada a precisão da hora, com acréscimo do Time Zone no intervalo de -14:00 a +14:00. Veja o exemplo abaixo:

DECLARE @dt DATETIMEOFFSET(0)
SET @dt = '20080415 22:00:00 -3:00' -- Brasilia

DECLARE @dt1 DATETIMEOFFSET(0)
SET @dt1 = '20080415 22:00:00 +9:00' -- Tokio

SELECT DATEDIFF(hh,@dt,@Dt1) 'Diferença Fuso Brasilia e Tokio'

-- Resultado abaixo:

Diferença Fuso Brasilia e Tokio
-------------------------------------------
-12


Vamos comparar agora todos os novos tipos de dados Data e Hora com os antigos DATETIME e SAMLLDATETIME:

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATE)
-- Resultado DATE: 2008-01-20

SELECT CAST('20080120 20:30:05.1234567 +5:0' as TIME(7))
-- Resultado TIME: 20:30:05.1234567

SELECT CAST('20080120 20:30:05.123' as SMALLDATETIME)
-- Resultado SMALLDATETIME: 2008-01-20 20:30:00.000

SELECT CAST('20080120 20:30:05.123' as DATETIME)
-- Resultado DATETIME: 2008-01-20 20:30:05.123

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATETIME2(7))
-- Resultado DATETIME2: 2008-01-20 20:30:05.1234567

SELECT CAST('20080120 20:30:05.1234567 +5:0' as DATETIMEOFFSET(7))
-- Resultado DATETIMEOFFSET: 2008-01-20 20:30:05.1234567 +05:00

No próximo post vou mostrar o tipo de dado hierarquico, Até lá.
Landry.

quarta-feira, 30 de abril de 2008

Novos tipos de dados do SQL 2008: DATETIME2

Este artigo foi escrito utilizando o CTP de Novembro do SQL Server 2008.

No último post, o primeiro da série que irá tratar dos novos tipos de dados no SQL Server 2008, falei sobre a tão esperada separação da data e hora nos novos tipos de dados DATA e TIME (http://sqlserver-brasil.blogspot.com/2008/04/novos-tipos-de-dados-do-sql-2008-date-e.html). Neste post vou apresentar uma extensão do tipo de dado DATETIME: DATETIME2.

DATETIME2
O tipo de dado DATETIME2 proporciona um melhor controle na precisão da HORA, além de aumentar o intervalo de datas aceito. Veja nas tabelas a seguir a comparação entre DATETIME2 e DATETIME.


Como exemplo execute o script abaixo:

DECLARE @DataHora3 DATETIME2(3)
SET @DataHora3 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(3)] = @DataHora3

DECLARE @DataHora7 DATETIME2(7)
SET @DataHora7 = '2004-02-27 16:14:00.1234567'
SELECT [DATETIME2(7)] = @DataHora7

-- Resultado abaixo:

DATETIME2(3)
-----------------------
2004-02-27 16:14:00.123

DATETIME2(7)
---------------------------
2004-02-27 16:14:00.1234567

No próximo post vou mostrar o tipo de dado TATETIMEOFFSET.
Até lá,
Landry.

domingo, 27 de abril de 2008

Novos tipos de dados do SQL 2008: Date e Time

Até a versão 2005 o SQL Server disponibilizava apenas dois tipos de dados para data e hora (DATETIME e SMALLDATETIME), armazenando a data sempre junto da hora. Armazenar a data e hora em conjunto gera algumas dificuldades quando a necessidade da aplicação é de apenas manipular a data. Vamos explorar algumas situações que geram dificuldade ao manipular a data junto da hora e depois veremos as novidades no SQL Server 2008.

O script abaixo criar uma tabela de Vendas contendo uma coluna DATETIME e depois inclui algumas linhas.

USE tempdb
go

CREATE TABLE Vendas (
IDVenda int not null,
DataVenda datetime not null,
Cliente char(4000) not null,
TotalVenda decimal(10,2))

-- Inclui duas linhas para utilizar no filtro WHERE das consultas
INSERT Vendas VALUES

(3,'20070303 12:00:00.000','Ana',320.00)

INSERT Vendas VALUES
(3,'20070303 00:00:00.000','Pedro',120.00)

-- Inclui 10.000 linhas
DECLARE @i int
SET @i = 1
WHILE @i <= 10000 BEGIN


INSERT Vendas VALUES
(@i,getdate()-@i,'Cliente ' + ltrim(str(@i)),10.00 + @i)

SET @i = @i + 1
END
go

CREATE INDEX ix_Vendas_DataVenda
ON Vendas(DataVenda)
go

Para retornar todas as vendas de um determinado dia, não importando a hora, você terá certa dificuldade. Utilizando uma comparação simples de data, o SQL Server retornará apenas as vendas de meia noite (hora padrão), como podemos observar na consulta abaixo:

SELECT * FROM Vendas WHERE DataVenda = '20070303'

Uma opção para retornar todas as vendas de um dia é utilizar a função CONVERT, extraindo apenas a porção data da coluna DataVenda e comparando com o dia. O problema desta solução é utilizar função em coluna na cláusula WHERE, inviabilizando o uso de índice (Index Seek) gerando problema de desempenho. A solução é entrar com um intervalo de datas, evitando o uso de função em coluna. Para comparar o volume de I/O vamos utilizar SET STATISTICS IO ON e o plano de execução gráfico.

SET STATISTICS IO ON

SELECT * FROM Vendas

WHERE convert(char(8),DataVenda,112) = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 29
-- Plano de Execução: Index Scan

SELECT * FROM Vendas

WHERE DataVenda >= '20070303' and DataVenda < '20070304'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek


O SQL Server 2008, finalmente, disponibiliza tipos de dados onde data e hora ficam separados, são eles DATE e TIME.

Vamos alterar a estrutura da tabela Vendas, criando uma nova coluna HoraVenda do tipo de dado TIME e depois iremos alterar a coluna DataVenda para o tipo de dados DATE.

DROP INDEX Vendas.ix_Vendas_DataVenda

-- Cria a coluna HoraVenda do tipo de dados TIME e
-- preenche com as horas existentes na coluna DataVenda
ALTER TABLE Vendas ADD HoraVenda time null
go
UPDATE Vendas SET HoraVenda = DataVenda

-- Altera a coluna DataVenda para o tipo de dados DATE
ALTER TABLE Vendas alter column DataVenda date not null

CREATE INDEX ix_Vendas_DataVenda

ON Vendas(DataVenda)

Agora ficou mais fácil retornar as vendas de um determinado dia utilizando comparação simples, veja as estatísticas da consulta abaixo.

SELECT * FROM Vendas WHERE DataVenda = '20070303'
-- Table 'Vendas'. Scan count 1, logical reads 5
-- Plano de Execução: Index Seek

Até o próximo post.
Landry