Esa útil tabla de Números en SQL Server


Documento sin título

De manera frecuente nos encontramos en escenarios en los que la utilidad de una tabla de números resulta una opción de gran valía, en este caso particular me quiero referir a una consulta hecha
por un usuario en los foros msdn (http://social.msdn.microsoft.com/Forums/es-ES/sqlserveres/thread/4ecf2763-9200-4a45-b184-ee240aa13970/#01137538-8c2b-488a-887b-20f2fbaa384c ), la cual cito textualmente:

 

“Hola amigos les expongo mi problema espero me puedan ayudar me super urge les explico a modo de ejemplo:

 

Tengo un contrato y su fecha de primer pago es el 15/06/2012 y el precio es de $1000 y ese saldo el cliente lo va a pagar en 10 pagos semanales lo que necesito es que apartir de la fecha de primer pago, se generen los registros de los 10 pagos y que se especifique que van a ser 10 pagos por 100 pesos cada uno mas o menos quedaria asi:

 

15/06/2012         $100.00

22/06/2012         $100.00

29/06/2012         $100.00

 

y asi hasta acompletar los 10 pagos de 100 pesos cada uno.

 

 

Creando una tabla de Numeros

En la opción que me planteaba para solventar la consulta buscaba como evitar cualquier posible uso de cursores o soluciones de tipo procedimental, con el fin de potenciar y generar una opción óptima y enfocada a un manejo de teoría de conjuntos (set theory).  El primer paso consistirá en crear una tabla de Números , esta estructura es muy útil, para una diversidad de escenarios que va desde la generación de datos de prueba hasta su uso con funciones de ventana.

La estructura de la tabla es muy simple, puesto que lo requerido es  una secuencia numerica, en cuanto al numero de filas o numeros que queremos almacenar en la tabla varia según los requerimientos o usos comunes que les pretendamos dar.  En este caso utilizare un codigo planteado por el excepcional Itzik Ben-Gan, cuyo código en lugar de utilizar un tipico ciclo While en donde se recorre un registro a la vez, se insertan las filas de manera masiva, minimizando la cantidad de vueltas o loop, siendo la diferencia en tiempo y rendimiento muy amplia, a continuación el codigo:

 

IF OBJECT_ID(‘dbo.Nums’, ‘U’) IS NOT NULL DROP TABLE dbo.Nums;

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);

DECLARE @max AS INT, @rc AS INT;

SET @max = 1000000;

SET @rc = 1;

INSERT INTO Nums VALUES(1);

WHILE @rc * 2 <= @max

BEGIN

INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;

SET @rc = @rc * 2;

END

INSERT INTO dbo.Nums

SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

 

Llegado a este punto ya contamos con nuestra tabla de Numeros, a continuación procedemos a construir una funcion definida por el usuario que nos retornara una tabla, lo interesante es la utilización de un CROSS JOIN que nos permitira evitar el uso de un ciclo para generar el cuadro de pagos que se requiere.  La funcion recibira como parametros de entrada la fecha inicial en la que se generara la tabla de pagos, seguido del monto (en este caso se asume como un monto fijo) y el numero de cuotas a calcular.

CREATE FUNCTION FDU_CalculoPagos(@i_fecha_inicial date,@i_monto money,@i_cuotas int)

RETURNS @Tabla TABLE

(

 numpago int,

 fechapago date,

 cuota  money

)

AS

BEGIN

DECLARE @cuota money

 

  SELECT @cuota=@i_monto/@i_cuotas

  

 INSERT INTO @Tabla

 SELECT N.n,DATEADD(month,n,F.fecha_inicio),@cuota

 FROM Nums AS N

 CROSS JOIN (VALUES(@i_fecha_inicial)) AS F(fecha_inicio)

 WHERE N.n <= @i_cuotas

 

 RETURN

END

 

GO

Como podemos observar la tabla de numeros nos permite realizar una operación para obtener las posibles combinaciones (recordemos que CROSS JOIN genera un producto cartesiano n x m), ubicando como unica restricción que el numero de filas tomadas sea menor o igual que las cuotas que se reciba como parametro en la funcion, en este caso @i_cuotas, ademas de auxilarnos de un uso no tan conocido del operador VALUES y disponible a partir de SQL Server 2008, en cuyo caso podemos representar valores como si se tratase de una tabla, para nuestro caso particular ese unico valor es el parametro de fecha inicial (@i_fecha_inicial).

Se procede a insertar en la variable de tipo tabla (@Tabla) el resultado de la instrucción SELECT que contiene un secuencial, pero sobre todo el calculo de las fechas correspondientes para los pagos haciendo uso de la funcion DATEADD que se encarga de adicionar a la fecha inicial una cantidad de meses (sumando un mes en cada fila) correspondiente a las cuotas definidas.

Como vimos en este pequeño ejemplo, se trato de plantear una solución generica y sin un manejo procedimental que pudiera afectar el rendimiento y eficiencia de nuestra aplicación.

 

 

 

Microsoft Community Contributor Award 2012

 

Hoy ha sido un dia especial, me he topado con la grata sorpresa de recibir el reconocimiento Microsoft Community Contributor 2012:

Dear Geovanny, 

Congratulations! We’re pleased to inform you that your contributions to Microsoft online technical communities have been recognized with the Microsoft Community Contributor Award. 

The Microsoft Community Contributor Award is reserved for participants who have made notable contributions in Microsoft online community forums such as TechNet, MSDN and Answers. The value of these resources is greatly enhanced by participants like you, who voluntarily contribute your time and energy to improve the online community experience for others. 

Y que mejor lugar para compartir la buena nueva que con mis colegas de Geeks, quienes siempre han tenido las puertas abiertas de esta valiosa comunidad.  Gracias a todos y a Microsoft por honrarme de esta forma, lo que me compromete cada nuevo día a dar lo mejor de mi y continuar ayudando y aprendiendo de la vasta comunidad de amantes de la tecnología.

 

Del Cursor y otros demonios

El titulo de este post es un pequeño
homenaje a una novela del Nobel Gabriel García Márquez, su nombre me vino a la
mente después de asociarlo a una serie de usos y malas prácticas que por lo
general los desarrolladores cometemos al iniciar con la programación  a nivel de Base de Datos; queriendo de forma
inconsciente extrapolar todos nuestros conocimientos y “manías”en lenguajes
procedimentales y orientados a objetos para transferirlos a nuestras soluciones
en T-SQL.

Cuantas veces nos hemos topado en
diferentes foros, reuniones o en la misma oficina con comentarios como: Eso es fácil,
mételo en un cursor y ….. La triste historia inicia, en especial cuando meses después
escuchamos: La consulta está demasiado lenta, se tarda N horas y sigue
creciendo… Generalmente comienzan a llover soluciones mágicas, en las que por
descabelladas que puedan parecer se han llegado a implementar, algunas simples
y poco efectivas hasta algunas muy caras y poco productivas.

Es en este punto donde elementos
como los Cursores transtornan la salud de nuestras aplicaciones, convirtiéndose
en un lastre difícil de quitar, en especial si forma parte de la cultura del
desarrollador.  Como principio
fundamental que puede resultar orientador esta una afirmación que hacía en uno
de sus textos Itzik Ben-Gan al mencionar que el uso de cursores representaba
una opción viable si se requería manipular una fila a la vez.

A continuación tomo prestado de
los foros de MSDN una consulta en la que el usuario se avocaba directamente al
cursor como el método de solución elegido, decía algo así:

“Quiero preguntarles como puedo hacer un script
en el que se maneje un cursor para depurar una base de datos…bueno tengo una
tabla llamada auditoria_homologacion donde tengo varios registros con id
autonumerico pero el resto de los campos están repetidos, entonces
cuando se busca un registro por cualquier campo
(menos el id_auditoria)  me aparecen varios registros iguales,
entonces debo buscar los registros repetidos, luego tengo que mirar que esos
registros que están repetidos no estén  amarrados a otra
tabla que se llama log_error_homologacion en donde tengo el fk_id_auditoria de
la tabla auditoria_homologacion, 
(entonces los que están en la tabla log_error_homologacion no
se borran)
, pero hay mas
registros repetidos los cuales no tienen error pero debo borrar
los demás que están repetidos 
(por ejemplo tengo 5 registros con diferente id_auditoria pero el resto
de los campos en igual..debo borrar 4 y dejar 1)
 y no
se como hacerlo por medio de un cursor, agradezco de antemano
la colaboración me es de gran ayuda gracias.”

Aunque la forma de explicarlo no
fue la idónea, la idea central consistía en obtener un conjunto de filas
duplicadas y verificar que las mismas se encontraban en una tabla de log, caso
contrario eliminarlas.   Partiendo de la
afirmación de Ben-Gan en este caso no es necesario procesar una fila a la vez,
por el contrario generar una solución basada en cursores sería extremadamente
deficiente y muy tediosa.

Para solventar esta situación
procedimos al uso de una solución basada en CTE (Common Table Expression) y la función
ROW_NUMBER(), aquí presento el código:

 

 

DECLARE
@AuditoriaHomologacion TABLE (IDAuditoria INT,Columna1 VARCHAR(30),Columna2
INT,Columna3 VARCHAR(30),Columna4 INT)

DECLARE
@LogErrorHomologacion TABLE (IDAuditoria INT)

 

INSERT
@AuditoriaHomologacion

VALUES  (1,’LAUREN FONSECA’,1,’NICARAGUA’,2),

               (2,’LAUREN
FONSECA’,1,’NICARAGUA’,2),

               (3,’LAUREN
FONSECA’,1,’NICARAGUA’,2),

               (4,’OCTAVIO
HERNANDEZ’,1,’ESPANA’,2),

               (5,’OCTAVIO
HERNANDEZ’,1,’ESPANA’,2),

               (6,’LAUREN GIOVANNA
HERNANDEZ’,1,’ESPANA’,2),

               (7,’LAUREN GIOVANNA
HERNANDEZ’,1,’ESPANA’,2)

 

INSERT
@LogErrorHomologacion

VALUES (7),(5)

 

; WITH MiCTE AS

(

  SELECT IDAuditoria,ROW_NUMBER()
OVER(PARTITION BY Columna1,Columna2,Columna3,Columna4 ORDER BY IDAuditoria)AS
‘SEC’

  FROM @AuditoriaHomologacion

)

DELETE FROM MiCTE

WHERE MiCTE.SEC >1 AND
NOT EXISTS(SELECT IDAuditoria FROM @LogErrorHomologacion L WHERE
L.IDAuditoria=MiCTE.IDAuditoria)

 

–Verificando
filas eliminadas

SELECT
* FROM @AuditoriaHomologacion

 

En esta solución planteada se
potenciaban aspectos que permiten contar con un código mantenible y sobre todo
enfocado en aspectos orientados al manejo de <<set theory>>.

 

Pero como no todo es desolador
y gris,  un escenario común en donde el
uso de cursores es viable puede ser en tareas administrativas de nuestras base
de datos,  el siguiente ejemplo permite
recorrer todas las tablas de una Base de Datos y obtener información sobre las
mismas (filas, tamaño,etc) a través de la ejecución del sp_spacedused.

 

USE AdventureWorks

GO

 

DECLARE  @tabla VARCHAR(128)

 

CREATE TABLE #rowcount
(tablename varchar(128), rowcnt int)

exec sp_MSforeachtable

   ‘insert into #rowcount select ”?”,
count(*) from ?’

 

DECLARE tablas CURSOR FOR

SELECT tablename FROM
#rowcount

OPEN tablas

FETCH NEXT FROM tablas INTO
@tabla

WHILE @@fetch_status = 0

 BEGIN

  EXEC sp_spaceused @tabla

  FETCH NEXT FROM tablas INTO @tabla

 END

CLOSE tablas

DEALLOCATE tablas

 

DROP TABLE #rowcount

 

Por lo pronto un hasta luego, seguimos escribiendo,
pero sobre todo aprendiendo.   

Email: info@geohernandez.com     Website: http://geeks.ms/blogs/ghernandez

A Lauren …

 

 

Aquellas pequeñas cosas, transformando datos XML en un rowset

El intercambio de información entre empresas y
organizaciones representan una tarea cada vez más común, por esto no es de
extrañar el amplio uso y aceptación del formato XML como estándar para el
intercambio de dicha información.  SQL Server cuenta entre sus muchas características con una
serie de funciones que nos permiten potenciar el uso de XML, pero en esta ocasión
nos centraremos el proceso de transformación de datos XML en un conjunto de
datos (rowset).

El procesamiento de XML se realiza en cinco pasos
fundamentales como se ilustra en la siguiente imagen:

 

Una vez que se cuenta con el documento XML (bien formado)
este se procede a procesar por medio del procedimiento almacenado del sistema
sp_xml_preparedocument, el cual nos permite  validar un documento XML y transformarlo en
una estructura de árbol en memoria, cuyo  contenido podemos consultar por medio de la
instrucción OPENXML para obtener las columnas deseadas y así manipular los
datos en la forma requerida.

Iniciemos con un ejemplo simple, suponiendo que formamos
parte de la empresa dedicada a la reventa de artículos por internet y estamos
recibiendo información de un proveedor externo, el documento XML es el
siguiente:

 

DECLARE @doc xml

SET @doc = ‘<?xml
version=”1.0″ ?>

            <Factura
FacturaID=”1000″ ClienteID=”123″
FechaFactura=”2012-01-14″ FormaPago=”1″>

              <Lineas>

                <Linea ProductoID=”12″
Cantidad=”2″
PrecioUnitario=”12.99″><Descripcion>Encendedor marca
Zippo</Descripcion></Linea>

                <Linea
ProductoID=”41″ Cantidad=”1″
PrecioUnitario=”17.45″><Descripcion>Ron Flor de
Cana</Descripcion></Linea>

               
<Linea ProductoID=”2″ Cantidad=”1″
PrecioUnitario=”2.99″><Descripcion>Bebida embotellada
natural</Descripcion></Linea>

              </Lineas>

            </Factura>’

 

DECLARE @IdentificadorDoc
int

EXEC sp_xml_preparedocument
@IdentificadorDoc OUTPUT, @doc

 

De forma simple podemos decir que
el sp_xml_preparedocument
nos permite procesar un documento XML y obtener una representación del mismo,
el resultado de este sp es un código identificador que apunta hacia la
estructura de árbol del XML creado previamente. 
Una descripción más amplia se encuentra en el siguiente link:

http://msdn.microsoft.com/es-es/library/ms187367.aspx

 

Una vez que construida la estructura o árbol en memoria debemos
acceder a ella, para esto nos apoyamos en la instrucción OPENXML , este nos
permite obtener un conjunto de datos a partir del árbol.  La instrucción OPENXML tiene la siguiente estructura:

OPENXML( idoc int [ in] , rowpattern nvarchar [
in ] , [ flags byte [ in ] ] )

[ WITH ( SchemaDeclaration | TableName ) ]

 

El parámetro idoc
representaría en nuestro caso el parámetro de salida resultante de la ejecución
del sp_xml_preparedocument, en donde especifica la representación interna del
documento XML, el patrón de filas (rowpattern)  es el patrón XPATH que permite  definir la ruta o nodos que deberá reconocerse
en la lectura del XML, a continuación nuestro código:

 

SELECT *
FROM

OPENXML(@IdentificadorDoc, ‘/Factura/Lineas/Linea’)

WITH

(              FacturaTarget       int ‘../../@FacturaID’,

                ClienteTarget       int ‘../../@ClienteID’,

                FechaFacturaTarget            datetime
‘../../@FechaFactura’,

                FormaPagoTarget   int ‘../../@FormaPago’,

                DetallesTarget      varchar(30) ‘./Descripcion’,

                CantidadTarget    int ‘@Cantidad’,

                PrecioUnitarioTarget          float ‘@PrecioUnitario’)

 

En este caso especifico  utilizamos una opción conocida como patrones
de columna, estos contienen un patron XPath para la recuperación de los nodos y
este será relativo al patrón de fila (rowpattern) especificado.  Y esto que quiere decir? Que la recuperación
de los nodos estará basada en una expresión o expresiones en donde se navegara
a través de la jerarquía del documento XML para obtener los datos deseados.

Aquí caben notar dos cosas:

OPENXML(@IdentificadorDoc, ‘/Factura/Lineas/Linea’)

Le estamos indicando a la
instrucción que debe tomar la representación interna perteneciente al documento
que acabamos de transformar, posteriormente le indicamos hasta que nivel deseo “navegar”
en la estructura del documento XML. 
Posteriormente comenzamos a construir la estructura que deseamos
recuperar, especificando el nombre del campo, su tipo de datos  y el origen o expresión XPATH a partir de
donde obtendrá los datos.

WITH

(              FacturaTarget       int ‘../../@FacturaID’,

                ClienteTarget       int ‘../../@ClienteID’,

                FechaFacturaTarget            datetime
‘../../@FechaFactura’,

                FormaPagoTarget   int ‘../../@FormaPago’,

                DetallesTarget      varchar(30) ‘./Descripcion’,

                CantidadTarget    int ‘@Cantidad’,

                PrecioUnitarioTarget          float ‘@PrecioUnitario’)

El manejo o navegación se realize desde el ultimo nodo que
se especifico en el patron de fila, cabe mencionar que los atributos se
representan por medio de @nombreatributo, el siguiente caso funciona así:

FacturaTarget       int ‘../../@FacturaID’ 

FacturaTarget
int: Es el campo con su respectivo tipo de datos que almacenara el resultado
devuelto por la expresión.

../../@FacturaID: Cabe recordar que
según el patrón de fila este tiene una estructura ‘/Factura/Lineas/Linea’ , en este caso se
quiere recuperar el atributo FacturaID, este se encuentra en el nodo
correspondiente a Factura, por lo que si partimos de la ruta especificada en el
patrón de filas y viendo el documento XML tenemos que subir dos niveles , para
este caso se usa la expresión << ../>>.

En los casos de los atributos que se encuentran en el ultimo
nivel (Linea) solo se debe de anteponer el símbolo @NombreAtributo.

Un caso especial aquí es el del ELEMENTO Descripcion, debido
a su naturaleza y ubicación dentro del XML para obtener su valor se debe de
especificar de la siguiente manera:

./Descripcion’

Aquí le indicamos que seleccione el nodo actual a partir del
nodo raíz (nodo Linea), como punto de ayuda presento una tabla resumen de las
principales expresiones de ruta usadas en XPath.

Expresión

Descripción

nodename

Selecciona todos los nodos hijos del
nodename

/

Selecciona a
partir del nodo raiz.

.

Selecciona el nodo actual

..

Selecciona el padre del nodo actual

@

Selecciona atributos

 

Con estos pasos descritos anteriormente ya tenemos como
resultado los campos extraidos directamente de un documento XML.  Nuestra consulta completa quedaría así:

DECLARE @doc xml

SET @doc = ‘<?xml
version=”1.0″ ?>

            <Factura
FacturaID=”1000″ ClienteID=”123″
FechaFactura=”2012-01-14″ FormaPago=”1″>

              <Lineas>

                <Linea
ProductoID=”12″ Cantidad=”2″
PrecioUnitario=”12.99″><Descripcion>Encendedor marca
Zippo</Descripcion></Linea>

                <Linea
ProductoID=”41″ Cantidad=”1″
PrecioUnitario=”17.45″><Descripcion>Ron Flor de
Cana</Descripcion></Linea>

                <Linea
ProductoID=”2″ Cantidad=”1″
PrecioUnitario=”2.99″><Descripcion>Bebida embotellada natural</Descripcion></Linea>

              </Lineas>

           
</Factura>’

 

DECLARE
@IdentificadorDoc int

EXEC sp_xml_preparedocument @IdentificadorDoc
OUTPUT, @doc

 

SELECT * FROM

OPENXML(@IdentificadorDoc, ‘/Factura/Lineas/Linea’)

WITH

(      FacturaTarget int ‘../../@FacturaID’,

       ClienteTarget int ‘../../@ClienteID’,

       FechaFacturaTarget  datetime ‘../../@FechaFactura’,

       FormaPagoTarget   int ‘../../@FormaPago’,

       DetallesTarget      varchar(30) ‘./Descripcion’,

       CantidadTarget      int ‘/@Cantidad’,

       PrecioUnitarioTarget       float ‘@PrecioUnitario’)

 

EXEC sp_xml_removedocument @IdentificadorDoc

GO

 

Espero les haya servido de ayuda y seguimos escribiendo,
pero sobre todo aprendiendo…

Gio

Email: info@geohernandez.com

 

 

Usando tablas derivadas con agrupaciones

 

El el foro de MSDN
un colega planteaba dudas acerca del uso de la clausula GROUP BY, entre ellas hacía
referencia a la restricción que existe en las columnas que pueden ser listadas
en el SELECT de la consulta, ya que solo es 
posible que listemos columnas que estén incluidas en el GROUP BY o que
utilicemos funciones de agregación.

Es interesante
notar que existen muchas situaciones en las que requerimos listar columnas que
no sería lógico que se incluyeran en el GROUP BY, entonces ¿Cómo podemos
sobrellevar la restricción inherente esta clausula?, es en estos casos donde
nos podemos auxiliar de las tablas derivadas.

Tomemos como modelo
la Base de Datos Northwinds, tenemos un requerimiento en el que se nos solicita
una consulta que nos retorne la sumatoria de las  ventas por producto, debiendo mostrar el código
del producto, el nombre del producto y las sumatoria de las ventas.  Si nos basamos en la restricción de la
clausula GROUP BY tendríamos que incluir no solo el código del producto, sino también
el nombre, y esto no sería muy lógico, aquí vienen las tablas derivadas a
darnos elegancia y coherencia a nuestra consulta.

Tablas:

dbo.Products

dbo.Order Details

 

La consulta es
la siguiente:

 

SELECT P.ProductID,
P.ProductName ,O.QuantityTotal

FROM Products AS P

INNER JOIN (SELECT
ProductID, SUM(Quantity) AS QuantityTotal

         FROM [Order Details]

         GROUP BY ProductID) AS O

ON P.ProductID = O.ProductID

ORDER BY
P.ProductID

 

Podemos ver que
en este caso la implementación de una tabla derivada nos brinda la ventaja de “separar”
y categorizar las ordenes  sumarizando las
ventas totales por producto, para luego simplemente utilizar el operador de
tabla (JOIN) ligándolo a la tabla Productos y permitiéndonos de forma natural
extraer el nombre o cualquier otra columna que nos interese, sin tener que
incluirla en el GROUP BY, permitiéndonos pasar por alto la restricción antes
mencionada.

 

Condiciones booleanas en Where?

 

Con
cierta frecuencia debemos recurrir a algunas tecnicas de desarrollo que nos
permiten optimizar nuestro trabajo en una forma simple y mantenible, en este caso
particular tiene que ver con el diseño de un procedimiento almacenado que me
permitiera hacer una consulta cuyas condiciones estaran basadas en los
parametros de entrada, delimitando o filtrando los  resultados.

 

El
ejemplo basico que utilizaremos usa la Base de Datos Northwind, y consiste en
obtener todos los registros de la tabla Orders que coincidan con los filtros
relacionados a: Codigo de Empleado, Codigo de Cliente y Via de Compra
(ShipVia). 

 

CREATE PROCEDURE uspGetOrders

(

 @CustomerID
nchar(5)=null,

 @EmployeeID
int=null,

 @ShipVia
int=null

)

As

 

SELECT * FROM dbo.Orders

WHERE (CustomerID=COALESCE(@CustomerID,CustomerID))

AND (EmployeeID=COALESCE(@EmployeeID,EmployeeID))

AND (ShipVia=COALESCE(@ShipVia,ShipVia))

  Set @tabla=’Products’

 

En este
segmento de código podemos observar que se reciben los parametros antes
mencionados, pero teniendo un default value igual a null, en caso de que no se
envie un parametro este sera obviado en el procesamiento o filtro de la
consulta. 

Al
momento de establecer las condiciones del Where por ejemplo: CustomerID=COALESCE(@CustomerID,CustomerID))

Estamos
indicando que la columna CustomerID sera igual al valor retornado por la
funcion Coalesce la cual nos retorna el primer valor no nulo, por lo que en
caso de que el parametro sea diferente de nulo se tendra conformada la
condicion, y en caso de ser null entonces procede a establecer CustomerID=CustomerID,
esto representa una condicion de igualdad, lo que no causa ningun procesamiento
y sirve perfectamente a nuestro objetivo.

Cuando
procedemos a ejecutar el sp pasamos los valores a determinados parametros según
nuestras necesidades.   Como ejemplo:

 

EXEC uspGetOrders

@CustomerID=’PICCO’,

@EmployeeID=2

 

Nos devuelven 3 registros.

 

O

 

EXEC uspGetOrders

@CustomerID=’PICCO’

 

Nos devuelve 10 registros

 

Esta
solucion a mi parecer resulta mas simple y menos compleja que otras en las que
utilizan IF o instrucciones CASE WHEN.

 

 

Precompilación de Vistas en el Entity Framework

En la búsqueda de
un mejor rendimiento dentro de mis aplicaciones que utilizan Entity Framework
(EF), logre encontrar información relacionada con la precompilación de vistas,
el cual es un procedimiento que permite disminuir de manera significativa el costo
de ejecución en consultas hechas contra el modelo.

La documentación
oficial de Microsoft menciona el hecho de que antes de que el EF ejecute una
consulta contra un modelo conceptual o salve cambios a la fuente de datos, este
debe generar un conjunto de vistas locales que acceden a la base de datos,  este proceso resulta demasiado costoso, como
referencia sobre este tema hay un blog titulado “Exploring the Performance of
the ADO.NET Entity Framework – Part 1” (http://blogs.msdn.com/b/adonet/archive/2008/02/04/exploring-the-performance-of-the-ado-net-entity-framework-part-1.aspx). 

Una solución propuesta para contrarestar esta
conducta del Entity es la precompilación de vistas y su inclusión en el
proyecto a fin de garantizar no tener que crearlas en tiempo de ejecución. 

Para este blog
usare  SQL Server 2008  y Visual Studio 2010, basando nuestro modelo
en la BD Adventure Works.  Iniciamos
creando una nueva solución, y especificamos un proyecto de tipo <<Class
Library>>, esto nos brinda la ventaja de poder usarlo en muchos
proyectos, el siguiente paso consiste en crear un archivo de tipo <<ADO .Net
Entity Data Model>> y definimos el modelo con el nombre AWModel.edmx como
muestra la siguiente pantalla:

 

Definimos que el
modelo se generara desde la BD y seleccionamos algunas tablas representativas
para su integración dentro del modelo de entidades. 

 

Una vez finalizado
este procedimiento se nos presenta el modelo con las entidades seleccionadas,
hasta el momento los pasos realizados son simplemente de configuración de
nuestro proyecto y son la antesala al proceso especifico de creación de las vistas
precompiladas.  El primer paso para este
proceso consiste en cambiar la propiedad Metadata
Artifact Processing
de nuestro modelo, el cual debe contener la opción
<<Copy to Output Directory>>

 

Una vez realizado
este cambio, procedemos a realizar un Build de la solución y verificamos en el
directorio de salida que se encuentren tres nuevos archivos que conforman los
archivos de mapeo del modelo.  La
siguiente imagen muestra la relación de estos archivos con el modelo:

 

 

Para mayor detalle
sobre el tema sugiero la lectura del segundo capítulo (Pag.29) del libro
Programming Entity Framework  Second
Edition de Julie Lerman.  Los archivos
generados para nuestro ejemplo son los siguientes:

Estos archivos los
incluiremos dentro de nuestra solución, damos click derecho en el proyecto y
escogemos la opción Add – Existing Items, y seleccionamos los tres archivos,
luego seleccionamos a cada uno de estos archivos y en la ventana de propiedades
establecemos Build Action al valor Embedded Resource este paso nos permite
definir dichos archivos como recursos embebidos en el Assembly que contiene
nuestro modelo.

Continuamos con la
creación de la denominada vista de entidades, para lo cual nos vamos a la
opción de propiedades del proyecto, y nos situamos en el tab Build
Events
y en la opción Prebuild Event Commands Line , en
este casilla debemos de especificar el siguiente código:

“%windir%Microsoft.NETFrameworkv4.0.30319EdmGen.exe”
/nologo /language:CSharp /mode:ViewGeneration “/inssdl:$(TargetDir)AWModel.ssdl”
“/incsdl:$(TargetDir)AWModel.csdl” “/inmsl:$(TargetDir)AWModel.msl”
“/outviews:$(ProjectDir)AWModel.Views.cs”

Cabe destacar que
para el caso de utilizar Visual Basic, solo se reemplaza CSharp por VB.  El siguiente paso consiste en hacer un
Rebuild de la solución.

 

A continuación incluimos el nuevo archivo generado AWModelView.cs, damos click
derecho en el proyecto y en la opción Add – Existing Item seleccionamos el
archivo de vista.

 

 

 

Con el apoyo de la
herramienta Red Gate’s .Net Reflector nos permite analizar el assembly
compilado, es interesante observar como en la sección de Resources nos
despliega los archivos que forman el esquema de nuestro modelo, además de
incluir la vista generada.

 

Llegando a este
punto contamos con un Assembly cuyo contenido esencialmente es nuestro modelo
de entidades, así como los archivos que conforman su metadata.  Esto nos permitirá incorporar nuestro
Assembly en diferentes proyectos, además de mantener una mayor eficiencia en
los tiempos de ejecución de nuestra aplicación, debido a la pre compilación de
las vistas. 

En posteriores blog
estare abordando otros puntos y tips que pueden resultar de utilidad en el
desarrollo de aplicaciones con Entity Framework.

 

 

 

Enlace de interés sobre LINQ

Despues de algunas lunas de ausencia, estoy de nuevo listo y con mucho material, por el momento quiero compartir un enlace que me sirvio de mucha ayuda para comprender el uso que hace LINQ de los delegados genéricos y comprender el uso de expresiones Lambda dentro de operadores como Where.  Aquí esta en enlace:

http://blog.linqexchange.com/index.php/understanding-generic-delegates-and-lambda-expressions/

En los siguientes posts estaré hablando sobre Entity Framework  y mostrare una clase modelo  para manejar un conjunto de operaciones de tipo CRUD.  

De Objetos y Clases( “Doing Objects in Visual Basic 2005”)

 




Existen libros fundamentales dentro de nuestro
trabajo diario, y los cuales sirven como una  guía de referencia en muchos temas,  este es mi caso con el libro “Doing Objects
in Visual Basic 2005”
escrito por la reconocida MVP Deborah Kurata.  
Mi encuentro con este valioso texto se debió a la curiosidad (casi
siempre interminable) por profundizar en el tema de la POO desde una  perspectiva práctica a través del lenguaje
Visual Basic 2005.

En los primeros capítulos del libro se
presentan un conjunto de conceptos centrales sobre la Orientación a Objetos,
 y es a través de estas definiciones
precisas que comienza la exploración del proceso de desarrollo de software como
un “todo”, iniciando con  el cliente y
terminando siempre con él,  siendo la
base fundamental de nuestro proceso la definición de requerimientos y sobre
todo la división en unidades de trabajo manejables.

Entre los beneficios mencionados por la autora con
el uso de una metodología orientada a objetos está la de poder  hablar el mismo lenguaje del cliente, es decir
que a través de la abstracción propia de la orientación a objetos y la
construcción de escenarios podremos discutir 
en términos del negocio, abordando únicamente los problemas que
pretendemos resolver y no la tecnología que utilizaremos.

La parte central del libro aborda el desarrollo
de una aplicación para el monitoreo de compras  en la cual se va mostrando de forma detallada  el desarrollo  de las diferentes capas (User Interface Layer,
Business Logic  Layer y Data Access Layer)
proporcionando un base solida sobre la implementación de la orientación a
objetos en Visual Basic , además podemos encontrar  a lo largo del libro  una serie de importantes y útiles
consejos,  soportados por la amplia
experiencia de Deborah en  el desarrollo
de software. 

Un buen detalle es el resumen que se incluye al
final de cada  capítulo y sobre todo la
guía de lecturas adicionales, en donde se hacen referencias a libros y enlaces
web sobre autores destacados en el tema, información que nos puede servir de
base para profundizar en determinados tópicos.

En resumen, podría recomendar sin temor a
equivocarme este libro, el cual denota un trabajo hecho con la pasión y la
experiencia de una colega excepcional.

 

 

Encontrando combinaciones numéricas con SQL Server

Cuando comencé a programar mis primeras aplicaciones en SQL Server tuve que trabajar mucho en el desarrollo de la  programación en T-SQL desde una perspectiva de lógica de conjuntos; al igual que muchos principiantes caí en el error de utilizar(o abusar?) de objetos que me resultaban familiares a los utilizados en  lenguajes de programación procedimental,  como  por ejemplo los cursores.  Con el objetivo de irme adaptando a este nuevo entorno de trabajo inicie realizando ejercicios prácticos, entre los cuales destaca el que presento a continuación y que servirá de base para el presente artículo.  El enunciado del problema es el siguiente:

 

“Se cuenta con un conjunto de series numéricas, en donde cada serie está formada por 6 números que oscilan en un rango de 1 a 49, dichos números nunca se repiten.  Se necesita saber cuáles son los pares numéricos que más se repiten en las series numéricas”.

 

Como base para este articulo adjunto un archivo de Excel con una serie de combinaciones numéricas como la mostrada a continuación: 

 

Sobre la base del problema planteado se  requiere conocer cuáles son los pares numéricos que más se repiten en estas series, tomemos una tabla modelo con cinco series numéricas como la que se muestra a continuación:

 

 

Podemos decir que tenemos tres pares numéricos que son los que se repiten con mayor frecuencia en este grupo, estos son:

 

Pares Numéricos

Repeticiones

12-34

2

13-32

2

27-39

2

 

Obviamente requerimos realizar este proceso de forma rápida y con un buen rendimiento para el manejo de un alto volumen de registros, por lo que iniciaremos con la importación de nuestro archivo de trabajo a una tabla para su posterior tratamiento.  El siguiente script nos permite crear nuestra base de datos de ejemplo.

 

CREATE DATABASE [ExampleSql] ON  PRIMARY

( NAME = N’ExampleSql’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAExampleSql.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N’ExampleSql_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAExampleSql_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

GO

 

Importando de Excel a SQL Server 2005

Abrimos el SQL Server Management Studio, y nos posicionamos en nuestra BD de trabajo

  

Luego damos click derecho y marcamos la opción de Tarea y posteriormente  Importación de Datos .  Procedemos a seleccionar nuestro archivo de origen (marcamos la casilla que indica que nuestra primera fila tiene los nombres de las columnas).

 

Finalmente seleccionamos como destino nuestra base de datos.

 

 

Luego debemos seleccionar la opción que indica copiar datos de una o más tablas o vistas.

 

 Para finalizar la importación se nos presenta una pantalla en la que podemos dejar el nombre por defecto de la tabla (en este caso el mismo de la hoja de cálculo de donde se tomaron los datos), o escribir un nombre específico, en nuestro caso se definió el nombre Tabla_Excel.

 

 

A partir de este momento ya tenemos nuestra tabla inicial de trabajo, la cual contiene más de 4500 series numéricas.  Iniciaremos el proceso adaptando esta tabla al modelo que nosotros requerimos.

 Diseñando nuestra tabla de trabajo

El primer paso para el diseño de nuestra solución consiste en la creación de la tabla DetailNumbers  en donde se almacenaran los números que forman las series numéricas.

 

CREATE TABLE DetailNumbers

(IdSerie int NOT NULL,

 Number smallint NOT NULL CONSTRAINT [CHK_DetailNumbers_Number]      CHECK(Number BETWEEN 1 AND 49),

 PRIMARY KEY (IdSerie,Number)

)

 

En la definición de las tabla se incluyeron las declaraciones de claves primarias, la invocación de las funciones getdate y suser_name como valores por defecto, así como la inclusión de la restricción referida al rango de los números para que estos se encuentren en un rango de 1 a 49.   

 

Para la adaptación de la tabla DetailNumbers debemos de aplicar una función que nos permita transformar las columnas (Number 1..6) en filas, agrupadas por el IdNumber que representa el código único de la serie.

 

 

Para realizar este proceso de transformación de datos nos auxiliamos de la función Unpivot incorporada en SQL Server 2005, esta nos permite rotar columnas dentro de filas.

 

Insert Into DetailNumbers(IdSerie,Number)

Select IdSerie,Number

From dbo.Tabla_Excel

UNPIVOT(Number For Numbers IN([Number1],[Number2],[Number3],[Number4],[Number5],[Number6])) AS U

 

Con esta función tomamos las columnas correspondientes a los números que forman las series y son convertidas en filas para su inserción en la tabla DetailNumbers.  Para más información sobre el uso de esta función podemos acudir a los libros en Pantalla de SQL Server.

Actualmente  los datos han sido cargados de forma tal que nos permitirá implementar la solución del problema planteado.  La solución está basada en el uso del operador Cross Join, el cual es definido como un operador que enlaza  cada fila de la primera entrada con cada fila de la segunda entrada, en nuestro caso aplicaremos este operador creando una copia virtual de nuestra tabla de trabajo.  El siguiente grafico resume el uso de este operador:

  

Al igual que el primer número (30) es enlazado con cada uno de los valores de la tabla T2, sucede lo mismo con los siguientes valores de la serie. Los resultados de este primer cruce seria la  combinación:  (30,30),(30,27),(30,35),(30,38),(30,11),(30,26).

 

En términos de T-SQL se representaría de la siguiente forma:

 

Hasta el momento lo que tenemos es una consulta que nos da el total de todas las combinaciones posibles, obviamente esto no es lo solicitado pero es un buen punto de partida.  El siguiente paso será ir refinando nuestra consulta, como primer paso aplicaremos un filtro dentro de una condición, y es excluir del producto cartesiano a los números que sean iguales, partiendo del hecho de que ningún numero de la serie puede repetirse.  Al aplicar el filtro el resultado reducirá el número de combinaciones (de forma poco significativa), pero cumplimos con una de las condiciones planteadas.

 


Es posible observar un hecho interesante y es que al hacer esta autocombinación, hay productos que se repiten, por ejemplo al combinar el primer número de la serie (30)  se genera un producto (30,27), y al proceder a realizar la combinación del siguiente número (27) este generara un producto (27,30) habiendo una duplicidad, por lo que aplicamos otro filtro en la condición que nos permita solventar este inconveniente.

 

Lo siguiente que debemos hacer es aplicar un filtro para que el producto cartesiano se realice únicamente entre los números que pertenecen a una misma serie.

  

La fase final de nuestra consulta requiere la implementación de la instrucción clave “Group By”, hasta el momento habíamos obtenido las diferentes parejas de combinaciones numéricas por serie, pero al utilizar esta función nos permitirá la agrupación de los pares numéricos encontrados en la tabla DetailNumbers, además complementamos con el uso de la función Count y Distinct .  La consulta final se muestra a continuación:

SELECT COUNT(DISTINCT T1.IdSerie) AS Coincidencias,T1.Number AS Number1,T2.Number AS Number2

FROM DetailNumbers T1

CROSS JOIN

DetailNumbers T2

WHERE T1.Number<>T2.Number AND T1.Number<T2.Number AND T1.IdSerie=T2.IdSerie

GROUP BY T1.Number,T2.Number

ORDER BY Coincidencias DESC

 

 

Para verificar los resultados podemos ejecutar la siguiente consulta, en donde solo debemos introducir los valores que conforman el par numérico.

 

Select IdSerie From DetailNumbers

Where Number In (9,38)

group by IdSerie

having  Count(IdSerie)=2

 

La pantalla anterior muestra una pequeña muestra de las apariciones del par numérico (9,38), además de especificar el número de la serie en donde está presente, estos registros los podemos comparar contra el archivo original de Excel y así comprobamos la fiabilidad de los resultados. 

Bueno, ha sido un placer haber compartido este pequeño articulo con ustedes, y cualquier consulta no duden en escribirme.