Eficiencia de consultas paramétricas vs consultas estáticas

[[movorack]]

Hola a tod@s.

Tengo una pequeña duda con respecto a la eficiencia de ejecución de las consultas paramétricas vs consultas estáticas.

¿A que me refiero?

Una consulta parametrica

Código Delphi [-]
Consulta.SQL.Add('SELECT A, B, C');
Consulta.SQL.Add('FROM TABLA')
Consulta.SQL.Add('WHERE A = :VALOR1')
Consulta.SQL.Add('  AND B = :VALOR2')

Consulta.Params.ParamByName('VALOR1').Value := Var1;
Consulta.Params.ParamByName('VALOR2').Value := Var2;

es más eficiente a una consulta estática

Código Delphi [-]
Consulta.SQL.Add('SELECT A, B, C');
Consulta.SQL.Add('FROM TABLA')
Consulta.SQL.Add('WHERE A = '+QuotedStr(Var1))
Consulta.SQL.Add('  AND B = '+IntToStr(Var2))

Si!. el código es mas legible y se puede administrar mejor, pero me refiero a si no solo es una buena práctica sino que también influye en el desempeño de la aplicación.

Parameterized statements are cached to facilitate reuse and avoid compilation overhead, which is especially important in high-volume OLTP environments. Furthermore, using stored procedures further promotes caching and reuse.

The only downside to parameters from a performance perspective is that a given plan may be optimal for some values but not others. In cases where sub-optimal cached plans are an issue, techniques such as RECOMPILE hints or compromise plan guides can help avoid a sub-optimal execution plans due to varying parameter values. See article Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005 for more information.

link: http://weblogs.sqlteam.com/dang/arch...-Practice.aspx

He encontrado lo anterior en una entrada de blog pero quisiera saber sus puntos de vista

De antemano, gracias por sus aportes.

[Casimiro Notevi]

Con parámetros, no solamente es más eficiente, que lo es, al no tener que "recompilar" la sentencia cada vez que la va a ejecutar. Sino que además es más segura, al evitar muchos problemas de "inyección sql".
También es más cómoda para asignar valores sin tener que hacer conversiones, sobre todo en datos de tipo fecha, blob, etc.
Y por si fuese poco, el código queda más legible, más fácil de seguir y comprender sin tantas comillas.

[[movorack]]

Si! eso es lo que siempre he manejado pero tengo un compañero algo terco que y no he hallado la respuesta técnica para terminar de convencerlo.

[[duilioisola]]

Sobre todo lo que dicen de conversiones de formato.
Dependiendo de la base de datos, por ejemplo, las fechas habría que enviarlas en un formato u otro 'yyyy-mm-dd' o 'mm/dd/yyyy' 'dd.mm.yyyy'.
En el caso de números deberás sacar el separador de miles y reemplazar el separador decimal por un punto.

Todo esto te lo ahorras mediante:

Código Delphi [-]
Consulta.Params.ParamByName('FECHA').AsDeteTime:= MiFecha;
Consulta.Params.ParamByName('ENTERO').AsInteger := MiEntero;
Consulta.Params.ParamByName('DECIMAL').AsFloat:= MiDouble;

[Casimiro Notevi]

Empezado por movorack

... tengo un compañero algo terco que y no he hallado la respuesta técnica para terminar de convencerlo.

Tú dile que esa forma de poner los parámetros cómo él usa es la típica de los novatos

[[movorack]]

A ver la cuestión no es de forma sino de fondo, y la verdad no me he hecho entender.

Mi compañero novato no es y el usa constantemente los parámetros al igual que yo y siempre he escuchado que lo mejor es usar consultas paramétricas porque son mas eficientes ya que son cacheadas en el motor pero hasta ahora no he encontrado información técnica de esto y ahí es donde está la "terquedad" de mi compañero de usarlo aunque para el el argumento de uso no es concluyente.

Hasta ahora para ambos, el tema del cacheado de la consulta paramétricas es como si fuese una leyenda urbana. Todos hablan de eso y hasta creen en eso pero no han encontrado pruebas de eso.

[ecfisa]

Hola movorack.

Empezado por movorack

Tengo una pequeña duda con respecto a la eficiencia de ejecución de las consultas paramétricas vs consultas estáticas.

Lo dicho por Casimiro y duilioisola, usar parámetros es superior en todos los aspectos.

Empezado por movorack

Si! eso es lo que siempre he manejado pero tengo un compañero algo terco que y no he hallado la respuesta técnica para terminar de convencerlo.

Para muestra basta un botón... Que pruebe este ejemplo con solo dos columnas:

Código Delphi [-]
const
  NO_ROWS = 100000;
var
  Freq, Start, Stop: Int64;

procedure TForm1.FormCreate(Sender: TObject);
begin
  Memo1.Font.Name:= 'Currier';
  QueryPerformanceFrequency(Freq);
end;

procedure TForm1.btnStaticClick(Sender: TObject);
var
  i: Integer;
  tt: Double;
begin
  with IBQuery1 do
  begin
    Close;
    SQL.Text:= 'DELETE FROM TEST';
    ExecSQL;
    QueryPerformanceCounter(Start);
    Close;
    for i:= 1 to NO_ROWS do
    begin
      SQL.Clear;
      SQL.Add('INSERT INTO TEST(ID,NOMBRE)');
      SQL.Add('VALUES('+QuotedStr(IntToStr(i))+', '+
        QuotedStr('Nombre '+IntToStr(i))+')');
    end; 
    ExecSQL;
  end;
  QueryPerformanceCounter(Stop);
  tt:= (Stop-Start)*1000000 div Freq;
  Memo1.Lines.Add(Format('Estatico            : %s ms.',[FormatFloat('0,', tt)]));
end;

procedure TForm1.ParameterClick(Sender: TObject);
var
  i: Integer;
  tt: Double;
begin
  with IBQuery1 do
  begin
    Close;
    SQL.Text:= 'DELETE FROM TEST';
    ExecSQL;
    QueryPerformanceCounter(Start);
    Close;
    SQL.Clear;
    SQL.Add('INSERT INTO TEST(ID,NOMBRE)');
    SQL.Add('VALUES(:ID,:NOMBRE)');
    Prepare;
    for i:= 1 to NO_ROWS do
    begin
      ParamByName('ID').AsInteger:= i;
      ParamByName('NOMBRE').AsString:= Format('Nombre %d',[i]);
    end; 
    ExecSQL;
  end;
  QueryPerformanceCounter(Stop);
  tt:= (Stop-Start)*1000000 div Freq;
  Memo1.Lines.Add(Format('Parametrizado: %s ms.',[FormatFloat('0,', tt)]));
end;

En mi caso obtuve estos resultados:

Estatico     : 665.696 ms.
Parametrizado: 135.562 ms.

Saludos

[[movorack]]

Gracias, TOPX también te lo agradece

[[duilioisola]]

También hay que mencionar que si se utiliza una sola vez el Query, es posible que incluso tarde más la versión parametrizada.
El cacheo se realiza como en el ejemplo de eficsa cuando se reutiliza el SQL una y otra vez.

De todos modos, por facilidad de lectura, por evitar transformaciones a los datos para volverlos strings y finalmente por el cacheo en el caso de utilización repetida del mismo SQL, creo que se debe utilizar.

[Casimiro Notevi]

Los parámetros ganan por goleada