September 2007 - Posts

Microsoft.VisualBasic.dll, ¿Eres tan malo como dicen?
Thursday, September 27, 2007 2:16 AM

Algunos años atrás, todo lo relacionado con Visual Basic (VB) 6.0 tendía a ser menospreciado o subvalorado. Los desarrolladores que utilizábamos VB 6.0 no éramos los primeros en levantar la mano para decir orgullosos que lo utilizábamos, como sí lo hacían los que usaban C o C++.

Una pequeña fracción de esa baja estima se mantuvo aún cuando apareció .net. Era cierto que teníamos un nuevo lenguaje (o un lenguaje muy remozado) que permitía lograr cosas impensadas en VB 6.0, tales como programar realmente orientado a objetos, crear threads, o deshacernos por fin de los problemas de los componentes marcados como Apartment, pero seguía existiendo "algo."

A mi entender, algunos de los problemas que NO ayudaron a la transición real de un lenguaje limitado a un lenguaje completo, se pueden desglosar en la siguiente lista:

• La existencia Option Explicit en vb.net. No existe programación seria sin declaración e inicialización de variables.

• La existencia de Option Strict en vb.net.

• Microsoft.VisualBasic.dll, librería para ayudar a la migración de proyectos, que implementa esas terribles funciones como Len, Left, Right, Trim, Mid, Replace y otras.

Respecto a este último punto, siempre que tenía la dicha de ver algún proyecto usándola, terminaba recomendado que no se use y que use las propiedades de los tipos de .net. (Ej: en vez de usar Len(variable) usar variable.length).

Ante la obvia pregunta de ¿por qué no debo usarla?, venía una respuesta que obtuve de variados lugares, pero nunca comprobé empíricamente, y que sostenía que tenía menor rendimiento que las nativas de los tipos.

A veces incluso utilizaba Reflector para justificar mi teoría, como muestro en la siguiente imagen. ¿Para qué usar Trim(variable) si al final lo que se ejecuta es variable.trim? Mejor hacerlo directamente.

En esta oportunidad he decidido hacer pruebas sobre las funciones más utilizadas de VB 6.0, pero que fueron reescritas en este ensamblado (Microsoft.VisualBasic.dll) y determinar bajo pruebas empíricas si son "tan" malas como aparentan.

Quiero hacer hincapié en algo muy importante. Las funciones antes mencionadas, implementadas en VB 6.0, y que califiqué como "terribles", justifican el calificativo asignado. Muchas de ellas, si es que no todas, reciben como parámetro de entrada un tipo de datos variant y retornan un tipo variant.

Esta conversión a variant penaliza el rendimiento de la aplicación, y peor aún, si se realiza en en ambos sentidos (entrada y salida). Siempre hablando en el contexto de VB 6.0, algunas funciones tenían variantes que terminaban en $, como Left$, y que tenían la gracia de retornar un tipo string, pero seguían recibiendo un variant. No entiendo que costaba hacer una que recibiese string y retornase string, pero ya está. Ya fue.

Por suerte, ahora en .net, las implementaciones en Microsoft.VisualBasic.dll incluyen parámetros correctamente tipificados, con la consecuente mejora en rendimiento.

Vamos a la prueba y a los sorprendentes resultados.

Código a ejecutar

Todas las funciones a medir fueron llamadas desde una única función general para cada lenguaje, llamadas ProcesarComoVB6 y ProcesarComoVBNET. No es mi intención medir y comparar el rendimiento de cada función específica sino que medir en general que sucede si se utilizan funciones de Microsoft.VisualBasic.dll o las nativas de los tipos en forma directa.

Cada función general recibe una cadena de 41.000 bytes y la procesa siguiendo la misma regla, detallada en el código de más abajo.

El siguiente es el código a ejecutar, utilizando Microsoft.VisualBasic.dll

El mismo código, utilizando las llamadas nativas de los tipo

La prueba comprende la ejecución una cantidad de 200 veces cada una de las funciones, midiendo los tiempos en terminar de procesar todas las instrucciones. Antes de iniciar las 200 iteraciones de cada una de las funciones, estas son llamadas un par de veces antes para JITtearlas (bonita palabra, no?)

Resultados de las pruebas

Ejecutando la prueba en un proyecto desarrollado en Visual Studio 2003 con el Framework 1.1, compilado en modo release, se obtienen los resultados de la siguiente tabla:

Ejecución N°	Microsoft.VisualBasic.dll	Métodos nativos
1	28,51 segundos	16,20 segundos
2	27,76 segundos	16,15 segundos
3	30,45 segundos	17,40 segundos

 

Wow...(no estoy haciendo propaganda a Vista ), impresionante. No nos engañemos todavía. Aún hay mucho que descubrir.

Analicemos antes de seguir las funciones que estamos midiendo y qué podría justificar la diferencia.

Tanto Left, Right como Mid debieran tener costos similares a la representación en VB.NET utilizando Substring. Técnicamente lo que hacen es obtener un grupo de caracteres de una cadena. No hay mucha ciencia.

Conversiones de caracteres a números y viceversa no son costosos, o no debieran serlo. Entonces, Convert.ToChar y Convert.ToInt32 debieran tener similar rendimiento a Asc y Chr respectivamente. Esto último no es necesariamente cierto para el resultado de la transformación. Los resultados de Asc y Chr pueden diferir de los de Convert.* de acuerdo a la cultura que se utilice.

¿Que nos queda?...Replace...el dolor de cabeza de un procesador....

Nuevas pruebas, sin Replace

El siguiente es el resultado de las pruebas removiendo el Replace del final, de ambas funciones. Los resultados son nuevamente sorprendentes.

Ejecución N°	Microsoft.VisualBasic.dll	Métodos nativos
1	7,59 segundos	7,46 segundos
2	9,20 segundos	8,73 segundos
3	8,98 segundos	8,07 segundos

 

Interesante, no? ¿Qué se puede concluir ahora?

Conclusiones aventuradas

Descartando la función Replace, como supusimos, no hay mucha diferencia en el tiempo entre utilizar el ensamblado Microsoft.VisualBasic.dll y las funciones nativas. Replace hace la diferencia. Veamos por qué.

Si se utiliza Reflector para ver el código de String.Replace se obtiene esto.

Si se utiliza Reflector para ver el código de Replace desde Microsoft.VisualBasic.dll, se obtiene esto, y pongan atención a la zona enmarcada en rojo. Ouch!! Eso afecta cualquier procesador.

Más aún, Strings.Split y String.Join son funciones implementadas en Microsoft.VisualBasic.dll y no las nativas del tipo string.

Consumo de recursos

Veamos ahora como anda el consumo de recursos y uso del procesador para cada ejecución. Para eso utilizaremos performance monitor y revisaremos contadores de CPU, proceso, excepciones en .net y memoria en .net.

Los contadores a medir son:

• Uso del procesador para el proceso, tanto para tiempo de proceso de usuario como de kernel

• Excepciones lanzadas por segundo

• Colecciones en cada generación

• Colecciones inducidas

• Total de bytes utilizados

• Bytes pedidos por segundo

• Tiempo utilizado por el GC

• Bytes privados del proceso

Grande fue mi sorpresa cuando los contadores de rendimiento de la memoria en .net, todos marcaban cero durante la ejecución. ¿El motivo? No se si es el motivo "oficial," pero al menos a mi me bastó como auto-explicación. El Framework 1.1 no corre de forma nativa en 64 bits ya que este sólo puede generar código ensamblado de 32 bits. Como mi sistema operativo es 64 bits, es posible que la instrumentación de 1.1 no funcione. Por lo tanto, a mover todo al Framework 2.0 y Visual Studio 2005.

Nota: Si te preguntas por que no hice todo el post utilizando 2.0, la respuesta es porque quería mostrar cual es el impacto de correr aplicaciones 32 bits sobre 64 bits emulando 32, tanto para el Framework 1.1 como 2.0. También haré la prueba con 2.0 compilado en 32 bits, que es lo que viene justo ahora.

Compilando para 32 bits (x86) una aplicación .net 2.0

En esta oportunidad no haremos mucho análisis. Me interesa dejar los tiempos como referencia y poder comparar en los diferentes ambientes.

Versión con Replace

Ejecución N°	Microsoft.VisualBasic.dll	Métodos nativos
1	23,32 segundos	25,15 segundos
2	23,29 segundos	25,16 segundos
3	23,28 segundos	25,46 segundos

Versión sin Replace

Ejecución N°	Microsoft.VisualBasic.dll	Métodos nativos
1	7,40 segundos	7,37 segundos
2	7,17 segundos	7,09 segundos
3	7,18 segundos	7,78 segundos

¿Conclusiones?...mmm, no gracias.. No tengo nada inteligente que aportar, como tampoco quiero hacer suposiciones. Full 64 bits por favor!!

Compilando para 64 bits (x64) una aplicación .net 2.0

Veamos los resultados de la misma prueba compilado para 64, o mejor dicho, compilado para cualquier CPU. Total, una vez que se JITtee (bonita palabra nuevamente, no?), lo hará para el procesador en que se está ejecutando.

Versión con Replace

Ejecución N°	Microsoft.VisualBasic.dll	Métodos nativos
1	14,07 segundos	19,35 segundos
2	13,98 segundos	18,79 segundos
3	14,25 segundos	19,12 segundos

Versión sin Replace

Ejecución N°	Microsoft.VisualBasic.dll	Métodos nativos
1	5,86 segundos	5,96 segundos
2	5,93 segundos	5,68 segundos
3	5,79 segundos	5,75 segundos

¿¿¿¿Qué????

Parece que el equipo de desarrollo de VB.NET se tomó en serio las críticas y decidieron mejorar el producto.

Veamos con reflector el código de Replace de Microsoft.VisualBasic.dll, para la versión 2.0 del Framework.

Interesante. Cambiaron esa idea de hacer splits y joins e implementaron una función con ese fin específico. ReplaceInternal utiliza un Stringbuilder para cumplir su labor. Bien.

Volvamos ahora que tenemos contadores al análisis del consumo de recursos.

Consumo de recursos, V 2.0

Analicemos la utilización de recursos para ambas versiones, con y sin Replace. En todos los contadores colocaré el valor por defecto, que en algunos casos es el promedio y otros el máximo.

Versión con Replace

Contador	Microsoft.VisualBasic.dll	Métodos nativos
# Exceptions Thrown/sec	0	0
# Gen 0 Collections	23.236	15.134
# Gen 1 Collections	2	61
# Gen 2 Collections	0	12
# Induced GC	0	0
# Total Commited Bytes	1,3 MB	3 MB
% Time en GC	7,1 %	2,8 %
Allocated bytes/sec	1,1 GB (así es)	512 MB
% User Time	89 %	85 %
% Privileged Time	3 %	12 %
Private Bytes	23 MB	24,8 MB

 

Mini conclusiones versión con Replace

La utilización de métodos nativos consume menos recursos en general. Hay una reducción importante en las recolecciones de primera generación (gen 0), pero las recolecciones en esa generación son muy económicas, al igual que las de la generación 1. Las de generación 2 son caras y hay que evitarlas, aunque 12 es un número pequeño.

Lo anterior se traduce en que la versión con métodos nativos consume menos procesador recolectando "basura," y genera mucho menos "basura" (Allocated bytes/sec).

En la versión con métodos nativos, podría existir una relación entre la cantidad de bytes commited y la cantidad de recolecciones de segunda y tercera generación (gen 1 y gen 2) ya que hay memoria que se mantiene ocupada más tiempo y no es liberada en recolecciones de primera generación. Esto es solo una suposición.

Versión sin Replace

Contador	Microsoft.VisualBasic.dll	Métodos nativos
# Exceptions Thrown/sec	0	0
# Gen 0 Collections	14.305	12.654
# Gen 1 Collections	0	52
# Gen 2 Collections	0	10
# Induced GC	0	0
# Total Commited Bytes	1,3 MB	3 MB
% Time en GC	9,3 %	8,2 %
Allocated bytes/sec	2,0 GB	1,5 GB
% User Time	93 %	94 %
% Privileged Time	4 %	4 %
Private Bytes	23,5 MB	25,9 MB

 

Mini conclusiones versión sin Replace

El consumo de recursos es equivalente. Llama la atención el aumento en ambas versiones de la cantidad de bytes pedidos por segundo (allocated bytes/sec).

El resto de los contadores no presenta diferencias importantes entre ambas funciones para la versión sin Replace.

Conclusiones

Las siguientes conclusiones puedo obtener del análisis.

1.- No correr aplicaciones 1.1 en 64 bits

2.- No correr aplicaciones compiladas para x86 en 64 bits

Tristemente, mi intención inicial de encontrar algún motivo para que no se utilice Microsoft.VisualBasic.dll en los proyectos no ha podido ser cumplida.

En el único escenario donde encarecidamente recomendaría no usarla es en aplicaciones 1.1, pero en aplicaciones 2.0, la diferencia es irrelevante.

Saludos,
Patrick. 

by pmackay | 7 comment(s)

Filed under: Memoria y GC, .net, Casos

¿Cómo reducir el costo de un LookUp de SQL Server?
Sunday, September 09, 2007 6:53 AM

Durante la exposición de nuestra charla de como optimizar consultas de SQL Server, hice bastante hincapié en la relación/dependencia de los índices no agrupados (no clustered) y el agrupado (clustered) de un tabla. En caso de que ésta no cuente con un índice agrupado, existe una relación/dependencia similar pero con una estructura interna de la tabla (RID).

En este post veremos qué es un LookUp (SQL 2005) o Bookmark LookUp (SQL 2000) y cómo eliminarlos o al menos mitigarlos cuando se pueda. Usaré SQL 20005, pero si tienes 2000, la manera de enfrentarlo es similar.

Antes de ir a la información del caso, haremos un pequeño repaso de lo revisado en la charlas de optimización de consultas.

Relación/dependencia entre índices en una tabla

Un índice no agrupado almacena, por cada elemento del índice, un "identificador" que dice donde encontrar en la tabla los datos asociados a este ítem del índice. De esta forma, cada vez que SQL Server utiliza el índice no agrupado y determina que debe ir a buscar los datos, utiliza ese "identificador" para ubicar los datos en la tabla.

Si la tabla no tiene un índice agrupado, el identificador utilizado es el identificador de la fila o row id (en inglés), el cual se representa con las siglas RID. Este RID está compuesto por el numero del archivo de la base de datos, el número de la página dentro de ese archivo y por último, el número de la fila en la página.

Si la tabla sí tiene un índice agrupado, el identificador utilizado es la llave del índice agrupado. La llave del índice agrupado son TODAS las columnas del índice agrupado. Por favor, no confundir la llave del índice con la llave primaria de una tabla, aunque muchas veces tienen el mismo "valor," en especial cuando la tabla se creó sin darle mayor importancia a este tema.

Para más información, revisa el BOL (Books On Line) de SQL Server, en la instrucción Create Index.

Bookmark LookUp o LookUp a secas

En SQL Server 2000 se le conoce como Bookmark LookUp. En la versión 2005 se llama solamente LookUp. La idea es la misma para ambas versiones; El problema es el mismo, las soluciones similares.

LookUp, traducido al español, significa operación de búsqueda, y representa exactamente eso. Realiza una operación de búsqueda, con un "identificador" de una fila, para traer más información, de la fila específica, desde la tabla.

La búsqueda se hace, como ustedes se imaginan, utilizando el RID si la tabla no tiene un índice agrupado, o utilizando la llave del índice agrupado, si la tabla cuenta con uno de ese tipo. La siguiente imagen sirve para aclarar el concepto que se trata de explicar.

Utilizando el índice no agrupado, primero se identifica las filas que cumplen con la condición de filtrado. Resultado: Celdas amarillas.     Se usan los tres identificadores de filas (rojo, verde y azul) y se realiza la operación de búsqueda en el índice agrupado. Si la tabla no tiene índice agrupado, la operación es similar, pero usa el RID. Se retornan los datos faltantes.

Veamos entonces ahora información del caso. En esta oportunidad reproduciré el escenario en una base de datos de prueba, con unas tablas creadas para la ocasión.

LookUp muy costoso

Nota: Hace un tiempo atrás estaba en un curso de optimización de SQL Server y le hice la siguiente pregunta a la instructora: "Si tengo el plan de ejecución de una consulta, y el operador de lookup es responsable de casi el 100% del costo del plan, ¿cómo puedo mejorar el rendimiento de mi consulta?."

Tristemente, no hubo una respuesta aclaratoria ante mi pregunta, como ocurrió en varias otras además. Hoy en día, retrospectivamente, pienso que la instructora era lo que nosotros acá en Chile llamamos "un Adán", de Adán y Eva. ¿Por qué?. Porque si a Adán le sacas la hojita, queda desnudo. En este caso, la "hojita" es el "libro del curso."

Si has estado tratando de optimizar consultas de SQL, es probable que ya te hayas topado con este problema. ¿Qué hacer cuando la operación LookUp es muy costosa?

Antes de comenzar, estructura de tablas

Para este ejemplo, utilizaremos dos tablas, t1 y t2, con la siguiente estructura cada una

CREATE TABLE [dbo].[t1](     [c1] [int] IDENTITY(1,1) NOT NULL,     [c2] [varchar](50) NOT NULL,     CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED ([c1] ASC) ON [PRIMARY]) ON [PRIMARY] CREATE TABLE [dbo].[t2](     [c1] [int] IDENTITY(1,1) NOT NULL,     [c2] [int] NOT NULL,     [c3] [varchar](50) NOT NULL,     [c4] [tinyint] NOT NULL CONSTRAINT [DF_t5_c4] DEFAULT ((1)),     [c5] [char](200) NOT NULL CONSTRAINT [DF_t2_c5] DEFAULT (''),     CONSTRAINT [PK_t2_1] PRIMARY KEY CLUSTERED ([c1] ASC) ON [PRIMARY]) ON [PRIMARY] CREATE NONCLUSTERED INDEX [idx_c2] ON [dbo].[t2] ([c2] ASC) ON [PRIMARY] ALTER TABLE [dbo].[t2] WITH CHECK ADD CONSTRAINT [FK_t2_t1] FOREIGN KEY([c2]) REFERENCES [dbo].[t1] ([c1])

La tabla t1 tiene 100.000 registros. La tabla t2 tiene 1.000.000 registros. La consulta que estamos realizando es la siguiente.

select t1.c1, t1.c2, t2.c1, t2.c3, t2.c4 from t2 inner join t1 on t2.c2 = t1.c1 where t2.c2 between 1000 and 2000 and t2.c3 like '2%' and t2.c4 = 1

Con esta consulta, estamos trayendo un cantidad importante de columnas de la tabla t2, como también las dos columnas de la tabla t1. Existe un índice en t2.c2, por lo tanto, el filtro between 1000 and 2000 se realizará utilizando el índice para reducir las filas.

El siguiente es el plan de ejecución generado, en versión texto y gráfico.

Table 't2'. Scan count 1, logical reads 30682, physical reads 0, ... Table 't1'. Scan count 1, logical reads 6, physical reads 0, ... Rows Executes StmtText ------------------------------------------------------------------------------------------- 561 1 |--Merge Join(Inner Join, MERGE:([test].[dbo].[t1].[c1])=([test].[dbo].[t 1001 1 |--Clustered Index Seek(OBJECT:([test].[dbo].[t1].[PK_t1]), SEEK:([ 561 1 |--Filter(WHERE:([test].[dbo].[t2].[c3] like '2%' AND [test].[dbo]. 10010 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[t2 10010 1 |--Index Seek(OBJECT:([test].[dbo].[t2].[idx_c2]), SEEK:( 10010 10010 |--Clustered Index Seek(OBJE...]) LOOKUP ORDERED FORWARD)

Si miramos la versión gráfica, podremos observar que el 100% del costo del plan esta en la operación lookup. Notar que hemos marcado algunos textos en blanco arriba, los cuales revisaremos más adelante. Para ver el plan gráfico más grande, haz clic sobre él.

En el plan gráfico, podemos ver que la operación Key Lookup es responsable del 100% del costo de plan, con un costo de 22,06 (muy malo para un operador, y en consecuencia para el plan). Complementando con el plan de texto, en éste aparece la palabra LOOKUP, y está usando el índice agrupado (clustered) para recuperar las demás columnas que necesita.

Además, se realizan más de 30.000 lecturas sobre la tabla t2. 

¿Donde está el problema?¿Por qué tiene un costo tan elevado?

Una única operación de LookUp es de bajísimo costo, más aún si utiliza un índice para hacer un seek a una fila específica en la tabla. Sin embargo, si se ejecuta 10.010 veces, la sumatoria de estos costos pequeñísimos pasa a tener relevancia, como es nuestro caso.

La cantidad de veces que se ejecuta la operación LookUp se puede ver en 2 partes. Una de ellas es el texto marcado con blanco en el plan de ejecución de texto y la otra es en el Tooltip amarillo, en "Actual Number of Rows."

Solución[es] al problema

Existen dos formas de enfrentar este problema, y por supuesto, dependen de cada escenario.

Una de ellas, y que no veremos ahora porque no tienen ninguna gracia, es agregar todas las columnas necesarias al índice, y con eso, evitar la operación LookUp. Si tengo toda la información en el índice, no necesito ir a buscar más columnas a la tabla. Esta no falla nunca, pero uno debe evitar incluir muchas columnas en los índices.

La segunda solución, y que es mucho más elegante, sólo es posible realizar en un escenario como el que tenemos ahora.

Si miran con detenimiento el plan de ejecución, el operador filter está filtrando en las columna c3 y c4, aplicando los criterios definidos en la consulta SQL. Esto es esperable. Más aún, como resultado del filtrado, la cantidad de filas retornadas disminuye (anchos de las flechas de entrada y de salida del filter). Esto también es esperable, ¿o no?

Si todo es esperable, ¿dónde está la gracia?

El costo del operador LookUp es muy alto porque se ejecuta muchas veces, ¿verdad?. ¿Qué sucedería si logro reducir la cantidad de veces que deba ejecutarse? Seguramente el costo de la consulta será menor y el rendimiento de ésta mucho mayor.

Para reducir la cantidad de veces que se ejecuta, debo reducir la cantidad de filas que son retornadas luego de que se filtre utilizando el índice idx_c2. Para reducir la cantidad de filas retornadas, agrego una columna más al índice, la que más me ayude a filtrar.

Debido a que la selectividad de la columna c3 es mejor que la de c4, incluiré sólo la columna c3 en el índice idx_c2. Con eso, reduciré la cantidad de filas que requerirán un lookup de más información. Recuerden que estamos tratando de minimizar el costo del LookUp. Si se quiere suprimir totalmente, se deberán agregar todas las columnas necesarias al índice, solución que deberá dejarse sólo para casos ultra-extremos.

Veamos el plan de ejecución de texto de la misma consulta, pero con el índice idx_c2 incluyendo a la columna c3 además de la c2 que ya tenía.

Table 't2'. Scan count 1, logical reads 3332, physical reads 0, ... Table 't1'. Scan count 1, logical reads 6, physical reads 0, ... Rows Executes StmtText ------------------------------------------------------------------------------------------- 561 1 |--Merge Join(Inner Join, MERGE:([test].[dbo].[t1].[c1])=([test].[dbo].[t 1001 1 |--Clustered Index Seek(OBJECT:([test].[dbo].[t1].[PK_t1]), SEEK:([ 561 1 |--Filter(WHERE:([test].[dbo].[t2].[c4] = (1))) 1075 1 |--Nested Loops(Inner Join, OUTER REFERENCES:([test].[dbo].[t2 1075 1 |--Index Seek(OBJECT:([test].[dbo].[t2].[idx_c2]), SEEK:( 1075 1075 |--Clustered Index Seek(OBJE...]) LOOKUP ORDERED FORWARD)

 

Ahora sólo ejecutó 1.075 veces el LookUp. No nos hemos deshecho de él, pero al menos hemos reducido su impacto en el costo de la consulta.   Las lecturas lógicas fueron reducidas desde más de 30.000 a sólo 3.332. Una excelente optimización. El nuevo costo del operador LookUp es 3,58, como se puede observar a la derecha en el Tooltip. El costo antes de modificar el índice era de 22,06. Otra excelente reducción en el costo.

Conclusión

Hemos visto como minimizar el impacto de la operación LookUp en SQL Server. No vimos como eliminarlo ya que para lograr eso, el camino es directo y simple. A veces no es necesario eliminarlo para mejorar drásticamente el rendimiento de una consulta.

 

Hasta la próxima, desde Santiago de Chile
Patrick. 

by pmackay | 3 comment(s)

Filed under: SQL Server