none
SqlParameterCollection.AddWithValue Method RRS feed

  • Debate general

  • Hola,

    La verdad que llevo unos días un poco desconectado del foro y lo primero es sorry, pero ando con un tema como se dice hasta las cejas:)

    Pongo ese titulo puesto que lo he visto en una revista concretamente en http://www.dnmplus.net/ y sobre todo las fuentes me parecen de lo más fiable Alberto Población y Eduardo Quintas.

    Bueno vamos al grano acabo de realizar una busqueda en el foro con AddWithValue y las entradas son 3206 casi nada y es más casi seguro que en alguna de ellas me encuentro presente recomendandolo.

    No quiero más protagonismo y lo interesante es que leáis el artículo de Eduardo Quintas, los que no tengais acceso a la revista algo que recomiendo a todo el mundo, es decir una suscripción a dotnetmania.

    Una cosa importante se que cuesta más a la hora de responder pero me gustaría que todos los que respondemos evitasemos el uso de AddWithValue con string:)

    Saludos,


    phurtado

    lunes, 5 de marzo de 2012 20:58
    Moderador

Todas las respuestas

  • Hola,

    Pues yo incurria en el Error :S .... y como dice el autor Eduardo Quintas en su entrada, eso hace parte de la confianza que hemos depositado en NET Framework, por eso NUNCA esta demas comprender que hacen por detras todos estos metodos y MAS aun cuando de acceder a datos se tarta.

    Nuevamente gracias Pedro :)

    Saludos.


    Nicolás Herrera
    Bogotá - Colombia
    BLOG - Leader Group BogotaDotNet
    "Daría todo lo que sé, por la mitad de lo que ignoro." Rene Descartes


    • Editado Nicoloco lunes, 5 de marzo de 2012 21:36
    lunes, 5 de marzo de 2012 21:35
  • el articulo que mencionas en el link no lo encontre, asi que no podria apinar acerca de lo que alli comenta

    lo que si puedo decirte que uso el AddWithValue() y por mas que tenga algun que otro problema no veo porque no seguir usandolo

    es sabido que dejar que el metodo deduzca por nosotros el tipo de datos implica algo de perdidad de algunos nanosegundos del procesador, pero tampoco lo veo para tanto

    yo he usado AddWithValue() desde que nacio ado.net y no he tenido ni siquiera un solo problema, ni de performance,  no se que dira el articulo pero no veo porque no usarlo, me remito solo a la experiencia de uso, si algo funciona se usa

    saludos


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    lunes, 5 de marzo de 2012 23:39
  • El asunto es que utilizando el addwithvalue cada vez crea un plan de ejecucion a ala hora de ejecutar una instruccion y esto es costoso ya que por cada instruccion genera una plan de ejecucion, mientras con add esto ya queda en cache y no genera plan de ejecucion  , vuelve a reutilizar el que ya tiene,los tiempos de repuesta mejoran , claro esta diferencia se mira cuando se realiza inserciones masiva de datos. tiene  sentido  lo del articulo, de igual manera opino en no utilizar addwithvalue.

    dejo el articulo completo para aquellos que tienen problema para entrar, estos temas son interesantes ya que uno nunca deja de aprender.

    Parameters.AddWithValue ... with Cuidadito

    Publicado 13/7/2007
    11:00
    por Eduardo Quintás

    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 SqlServerse 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 SqlServeral 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 SqlServerlo
    crea como un nvarcharde la longitud
    del valor pasado. Tranquilos, esto no
    ocurre con tipos fijos como into 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:


    Pantallazo de una consulta ...

    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 8192tuplas
    (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 SQLServeren 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 frameworksse 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 SqlServeren las consultas auto
    parametrizadas. También sería recomendable una sobrecarga de AddWithValueque permita especificar el
    tipo de dato explícitamente.

    Capacitaciones Corporativas
    Si pequeña es la Patria, uno grande la sueña
    Rubén Darío
    Principe de las letras Castellanas
    Poeta Nicaragüense
    Ay Nicaragua, Nicaraguita Video Clip

    martes, 6 de marzo de 2012 4:29
  • Hola :)

    Me encuentro interesante el articulo y claroo, no sabia que un AddWithValue cuando tienes campos string sin especificar el tipo realiza toda esta historia en el servidor que al final es donde se ve el performance, que no se nota como comenta @Eduardo Quintas cuando se esta hablando de uno o dos registros, pero que cuando tenemos que meter una Catelba como decimos en mi pais de registros ufff, de verdad que carga el servidor a un extremo capaz de reventarlo :).

    Muy interesante el articulo puesto que como comenta @Nicolas y el mismo @Eduardo en el articulo, hemos depositado nuestra confianza en el .NET Framework a tal punto de que, si nos facilitan la vida por que no confiar en las personas que lo inventaron Microsoft, que se supone que van a hacer los Test de lugar, y esto es de por alla cuando salio Ado.Net quien hiba a ponerse a pensar la historia que monta en el servidor el AddWithValue este, que en realidad lo he usado pocas veces por que me gusta trabajar bien explicito en esto de Ado.Net y por eso siempre utilizo el Add().

    Otra cosa para las personas que vean el tema y no tengan ninguna idea de lo que se habla, a continuacion dejo varios link de consulta para las personas que se inician en Ado.Net entiendan que es AddWithValue

    SqlParameterCollection

    AddWithValue

    y como siempre, este linq lo he recomendado a mas de 50 personas, no se puede quedar una charla magistral diria yo de Acceso a Datos que dio nuestro compañero Omar del Valle, que para mi sin lugar a dudas una de las mejores charlas que he tomado sobre Acceso a Datos con Ado.Net

    Pilares de Ado.NET

    Saludos :), y

    Parameters.AddWithValue ... with Cuidadito ;)


    Luis Y. Ramirez "Recuerda marcar la repuesta como VALIDA si te ha ayudado"

    martes, 6 de marzo de 2012 13:02
  • ahh ahora que leo el articulo entiendo a donde apuntaba

    bueno igualmente sigo pensando que porque algo no tenga una perfecta implementacion se lo deja de usar, porque si fuera asi entity framework en las primeras versiones nadie lo hubiera usado y eso no fue asi

    tenga una excelente implementacion o no se solo usa igual (es mas ahora esta de moda desarrolar con prouctos en beta, asi que imaginense), pero es bueno conocer estos aspectos por si se llega dar la situacion

    igualmente le falto analizar que sucede con el AddWithValue() cuando se invoca a un stored procedure, porque si alguien va a procesar miles de registros seria descabellado no usar un procedure, para consultas simples si es valido una query en linea en el codigo, pero para procesar miles de registros es casi obligatorio contar con un stored, bueno al menos eso es lo que pienso

    quizas sea por eso que use el AddWithValue y nunca he tenido problemas porque el plan de ejecucion lo define el stored procedure y no me afecta como se le pasen los parametros, la verdad nunca lo analice

    saludos


    Leandro Tuttini

    Blog
    Buenos Aires
    Argentina

    martes, 6 de marzo de 2012 13:24
  • leyendo el articulo y viendo la version de VS con la que se hizo la prueba, alguien de ustedes se tomo la molestia de probarlo en VS2010 o VS2008?

    digo!!... el articulo es interesante no dire que no, pero!!!!... haciendo pruebas y pensando... quien escribiria un programa para que este subiendo información masivamente?, si contamos quienes hacen eso, nos sobraria 1 mano y dedos de la otra.

    yo ya hice mis pruebas, controladas, y solo me creo 5 planes por cada 900 registros insertados, y mis conclusiones son:

    Si tienes un programa que suba información masiva a la base de datos (que lo dudo) no uses el metodo AddWithValue(), mejor usa el metodo Add() indicando el Lenght de texto ya que en este esta la clave para que no se creen planes por diferentes tamaños del texto.

    pero si es un sistema cuya información es digitada por el usuario, como un sistema de RRHH, nomina, facturación, cxc, cxp que su uso no es para subir información maxiva a la base de datos, el de facturacion pueda que si, pero generalmente se sube el codigo como string con un length de 20, los demas son int o monetarios que en estos no hay problema. bien se puede seguir usando AddWithValue().

    pero!!!!... ya queda a criterio de cada quien dependiendo del programa a escribir. ademas... hagan sus pruebas para que saquen conclusiones.

    gracias por la información Pedro, muy buena.

    Salu2,


    Marvin E. Pineda

      ComboBoxMultiColumns

     NetBarControl

      TextEditor

    martes, 6 de marzo de 2012 14:45
    Moderador
  • Hola,

    Desde el principio comente que no quería protagonismo y la idea no era otra que ayudar a la comunidad a tener claro una serie de problemas que se pueden derivar del uso de esta instrucción.

    Lo primero es que no tiene que ver con inserciones masivas ni con la versión de visual estudio, es simplemente un problema de como gestiona Sql Server la cache de procedimientos y pensemos un poco, si yo quiero cachear algo lo primero que haré sera comprobar que todo es igual y con el uso de AddWithValue para un string todo no es igual, con lo cual el problema es ese.

    Mirad que sencillo una app que tiene miles de usuarios en internet haciendo busquedas  y el uso de una app sencilla busca en una lista de productos para comprar en una tienda online, claro yo busco por "Hola" otro por "Hola y Medio" y el otro por "Hola y tres cuartos", vamos a multiplicar esto por miles de usuarios y nuestra cache de procedimientos crecerá por culpa de AddWithValue, vamos con un ejemplo.

    --elimina la cache de procedimientos
    dbcc freeproccache
    
    select * from Errores
    where Nombre = 'Hola'
    go
    select * from Errores
    where Nombre = 'Hola y medio'
    go
    select * from Errores
    where Nombre = 'Hola y tres cuartos'
    go
    select *
    from master..syscacheobjects
    where dbid = 9 
    -- este es id de la base de datos que podemos obtener de 
    --select * from sys.databases


    Vamos con la simulación no AddWithValue

    dbcc freeproccache
    
    declare @nombre varchar(50)
    set @nombre = 'Hola'
    select * from Errores
    where Nombre = @nombre
    go
    declare @nombre varchar(50)
    set @nombre = 'Hola y medio'
    select * from Errores
    where Nombre = @nombre
    go
    declare @nombre varchar(50)
    set @nombre = 'Hola y tres cuartos'
    select * from Errores
    where Nombre = @nombre
    
    select sql
    from master..syscacheobjects
    where dbid = 9 
    -- este es id de la base de datos que podemos obtener de 
    --select * from sys.databases


    Bueno Leandro, vamos contigo.

    1. Comentas esto 

    yo he usado AddWithValue() desde que nacio ado.net y no he tenido ni siquiera un solo problema, ni de performance,  no se que dira el articulo pero no veo porque no usarlo, me remito solo a la experiencia de uso, si algo funciona se usa 

    Pues veo que no naciste con Ado.Net en la versión 1.0 y 1.1 del Framework no existía y gracias porque otros optamos por caminos desde entonces y eso nos evito este tipo de cosas.

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection_members(v=vs.71).aspx 

    es sabido que dejar que el metodo deduzca por nosotros el tipo de datos implica algo de perdidad de algunos nanosegundos del procesador, pero tampoco lo veo para tanto 

    No veo en la perdida de esos nanosegundos el problema sino en la compilación de cada sql. 

    Y logicamente lo mejor es aportar una solución si es que queremos tener un método ágil que nos permita agregar un parámetro sin tener que utilizar este http://msdn.microsoft.com/en-us/library/40959t6x.aspx que es el correcto, eso sí nos toca escribir más. Y si pensamos que a partir de 3.5 existe los extension methods y con ellos podemos crearnos un metodo extensor donde podamos pasar el nombre del parametro longitud y valor lo mismo con tres lineas de código conseguimos que todos los compañeros del foro utilicen algo eficiente y que nosotros lo recomendemos o es mejor recomendar algo ineficiente?

    public static class ExtensionSqlParameter
        {
            public static void AddStringWithValue(this SqlParameterCollection Collection, string Name, int Size, string Value)
            {
                SqlParameter parameter = new SqlParameter(Name, SqlDbType.VarChar, Size);
                parameter.Value = Value;
                Collection.Add(parameter);
            }
        }

    y ademas nos queda bonito

    SqlCommand cmd = new SqlCommand();
    cmd.Parameters.AddStringWithValue("@p1", 10, "Hola");

    Y al igual que esto escribir 4 o cinco métodos una vez y que nos permita controlar NVarchar,etc,etc y Null que tambien es otro problema y no pequeño:)

    Leandro, de verdad recapacita con lo que has dicho en este hilo que es para pensarlo:) 

    Saludos,


    phurtado

    martes, 6 de marzo de 2012 16:32
    Moderador
  • :-)
    Hola Pedro,

    Siento no pasarme más a menudo por aquí pero voy hasta las cejas de trabajo... :-S

    En primer lugar, muy buen análisis. Aunque cualquiera que se haya pasado 10 minutos mirando los planes de ejecución de SQL Server debería darse cuenta del grave error que es usar AddWithValue. O tal vez ni eso, porque si buscas en google te salen más de un 75% de resultados (hasta un 90%) advirtiéndote de posibles problemas derivados de su uso.

    Realmente es tan grave? Depende del tipo de aplicación:

    En una aplicación de escritorio con poco volumen de actualizaciones apenas es relevante. Sin embargo en una aplicación web, esos nanosegundos que alguien ha comentado son lo que se llama escalabilidad, y es el factor responsable de que una aplicación tenga éxito o se arrastre como una babosa hasta morir.

    Otra cosa es que nosotros (como responsables del foro y de buenas prácticas) minimicemos el daño de estas malas prácticas. Por favor! Hay que abandonarde una vez por todas el "bueno vale, pero funciona", porque no sólo estamos respondiendo al usuario que escribió el post. Lo estamos haciendo a todos los usuarios que posteriormente leerán ese post.

    Y sinceramente, un moderador y líder de los foros no debería contestar tan a la ligera. Si nosotros no damos ejemplo quién lo va a hacer? :-/

    Uno de los motivos por los que cada vez paso menos tiempo en los foros en Español es porque el nivel técnico ha caído bastante en los últimos tiempos, no se puede comparar con los foros en Inglés ni de lejos. Y es una verdadera lástima, porque estoy convencido de que hay excelentes profesionales en este sitio.

    Y parte del problema lo tenemos los que tenemos que tirar del carro (me incluyo), ya que en ocasiones ofrecemos una respuesta simple sin pararnos a pensar demasiado. Hay que formar a la gente, no resolver dudas puntuales. Ya sabéis, el dicho de "no le des un pez, enséñale a pescar".

    Venga, no vemos!

    PD - Pedro, lo de 4 ó 5 métodos igual se puede mejorar con generics, no? :-P


    No olvides marcar la respuesta como correcta si te ha sido de utilidad :-)

    [MS-MVP-MCTS]

    Follow me on Facebook or Twitter!

    Mi Perfil MVP en: https://mvp.support.microsoft.com/profile/Lluis
    NUG: http://andorradotnet.com
    Web: http://www.ordeeno.com
    Geeks: http://geeks.ms/blogs/lfranco

    martes, 6 de marzo de 2012 17:00
    Moderador
  • Hola Lluis,

    PD - Pedro, lo de 4 ó 5 métodos igual se puede mejorar con generics, no? :-P

    Pues sí.

    Saludos,


    phurtado

    martes, 6 de marzo de 2012 17:05
    Moderador
  • pero nadie respondio a lo que dijo leandro de si era mas rapido tambien en store procedure.
    martes, 6 de marzo de 2012 18:09
  • Hola Niquel,

    No he analizado si con un sp pasa o no pasa, pero ten claro que esto solo afecta a parametros de tipo string no pasa con int,etc. De todas foras si puedes evitarlo hazlo.

    Saludos,


    phurtado

    martes, 6 de marzo de 2012 18:54
    Moderador
  • :-)
    Hola,

    En ese caso ya te contesto yo :-D

    A ver, no sólo es cuestión de 'performance directa', hablamos de eso porque es su principal problema. Sin embargo hay otros. El principal es que al llamar a AddWithValue en ningún momento se le está pasando el tipo, y esto que a priori puede parecer una ventaja porque escribimos menos, a la larga puede ser muy perjudicial afectando -otra vez más- al rendimiento.

    Por ejemplo, SQL SERVER no realiza conversiones implícitas de modo que si utilizamos en el filtro WHERE una columna VARCHAR y le pasamos una valor NVARCHAR, el servidor no tiene modo alguno de hacer la comparación y debe convertir todos los valores de la columna a NVARCHAR con el sobrecoste que conlleva.

    Por ejemplo, especificando el tipo una consulta tarda 0ms (instantánea) y 41 lecturas, mientras que sin especificar el tipo tarda 200ms y más de 1.000 lecturas, de modo que no son 'solo unos milisegundos'.

    Si la cuestión es que se discute la ventaja de escribir menos, hacer esto:

    cmd.Parameters.Add("@paramname", SqlDbType.NVarChar).Value = paramvalue;
    

    Por no hablar de que al no especificar el tipo podrías llegar a tener problemas de conversión de tipos cuando se usan diferentes culturas. Pero es hablar de más... creo que está bastante claro que lo mejor es no usar AddWithValue en ningún caso.

    Nos vemos,


    No olvides marcar la respuesta como correcta si te ha sido de utilidad :-)

    [MS-MVP-MCTS]

    Follow me on Facebook or Twitter!

    Mi Perfil MVP en: https://mvp.support.microsoft.com/profile/Lluis
    NUG: http://andorradotnet.com
    Web: http://www.ordeeno.com
    Geeks: http://geeks.ms/blogs/lfranco

    martes, 6 de marzo de 2012 20:48
    Moderador