Leyendo el hace unos días unos posts muy recomendables del Sql Server Programmability & API Development Team Blog acerca de cómo funciona caché de planes de consultas y procedimientos de SqlServer 2005, me enteré de una buena práctica para nuestro código .Net que accede a datos de SqlServer. Aunque encuentro lo descubierto más interesante para evitar una mala práctica que, por despiste, seguro que hacemos más de muchos. Y como veremos… me parece que algún chico de Redmond también.
Entremos en vereda…
Cuando ejecutamos una consulta contra SqlServer se emplea un tiempo en crear un plan de ejecución. Ese plan, relativamente costoso de crear, lo almacena en un caché de planes de ejecución. La reutilización de dicho plan mejora considerablemente el rendimiento de SqlServer al ahorrarnos un tiempo precioso, y por ende, también mejora el tiempo de respuesta de nuestra aplicación.Todos estamos más o menos aleccionados a utilizar parámetros en nuestras consultas SQL, sobre todo por razones de seguridad ya que filtra lo pasado a la consulta, evitando ataques por inyección de código SQL. Las consultas parametrizadas son planificadas con sus parámetros, así pueden ser reutilizadas aunque cambie el valor de los parámetros.Una cosa que me sorprendió es que SqlServer autoparametriza las consultas que planifica en previsión de que sean reutilizadas:
SqlCommand command = new SqlCommand(conn);
command.CommandText = «select * from t1 where col1 = 1 and col2 = ‘abcd'»;
command.ExecuteNonQuery();
command.CommandText = «select * from t1 where col1 = 1 and col2 = ‘abc'»;
command.ExecuteNonQuery();
Genera un único plan de ejecución para la consulta:
(@1 tinyint,@2 varchar(8000))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2
Es bastante común ejecutar un SqlCommand definiendo los valores de los parámetros y no especificar su tipo, especialmente si utilizamos AddWithValue que ya no nos da opción a definir el tipo del parámetro, muchos desarrolladores lo utilizan porque ahorras una llamada a un método.
Es típico ver esta porción de código:
SqlCommand command = new SqlCommand(«select * from t1 where col1 = @id and col2 = @str», conn);
command.Parameters.AddWithValue(«@id», 1);
command.Parameters.AddWithValue(«@str», «abc»);
command.ExecuteNonQuery();command.Parameters[0].Value = 2;
command.Parameters[1].Value = «abcd»;
command.ExecuteNonQuery();
Examinando el cache de planes de ejecución obtenemos:
(@1 tinyint,@2 nvarchar(3))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2
(@1 tinyint,@2 nvarchar(4))SELECT * FROM [t1] WHERE [col1]=@1 AND [col2]=@2
Uppps, son dos planes de ejecución distintos. No ha reutilizado la consulta. Se ha perdido un tiempo valioso. Se da la paradoja que la consulta sin parámetros sería más eficaz que la parametrizada en este segundo ejemplo. ¿Por qué ocurre esto? Pues al parecer porque no se ha especificado la longitud del parámetro alfanúmerico y SqlServer lo crea como un nvarchar de la longitud del valor pasado. Tranquilos, esto no ocurre con tipos fijos como int o datetime, pero si en nuestra consulta tenemos un parámetro que es un string, evitad utilizar AddWithValue.
Este otro fragmento de código si sería más correcto:
command.CommandText = «select * from t1 where col1 = @id and col2 = @str»;
command.Parameters.Add(«@id», SqlDbType.Int);
command.Parameters.Add(«@str», SqlDbType.NVarChar, 50);
command.Parameters[0].Value = 1;
command.Parameters[1].Value = «abc»;
command.ExecuteNonQuery();command.Parameters[0].Value = 2;
command.Parameters[1].Value = «abcd»;
command.ExecuteNonQuery();
Produciendo un único plan de ejecución:(@id int,@str nvarchar(50))select * from t1 where col1 = @id and col2 = @str
¿Os imagináis que pasaría si está dentro de un bucle que va insertar masivamente valores en nuestra base de datos? Pues que el caché de planes de ejecución se llenaría de consultas preparadas para cada combinación de parámetros alfanuméricos, sacando otras consultas preparadas (planificadas) del caché de planes de ejecución, además de incurrir en un tiempo extra en planificarla.
Lo curioso es que ayer mismo, utilizando un plan de generación de datos en VisualStudio 2005 for Database Professionals, descubrí un pequeño detalle al respecto mientras investigaba otra cosa… estaba insertando 524288 tuplas en una tabla de pruebas en la que tenía dos campos con NVarChar (psche… lo hago todos los días antes de desayunar) cuando paralelamente ejecutó una consulta Transact-sql en Sql Server Studio Management Express sobre la tabla del sistema con los planes de ejecución cacheados y me encuentro con esto:
Cómo podéis observar en la imagen, ha creado un plan de ejecución por consulta, debido al problema que antes os mencionaba. Probé a hacer un count(*) en la tabla del caché de planes de ejecución y contenía 8192 tuplas (oh, curioso límite) con planes de ejecución de esta operación de generación de datos. El impacto de este problema no es excesivamente grave pues los planes de generación de datos se suelen ejecutar en instalaciones de SQLServer en entornos de desarrollo y no en producción, por lo que no parece importante que afecte al rendimiento de otras consultas.Desde mi punto de vista el problema se encuentra en una mala implementación de AddWithValue, el desarrollador no puede estar pendiente de si un método aparentemente cómodo y eficiente no lo sea, esa es la confianza que depositamos en el .Net Framework. Quizás sería recomendable que en futuros frameworks se reescribiera este método sobre todo para que intercepte los tipos de datos alfanuméricos y le ponga un tamaño por defecto al igual que hace SqlServer en las consultas auto parametrizadas. También sería recomendable una sobrecarga de AddWithValue que permita especificar el tipo de dato explícitamente.
Wow, no tenía ni idea, utilizo AddWithValue en alguna web así que muchísimas gracias por el aviso.
Esto va a favoritos.
Excelente la advertencia Edu, no uso el AddWithValue (ni sabia que existía 🙁 ) pero vamos que ya ni se me pasará por la cabeza usarlo.
Una sola puntualización: El método Add no estará disponible en ADO.NET vNext y el compilador muestra un aviso cuando se utiliza. Viendo el poco impacto de rendimiento que tiene, yo no recomendaría utilizar Add, sino AddwithValue
Entonces en qué quedamos, se utiliza o no??
Pues… yo soy de la opinión de no uilizar AddWithValue, es mejor utilizar add(), por razones de rendimiento, que quedan bién aclaradas en este artículo.
Aclarando; el método add de una colección no puede desaparecer de la misma, a menos que parámeters dejara de ser una colección dentro de la clase SqlCommand, así que no veo que vaya a desaparecer, sin embargo, lo que sí cambió fue la sobre carga con la firma: Parameters.Add(string,object), esta sobrecarga que agregaba el parámetro con un valor ha sido obsoletada y se ha sustituido por AddWithValue, sin embargo, las sobrecargas que muestra el ejemplo de esta publicación son válidos y no veo dónde se diga que serán removidas en la documentación. Así que si hay alguna referencia sería bueno ponerla.
En mi opinión es mejor definer explicitamente el tipo del parámetro, ya que dejar al framework la insinuación, especulación o adivinación del tipo para convertir el valor siempre acarreará un gasto en rendimiento. Recuerden, no hacémos más software escribiendo menos código, sino escribiendo bien el código.
Saludos…
Octavio Telis
Muy buena información no tenia idea sobre este asunto, me date unas vueltas por la pagina seguro encuentro mas articulos de este tipo, saludos