iMasters - Se��o: sql_server

SQL Server no Linux � poss�vel?

pichiliani@gmail.com (Mauro Pichiliani) — Mon, 08 Feb 2010 11:15:00 +0100

Ol�, pessoal. Neste artigo vou apresentar um estudo que tenta responder a uma das perguntas mais freq�entes em f�runs de bancos de dados: o SQL Server pode ser executado no Linux? Vou apresentar algumas id�ias, discutir conceitos e mostrar at� onde � poss�vel trabalhar utilizando estes dois produtos em conjunto: o SQL Server e o Linux.

Podemos imaginar um cen�rio onde uma empresa adquire um software do tipo ERP que pode ser executado em diversas plataformas, por�m requer obrigatoriamente o SQL Server como banco de dados. Se isto � ou n�o uma venda casada, fica o assunto para um pr�ximo artigo. Por enquanto vamos nos preocupar em estudar alguma solu��o para este cen�rio onde � preciso, de alguma maneira, evitar que a empresa compre licen�as e possa testar o seu ERP diretamente no Linux.

Neste cen�rio, � preciso instalar a parte servidora do SQL Server para que o software possa ser utilizado. A parte cliente do banco de dados j� est� programada na aplica��o. Ali�s, se algu�m precisar executar a parte cliente do SQL Server no Linux j� existem alternativas. J� discuti aqui no iMasters o uso de um driver JDBC como mecanismo de conex�o ao SQL Server e tamb�m o uso do freeTDS como alternativas para a conex�o de aplica��es que rodam no Linux em um servidor SQL Server. Al�m disso, existem outras abordagens como o uso de ODBC que podem ser executadas na plataforma Unix sem problemas com unixODBC.

O primeiro aspecto a ser estudado quando se fala em SQL Server no Linux � a documenta��o do fabricante. De acordo com a documenta��o oficial do SQL Server fornecida pela Microsoft, ele pode ser executado apenas na plataforma Windows e em diversos servidores (Windows 98, 2000, 2003 etc.) dependendo da vers�o (7, 2000, 2005, 2008, 2008 R2) e da edi��o (Standard, Developer, Enterprise, MSDE etc.).

Posto isso, podemos avaliar a primeira alternativa e a mais vi�vel: o uso de virtualiza��o. Nesta t�cnica � preciso utilizar no Linux um software de virtualiza��o como o Xen ou VMWare, por exemplo, e instalar o Windows e o SQL Server dentro deste servidor virtual. Depois desta instala��o � preciso configurar a rede e pode-se utilizar o SQL Server no Linux rodando sobre um Windows virtualizado. Para casos onde � preciso apenas realizar alguns testes � poss�vel utilizar as vers�es de avalia��o do Windows e do SQL Server, que v�o expirar em um per�odo determinado e que podem ser �teis para testes. Contudo, estas vers�es de avalia��o n�o requerem licen�a e geralmente t�m algum tipo de limita��o nos recursos dos software, como quantidade de mem�ria m�xima alocada, limite de uso de processadores, etc.

A pr�xima t�cnica que pode permitir a utiliza��o do SQL Server no Linux de forma nativa � o uso do WINE� (Wine is not na emulator). A id�ia � que este software crie todo o ambiente para executar aplica��es Windows no Linux sem emular todo o sistema operacional.

O primeiro passo no estudo da emula��o de alguma vers�o do SQL Server no Linux me levou ao reposit�rio de aplica��es no site do WINE, onde podemos verificar se � poss�vel ou n�o rodar certa aplica��o no WINE. Pesquisando no site, encontrei esta tentantiva de execu��o do SQL Server no Linux, postada em mar�o de 2009, indicando que n�o seria poss�vel executar o SQL Server MSDE SP3 no WINE. A descri��o do erro diz respeito a um problema com a instala��o do servi�o. Resolvi arrega�ar as mangas e fazer eu mesmo um teste com o WINE.

Depois de instalar o WINE� em um Linux Ubutu 8.04, chamado ubutu01, eu peguei os arquivos bin�rios, os arquivos de dados e as entradas do registro (o registry do Windows) de uma inst�ncia padr�o do SQL Server MSDE SP3 instalado previamente em um Windows XP e joguei tudo no Linux que continha o WINE, junto com a aplica��o das chaves no registro. Al�m disso, tamb�m configurei o WINE utilizando o poderoso WineTools para instalar os componentes necess�rios no Linux: DCOM, MDAC, IE6, .NET framework, Windows Commom Controls e outros. Tudo ocorreu sem problemas at� este ponto.

Apesar de n�o seguir a instala��o do produto como o recomendado, eu iniciei o servi�o do SQL Server MSDE no modo console utilizando o arquivo sqlservr.exe com o par�metro -c dentre outros. Contudo, infelizmente n�o consegui executar o servi�o com sucesso, como visto na Figura 1:

Figura 1: Tentativa de executar o SQL Server 2000 MSDE pelo WINE

Pelo log de erros do SQL Server, mostrado na Figura 1, podemos ver dois erros: um problema de autentica��o do NTLMSP, respons�vel pela comunica��o e autentica��o com o Windows, e um com a biblioteca de rede SSNETLIB, que foi o motivo pelo qual o servi�o n�o iniciou completamente. Pesquisando um pouco, descobri que a biblioteca SSNETLIB.dll � um dos componentes do SQL Server que fazem parte das bibliotecas de rede do servidor e que fazem chamadas diretas � API do sistema operacional Windows Sockets 2, entre outras. Tentei de tudo: registrar a DLL, instalar outras vers�es do SQL Server, modificar as bibliotecas de redes pelas chaves do registro, mas n�o consegui passar deste ponto.

O curioso � que mesmo com iniciativas da Microsoft para interoperabilidade, como a cria��o de v�rios laborat�rios de interoperabilidades espec�ficos para isso, parece que n�o h� nenhum interesse na portabilidade do SQL Server. H�, inclusive, at� piadinhas de primeiro de abril falando sobre uma suposta vers�o do SQL Server para Linux utilizando a biblioteca Mono.

Al�m disso a comunidade de c�digo livre tamb�m n�o apresentou muito interesse nesta linha de interoperabilidade, apesar de j� existirem casos onde diversos jogos e softwares do Office s�o executados no WINE. Caso algu�m se interesse por continuar o esfor�o, basta colocar um coment�rio no final do artigo.

Mas o estudo n�o acaba aqui. Vou comentar algumas outras abordagens que talvez possam ser interessantes para aqueles que desejam, de alguma, forma migrar do SQL Server para outro banco de dados. Algumas destas abordagens requerem modifica��o na aplica��o no banco de dados utilizado e talvez possam n�o ser adequadas ao cen�rio descrito no come�o do artigo.

Uma das alternativas interessantes � a utiliza��o de um banco de dados que possa emular o comportamento do SQL Server de forma adequada, mais especificamente o comportamento da linguagem T-SQL, o dialeto do SQL utilizado SQL Server. Neste ponto podemos pensar no Access e tamb�m na utiliza��o do arquivo .mdf de dados do SQL Server diretamente acoplado � aplica��o no que � conhecido por SQL Server 2005 Express Edition Edition User Instances� ou pelo acr�nimo RANU (Run As Normal User). Para acoplar diretamente a instala��o do SQL Server a um software, eu recomendo este link que explica os detalhes deste acoplamento.

Uma outra abordagem interessante � a utiliza��o de outro banco de dados que possua alguma compatibilidade com o T-SQL. Existem diversas alternativas que podem ser mais adequadas, por�m esperava encontrar algo como o Fyracle, que � o uso do Firebird com um m�dulo pr�prio para compatibilidade com o Oracle. O mais pr�ximo disso que encontrei foi o DotNetFirebird que possui muitas similaridades com o Access e o MSDE. Outros bancos acopl�veis que talvez possuam uma compatibilidade maior com o SQL Server podem ser o SQLLite e o Sharphsql.

Outro caminho interessante seria a convers�o em tempo real das instru��es SQL enviadas pela aplica��o para um outro banco de dados. Algo como o MySQL Proxy, que j� discuti em um artigo anterior, e que possui a capacidade de captar, interpretar e modificar em tempo real as instru��es enviadas pela aplica��o antes delas chegarem ao banco de dados. Contudo, n�o encontrei nenhum software ou projeto que n�o precise modificar o c�digo fonte e que fa�a esta tradu��o de instru��es em tempo real. Aqui temos uma boa oportunidade para um novo projeto que talvez tenha algum mercado. Algu�m se candidata?

Se observarmos o ponto de vista de convers�o, podemos encontrar uma infinidade de ferramentas, aplica��es, middlewares e softwares que permitem a convers�o de um banco de dados para outro, com destaque especial para o MySQL e o PostgreSQL, considerados os principais bancos de dados de c�digo livre. Estas ferramentas transformam os objetos e os dados de um banco para outro de forma off-line, ou seja, � preciso converter e trocar um banco de dados por outro. Esta op��o � recomendada, mas aqui vou destacar algumas ferramentas que podem auxiliar apenas na migra��o das instru��es.

Uma das v�rias ferramentas gr�ficas existentes que podem auxiliar na convers�o das instru��es entre diferentes bancos de dados � o SwissSQL Console, cuja interface � mostrada na Figura 2:

Figura 2: Interface gr�fica do SwissSQL Console

Notem que a interface da Figura 2 apresenta dois pain�is para a digita��o das instru��es e que no segundo painel, mais abaixo, existem diversas abas com os nomes de alguns bancos de dados. Na parte inferior � mostrado o resultado tamb�m com as diversas abas para os bancos de dados. Esta ferramenta tem o potencial de auxiliar muito os desenvolvedores que desejam converter instru��es, seja para mudar o banco de dados ou para criar uma aplica��o que suporte diferentes bancos de dados (multi-banco), assim como um potencial enorme para ensinar a novos desenvolvedores a diferen�as entre os dialetos da linguagem SQL.

Uma alternativa ao SwissSQL Console de c�digo livre e voltada para o Linux � o conjunto de scripts chamado SQL Translator. Esta ferramenta permite converter todos os comandos de um arquivo .sql diretamente pela linha de comando, al�m de converter o esquema do banco de dados e gerar c�digo automaticamente, entre outras funcionalidades.

Do ponto de vista de aplica��o, � comum encontrar uma camada a mais de software que encapsula os detalhes da instru��o SQL e da intera��o da aplica��o com o banco de dados. Existem diversos mecanismos, APIs, frameworks, aplica��es, bibliotecas e outros recursos que separam o desenvolvedor da instru��o que � enviada para o banco de dados como objetivo de criar aplica��es que n�o dependam diretamente de um banco de dados espec�fico. Esta pr�tica � muito comum, principalmente quando se fala em aplica��es web que utilizem mecanismo de mapeamento e persist�ncia de objetos e que muitas vezes nem utilizam um banco de dados propriamente dito. J� existe, inclusive, alguns design patterns que facilitam a implementa��o deste tipo de camada de software.

Sem entrar em uma discuss�o mais profunda do uso de camadas de software para separar o acesso aos dados, eu cito o SwissSQL API para Java como uma API que permite este tipo de programa��o multi-banco. Por�m, existem v�rias solu��es que seguem esta linha de racioc�nio.

Com isso termino aqui este pequeno estudo que mostrou algumas abordagens e id�ias para a integra��o do SQL Server no Linux. Apesar de existirem diversas alternativas, vale a pena lembrar que os fatores t�cnicos s�o apenas um dos aspectos a serem considerados, pois deve-se levar em conta quest�es como profici�ncia no banco de dados, acordo de licen�a, pol�tica, idealismo, filosofia, regras e outros fatores que acabam norteando as escolhas das ferramentas e recursos t�cnicos utilizados no que diz respeito a banco de dados.

�Um grande abra�o, pessoal, e at� a pr�xima.

Usando dados espaciais no SQL Server 2008 - Parte 02

lucassouzace@hotmail.com (Lucas Souza) — Mon, 01 Feb 2010 09:30:00 +0100

No artigo anterior falamos da import�ncia dos dados espaciais em nossas aplica��es, porque e quando devemos utilizar, o que � o plano geom�trico e suas dimens�es, qual diferen�a dos tipos de dados geometry e geography os quais s�o novos tipos de dados que est�o presente no SQL Server 2008 e por �ltimo vimos quais s�o os tipos espec�ficos para cada tipo de dados existente.

Ao termos conhecido as diferen�as dentre os tipos de dados geometry e geography o que nos resta neste momento � conhecermos quais s�o os diferentes formatos que podem ser utilizados para representar os dados espaciais, existe exatamente tr�s:

1.�� The Well-Known-Binary (WKB) format
2.�� The Well-Known-Text (WKT) format
3.�� The Geography Markup Language (GML) data format.

Todos os formatos mostrado acima foram publicados pela Open Geospatial Consortium (OGC), o qual � lider dos padr�es geoespaciais e servi�os baseados em localiza��es.

1.�� The Well-Known-Binary (WKB) format : Este � o formato bin�rio pelo qual representa a inst�ncia do tipo de dados geography, � um dos formatos preferidos serializados das aplica��es que necessitam armazenar informa��es geoespacial em compacta��o.
2.�� The Well-Known-Text (WKT) format: Este formato � compacto, ou melhor nos garante uma leitura f�cil, isto porque � um formato amig�vel e o mais comum para se usar em consultas geoespaciais.
3.�� The Geography Markup Language (GML) data format: Este formato representa o padr�o (XML), o qual � a melhor alternativa para incluir informa��es geoespaciais em documentos XML. Este formato � �til em aplica��es onde � necess�rio alterar informa��es geoespaciais por meios de XML Web Service.

Os padr�es, conforme foi ilustrado acima, nos oferecem vastas vantagens, o que precisa ser analisado �: qual deles se comportaria de melhor forma em nossas aplica��es. Os dados espaciais tamb�m suportam algumas fun��es, atualmente existem mais de 60 nos tipo de dados geometry e geography.

STDifference

Retorna uma nova inst�ncia consistindo em pontos da inst�ncia base em que n�o cont�m pontos da inst�ncia par�metro

-- Results: POLYGON ((10 10, 40 10, 40 26.666666666666668, 20 20, 26.666666666666668 40, 10 40, 10 10))

DECLARE @g geometry

='POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'

DECLARE @h geometry

='POLYGON((20 20, 50 30, 50 50, 30 50, 20 20))'

SELECT @G.STDifference(@H).ToString();

Figura 1 - Function - STDifference

STIntersects

Retorna verdadeiro se todos os valores geometric possu�rem mais de uma intersec��o, por outro lado este m�todo ir� retorna NULL se todos SRID da inst�ncia n�o possu�rem o valor adequado ao SRID da outra inst�ncia ou melhor. Este m�todo tem por objetivo conter apenas os pontos comuns entre a inst�ncia base e a inst�ncia par�metro.

-- Results: POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))

DECLARE @g geometry

='POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'

DECLARE @h geometry

='POLYGON((30 30, 50 30, 50 50, 30 50, 30 30))'

SELECT @g.STIntersection(@h).ToString();

Figura 2 - Function - STIntersects

STSymDifference

Retorna uma nova inst�ncia contendo apenas os pontos �nicos da inst�ncia base e par�metro ( i.e., excluindo os pontos que retornam STIntersection() ).

-- Results: MULTIPOLYGON (((50 30, 50 50, 30 50, 40 40, 50 30)),

((10 10, 40 10, 40 40, 10 40, 10 10)))
DECLARE @g geometry
='POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'

DECLARE @h geometry

='POLYGON((40 40, 50 30, 50 50, 30 50, 40 40))'

SELECT @g.STSymDifference(@h).ToString();

Figura 1.2 (Function - STSysmDifference)

Figura 3 - Fucntion - STSysmDifference

STUnion

Retorna uma nova inst�ncia contendo todo os pontos aninhados da inst�ncia base e par�metro.

-- Results: MULTIPOLYGON (((50 30, 50 50, 30 50, 40 40, 50 30)),

((40 10, 40 40, 10 40, 20 20, 40 10)))

DECLARE @g geometry

='POLYGON((20 20, 40 10, 40 40, 10 40, 20 20))'

DECLARE @h geometry

='POLYGON((40 40, 50 30, 50 50, 30 50, 40 40))'

SELECT @g.STUnion(@h).ToString();

Figura 4 - Function - STUnion

Blended Types

Este m�todo descreve n�o justamente os pol�gonos, no entanto � poss�vel usar diferentes tipos espec�ficos em cada cole��o ou em cada tipo de definido.

-- Results: MULTILINESTRING ((40 40, 30 30), (25 25, 8 8))

DECLARE @g geometry='LINESTRING(8 8, 40 40)'

DECLARE @h geometry='POLYGON((25 25, 15 30, 30 30, 30 15, 25 25))'

SELECT @g.STDifference(@h).ToString();

Figura 4 - Function Blended Types

STArea, STLength

Retorna a soma de todas as �reas calculadas na superf�cie definida pelo usu�rio do tipo geometric.

STArea() retorna o tipo float indicando a �rea quadrangular da inst�ncia
STLength() retorna o tipo float indicando o tamanho de unidade da inst�ncia (0 ou se a inst�ncia � um ponto ou se n�o possui tamanho)

-- Results: 400 | 121.28990204492
DECLARE @g GEOMETRY='POLYGON((10 10, 30 50, 50 50, 10 10))'

SELECT @g.STArea(), @g.STLength()

Figura 5 - Function STArea, STLength

STCentroid

Este � um dos m�todos padr�es OGC que retorna 'Point' indicando o centro da forma quandrangular, se a inst�ncia adotada n�o for Polygon ou MultiPolygon o valor NULL ser� retornado.

-- Results: POINT (20 30)
DECLARE @g GEOMETRY='POLYGON((10 10, 10 40, 40 40, 10 10))'
SELECT @g.STCentroid().ToString()

Figura 6 - Function - STCentroid

STWithin, STContains

Dois m�todos que seguem tamb�m o padr�o OGC retornando 1 ou 0 e indica se todos 'Points' da inst�ncia existe totalmente ao lado de outra inst�ncia.

STContains() testa se o par�metro inst�ncia est� do lado da inst�ncia base
STWithin() testa se a inst�ncia base est� do lado da inst�ncia par�metro, retorna verdadeiro se dado o valor geometric estiver juntamente � outro, de outra forma, retorna falso. Este m�todo ir� retorna NULL se todos SRID da inst�ncia n�o possuir o valor adequado ao SRID da outra inst�ncia.

-- Results: 1 0

-- 0 1

DECLARE @g geometry='POLYGON ((10 10, 13 30, 30 30, 30 15,10 10))'

DECLARE @h geometry='LINESTRING (16 16, 16 24, 25 18)'

SELECT @g.STContains(@h), @g.STWithin(@h)

SELECT @h.STContains(@g), @h.STWithin(@g)

Figura 7 - Function - STContains

STPointOnSurface

Este m�todo ir� retornar de certa forma um ponto aleat�rio que nos garante a localiza��o dentro da inst�ncia base.

-- Results: POINT (23 25)
DECLARE @g geometry='POLYGON((10 10, 14 15, 50 12, 45 30,10 30, 10 10))'

SELECT @g.STPointOnSurface().ToString()

Figura 8 - Function - STPointOnSurface

STGeometryType

Retorna o tipo Open Geospatial Consortium (OGC) o qual representa a inst�ncia geometry.

-- Results: Polygon

DECLARE @g geometry;

SET @g =geometry::STGeomFromText('POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))', 0);

SELECT @g.STGeometryType();

STGeomFromText

Retorna uma inst�ncia geometry da representa��o Open Geospatial Consortium (OGC) Well-Known Text(WKT) representando assim o argumento de eleva��es (Z) e medidas (M).

-- Results: LINESTRING (100 100, 20 180, 180 180)

DECLARE @g geometry;

SET @g =geometry::ST

GeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0);

SELECT @g.ToString();

STTouches

Retorna verdadeiro se dado o valor geometric faz parte de outro; de outra forma, retorna falso. Este m�todo retorna null se todos SRID (Spatial Reference Identifier) da inst�ncia n�o possu�rem o valor adequado SRID da outra inst�ncia.

STDistance

Retorna a dist�ncia entre os valores do tipo espec�fico Point geometric. Este m�todo ir� retorna null se SRID (Spatial Reference Identifier) da inst�ncia n�o possuir o valor adequado ao SRID da outra inst�ncia.

Figura 1.9� - Geography Methods

Aos analisarmos algumas fun��es e formatos existentes nos dados espaciais, o que nos resta agora � apenas aplic�-los. Os tipos espec�ficos podem ser usados em defini��es de tabela e tipos vari�veis. Dependendo de sua manipula��o dos dados espaciais, sendo plano ou el�ptico, pode-se usar os� dois tipo de dados, geometry e geography.

Exemplo pr�tico:

USEmaster

GO

IFEXISTS(SELECT Name FROMsys.databasesWHERE name =N'SpatialDatabase')

DROPDATABASE SpatialDatabase

CREATEDATABASE SpatialDatabase

ONPRIMARY

(NAME = SpatialDatabase_data,

FILENAME='C:\DatabaseSpatial\SpatialDatabase.mdf'),

FILEGROUP SpatialGroup1

(NAME = SpatialGroup2,

FILENAME='C:\DatabaseSpatial\SpatialDatabase.ndf')

LOGON

(NAME = Spatial_log,

FILENAME='C:\DatabaseSpatial\SpatialDatabase.ldf')

GO

USE SpatialDatabase;

CREATETABLE #GeometrySpatial

(

LocationID INTPRIMARYKEYCLUSTEREDNOTNULL,

LocationName NVARCHAR(30),

Position GEOGRAPHY

);

INSERTINTO #GeometrySpatial(LocationID,LocationName,Position)

VALUES (1,'Nova York',geography::Parse('POLYGON((

-75.17031 39.95601, -75.16786 39.95778, -75.17921 39.96874,

-75.18441 39.96512, -75.17031 39.95601 ))'))

INSERTINTO #GeometrySpatial(LocationID,LocationName,Position)

VALUES (2,'Chicago',geography::Parse('POLYGON((

-75.17031 39.95601, -75.16786 39.95778, -75.18870 39.97789, -75.18521 39.99237,

-75.18603 40.00677, -75.19922 40.01136, -75.21746 40.03142, -75.22534 40.02586,

-75.21052 40.01430, -75.19192 40.00634, -75.19248 39.99570, -75.20526 39.98374,

-75.19437 39.97704, -75.19087 39.96920, -75.17031 39.95601))'))

INSERTINTO #GeometrySpatial(LocationID,LocationName,Position)

VALUES (3,'Miami',geography::Parse('POLYGON((

-75.22280 40.02387, -75.21442 40.02810, -75.21746 40.03142,

-75.22534 40.02586, -75.22280 40.02387))'))

-- Trazendo os valores de forma compreensivel.

SELECT

LocationID,LocationName,Position.ToString()as WKT,

Position.STLength()as longitude

FROM #GeometrySpatial

�

Figura 10 - Results measures and longitude

�-- Visualizando o Spatial Results
SELECT * FROM #GeometrySpatial

�

Figura 11 - Spatial Results - Map

OBS: Se por acaso quisermos obter a lista de todos SRIDs suportados pelo SQL Server, podemos executar a instru��o:

SELECT * FROM sys.spatial_reference_systems

Para mais informa��es relacionado aos SRIDs, visite: http://msdn.microsoft.com/en-us/library/bb964707.aspx

Figura 12 - Results - SRIDs

Conclus�o

�timo, agora que conhecemos o que s�o os dados espacias, quando devemos utilizar, quais s�o as diferen�a dentre os tipos de dados geometry e geography, quais s�o os formatos e tipos existentes suportados pelo SQL Server e o exemplo pr�tico, o que resta neste momento � apenas implement�-los em nossa aplica��o e, assim, termos a convic��o de que possu�mos uma aplica��o que tem �timas vantagens quando o assunto se relata em buscas (mundialmente).

Usando dados espaciais no SQL Server 2008 - Parte 01

lucassouzace@hotmail.com (Lucas Souza) — Tue, 26 Jan 2010 10:00:00 +0100

Al�m de nos fornecer diversas op��es relacionadas � integra��o, relat�rio e replica��o de dados, pol�ticas e manuten��o de planos, o SQL Server agora tamb�m nos d� acesso a dados espaciais, na vers�o 2008. Os dados espaciais s�o implementados por 'CLR Types' internos no banco de dados, e t�m por objetivo armazenar informa��es, representando posi��es e estruturas geom�trica no SQL Server. Com isso em mente, podemos agora desenvolver aplica��es que nos concedam dist�ncia, localiza��o e espa�o em um certo ponto, localizado em uma certa circunst�ncia; podendo, al�m disso, virem at� de aplica��es que utilizem GPS.

Por que e quando devemos utilizar os Dados Espaciais?

Quando temos aplica��es que disponibilizam produtos mundialmente, alguns dos grandes aspectos que podem influenciar ao utilizar os dados espaciais s�o:

01. Onde o produto ser� entregue?

02. Onde o produto se encontra neste momento?

03. Por quais cidades j� passou?

Quando precisamos trabalhar com os dados espaciais, temos que ter em mente alguns requisitos, por exemplo:

01.� Os tipos de dados de espaciais podem ser usados para al�m de coordenadas, consistindo assim em mistura de pontos, linhas, um �nico segmento ou multi-segmento.

02.� Habilidade de executar grandes massas de opera��es de dados geoespaciais, por exemplo: aplica��es que necessitam calcular a �rea de um ponto complexo a outro.

03.� Armazenar coordenadas espaciais diretamente no banco de dados, ou melhor, armazenar informa��es de uma localiza��o � outra.

Relembro de h� alguns dias estive estudando geometria e o plano geom�trico pode ser descrito por duas dimens�es criadas no espa�o, dimens�es estas que podem ser descritas usando as posi��es em uma ordem de n�meros indicando a posi��o de cada uma, ou melhor 'o verdadeiro X e Y'.

A figura acima representa um plano que possui dois pontos, identificando as coordenadas X (esquerdo e direito) e Y (acima e abaixo). Neste caso, com esses valores conforme mostra a imagem, podemos identificar dois pontos no espa�o dimensional.

Qual diferen�a entre Geometry e Geopraphy?

Atualmente o SQL Server 2008 suporta estes dois tipos de dados, por�m eles possuem algumas diferen�as entre si. Vale lembrar que, apesar de ambos fazerem parte de dados espaciais, cada um tem por objetivo uma determinada forma.

Geometry: � um tipo de dados .NET common languague runtime (CLR), tem por objetivo armazenar dados baseados em pontos nas dimens�es(X,Y) e opcionalmente nas coordenadas (Z) em um �nico modelo tra�ado. Essas dimens�es podem ser conhecidas atrav�s do 'plano geom�trico', representando, assim, os dados em um sistema de coordenadas Euclidean (flat). Para mais informa��es a respeito desse tipo de dados, visite geometry Data Type Method Reference.

Geometry: Relaciona-se a um tipo de dados com mapeamento em um plano com duas dimens�es (sistema de coordenadas x e y)

Geopraphy: Pode ser visto tamb�m como outro tipo de dados .NET common language runtime (CLR), os dados geogr�ficos t�m por objetivo armazenar linhas, pontos, c�rculos, pol�gonos e uma cole��o de cada um desses, usando um modelo oposto ao 'round earth', o 'flat earth'; utilizando as coordenadas X e Y, o tipo de dados geometry utiliza as combina��es de latitude/longitude (GPS) representando um �nico ponto. Para mais informa��es sobre esse tipo de dados, visite geometry Data Type Method Reference.

Geography: Relaciona-se a um tipo de dados que armazenam informa��es em rela��o � superf�cie terrestre.

Obs: Vale lembrar que os dois tipos de dados armazenam linhas, pontos, pol�gonos, c�rculos. A diferen�a � que o tipo de dados geometry utiliza o plano 2d e o tipo de dados geopraphy utiliza o plano 3d.

Os dados geometry e geography s�o abstra�dos por tipos espec�ficos, basicamente eles possuem os mesmos modos, a diferen�a entre eles � apenas se a cria��o especificada � direcionada para um plano ou espa�o.

Polygon
MultiPolygon
LineString
MultiLineString
Point
MultiPoint
GeometryCollection

Cada tipo corresponde a uma cole��o. Podemos armazenar um �nico ponto utilizando Point; se por acaso for �til armazenar dois pontos, podemos utilizar MultiPoint, que nos prov� uma cole��o de agrega��o dos tipos espec�ficos. O SQL Server possui tamb�m uma hierarquia que pode ser vista na imagem abaixo:

Polygon: Pode ser descrito como um modelo fechado, por exemplo: bola. O Polygon pode consistir em uma bola onde pode conter zero ou mais lados. Onde o Polygon foi constru�do nunca ter� intersec��o.

MultiPolygon: Esse tipo representa uma cole��o de pol�gonos v�lidos, neste caso se um pol�gono dentro da cole��o estiver inv�lido, a cole��o estar� completamente inv�lida. Onde o MultiPolygon foi constru�do nunca ter� intersec��o.
LineString: LineString � um objeto de uma dimens�o que consiste em dois ou mais pontos identificados com segmentos em linhas. As linhas n�o possuem dimens�es correta, no entanto pode ser simples, complexa e fechada.

MultiLineString: Este tipo representa a cole��o dos objetos LineString, similar ao objeto MultiPoint, no entanto pode-se tamb�m fornecer express�es WKT sendo identificado por todos LineStrings na cole��o. MultiLineString podem ser simples, intersec��o ou fechado.
Point: Representa uma �nica dimens�o, sendo referenciado e identificado pelo par. Pode-se utilizar o Point em uma posi��o para identificar uma localiza��o na superf�cie.
MultiPoint: Representa a cole��o de zero ou mais pontos, geralmente � utilizado para identificar a localiza��o na superf�cie.
GeometryCollection: Os tipos mostrados acima s�o espec�ficos para cada um. O tipo GeometryCollection pode armazenar v�rios outros tipos de objeto em apenas um s�. Por exemplo, podemos armazenar tanto LineString como Point em um �nico GeometryCollection.

Na pr�xima semana continuaremos o assunto, falando dos diferentes formatos que podem ser utilizados para representar os dados espaciais. At� l�.

Como obter uma organiza��o robusta no SQL Server 2008

lucassouzace@hotmail.com (Lucas Souza) — Wed, 20 Jan 2010 11:00:00 +0100

Atualmente um dos maiores objetivos de empresas � possuir uma plataforma ao mesmo tempo organizada e robusta. Sendo assim, o SQL Server 2008 nos trouxe o framework Policy Management, tamb�m conhecido como Declarative Management Framework, que tem por objetivo manter os dados nas regi�es pr�-definidas pelo usu�rio.

Componentes do Policy Management

Existem cinco componentes fundamentais que englobam o framework Policy Managemen. Estes componentes podem ser usados para cria��o e verifica��o de condi��es. S�o eles:

Facets (Facetas)
Conditions (Condi��es)
Policies (Pol�ticas)
Policy targets (Pol�tica alvo)
Policy categories (Categorias de Pol�tica)

01. Facets

As facetas podem ser descritas como objetos padr�es que automaticamente est�o dispon�veis para o uso ap�s a instala��o do aplicativo SQL Server 2008. Neste caso, as facetas definem o tipo de objeto ou op��o assinalada para: Banco de dados, Indexes, Endpoint, entre outros. Existem aproximadamente 74 facetas dispon�veis, s�o elas implementadas por .NET assemblies possuindo suas propriedades �nicas para cada uma.

Todos os objetos do framework Policy Management est�o armazenados no banco de dados msdb, e as facetas podem ser ilustradas de duas formas: uma delas utilizando o comando dbo.syspolicy_management_facets, ou ent�o, utilizando o SQL Server Management Studio (SSMS).

02. Conditions

As condi��es t�m por objetivo irem mais al�m do que as facetas oferecem, ou seja, podemos definir express�es Transact-SQL avan�adas utilizando operadores =, <>, LIKE, NOT LIKE, IN ou NOT IN. Por exemplo, a cl�usula WHERE nos garante a filtra��o dos dados para chegarmos a um determinado ponto utilizando menos recurso de processador, as condi��es s�o similares, todavia as condi��es s�o verificadas em um �nico passo.

03. Policy Targets

A pol�tica alvo tem por objetivo principal a efetua��o de in�meras condi��es as quais ser�o executadas em inst�ncias remotas, por exemplo: nosso servidor est� localizado em Fortaleza, temos 2 Filiais atualmente localizadas em S�o Paulo e Rio de Janeiro, podemos aplicar as pol�ticas padr�es do servidor central (Fortaleza) para ambos as filiais citadas acima.

04. Policies

As pol�ticas est�o assinaladas a uma �nica condi��o. Os objetivos principais s�o verificar uma determinada circunst�ncia e refor�ar uma determinada cl�usula. As pol�ticas podem ser executadas das seguintes formas:

On schedule - a execu��o � feita por um agendamento definido pelo usu�rio, neste caso � utilizado um job atrav�s do SQL Server Agent.
On demand - disponibiliza a pol�tica quando � feita a execu��o pelo usu�rio.
On change, prevent - efetua a cria��o de um gatilho data definition language (DDL) impedindo que ocorra alguma mudan�a inesperada.
On change, log only - se por acaso ocorrer alguma mudan�a, automaticamente � criado um evento de notifica��o.

05. Policy Categories

As categorias de pol�tica podem ser usadas para um ou mais grupo de uma �nica unidade, a categoria padr�o se n�o assinalada ser� DEFAULT. Para verificar ou for�ar uma determinada pol�tica � necess�ria a cria��o de uma assinatura para uma ou mais pol�ticas.

Posso importar ou exportar pol�ticas?

Sim, � poss�vel fazer isso, as pol�ticas e as condi��es podem ser exportadas para arquivos formados e, al�m disso, pode-se tamb�m importar atrav�s deles. O SQL Server possui 53 pol�ticas as quais est�o localizadas no diret�rio Microsoft SQL Server\100\Tools\Policies, dentre as existentes podemos categoriza-las como:

50 Pol�ticas direcionadas a Engine do Banco de Dados;
2 Pol�ticas direcionadas ao Reporting Services;
1 Pol�tica direcionada ao Analysis Services.

Como importar e exportar as pol�ticas

Podemos importar/exportar as pol�ticas utilizando a janela Object Explorer a qual localiza-se na parte interior do SQL Server Management Studio (SSMS). Clique com o bot�o direito em Policies > Import Policy. Para exportar as pol�ticas devidamente, � necess�rio ter pelo o menos uma, sendo assim crie a sua pol�tica, em seguida clique com o bot�o direito encima e Export Policy, a figura abaixo ilustra de forma clara, veja:

Figura 1.0 - Object Explorer Policies

Exemplo Pr�tico

Este exemplo tem por objetivo for�ar a integridade dos dados, ou seja, todas as tabelas em nosso banco de dados atual ter�o que iniciar com o prefixo tbl_. Caso a cria��o de alguma tabela se iniciar fora desse padr�o, a aplica��o n�o ser� sucedida, ou melhor, ir� sofrer um rollback, caso contr�rio, aplica��o ir� sofrer um committed e assim ser� bem sucedida a transa��o pela qual est� dentro do padr�o adotado pela empresa.

1�) Clique em Management, em seguida Policy Management, ap�s isto clique com o bot�o direito em Conditions > New Condition.

Figura 1.1 - Object Explorer Conditions

2�) Defina as seguintes caracter�sticas: Name: PM_Table (ou desejado), Facet: Multipart Name, abaixo do campo Field selecione @Name, em Operator selecione LIKE, e por �ltimo emValue s digite 'tbl_%', e por fim clique em OK.

Defini��o: Essa janela tem por objetivo definirmos qual o padr�o que a pol�tica ir� adotar, neste caso ir� se chamar: PM_Table, a faceta por nome Multipart Name, e por �ltimo foi definido que a vari�vel @Name, ou seja, o nome de cada tabela ter� que se iniciar com o prefixo tbl_.

Figura 1.2 (Conditions)

NOTA: Veja que ap�s termos clicado em Ok foi exibida a nossa condi��o na raiz Conditions, a imagem abaixo ilustra de forma clara.

Figura 1.3 - Conditions PM_Table

3�) Estando posto ao n� Policy Management, clique com o bot�o direito em Policies, em seguida New Policy.

4�) Defina as seguintes propriedades, Name: PM_TableComplete, habilite a op��o Enabled, em Check Condition selecione a condi��o conforme criamos nos passos anteriores, marque a op��o Table, em Evaluation Mode selecione On Change: prevent, clique na aba Description, em seguida defina uma descri��o conforme pode nos ajudar futuramente, e por �ltimo clique em OK.

Figura 1.4 - New Policy

Figura 1.5 - New Policy - Description

�timo, nossa pol�tica est� completamente finalizada, se por acaso algum usu�rio tentar efetuar alguma instru��o Transact-SQL juntamente com a cl�usula CREATE TABLE sem adicionar os seguintes par�metros: tbl_, a aplica��o ir� sofrer um rollback, veja o exemplo abaixo:

Figura 1.6 - Messages

Como pode ser visto, aplica��o n�o foi bem sucedida, isto devido a nossa pol�tica conforme criamos nos passos anteriores, veja que com este framework temos uma grande facilidade em definirmos um certo padr�o e utiliz�-lo de uma forma f�cil/clara.

Conclus�o

Este artigo teve por objetivo a exibi��o de um novo framework do SQL Server 2008, Policy Management, que tem por objetivo for�ar a integridade dos dados em uma aplica��o comercial. Mostramos quais s�o os componentes que englobam este framework, como devemos exportar/importar pol�ticas, e por �ltimo vimos um exemplo pr�tico de como aplicarmos uma determinada pol�tica em um ambiente real.

[]�s

ASP .NET - Preenchendo um DropDownList com LINQ to SQL

macoratti@yahoo.com (Jos� Carlos Macoratti) — Thu, 07 Jan 2010 11:00:00 +0100

A partir da vers�o 3.5 da plataforma .NET o Visual Studio trouxe integrado o LINQ como uma nova ferramenta para trabalhar com a camada de dados em aplica��es que usam a linguagem VB.NET ou C#. Antes do LINQ, geralmente era criada uma camada de acesso a dados com a utiliza��o dos objetos SqlConnection, SqlCommand, DataReader, DataSet, etc para manipular os dados.

Com o advento do LINQ e do LINQ to SQL n�o precisamos nos preocupar com detalhes relacionados aos objetos SqlConnection, SqlCommand, DataReader e DataSet, pois podemos usar os objetos de neg�cios gerados pelo LINQ e a camada de acesso a dados.

Por ser uma tecnologia recente, ainda estamos aprendendo a melhor forma de usar o recurso oferecido pelo LINQ e o LINQ to SQL em nossas aplica��es, e este artigo tem o objetivo de mostrar como voc� pode preencher um controle dropdownlist em uma aplica��o ASP .NET usando o LINQ to SQL.

Portanto eu vou mostrar duas das formas mais usadas de preencher o controle Dropdownlist em um web site ASP .NET. Usaremos o Visual Web Developer 2008 Express Edition e o SQL Server 2005 Express Edition e o banco de dados Northwind.mdf.

Vamos l�.

Abra o Visual Web Developer 2008 Express e crie um novo web site no menu File->New Web Site, usando o template ASP .NET Web Site e a linguagem Visual Basic, com o nome DropDownList_LINQ;

Selecione o projeto e clique com o bot�o direito do mouse sobre o nome e selecione Add -> New Item;

A seguir selecione o template LINQ to SQL Classes e informe o nome Northwind.dbml, pois vamos usar o banco de dados Northwind.mdf;

O arquivo .dbml ser� copiado para a pasta AppCode e o assistente LINQ ser� aberto.

A seguir abra a janela DataBase Explorer e selecione a conex�o com o banco de dados Northwind.mdf (se ela n�o existir voc� dever� cri�-la). Expanda os objetos Table e arraste e solte a tabela Products no descrito LINQ para gerar o mapeamento OR/M e criar a entidade Product e contexto NorthwindDataContext para gerenciar o relacionamento entre a entidade e a tabela usada no exemplo conforme a figura abaixo:

Com isso j� temos o ambiente preparado para acessar os dados a partir do LINQ to SQL.

1. Preenchendo um dropdownlist com o LinqDataSource

Usar o controle para preencher o controle dropdownlist � uma das maneiras mais simples.

Selecione e abra a p�gina Default.aspx e inclua uma tabela a partir do menu Table-> Insert Table;

A seguir inclua um controle DropDownList na p�gina e a partir da guia Data arraste e solte um controle LinqDataSource conforme o leiaute abaixo:

Vamos configurar o componente LinqDataSource. Selecione o componente e clique em Configure DataSource;

Na janela do assistente voc� ver� o contexto NorthwindDataContext sendo exibido, clique em Next>;

Na pr�xima janela selecione a tabela Products e marque os campos que vamos usar: ProductID e ProductName e clique em Finish;

Agora selecione o controle Dropdownlist e defina as seguintes propriedades:

ID = ddl1 - o nome do controle;
DataSourceID - LinqDataSource1 - a fonte de dados do controle;
DataTextField - ProductName - o campo que ser� exibido no controle;
DataValueField - ProductID - o valor que ser� tratado quando um item for selecionado no controle;

Executando o web site iremos obter:

Esta � a forma mais f�cil de fazer a vincula��o com o dropdownlist, n�o usa nenhum c�digo e � f�cil de entender e de manter, mas nem sempre o que � o mais f�cil � o mais correto, e dependendo da arquitetura do seu projeto, efetuar a vincula��o direta usando o controle LinqDataSource com a fonte de dados viola a hierarquia de uma arquitetura em camadas e n�o lhe oferece nenhum objeto com o qual voc� possa trabalhar usando padr�es de projeto.

2. Preenchendo um dropdownlist com vincula��o a objetos LINQ

Vamos agora mostrar uma outra forma de preencher o dropdownlist usando objetos LINQ atrav�s da vincula��o direta.

Inclua um novo controle DropDowList na p�gina e defina as seguintes propriedades:

ID = ddl2 - o nome do controle;
DataTextField - ProductName - o campo que ser� exibido no controle;
DataValueField - ProductID - o valor que ser� tratado quando um item for selecionado no controle;

Agora no evento Load da p�gina inclua o c�digo abaixo:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ' Vinculando diretamente com a entidade LINQ
            ddl2.DataSource = GetProdutos()
            ddl2.DataBind()
        End If
    End Sub

Agora defina o m�todo est�tico GetProdutos conforme abaixo:

  Public Shared Function GetProdutos() As List(Of Product)
        'Usamos o contexto criado pelo LINQ to SQL
        Using contexto As New NorthwindDataContext()
            Return (contexto.Products.ToList())
        End Using
    End Function

Usando este m�todo tivemos que escrever um pouco de c�digo. O ideal seria ter criado uma camada separada para definir os objetos LINQ atrav�s da cria��o de um novo projeto do tipo Class Library e em seguida referenciar o projeto ao nosso web site.

A fun��o GetProdutos() acessa atrav�s do contexto criado pelo LINQ a tabela Products; chamamos o m�todo ToList() para retornar uma cole��o de objetos.

Executando o web site teremos o seguinte resultado:

Neste m�todo temos um controle maior sobre como os dados podem ser exibidos, pois podemos alterar o m�todo GetProdutos() incluindo algum processamento pr�vio. Al�m disso, dessa forma podemos trabalhar com uma arquitetura em camadas. O problema � que estamos fazendo a vincula��o diretamente com os objetos LINQ e o nosso c�digo n�o fica t�o f�cil de ser reusado.

Existe ainda uma outra forma de fazer esta vincula��o atrav�s da defini��o de uma interface para os tipos que estamos tratando e da implementa��o desta interface e da utiliza��o de Reflection, mas abordarei este m�todo em outro artigo.

Aguarde mais artigos sobre ADO .NET.

Raspagem de dados

pichiliani@gmail.com (Mauro Pichiliani) — Wed, 06 Jan 2010 11:00:00 +0100

Ol�, pessoal. Na coluna desta semana vou comentar sobre uma id�ia que j� � antiga, mas vem ganhando alguma aten��o recentemente: a raspagem de dados. Comentarei sobre o que se trata, alguns detalhes, um exemplo e tamb�m apresentarei outras quest�es relevantes.

O conceito de raspagem de dados, ou data scrapping, n�o � novo. De acordo com a p�gina americana da Wikip�dia, desde os prim�rdios da inform�tica j� havia a necessidade e solu��es para se obter dados a partir da tela do computador, ou seja, obter os dados que est�o em telas, relat�rios e outros formatos que n�o s�o convencionais.

Com o surgimento da internet, cada vez mais dados s�o publicados on-line. Por�m a maioria destes dados n�o � pronta para ser consumida, sob o ponto de vista de um banco de dados, e ser transformada em informa��o relevante. Isso quer dizer que � preciso, de alguma maneira, obter os dados publicados nos seus mais variados formatos, limp�-los e armazen�-los em um formato que possa ser facilmente manipulado.

Na �rea de banco de dados j� existe uma sigla que resume este processo: ETL. Um processo ETL (Extract Transform Load - Extra��o Transforma��o Carga) � adotado periodicamente ou esporadicamente e sua fun��o � a extra��o de dados de diversos sistemas, a transforma��o desses dados conforme regras de neg�cios e, por fim, a transfer�ncia dos dados para um local de armazenamento. Geralmente utiliza-se ferramentas ETL que possuem recursos para a manipula��o de diversos formatos de dados, como arquivos texto, PDF, HTML, XML, dados relacionais, DBF, etc. durante o processo ETL. Al�m disso, as ferramentas espec�ficas para se implementar o processo ETL contam com mecanismos para modificar, complementar, consolidar, acrescentar, transformar, apagar e muitas outras formas de manipular dados antes de envi�-los ao seu destino final.

Devido � peculiaridade, � varia��o de formato e at� � forma de disponibilizar os dados na internet, as ferramentas que fornecem suporte para implementar ETL n�o s�o capazes de obter e manipular os dados on-line muito bem. Devido a isso, muitas pessoas come�aram a chamar o processo de obten��o e manipula��o de dados on-line de raspagem de dados e criar ferramentas para auxiliar a obten��o de dados. J� existem at� livros que abordam este assunto focando em como implementar a raspagem de dados em linguagens espec�ficas. Faz muito sentido pensar em ETL em um ambiente corporativo, pois algumas pesquisas recentes indicam que aproximadamente 80% do total de informa��o corporativa s�o armazenados em dados n�o estruturados.

Lembro-me de que uma das primeiras vezes em que ouvi o termo raspagem de dados foi durante a confer�ncia Transpar�ncia Hack Day, realizada em 2009. Neste evento diversos projetos foram apresentados para obter dados visando ao ativismo digital, em especial para a �rea de fiscaliza��o, ao acompanhamento pol�tico e tamb�m � obten��o de not�cia e fotos. Este projetos sugerem que a obten��o de dados p�blicos pode facilitar e tornar transparente o acompanhamento de a��es pol�ticas, destino de recursos, causas e efeitos de atitudes, conscientiza��o e outros fins. Contudo, � preciso separar a parte t�cnica envolvida na obten��o e na manipula��o dos dados da parte que apresenta, interpreta e faz uso dos dados.

Focando na parte t�cnica, geralmente as ferramentas de raspagem de dados se baseiam na premissa de que o dado est� dentro de uma p�gina HTML disponibilizada em um site. Neste cen�rio a raspagem de dados se resume na obten��o e na manipula��o destes dados, geralmente por meio de um script que acessa o c�digo fonte da(s) p�gina(s) HTML, faz uma c�pia local deste conte�do, aplica alguma regra para a obten��o do dados em si (geralmente atrav�s de uma express�o regular ou instru��es SQL) e joga os dados em uma tabela de um banco de dados. Apesar de existirem outras fontes de dados OnLine, como arquivos PDF, imagens, anima��es flash, podcasts, planilhas on-line e outros, o princ�pio das ferramentas que fazem raspagem de dados segue o mesmo conceito b�sico.

O uso de scripts � feito devido � facilidade e � quantidade de recursos que podem ser empregados na raspagem de dados. Geralmente este script cont�m uma chamada para uma ferramenta ou instru��o que faz uma requisi��o web por meio de um POST ou GET, que s�o comandos do protocolo HTTP. Depois de obter a p�gina, o script geralmente salva o seu conte�do em um arquivo no disco ou faz um parser no mesmo, ou seja, l� o c�digo da p�gina e obt�m o dado que � relevante. Devido � variedade de formatos, layouts, designs e outros fatores visuais encontrados nas p�ginas da web, geralmente os scritps de raspagem de dados s�o customizados para uma determinada fonte.

Como exemplo, posso citar o projeto Legisdados, um projeto para agregar software de extra��o de dados do Legislativo Brasileiro. A iniciativa partiu dos projetos CongressoAberto e ParlamentoAberto que, como fica claro pelos nomes, t�m muito em comum. Para evitar repeti��o de esfor�os e desacoplar extra��o de dados de aplica��es escritas usando esses dados, o projeto agrega scripts de raspagem de dados relativos ao poder Legislativo. A id�ia � aumentar a colabora��o entre os dois projetos e incentivar o surgimento de novas aplica��es.

Atualmente j� existem algumas ferramentas e produtos al�m de projetos independentes para a raspagem de dados. Destaco o Yahoo! Query Language (YQL), uma excelente linguagem que pode ser utilizada em conjunto com a plataforma Yahoo! Pipes. Al�m disso, existem diversas outras ferramentas que permitem a raspagem de dados, como apresentado no link na parte final do artigo.�

Uma r�pida pesquisa por ferramentas de screen scraper em qualquer buscador vai retornar uma quantidade consider�vel de ferramentas para a raspagem de dados. Por�m, por mais adequada que seja a ferramenta, sempre � preciso algum n�vel de customiza��o ou, na maioria das vezes, algum esfor�o de programa��o.

Para quem acompanha as minhas colunas aqui no iMasters pode ter percebido que eu j� escrevi uma coluna onde acabei precisando criar um pequeno script para a raspagem de dados. A coluna em quest�o chama-se Estat�sticas do canal #cparty, onde tive que obter os dados do canal #cparty fornecido pelo Livestream do BlogBlogs e que poderia ser atualizado por v�rios meios. Na sequ�ncia apresentarei os 6 passos que utilizei para obter os dados que estavam separados em v�rias p�ginas HTML. Destaco que estes 6 passos podem ser utilizados como um pequeno guia para quem est� pensando em montar um script ou utilizar uma ferramenta de raspagem de dados.

1. Entenda a fonte de dados

Antes de obter o dado on-line � preciso conhecer muito bem a fonte de dados. Isso quer dizer que � preciso navegar pela p�gina, verificar se as informa��es s�o paginadas, colocadas em uma tabela, frame, �rea din�mica ou controle, se requerem um login, se s�o disponibilizadas em um arquivo, possuem acesso via API, seguem algum padr�o de apresenta��o e organiza��o e outros detalhes relevantes. No exemplo da #cparty eu estudei a p�gina e vi que apenas certa quantidade de dados eram apresentados na p�gina do Livestream. Como eu queria todos os dados de certo per�odo, eu tive que entender o par�metro da URL utilizada na parte de arquivos. Isso quer dizer que a cada requisi��o do tipo GET eu precisava passar um par�metro da p�gina que gostaria de consultar.

Este tipo de atitude � comum. Fazer raspagem de dados com certeza vai acabar envolvendo a manipula��o de par�metros de uma URL, principalmente quando n�o se deseja obter todos os dados de um vez s�, ou seja, s�o necess�rios filtros. Novamente, aqui n�o h� regra: cada site da web possui um jeito de apresentar informa��es, por�m para evitar a sobrecarga geralmente algum tipo de pagina��o � feita, que pode apresentar a forma de links com o n�mero da p�gina, controles do tipo combo-box ou outros. Em alguns casos � preciso at� fornecer par�metros de pesquisa em um controle e for�ar um POST para se chegar os dados ao inv�s de simplesmente fazer uma requisi��o do tipo GET.

H� casos mais complexos onde � preciso realizar um login antes ou at� elaborar alguma maneira de se ler um dado que n�o est� armazenado em texto, como dados colocados dentro de uma figura, anima��es flash ou arquivos PDFs. Nestas situa��es � preciso primeiro entender o que � necess�rio para se obter o dados para s� depois pensar na tecnologia necess�ria para se extrair o dado e transform�-lo no formato adequado.

2. Planejar como ser� feita a obten��o bruta dos dados

Uma vez que � j� se conhecem os detalhes da fonte de dados � hora de planejar como ser� planejada a obten��o dos dados. Isso quer dizer � preciso relacionar ferramentas, ambiente, plataforma, link de internet e outros recursos tecnol�gicos necess�rios para se obter os dados. Aqui tamb�m n�o h� uma regra geral, pois cada situa��o pode requerer uma ferramenta espec�fica. Por exemplo, se o dado est� armazenado em uma imagem pode ser preciso utilizar uma biblioteca OCR ou algo similar para se obter o dado. Outra situa��o envolve anima��es em flash, estruturas de dados em JavaScript, aplicativos em Java, silverlight ou algo assim. Obter dados que est�o dentro destas tecnologias pode ser complexo, por�m n�o imposs�vel.

No exemplo eu decidi utilizar um script em SQL por conveni�ncia, uma vez que tenho experi�ncia nesta linguagem. Planejei montar um script que faria uma chamada a um execut�vel no console com o objetivo de obter a p�gina em um arquivo texto. Esta chamada foi colocada dentro de um loop onde criei dinamicamente o n�mero da p�gina na URL que seria passada como par�metro para o aplicativo de c�digo livre wget.exe para plataforma Windows. Este pequeno programa wget.exe recebe como par�metro a URL da p�gina e o arquivo texto onde ser� armazenado o c�digo HTML. Ap�s o loop eu joguei todas as p�ginas em um �nico arquivo HTML.

Depois de capturar os dados eu teria um arquivo texto grande com todo o conte�do das p�ginas. Apesar de conter os dados que eu queria, o arquivo tamb�m tinha muitas tags HTML que n�o me interessavam e que foram removidas em um passo posterior.

3. Programar a captura dos dados

Uma vez que o planejamento estava pronto, bastou programar o script que montava o loop e fazer a chamada ao arquivo wget.exe. Neste ponto me deparei com alguns detalhes de implementa��o, como a necessidade de criar um arquivo .bat para poder chamar o arquivo wget.exe.

Geralmente a implementa��o da raspagem de dados envolve muitos detalhes espec�ficos, como a utiliza��o de bibliotecas no script, a cria��o de nomeclaturas para os arquivos, a aloca��o de espa�o em disco e outros detalhes t�cnicos. Vale a pena lembrar tamb�m que neste passo de programa��o � preciso efetuar testes e tamb�m considerar quest�es de desempenho e acesso aos dados. Algumas p�ginas da Web podem perceber que est�o recebendo muitas requisi��es simult�neas de um mesmo endere�o IP e bloquear o acesso. Para isso, fa�a testes que possam ajudar a identificar problemas quando o script da raspagem for executado.

4. Verificar os dados capturados e realizar a limpeza

Ap�s programar e executar o script que faz a raspagem de dados � preciso verificar se tudo ocorreu bem e se os dados brutos realmente foram capturados. Aqui n�o � preciso realizar uma verifica��o completa, e sim apenas checar se o processo autom�tico realmente capturou os dados.

Na maioria das situa��es a raspagem de dados n�o trar� os dados j� prontos e no formato adequado, a n�o ser que isso seja planejado desde o come�o by design. Isso quer dizer que geralmente muito do que foi capturado precisa ser limpo e transformado em informa��o que realmente pode ser manipulada da maneira desejada.

Aqui temos algumas op��es que envolvem trabalhar diretamente no que foi capturado ou jogar tudo em um local que forne�a recursos para manipula��o dados. Os bancos de dados atuais trabalham muito bem com a linguagem SQL, que � feita sob medida para tornar f�cil a manipula��o de dados. Por�m existem diversas outras ferramentas que permitem o uso de express�es regulares para limpar os dados, em especial a linguagem Perl e v�rios comandos do shell do Unix, que tamb�m conta com o conveniente mecanismo de redirecionamento de dados conhecido como pipes.

Na maioria das vezes � preciso eliminar dados irrelevantes que vieram junto com os dados desejados. No exemplo da #cparty todo o HTML da p�gina que n�o continha o que foi escrito no Twitter deveria ser eliminado. Optei por carregar todo o arquivo com os dados capturados em uma tabela com uma �nica coluna contendo caracteres sem limites. A separa��o em linhas foi feita de acordo com os caracteres separadores de final de linhas (CR+LF). Ali�s, quem trabalha com raspagem de dados em texto deve estar preparado para conhecer bem certos caracteres especiais, especialmente os caracteres de controle.

J� com os dados carregados em uma s� tabela eu complementei o script com diversas instru��es UPDATE e DELETE que eliminaram as linhas que eu n�o queria. Al�m disso, pude utilizar a instru��o SELECT para prototipar as chamadas �s fun��es que manipulam strings (SUBSTRING, REPLACE, etc) e montar as colunas com os dados no formato que eu precisava.

5. Carregar no banco de dados e adequar os dados ao formato modelado

Uma vez que j� se tenha realizado a limpeza nos dados e outras manipula��es, recomenda-se colocar os dados em um formato que seja adequado. Este passo muitas vezes requer a modelagem de uma ou mais tabelas em um banco de dados visando � integra��o com um sistema ou com uma ferramenta que possa fazer a visualiza��o de dados de forma gr�fica. Al�m disso, as ferramentas de relat�rios ou de minera��o de dados podem exigir um formato propriet�rio que pode ser facilmente gerado atrav�s de ferramentas de importa��o e exporta��o dos banco de dados.

Destaco que a modelagem � uma tarefa muito importante, principalmente quando se fala em integra��o com dados j� existentes que est�o separados em v�rias tabelas. Aqui � preciso tomar cuidado na ordem de inser��o, pois relacionamentos de chave prim�ria e chave estrangeira for�am a integridade relacional do banco de dados e requerem uma ordem espec�fica de inser��o, dele��o e atualiza��o dos dados.

No exemplo da #cparty foi f�cil criar uma tabela a partir da instru��o SELECT que j� separava corretamente as colunas da tabela. Fiz isso utilizando a cl�usula INTO da instru��o SELECT, pois no SQL Server esta cl�usula permite a cria��o de uma tabela a partir do que a instru��o SELECT retornar. Por�m tive tamb�m que adequar os dados obtidos ao formato dos tipos de dados do SQL Server, o que deu certo trabalho. Foi especialmente complicado adequar a data da mensagem devido ao formato necess�rio para a inser��o desta data no tipo de dados DATETIME do SQL Server.

Quanto todos os dados j� estavam armazenados na tabela fiz quest�o de criar �ndices e tamb�m uma chave prim�ria artificial para que a manipula��o apresentasse uma performance razo�vel. Como utilizei o site ManyEyes para realizar algumas visualiza��es, tive que exportar os dados para outro arquivo texto j� com as palavras separadas por cada dia do evento.

6. Deixar a solu��o pronta para ser utilizada novamente

Algumas raspagens de dados precisam ser peri�dicas, ou seja, devem ser executadas com alguma freq��ncia. Nestes casos vale a pena incrementar o script e mont�-lo de forma que possa ser chamado v�rias vezes e em v�rios hor�rios automaticamente.

Isso implica em planejamento de armazenamento, expurgo de dados, hor�rio de execu��o e recursos adicionais como backup, garantia de largura de banda e backup. A forma de implementar a reutiliza��o do script varia de plataforma para plataforma, por�m a maioria dos bancos de dados e o pr�prio sistema operacional cont�m ferramentas, funcionalidades e maneiras de se criar tarefas agendadas, cujo resultado de execu��o pode ser acompanhado posteriormente.

No exemplo da #cparty n�o houve a necessidade de executar o script de raspagem de dados novamente, pois o que eu queria era obter apenas a quantidade de dados de um per�odo fechado. Por�m nem todas as solu��es seguem esta id�ia e podem precisar de extra��o de dados peri�dica.

Para terminar, destaco a quest�o do uso e da disponibiliza��o dos dados. Ao contr�rio do que muitos podem pensar, nem tudo que � colocado na internet � p�blico e pode ser utilizado como bem entender. Existem muitos dados que s�o publicados na internet, independente da sua origem/site, que est�o protegidos por copyright ou outro modelo de licen�a. Infelizmente nem sempre � poss�vel obter informa��es sobre a licen�a do dados, por�m, em caso de d�vida, � preciso adotar uma postura �tica e perguntar para quem est� disponibilizando a informa��o e como ela pode ser utilizada.

Um grande abra�o e at� a pr�xima, pessoal!

Refer�ncias:

P�gina da Wikip�dia sobre raspagem de dados: http://en.wikipedia.org/wiki/Screen-scraping#Screen_scraping
Projeto Legisdados: http://github.com/legisdados/legisdados
Projetos do Transpar�ncia HackDay: http://blog.esfera.mobi/balanco-thackday-1/
Linguagem YQL (Yahoo! Query Language): http://developer.yahoo.com/yql/
Plataforma Yahoo! Pipes: http://pipes.yahoo.com/pipes/
Livro Data Scraping Information from the Web with ASP.NET: http://www.amazon.com/Data-Scraping-Information-Web-ASP-NET/dp/B00095M8P6
Ferramentas para Data Scrapping: http://www.sharewareconnection.com/software.php?list=Pdf+Screen+Scraper

Performance Data Warehouse

rodrigo@crespi.pro.br (Rodrigo Crespi) — Tue, 05 Jan 2010 11:00:00 +0100

O Performance Data Warehouse, que tamb�m � conhecido como Performance Studio, � um recurso implementado no SQL Server 2008 que permite a coleta de informa��es para estudo e an�lise de performance.

Esse novo recurso � baseado em uma feature chamada de Data Collector e est� sobre dois pilares:

SSIS (SQL Server Integration Services)
SQL Server Agent

Toda a coleta de dados feita � armazenada em um database previamente configurado, e a coleta pode ser feita atrav�s dos tipos de coletores abaixo:

T-SQL Query
SQL Trace
Performance Counter
Query Activity

O Data Collector gera dinamicamente os resultados gravando em tabelas de output. Quando os resultados forem gerados a partir de query devemos ter o cuidado de n�o incluir na consulta campos com nomes como snapshot_time, snapshot_id ou database_name. Isso porque s�o nomes reservados do Data Collector.

Outra considera��o � que campos do tipo XML, Text, Image, nText n�o ser�o inclu�dos no retorno e n�o ser�o coletados.

As DMVs para consultar o Query Activity Collector s�o:

Sys.dm_exec_requests
Sys.dm_exec_sessions
Sys.dm_exec_query_stats

Para utilizarmos esse recurso precisamos seguir os passos que eu vou descrever abaixo:

Habilitar o Data Collection
Configurar o banco de dados que gravar� a coleta
E analisar os dados
Vejamos isso na pr�tica, � muito f�cil.

Configurando o Data Collector

No Manegement Studio, abra o Object Explorer e expanda a guia Management. L� haver� um item chamado Data Collection. Para habilitar, basta clicar com o bot�o direito e clicar na op��o Enable Data Collection.

Acesse o Data Collection novamente com o bot�o direito do mouse e clique na op��o Configure Management Data Warehouse, isso abrir� um wizard. Basta avan�ar na tela de Welcome e na pr�xima tela marcar Create or upgrade a management data warehouse e clique em avan�ar.

Na pr�xima tela iremos definir os dados de grava��o da coleta, no caso, o database. Para isso, clique em New, isso far� com que abra a tela de New Database. Crie um banco de dados chamado coleta, clique em OK, e voltaremos ao Wizard do Data Collection, e � s� avan�ar para continuarmos.

Map Logins and Users � onde selecionaremos os usu�rios e os logins que ser�o usados para coleta. Nesse caso, selecione o usu�rio que corresponde ao SQL Server Service e a role mdw_admin. Ap�s configurar essa etapa, basta avan�ar e concluir.

Configurando o Management Data Warehouse

Agora que temos o Data Collector configurado, vamos configurar o Management Data Warehouse. Clique com o bot�o direito sobre o item Data Collection e selecione a op��o Configure Management Data Warehouse. Selecione Set up data collection e clique em avan�ar.

Na pr�xima tela, vamos deixar as op��es padr�o e avan�ar. Ao fazer isso, abrir� a tela de autentica��o do SQL Server, confirme as credenciais e clique em finalizar.

Agendando a coleta

As coletas n�o precisam ser feitas sempre? Ou precisam? Essas perguntas s�o respondidas apenas com estudo de caso da situa��o. Algumas vezes, na maior parte dos casos, vamos coletar apenas em per�odos em que uma determinada situa��o ocorre.

Para realizarmos o agendamento de um item, como exemplo a utiliza��o de disco, vamos expandir o item System Data Collection Sets e clicar sobre Disk Usage com o bot�o direito e selecionar propriedades.

Na tela de propriedades, sobre a op��o Data collection and upload, temos o Schedule, clique em New. Isso abrir� a tela de New Job Schedule e � s� preencher o nome e o per�odo do agendamento. Veja a imagem abaixo - o agendamento foi feito para acontecer todas as segundas-feiras, quartas-feiras, sextas-feiras e s�bados �s 13 horas.

Importante lembrar que o agendamento s� ir� funcionar se o servi�o do SQL Server Agent for inicializado.

Relat�rio

Ok, temos um coletor de informa��es que est� programado para acontecer em determinado per�odo, mas como vamos visualizar essas informa��es?

Simples: na op��o Data Collection clicamos com o bot�o direito do mouse e sobre o item Reports, abrir� o item Management Data Warehouse e nessa op��o encontraremos o item Disk Usage Summary.

Conclus�o

O SQL Server 2008 traz novas ferramentas para an�lise e monitoramento de ajustes de performance. Esse tipo de monitoramento em vers�es anteriores era apenas feito atrav�s de ferramentas de terceiros.

Espero que esse artigo ajude a iniciar boas an�lises e grandes descobertas.

Dicas para uma boa performance em seus c�digos T-SQL

rzuini@gmail.com (Rodrigo Zuini) — Fri, 13 Nov 2009 11:00:00 +0100

Melhorar a performance nos bancos de dados � sempre algo desejado. Na hora de fazer isso, surgem v�rias d�vidas de como fazer da melhor forma. Talvez uma das perguntas mais comuns em f�runs seja como melhorar a performance de uma consulta que est� muito lenta. Situa��o que traz uma resposta totalmente aberta: depende! E depende mesmo, de v�rios fatores. A lista a seguir traz apenas 10 dicas que podem fazer a diferen�a em seus scripts T-SQL. Espero que ela o ajude a encontrar uma boa solu��o, mesmo sabendo que os fatores que causam perda de performance precisam ser analisados antes de qualquer a��o. Bom proveito!

01. No comando SELECT, utilize apenas o n�mero de colunas que seja necess�rio

Deve-se evitar a utiliza��o do * para o retorno da consulta. Tudo porque ao utilizar o * em sua consulta, o SQL consulta a tabela syscolumns para retornar todas as colunas e agregar a seu comando o resultado.

Exemplo:

SELECT cNome, dNascimento, lAtivo 
FROM tbPessoaFisica

02. N�o declare vari�veis que voc� n�o utilizar� ou reaproveite vari�veis existentes.

Este procedimento aumenta sua banda de cache, deixando assim, uma margem extra de cache para outros processos.

03. Utilize CONVERT apenas para convers�o de dados do tipo DATETIME

Para as demais convers�es de tipos, utilize CAST, pois CAST faz parte do padr�o ANSI-92 e CONVERT funciona apenas para o SQL Server, sendo que em vers�es futuras este comando corre o risco de ser cortado do Transact.

Exemplo:

SELECT 
CAST(GETDATE() AS VARCHAR(20)) AS dCast, 
CONVERT(VARCHAR(20), GETDATE(), 100) AS dConvert

04. Utilize SELECT ao inv�s de SET

Com apenas um SELECT voc� pode definir valores para uma ou mais vari�veis, enquanto para o comando SET voc� precisaria de v�rios comandos. Sendo assim, a utiliza��o do comando SELECT para atribui��o de valores a vari�veis fica bem mais r�pida.

Exemplo:

DECLARE @id AS INT, @nome AS VARCHAR(25), @nascimento AS DATETIME

-- EX de atribui��o de valores utilizando SET


SET @id = 10


SET @nome = 'Rodrigo'


SET @nascimento = '1984-02-29'

-- EX de atribui��o de valores utilizando SELECT


SELECT @id = 10, @nome = 'Rodrigo', @nascimento = '1984-02-29'

05. Cuidado com ORDER BY e DISTINCT

Use apenas se necess�rio. A utiliza��o destes comandos pode gerar sobrecarga no engine do SQL Server, causando a lentid�o no processo ou em processos simult�neos.

06. Utilize as palavras reservadas do transact em letras mai�sculas para se diferenciar das demais.

Esta dica n�o influencia no quesito performance do script, mas ajuda a encontrar poss�veis erros de sintaxe logo na escrita. Isto pode aumentar sua produtividade.

Exemplo:

--SEM ERRO
SELECT cNome, dNascimento, lAtivo


FROM tbPessoaFisica


WHERE idPessoaFisica = 10
GO

--COM ERRO
SEELCT cNome, dNascimento, lAtivo


FROM tbPessoaFisica
WHERE idPessoaFisica = 10;

07. SELECT * INTO funciona muito bem para com pequenas tabelas

Mas, ao lidar com grande n�mero de registro ou longo processo de consulta, pode causar bloqueios em objetos da base tempDB, ocasionando filas para outros processos que utilizam a tempDB. Isto ocorre porque, ao criar um objeto, � causado um lock exclusivo nas tabelas sysobjects e syscolunms. Ent�o, utilize este recurso com cuidado!

08. Utilize vari�veis de tabela ao inv�s de tabelas tempor�rias

Tabelas tempor�rias podem causar recompila��es em algumas procedures. Vari�veis de tabelas foram criadas para solucionar este problema de recompila��es em procedures que necessitam de objetos tempor�rios.

09. Clausula WHERE

Os diversos operadores, usados diretamente, afetam no tempo de resposta de suas consultas. Para conhecer melhor sua profici�ncia, voc� pode ler o artigo disponibilizado pela Microsoft em http://msdn.microsoft.com/en-us/library/ms190276.aspx

10. Utilize a procedure sp_executesql

A utiliza��o da sp_executesql evita recompila��es de procedures. Mas, lembre-se, recompila��es nem sempre s�o ruins. Para maior informa��o sobre recompila��es e cache de planos de execu��o, leia o artigo do TechNet http://technet.microsoft.com/en-us/library/cc966425.aspx

�Exemplo:

EXECUTE sp_executesql
N'SELECT cNome, dNascimento, lAtivo FROM tbPessoaFisica
WHERE idPessoaFisica = @idPessoaFisica', N'@idPessoaFisica INT', @idPessoaFisica =
10

� isso a�, pessoal, espero que essas dicas sejam �teis no dia-a-dia de voc�s.

Grande abra�o e at� a pr�xima.

�

O paradigma FillFactor - Parte 02

laertejuniordba@hotmail.com (Laerte Poltronieri Junior) — Tue, 03 Nov 2009 11:15:00 +0100

Na primeira parte do artigo vimos como funciona internamente quando trabalhamos com um fillfactor de 100%. Hoje mostrarei o que acontece se usarmos um fillfactor baixo, de 30%,� por exemplo. E garanto que neste caso n�o veremos "Page Spliiiiiiiiits".

A verdade com 30% de FillFactor

Digamos que John , como um bom amigo pensou: "Bom, eu conhe�o a mala do Luca h� muito tempo e sei que ele vai mudar de ideia. Vou deixar um espa�o em cada carro, caso ele queira levar mais algu�m tamb�m".

E isto aconteceu, no �ltimo minuto Luca chegou. E trouxe sua amiga, Jana. Como John deixou um espa�o livre em cada carro, n�o houve problema em acomodar todo mundo, e todos curtiram o final de semana!

Uma coisa j� vemos de cara: o n�mero de carros aumentou com o mesmo n�mero de pessoas que iam (antes do Luca e Jana).

Vamos usar o (script 1) para vermos o que acontece , mas alterando o FillFactor para 30%.

if object_id('Testfillfactor') is not null

    drop table Testfillfactor

    go


create table Testfillfactor    (    id int not null,    

                                    name char(2000)

                                )

create unique clustered index  PK_TestFill on Testfillfactor(id)


WITH(    PAD_INDEX = OFF,

        FILLFACTOR = 30,

        STATISTICS_NORECOMPUTE = OFF,

        IGNORE_DUP_KEY = OFF,

        ALLOW_ROW_LOCKS = ON,

        ALLOW_PAGE_LOCKS = ON

    )

ON [PRIMARY]

go


set nocount on

declare @loop int

set @loop = 1

while @loop < 11

begin

    insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))

    set @loop = @loop + 2

end 

go


dbcc ind(dba, Testfillfactor, 1, 0)

go

alter index PK_TestFill on Testfillfactor Rebuild

E o que a figura abaixo nos diz, sem pensar muito?

Uma p�gina de dados foi criada, mas com o mesmo n�mero de linhas com o FillFactor de 100%

Como podemos ver:

Tr�s P�ginas de Dados (Data Pages) Coluna PageType = 1, que s�o as p�ginas 7430,7406 e 7408;
Uma P�gina de �ndice (Index Page) Coluna Pagetype = 2, que � a p�gina 7407;
Uma IAM Page� - Coluna PageType = 10, que � a p�gina 7431.

Nosso foco s�o as p�ginas de dados (Data Pages)

01. P�gina 7430

Pr�xima P�gina de dados (NextPageID) = 7406
P�gina de Dados Anterior (PrevPageID) = 0

02. P�gina 7406

Pr�xima P�gina de dados (NextPageID) = 7408
P�gina de Dados Anterior (PrevPageID) = 7430

03. P�gina 7408

Pr�xima P�gina de dados (NextPageID) = 0
P�gina de Dados Anterior (PrevPageID) = 7406

Com eu criei a coluna fixa (char 2000) devemos ter na p�gina 7430 duas linhas , que seriam o ID 3 e 5. Lembre-se que o �ndice cluster
sobre o ID.

Verifcando:

dbcc page( 0, 1, 7430, 3)

Como a coluna m_slotCnt me informa o n�mero de linhas que esta p�gina tem (2) e a coluna m_freeCnt o n�mero de bytes livres na p�gina, minha conta novamente est� correta. E outra, a p�gina 7406, ter� os IDs 5 e 7, e a p�gina 7408 o ID 9.

Podemos executar este script para visualizar esta afirma��o:

dbcc traceon( 3604 )       
go
create table #dbccpageresults (    ParentObject varchar(max),
                                [Object] varchar(max),
                                [Field] varchar(max),
                                [Value] varchar(max)
                              )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7430, 3 ) with tableresults')                                
go
select 'PAGE 7430',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7406, 3 ) with tableresults')   
go
select 'PAGE 7406',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7408, 3 ) with tableresults')   
go
select 'PAGE 7408', * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Novamente vamos inserir mais 4 linhas pares :

Insert into Testfillfactor  values (2,'Name 2')
go
Insert into Testfillfactor  values (4,'Name 4')
go
Insert into Testfillfactor  values (6,'Name 6')
go
Insert into Testfillfactor  values (8,'Name 8')

E vamos rodar o script novamente� :

dbcc traceon( 3604 )       
go
create table #dbccpageresults (    ParentObject varchar(max),
                        [Object] varchar(max),
                        [Field] varchar(max),
                        [Value] varchar(max)
                              )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7430, 3 ) with tableresults')                                
go
select 'PAGE 7430',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7406, 3 ) with tableresults')   
go
select 'PAGE 7406',* from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7408, 3 ) with tableresults')   
go
select 'PAGE 7408', * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

O resultado ser� :

As p�ginas est�o cont�nuas, e o engine n�o precisa pular de uma p�gina a outra para achar os dados.

N�o temos Page Split!!!!

Ser�?

dbcc ind(dba, Testfillfactor, 1, 0)

O resultado � o mesmo! Vamos mais a fundo:

dbcc page( 0, 1, 7430, 1 )
go
dbcc page( 0, 1, 7406, 1 )
go
dbcc page( 0, 1, 7408, 1 )

E representando:

"Eu n�o vejo Page Spliiiiiiitttsssss"

Qual � a m�gica?

N�o tem m�gica. � matem�tica pura. Temos espa�o em cada p�gina para acomodar os dados novos!

O Paradigma FillFactor III - �ndice Cluster+Identity = 100% FillFactor sem Page Splits?

� comum encontrarmos em f�runs a seguinte pergunta (ou parecida com ela): "tenho um CIX (clustered index) no campo identity. Posso deixar com FillFactor de 100%?". E a resposta tradicional: "Sim. J� que os dados s�o inseridos no final da p�gina, n�o haver� page splits".

A resposta correta na verdade � DEPENDE.

Depende dos tipos de colunas que ter�o sua tabela e, principalmente, se elas ter�o atualiza��es no tamanho do seu conte�do (varchar, nvarchar, varbinary).

Page Split ocorre no update tamb�m.

Vamos l�?

if object_id('Testfillfactor') is not null
    drop table Testfillfactor
    go

create table Testfillfactor    (    id int identity(1,1) not null,    
                                    name1 varchar(1000),
                                    name2 varchar(1000),
                                    name3 varchar(1000),
                                    name4 varchar(1000),
                                    name5 varchar(1000),
                                    name6 varchar(1000),
                                    name7 varchar(1000),
                                    name8 varchar(1000),
                                )
create unique clustered index  PK_TestFill on Testfillfactor(id)

WITH(    PAD_INDEX = OFF,
        FILLFACTOR = 100,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )
ON [PRIMARY]
go

set nocount on
declare @loop int
set @loop = 1
while @loop < 50
begin
    insert into TestFillfactor(name1,name2,name3,name4,name5,name6,name7,name8)
    values ('Name 1 ' + convert(char(10),@loop),'Name 2 ' + convert(char(10),@loop),'Name 3 ' + convert(char(10),@loop),
    'Name 4 ' + convert(char(10),@loop),'Name 5 ' + convert(char(10),@loop),'Name 6 ' + convert(char(10),@loop),'Name 7 ' + convert(char(10),@loop),
    'Name 8 ' + convert(char(10),@loop))
    set @loop = @loop + 1
end 
go

Criamos uma tabela com tipos de dados variante. Reparem que os campos nome s�o varchar. Populamos com 50 linhas cont�nuas (identity , com CIX no campo ID). Teoricamente n�o haveria Pages Splits.

Ser�?

alter index PK_TestFill on Testfillfactor Rebuild
go
dbcc ind(dba, Testfillfactor, 1, 0)
go

Correto. N�o vemos "Page SPLIIIIIIIIITTSSS". MAS, vamos alterar o conte�do dos campos nome?

update Testfillfactor set    name1 = REPLICATE('X',1000),
                            name2 = REPLICATE('X',1000),
                            name3 = REPLICATE('X',1000),
                            name4 = REPLICATE('X',1000),
                            name5 = REPLICATE('X',1000),
                            name6 = REPLICATE('X',1000),
                            name7 = REPLICATE('X',1000),
                            name8 = REPLICATE('X',1000)
go
dbcc ind(dba, Testfillfactor, 1, 0)

Vendo como ficou:

Uma CIVILIZA��O DE PAGE SPLITS!

Certeza, Laerte? Apesar de o campo NextPageId j� me mostrar isso, mas vamos ver:

Peguei a P�gina 7548. O pr�ximo deveria ser a P�gina 7549.

create table #dbccpageresults (    ParentObject varchar(max),
                        [Object] varchar(max),
                        [Field] varchar(max),
                        [Value] varchar(max)
                      )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7548, 3 ) with tableresults')   

select * from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7549, 3 ) with tableresults')   
go
select * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Errado!

Segundo o NextPageID, a Pr�xima P�gina � a 8174, que est� l� embaixo e nem aparece na imagem por n�o caber!

Pegando as p�ginas 7548 e 8174:

create table #dbccpageresults (    ParentObject varchar(max),
                        [Object] varchar(max),
                        [Field] varchar(max),
                        [Value] varchar(max)
                      )   
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7548, 3 ) with tableresults')   

select * from #dbccpageresults where [field] = 'id'
go
truncate table #dbccpageresults
go
insert into #dbccpageresults exec ('dbcc page( 0, 1, 8174, 3 ) with tableresults')   
go
select * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Agora sim! Ou seja da p�gina 7548 pulou para a p�gina 8174 para continuar a ordem do CIX (clustered index)!

Ou seja, a afirma��o que s� basta colocar o CIX no identity e pode deixar o FillFactor com 100% que n�o ter� Page Splits � LENDA. Al�m do que, fazendo isso, se sua tabela tiver uma carga alta de inserts, voc� poder� criar um hotspot (muita inser��o ao mesmo tempo).

Se sua tabela tem campos de tamanho vari�vel (varchar, nvarchar, varbinary) e estes s�o altamente atualizados (seja com valores maiores ou menores) provavelmente voc� ter� Page Split.

Se sua tabela tem campos de tamanho vari�vel (varchar, nvarchar, varbinary) e estes N�O s�o atualizados ou n�o tem, a coloca��o do CIX no identity e FillFactor de 100% parece ser mais vi�vel.

O Paradigma FillFactor IV - Muito cuidado!

Espera, deixa eu ver se entendi. NESTE CASO:

Com FillFactor de 100% o Engine acomoda 4 linhas na p�gina. Eu tenho um menor n�mero de p�ginas e Page Split.
Com FillFactor de 30% o Engine acomoda 2 linhas na p�gina. Eu tenho um n�mero maior de p�ginas, mas n�o tenho Page Split.

Mas um n�mero maior de p�ginas n�o quer dizer mais Logical Reads?

CORRETO!

Vamos ver se � verdade?

Primeiro, com 30% de FillFactor:

if object_id('Testfillfactor') is not null
��� drop table Testfillfactor
��� go

create table Testfillfactor��� (��� id int not null,���� 
����������������������������������� name char(2000) 
��� ��������������������������� )
create unique clustered index� PK_TestFill on Testfillfactor(id) 

WITH(��� PAD_INDEX = OFF, 
������� FILLFACTOR = 30, 
������� STATISTICS_NORECOMPUTE = OFF, 
������� IGNORE_DUP_KEY = OFF, 
������� ALLOW_ROW_LOCKS = ON, 
������� ALLOW_PAGE_LOCKS = ON
��� ) 
ON [PRIMARY]
Go
go 
alter index PK_TestFill on Testfillfactor Rebuild 
go
set nocount on
declare @loop int
set @loop = 1
while @loop < 10000
begin
��� insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))
��� set @loop = @loop + 1
end� 
go 
set statistics io on
go
select ID from TestFillfactor where id = 9999
go
set statistics io off

Table
'Testfillfactor'. Scan count 1, logical reads 55, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

Agora, com 100% de FillFactor:

if object_id('Testfillfactor') is not null
    drop table Testfillfactor
    go

create table Testfillfactor    (    id int not null,    
                                    name char(2000)
                                )
create unique clustered index  PK_TestFill on Testfillfactor(id)

WITH(    PAD_INDEX = OFF,
        FILLFACTOR = 100,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )
ON [PRIMARY]
go
alter index PK_TestFill on Testfillfactor Rebuild
go

set nocount on
declare @loop int
set @loop = 1
while @loop < 10000
begin
    insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))
    set @loop = @loop + 1
end 
go
set statistics io on
go
select ID from TestFillfactor where id = 9999
go
set statistics io off

Table 'Testfillfactor'. Scan count 1, logical reads 30, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

� verdade!

Baixo FillFactor --> Alto Page Reads
Alto FillFactor --> Baixo Page Reads

Se formos pela l�gica:

Muito Insert, update = Baixo FillFactor
Muito Select = Alto FillFactor

Mas sabemos que n� pr�tica n�o existe regra. Cada ambiente � diferente do outro e possui caracter�sticas pr�prias, seja pela regra de neg�cio envolvida, seja por limita��o de infra (falta de janela de reindexa��o) ou alta disponibilidade.

Algumas vezes � interessante deixar um alto Fillfactor para uma tabela de muito insert e update, mas tamb�m esta tabela tem muito select, pois alimenta um relat�rio muito importante pra sua organiza��o e � chamada muitas vezes ao dia... Neste caso, reindexar ONLINE (ou reorganizar) mais vezes este �ndice pode ser mais vantajoso.

Eu tenho certeza disso? Claro que n�o.

Mas eu fa�o algumas coisas que me ajudam na an�lise e defini��o da solu��o:

Analise a carga de seus �ndices separadamente (cada um deles tem uma carater�stica diferente)
Identifique as consultas que usam as tabelas destes �ndices (DMVs s�o uma boa ajuda)
Converse com o pessoal de Neg�cios (analista de neg�cios) e entenda o "produto final" do uso destas consultas. Por exemplo, um relat�rio de vendas online por filial
Crie suas pr�prias m�tricas de controle e organiza��o. Um baseline n�o serve somente para dados macros do servidor
Se o seu problema � um caso particular, crie um baseline para este processo. Como voc� poder� saber se melhorou ou ficou pior com seus ajustes se voc� n�o sabe com era o comportamento dele antes, durante e depois?
Entenda o Produto (SQL Server)

Nada melhor que voc� entender um pouco como funciona internamente o que est� acontecendo. Isto lhe dar� uma vis�o clara e objetiva para definir sua solu��o.

Exemplo de �timas leituras:

Inside Microsoft SQL Server 2005: T-SQL Querying
Inside Microsoft SQL Server 2005: The Storage Engine
Inside Microsoft SQL Server 2005: Query Tuning and Optimization
E os mesmos livros no� SQL SERVER 2008

N�o tenha medo de dizer "eu n�o sei, mas vou ver e aprender". Isso � muito melhor do que dizer "eu n�o sei... sempre foi assim... aprendi assim". Na verdade voc� esta dizendo "eu n�o sei, n�o quero saber e n�o tenho a m�nima vontade de aprender" e isso n�o soa bem.

Quando tiver todo controle da situa��o: teste. Teste muito. Canse de testar antes de dizer "o jeito de fazer � assim". As pessoas esperam isso de voc�.

A decis�o correta � "fa�a assim. Eu sei o que estou falando!".

Conclus�o

Vimos que o FillFactor pode ser o her�i ou vil�o da hist�ria.

Teste, implemente com cuidado.

Trate cada objeto e entidade do seu banco de dados como �nica e sendo assim o comportamento � diferente uma das outras.

E n�o olhe para Paradigmas. Especialmente em TI.

At� a pr�xima!

O paradigma FillFactor - Parte 01

laertejuniordba@hotmail.com (Laerte Poltronieri Junior) — Tue, 27 Oct 2009 10:30:00 +0100

Um grupo de cientistas colocou cinco macacos numa jaula. No centro dela colocaram uma escada e, sobre ela, um cacho de bananas. Quando um macaco subia a escada para apanhar as bananas, os cientistas lan�avam um jato de �gua fria nos que estavam no ch�o.

Depois de certo tempo, quando um macaco ia subir a escada, os outros o enchiam de pancada. Passado mais algum tempo, mais nenhum macaco subia a escada, apesar da tenta��o das bananas. Ent�o, os cientistas substitu�ram um dos cinco macacos.

A primeira coisa que ele fez foi subir a escada e foi rapidamente retirado pelos outros, que lhe bateram. Depois de algumas surras, o novo integrante do grupo n�o subia mais a escada.

Um segundo foi substitu�do, e o mesmo ocorreu, tendo o primeiro substituto participado, com entusiasmo, na surra ao novato. Um terceiro foi trocado, e repetiu-se o fato. Um quarto e, finalmente, o �ltimo dos veteranos foi substitu�do.

Os cientistas ficaram, ent�o, com um grupo de cinco macacos que, mesmo nunca tendo tomado um banho frio, continuavam a bater naquele que tentasse chegar �s bananas.

Moral da Hist�ria

Se fosse poss�vel perguntar a algum deles por que batiam em quem tentasse subir a escada, com certeza a resposta seria:

"N�o sei, as coisas sempre foram assim por aqui"

O Paradigma FillFactor

Tive a oportunidade de participar em um projeto em que a empresa tinha um DBA Junior. Em determinada an�lise que est�vamos fazendo, uma an�lise e reorganiza��o dos �ndices com base na carga de cada um, surgiu o di�logo:

- Essa tabela eu conhe�o � muito Insert . Pode colocar um Fillfactor baixo!
- Por que?, perguntei eu.
- Eu n�o sei, aprendi assim e todo mundo fala isso...

O paradigma FillFactor I "A exist�ncia do page split"

Continuando a minha pergunta, questionei-o sobre Page Split e a resposta foi:

"Ah, isso eu sei, � f�cil. � quando o SQL SERVER fica parecendo pipoca pulando de um lado para outro para achar os dados."

De uma certa maneira ele n�o estava errado e eu, particularmente, adorei o "pulando feito pipoca". Mas, sinceramente, essa era uma resposta que eu preferia n�o ter ouvido. Imagine se fosse algu�m do Storage Team...

O que � Page Split?

N�s sabemos que o SQL SERVER trabalha com p�ginas de 8kb e cada p�gina possui um fator de preenchimento, chamado fillfactor. Por default este fator � 0, ou seja, p�gina totalmente preenchida e sem espa�o livre para novos registros.

Quando um registro � inserido ou atualizado, este registro tem que ser armazenado no espa�o da p�gina. Se a p�gina n�o possui espa�o livre, o SQL Server precisa reorganizar todas as p�ginas para armazenar o novo registro.

Mas estas novas p�ginas n�o s�o cont�nuas.

O FillFactor � aplicado em um �ndice quando este � criado ou reorganizado e informa o quanto cada p�gina do n�vel folha ser� preenchida. Page Split � um processo interno que o Engine usa para acomodar os dados novos (ou quando mudamos o tamanho da informa��o em um campo varchar, por exemplo). Este processo consome uma carga consider�vel de I/O, tendo em vista que o Engine tem que ler a IAM (Index allocation Map), alocar novas p�ginas, mover os dados na ordem correta do �ndice etc. O FillFactor � mais efetivo no �ndice cluster, mas pode ser aplicado nos �ndices n�o cluster.

O Paradigma FillFactor II "A verdade est� l� dentro"

A verdade com 100% de FillFactor

Simplificando:

John, Robert, Phil, Luca, Lucy, Chloe, Hannah and Julia decidiram ir � praia. John era o organizador e resolveu colocar homens em um carro e mulheres em outro. Um dia antes da viagem, Luca resolveu n�o ir e Richard foi convidado. O layout ficou parecido com esse :

No �ltimo minuto, Luca decidiu ir, mas n�o havia espa�o no carro e, como a ordem era carro dos homens primeiro e mulheres depois, ele teve que ir com o seu.

Se John quisesse falar com Luca, ele tinha que pular o carro das garotas e, o pior, se ele quisesse falar com Lucy , ele tinha que pedir pro Luca, pois ele era o �nico a ter o n�mero do celular dela.

Se isto � custoso para John, imagine pro Engine do SQL Server....

Na verdade, aqui temos um Page Split� "praiano."

Vamos ver o que acontece:

if object_id('Testfillfactor') is not null
drop table Testfillfactor
go
create table Testfillfactor ( id int not null,
name char(2000)
)
create unique clustered index PK_TestFill on Testfillfactor(id)
WITH( PAD_INDEX = OFF,
FILLFACTOR = 100,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
)
ON [PRIMARY]
go
set nocount on
declare @loop int
set @loop = 1
while @loop < 11
begin
insert into TestFillfactor values (@loop,'Name ' + convert(char(10),@loop))
set @loop = @loop + 2
end
go
dbcc ind(dba, Testfillfactor, 1, 0)
go
alter index PK_TestFill on Testfillfactor Rebuild

Script 1

Este script nos gera um page split e o resultado � :

Como a gente pode ver :

Duas P�ginas de Dados (Data Pages) - Coluna PageType = 1 que s�o as p�ginas 7412 e 7414
Uma P�gina de �ndice (Index Page) - Coluna Pagetype = 2 que � a p�gina 7426
Uma IAM Page - Coluna PageType = 10 que � a p�gina 7413

Nosso foco s�o as p�ginas de dados (Data Pages)

01. P�gina 7412

Pr�xima P�gina de dados (NextPageID) = 7414
P�gina de Dados Anterior (PrevPageID) = 0

02. P�gina 7414

Pr�xima P�gina de dados (NextPageID) = 0
P�gina de Dados Anterior (PrevPageID) = 7412

Como eu criei a tabela com um valor fixo (char 2000), dever� caber na p�gina 7412 quatro linhas, que devem ser o ID 1, 3, 5 e 7.

Para verificar usamos:

dbcc page( 0, 1, 7412, 3)

Como a coluna m_slotCnt me informa o n�mero de linhas que esta p�gina tem (4) e a coluna m_freeCnt o n�mero de bytes livres na p�gina, minha conta est� correta.

Podemos executar este script para visualizar esta afirma��o:

create table #dbccpageresults ( ParentObject varchar(max),
[Object] varchar(max),
[Field] varchar(max),
[Value] varchar(max)
)
insert into #dbccpageresults exec ('dbcc page( 0, 1, 7412, 3 ) with tableresults')
select * from #dbccpageresults where [field] = 'id'
go
drop table #dbccpageresults

Correto. Na p�gina 7412 n�s temos os IDs 1, 3, 5 e 7

Na p�gina 7414 n�s temos somente uma linha que � o ID 9, sobrando espa�o n�o pelo FillFactor, e sim porque n�o tivemos dados suficientes para preench�-la.

Usando o script acima, podemos confirmar:

E representando:

Agora vamos inserir mais 4 linhas pares (no script inserimos linhas �mpares). Lembre-se de que o �ndice cluster � sobre o ID

Insert into Testfillfactor values (2,'Name 2')
go
Insert into Testfillfactor values (4,'Name 4')
go
Insert into Testfillfactor values (6,'Name 6')
go
Insert into Testfillfactor values (8,'Name 8')
go
dbcc ind(dba, Testfillfactor, 1, 0)
go

Como podemos ver, uma nova p�gina de dados (Data Page) foi inserida e n�s temos o Page Split.

Isso me lembra o Haley Joel Osment no filme Sexto Sentido: "Eu Vejo Page Spliiiiiiiiitsssss!!!!"

Perceba que pelas colunas NextPageID e PrevPageID, o Engine tem que ir da p�gina 7412 para a 7427 e depois para a 7414 para completar o ciclo da informa��es.

Representando:

Este "pulando feito pipoca" � uma opera��o de I/O muito pesada pro Engine.

Com isso verificamos como � a ocorr�ncia de Page Split e internamente representada.

Na continua��o deste artigo veremos como ser� se usarmos um fillfactor baixo, de 30%, por exemplo. At� l�.