Grimpi IT Blog

noviembre 29, 2008

Lo nuevo de SQL Server 2008: Grouping Sets

Filed under: SQL Server, SQL Server 2008, T-SQL — Etiquetas: , — grimpi @ 4:33 pm

Una característica piola que tiene la nueva versión de SQL Server, es el nuevo operador GROUPING SETS, que ya existía en otros motores como Oracle hace tiempo (es un estándar ANSI 2006) y permite combinar consultas de agrupación distintas en una sola consulta. El operador GROUPING SETS es una extensión de la cláusula estándar GROUP BY.
Cuando no se requieren todas las agrupaciones posibles que se generan utilizando un operador ROLLUP o CUBE (que ya existían en SQL Server 2005), se debe utilizar GROUPING SETS para especificar sólo las agrupaciones que se deseen. O sea, gracias a GROUPING SETS obtenemos los niveles de agrupación deseados y además nos devuelve el subtotal para cada subconjunto de agrupación.
Podríamos decir que GROUPING SET es mas abarcativo y genérico que ROLLUP o CUBE. Generalmente consultas que usen este tipo de operadores están relacionadas con el análisis de datos, reporting y todo lo relacionado con el mundo BI.
Este nuevo operador no permite hacer nada nuevo que antes no se pudiese, solo simplifica y optimiza determinadas consultas, lo ya de por si, es un factor importante.
Veamos un ejemplo de donde utilizarlo:

Supongamos que tenemos la siguiente tabla de Clientes con datos ya cargados:

CREATE TABLE [dbo].[Client]
(

[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NULL,
[Country] [varchar](50) NULL,
[Zone] [varchar](50) NULL,
[Year] [int] NULL,
[Precio] [numeric](12, 4) NULL,

)

Y ahora supongamos que queremos traer la suma del campo Precio, agrupada por los Campos Country y Zone, luego solo por Zone y luego por el total y todo en una sola misma sentencia de tal manera que el resultado de la consulta fuese el siguiente:


La primera opción que uno piensa, es hacer una consulta usando UNION ALL con tres consultas diferentes, una por cada agrupación:

SELECT SUM(Value) AS Total, Zone, Country FROM dbo.Client
GROUP BY Zone,Country

UNION ALL
SELECT SUM(Value), NULL, Country FROM dbo.Client
GROUP BY Country

UNION ALL
SELECT SUM(Value), NULL, NULL FROM dbo.Client
ORDER BY Zone, Country

Sin embargo, podemos escribir una consulta equivalente y acá entra GROUPING SETS de SQL Server 2008 para simplificarnos la vida:

SELECT SUM(value) AS Total, Zone, Country FROM dbo.Client
GROUP BY
GROUPING SETS
(
(Zone, Country),
(Country),
()
)
ORDER BY GROUPING(Zone), GROUPING(Country)

Ahora como podrán ver en este último ejemplo, además de usar el operador GROUPING SETS, hago uso de la función GROUPING en el ORDER BY, para ordenar el resultado por Zona y Country.
Esta función (que ya existía en SQL Server 2005), nos indica si una expresión de columna especificada en una lista GROUP BY es agregada o no. GROUPING devuelve 1 para agregado y 0 para no agregado, en el conjunto de resultados.

Performance:

La ventaja del uso GROUPING SETS no está solo dada por simplificar sintácticamente las consultas, sino también en cuestiones de performance.
En las pruebas que hice para monitorear el uso de recursos (SET STATISTICS IO ON) con 90000 registros en la tabla Client estos fueron los resultados:

Usando UNION ALL:
Table ‘Client’. Scan count 3, logical reads 1728, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Usando GROUPING SETS:
Table ‘Client’. Scan count 1, logical reads 576, physical reads 6, read-ahead reads 590, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Como podemos ver, GROUPING SETS hace menor uso de recursos de I/O. La razón de la mejor performance, se debe a que usando la nueva característica de SQL Server, el motor necesita leer menos paginas de datos, ya que hace el cálculo de agregación de más alto nivel sobre las agregaciones de menor nivel (usando como ejemplo nuestra consulta, el nivel de menor de agregación seria Zone y Country, luego solo Country y por último a partir del resultado de la prime).
Dejo la tarea al lector para que compare los planes de ejecución y pueda apreciar las diferencias en las operaciones que realiza el motor entre una consulta y otra.

Links:
Recomiendo leer el articulo de MSDN de equivalencias de GROUPING SETS.

Anuncios

noviembre 22, 2008

Patrones de Acceso a Datos: Active Record

Filed under: .NET, Arquitectura, Capa de Datos, Patrones — Etiquetas: , — grimpi @ 10:12 pm

Existen varias estrategias, arquitecturas y patrones de diseño para el manejo de la lógica de negocio y el acceso a la base de datos.
Que determina el uso de una u otra arquitectura esta dado por la especificadores y características del software a desarrollar y también por el gusto de quien diseña el esqueleto de la aplicación (la subjetividad es un factor muy determinante en el desarrollo de software, muchas veces más que cualquier argumento racional).
Hoy vamos a ver uno de estos patrones: Active Record.
ActiveRecord es un patrón en el cual, el objeto contiene los datos que representan a un renglón (o registro) de nuestra tabla o vista, además de encapsular la lógica necesaria para acceder a la base de datos. De esta forma el acceso a datos se presenta de manera uniforma a través de la aplicación.
Lógica de Negocio + Acceso a Datos en una misma clase.
Una clase Active Record consiste en el conjunto de propiedades que representa las columnas de la tabla más los típicos métodos de acceso como las operaciones CRUD, búsqueda (Find), validaciones, y métodos de negocio.
Personalmente me gusta mucho este enfoque, es muy elegante y simple.

En que situaciones CONVIENE usar este patrón?

  • Lógica de negocio simple y poco relacionada con otras entidades.
  • Es ideal cuando la estructura de la tabla coincide con la estructura de la clase.

En que situaciones NO CONVIENE de Active Record:

  • Es simple. Esto es bueno y malo al mismo tiempo. Con lógica de negocio compleja, este patrón pierde coherencia.
  • Otra desventaja que al estar tan acoplado a la estructura de la clase, un cambio en el diseño de la tabla, implica cambiar la clase.
  • En situaciones de operaciones de alto volumen de datos, el overhead que se paga en el pasaje y carga de datos, es innecesario. Esta desventaja aplica tanto a Active Record, como a cualquier otro diseño orientado a objetos.
  • Muchos “puristas” de OOP critican que ActiveRecord tiene una misma clase tanto la responsabilidad de acceder a la base como de manejar la lógica de negocio y “ensucia” el código. Nunca coincidí con ese fundamentalismo que a veces prioriza el purismo por sobre la simplicidad, pero es algo que muchos critican de este patrón.

Ejemplo de una clase típica de Active Record:

public class Order
{

public Order()
{
}

public Order(int orderID)
{
}

public int OrderID {get; set;}
public DateTime OrderDate {get; set;}
public DateTime ShipDate {get; set;}
public string ShipName {get; set;}
public string ShipAddress {get; set;}
public string ShipCity {get; set;}
public string ShipCountry {get; set;}

public void Insert()
{
// Inserta un registro en la tabla
}

public void Delete()
{
// Elimina el registro de la tabla
}

public void Update()
{
// Modifica el registro en la tabla
}

public static int GetCount()
{
// Retorna el total de registros de la tabla
}

public static Order FindById(int id)
{
//Busca en la tabla el objeto usando como criterio su id.
}

public static List<Order> LoadAll()
{
// Carga todos los regisotros de la tabla.
}

}
Por lo general siempre existen algunos métodos estáticos, como por ejemplo LoadAll(), que devuelve una colección de objetos de la misma clase. Otro típico ejemplo de un método estático es el FindById()

Conversión de datos

Este es un problema habitual en cualquier metodología que haga un mapeo de datos de un objeto con la base de datos.
Por ejemplo, si tenemos en la tabla una columna de tipo int cuyo valor en un registro es nulo, como convertimos este valor dentro de una propiedad del objeto? Por un lado podemos usar Nullable<int> que viene a partir de .NET 2.0. Otra estrategia es transformar valores nulos en 0. Puede parecer un poco desprolija esta metodología, pero muchas veces hay que preguntarse si queremos identificar entre un campo numérico nulo y uno cuyo valor sea 0. Obviamente, si queremos poder distinguir entre estos 2 valores, deberemos usar Nullable<int>.

Foreign Key Mapping Pattern (FKM)

Supongamos con el ejemplo que vimos anteriormente, que la tabla Order, tiene una FK a la tabla Customer. Como se debe comportar una clase Active Record cuando la tabla con la que trabaja tiene una Foreing Key?
Existen 2 maneras:

En primer lugar, mantener lo mas purista y simple posible y agregar solamente una propiedad más que sea el CustomerId, de manera que la estructura del objeto sea idéntica a la estructura de la tabla.

public int CustomerID {get; set;}

La segunda opción, es usar el patron Foreign Key Mapper. En mi opinión, una opcion mucho mas clara mucho más clara que consiste en vez de agregar una propiedad que represente el ID de la tabla, agregar una propiedad que sea una referencia directa al objeto.

public Customer Customer {get; set;}

Row Data Gateway Pattern (RDG)

Row Data Gateway es un patrón exclusivamente orientado al acceso a la base de datos, pero de características similares a Active Record.
La gran diferencia entre ambos, es que Active Record incluye métodos de acceso a la base de datos y métodos de lógica de negocio, mientras que Row Data Gateway, solo incluye métodos de acceso a la base.

Frameworks y generadores de codigo

  • Castle ActiveRecord: Por lejos, el framework más común que para trabajar con Active Record en .NET. Esta implementado sobre nhibernate, por lo cual también hay que tener esta librería para poder usarlo.
  • LINQ to SQL: Es la solución desarrollada por Microsoft para el mapeo de objetos con la base de datos. Puede también usarse con el patrón Active Record.
  • Además de estos frameworks, existen herramientas de generación de código en base a este patrón como .netTiers. También uno se puede crear su propio template de generación de código con MyGeneration o CodeSmith.


noviembre 12, 2008

Interop y cómo usar un componente .NET desde Visual Basic 6 y ASP

Filed under: .NET, Interop — Etiquetas: , , — grimpi @ 2:22 am

La tecnología COM fue la solución de Microsoft para el desarrollo de componentes reutilizables que puedan funcionar de manera distribuida y que sean independientemente del lenguaje de programación.
Con el desarrollo de .NET, COM quedo relegado. Sin embargo, muchos sistemas actualmente en producción, especialmente los desarrollados en VB 6 y ASP legacy, siguen usando esta tecnología.
Dado que existe una plataforma de desarrollo mucho más robusta y potente como .NET, es posible crear componentes en esta tecnología y que puedan ser invocados desde una aplicación VB 6 o ASP? La respuesta es afirmativa.
Dentro del Framework .NET, existe una librería llamada Interop que permite tanto usar un componente COM desde .NET como permitir llamar un assembly desde una aplicación COM.

Hoy vamos a explicar esto último, cómo usar un componente desarrollado en .NET desde ASP legacy o Visual Basic 6. La idea no es entrar en profundidad en este tema ni todas sus desventajas, sino hacer una guía rápida para el desarrollo de componentes COM en .NET.

  • El primer paso es crear explícitamente interface POR CADA clase que queremos exportar a COM. Si no lo hacemos, el compilador lo va a hacer solo, con el riesgo de perder compatibilidad binaria. Para decirle al compilador que no genera una interface por cada clase a exportable, hay que poner en el archivo assembly esta sentencia:

[assembly: ClassInterface(ClassInterfaceType.None)]

  • Hay que indicar como queremos que se instancien las clases COM (Late binding o Early bindin o ambos). Si queremos que se puedan crear de las 2 maneras, en cada interfaz hay que poner el atributo:

    [InterfaceTypeAttribute(ComInterfaceType.InterfaceIsDual)]

    Si solo queremos Late bindig hay que poner (recomendado)

    [InterfaceTypeAttribute(ComInterfaceType.InterfaceIsDispatch)]

  • Las clases que no se desean exportar a COM deben tener el atributo ComVisible(false)
  • Se debe firmar el componente, ya sea usando el programa sn.exe –k o directamente desde la IDE (esto solo se puede con VS 2005/2008).
  • Una vez compilado, se debe usar el programa Gacutil.exe para poner el assembly en la GAC (mas info sobre la GAC aca) y regasm.exe para generar y registrar el TLB del assembly.
    Ejemplo:

regasm Nombre.dll /tlb: Nombre.tlb

gacutil /i Nombre.dll

Para deregistrar el componente, hay que ingresar

regasm /u Nombre.dll /tlb: Nombre.tlb

gacutil /u Nombre.dll

Lamentablemente por alguna razón, el instalador de Visual Studio 2005/2008 no viene con el regasm.exe, por lo cual para conseguirlo es necesario descargar todo el SDK de .NET (http://www.microsoft.com/downloads/details.aspx?FamilyID=fe6f2099-b7b4-4f47-a244-c96d69c35dec&displaylang=en).

  • Una vez registrada el tlb, podemos usarlo desde VB 6 como si fuera un típico componente COM tradicional.

Consideraciones y Restricciones:

  1. Los constructores con parámetros no se pueden exportar a COM.
  2. Cuando se pasa un parametro variant en ASP o VB6 a un metodo de .NET se debe pasar entre parentesis. Sino genera el error Invalid Procedure or call Argument.
    Ejemplo:
    call ClaseInstancia.MetodoNuevo((ParametroVariant),OtroParametro)
  3. No se pueden usar NullTypes ni Generics.
  4. El paso de .NET a COM, requiere de una capa intermedia (marshaling), que por supuesto tiene un costo de procesamiento, algo que hay que tener en cuenta en sitios con alta cantidad de usuarios.
  5. Interop debe ser entendido y usado como una solución de corto y mediano plazo, para facilitar la migración de un sistema VB 6/ASP legacy a la plataforma .NET, no como una práctica recomendable.

noviembre 3, 2008

Detectar la versión de una base SQL Server Compact Edition

Filed under: SQL Server CE — Etiquetas: , — grimpi @ 1:16 am

Ayer descubrí un link muy interesante de como detectar la versión de un archivo SDF sin necesidad de abrir una conexión a la base.

http://nativemobile.blogspot.com/2007/12/detecting-sdf-file-version.html

La técnica consiste en abrir el archivo como si fuera un binario común y leer una serie de caracteres del archivo en una posición determinada. Dependiendo de la marca que se encuentra en el archivo, es una versión diferente.
Porque complicarse la vida haciendo esto cuando podemos abrir la conexión, llamar a la propiedad ServerVersion del objeto SqlCeConnection?
Respuesta simple: No se puede abrir una base de SQL Server CE 3.1 usando las librerías de 3.5. Por lo tanto, si necesitamos hacer una aplicación que sepa trabajar con ambas versiones simultáneamente, antes de abrir la conexión, debemos averiguar la versión de la base para saber a qué versión de la librería de conexión a la base debemos instanciar.

Blog de WordPress.com.