05 octubre 2007

Creando consultas SQL con parámetros

En el artículo anterior vimos como realizar consultas SQL para INSERT, DELETE, UPDATE y SELECT utilizando el componente IBSQL que forma parte de la paleta de componentes IBExpress.

También quedó muy claro que la velocidad de ejecución de consultas con este componente respecto a otros como IBQuery es muy superior. Todo lo que hemos visto esta bien para hacer consultas esporádicas sobre alguna tabla que otra, pero ¿que ocurre si tenemos que realizar miles de consultas SQL de una sola vez?

UTILIZANDO UNA TRANSACCION POR CONSULTA

Supongamos que tenemos que modificar el nombre de 1000 registros de la tabla CLIENTES:

var
i: Integer;
dwTiempo: DWord;
begin
with Consulta do
begin
////////////// METODO LENTO ////////////////

dwTiempo := TimeGetTime;

for i := 1 to 1000 do
begin
SQL.Clear;
SQL.Add( 'UPDATE CLIENTES' );
SQL.Add( 'SET NOMBRE = ' + QuotedStr( 'NOMBRE CLIENTE Nº ' + IntToStr( i ) ) );
SQL.Add( 'WHERE ID = ' + IntToStr( i ) );

Transaction.StartTransaction;

try
ExecQuery;
Transaction.Commit;
except
on E: Exception do
begin
Application.MessageBox( PChar( E.Message ), 'Error de SQL', MB_ICONSTOP );
Transaccion.Rollback;
end;
end;
end;

ShowMessage( 'Tiempo: ' + IntToStr( TimeGetTime - dwTiempo ) + ' milisegundos' );
end;
end;

Como puede verse arriba, por cada cliente actualizado he generado una SQL distinta abriendo y cerrando una transacción para cada registro. He utilizado la función TimeGetTime que se encuentra en la unidad MMSystem para calcular el tiempo que tarda en actualizarme el nombre de los 1000 clientes. En un PC con Pentium 4 a 3 Ghz, 1 GB de RAM y utilizando el motor de bases de datos Firebird 2.0 me ha tardado 4167 milisegundos.

Aunque las consultas SQL van muy rápidas con los componentes IBSQL aquí el fallo que cometemos es que por cada registro actualizado se abre y se cierra una transacción. En una base de datos local no se nota mucho pero en una red local con muchos usuarios trabajando a la vez le puede pegar fuego al concentrador.

Lo ideal sería poder modificar la SQL pero sin tener que cerrar la transacción. Como eso no se puede hacer en una consulta que esta abierta entonces hay que utilizar los parámetros. Los parámetros (Params) nos permiten enviar y recoger información de una consulta SQL que se esta ejecutando sin tener que cerrarla y abrila.

UTILIZANDO PARAMETROS EN LA CONSULTA

Para introducir parámetros en una consulta SQL hay que añadir dos puntos delante del parámetro. Por ejemplo:

UPDATE CLIENTES
SET NOMBRE = :NOMBRE
WHERE ID = :ID

Esta consulta tiene dos parámetros: ID y NOMBRE. Los nombres de los parámetros no tienen porque coincidir con el nombre del campo. Bien podrían ser así:

UPDATE CLIENTES
SET NOMBRE = :NUEVONOMBRE
WHERE ID = :IDACTUAL

De este modo se pueden modificar las condiciones de la consulta SQL sin tener que cerrar la transacción. Después de crear la consulta SQL hay que llamar al método Prepare para que prepare la consulta con los futuros parámetros que se le van a suministrar (no es obligatorio pero si recomendable). Veamos el ejemplo anterior utilizando parámetros y una sóla transacción para los 1000 registros:

var
i: Integer;
dwTiempo: DWord;
begin
with Consulta do
begin
////////////// METODO RÁPIDO ////////////////

dwTiempo := TimeGetTime;

Transaction.StartTransaction;

SQL.Clear;
SQL.Add( 'UPDATE CLIENTES' );
SQL.Add( 'SET NOMBRE = :NOMBRE' );
SQL.Add( 'WHERE ID = :ID' );
Prepare;

for i := 1 to 1000 do
begin
Params.ByName( 'NOMBRE' ).AsString := 'NOMBRE CLIENTE Nº '+ IntToStr( i );
Params.ByName( 'ID' ).AsInteger := i;
ExecQuery;
end;

try
Transaction.Commit;
except
on E: Exception do
begin
Application.MessageBox( PChar( E.Message ), 'Error de SQL', MB_ICONSTOP );
Transaccion.Rollback;
end;
end;

ShowMessage( 'Tiempo: ' + IntToStr( TimeGetTime - dwTiempo ) + ' milisegundos' );
end;
end;

En esta ocasión me ha tardado sólo 214 milisegundos, es decir, se ha reducido al 5% del tiempo anterior sin saturar al motor de bases de datos abriendo y cerrando transacciones sin parar.

Este método puede aplicarse también para consultas con INSERT, SELECT y DELETE. En lo único en lo que hay que tener precaución es en no acumular muchos datos en la transacción, ya que podría ser peor el remedio que la enfermedad.

Si teneis que actualizar cientos de miles de registros de una sola vez, recomiendo realizar un Commit cada 1000 registros para no saturar la memoria caché de la transacción. Todo depende del número de campos que tengan las tablas así como el número de registros a modificar. Utilizad la función TimeGetTime para medir tiempos y sacar conclusiones.

Y si el proceso a realizar va a tardar más de 2 o 3 segundos utilizar barras de progreso e hilos de ejecución, ya que algunos usuarios neuróticos podrían creer que nuestro programa se ha colgado y empezarían ha hacer clic como posesos (os aseguro que existe gente así, antes de que termine la consulta SQL ya te están llamando por teléfono echándote los perros).

Pruebas realizadas con Firebird 2.0 y Dephi 7.

19 comentarios:

Enrique Ballarin dijo...

Hola, veo que nunca nadie hace un comentario. El mio es para animarte. Me parece un blog interesantisimo sobre temas de delphi. Darte las gracias y mucho animo. Continua asi

Administrador dijo...

El error fue mío al dejar la opción de que sólo los usuarios registrados pudiesen escribir comentarios. Lo he soltado hace poco.

Muchas gracias por tu apoyo.

Anónimo dijo...

En verdad leo tu pagina cada vez que me atoro con algo, es muy practica y facil de entender, espero sigas!

Anónimo dijo...

En verdad leo tu pagina cada que me atoro con algo, es muy practica y facil de entender, espero sigas asi!

Gracias!

Administrador dijo...

Me alegro mucho de que te guste la página. Aunque parezca mentira yo también aprendo mucho escribiendo artículos. Ganamos todos.

Un cordial saludo.

Anónimo dijo...

Se que quizas el hilo es viejo pero nunca esta de mas hacer una critica constructiva.
Con respecto a la estructura TRY...EXCEPT del codigo correspondiente a la manera rapida, esta mal iniciada ya q estas protegiendo un bloque de codigo (commit) que nunca falla. En caso de error, durante la ejecucion de la consulta, la excepcion nunca se detectaria y por lo tanto no harias el Rollback.

Saludos

Administrador dijo...

Gracias por tu aportación, pero en los programas de gestión que tengo hechos me falla en ambos casos:

ExecQuery -> Cuando me equivoco en la consulta SQL, sobre todo en errores sintácticos o de campos o tablas que no existe.

Commit -> Cuando envías la transacción al servidor y provoca concurrencia (deadlock), o intentas duplicar un registro (primery key).

Al final tube que meter las dos sentencias en el try. Además tengo hecho una ventana de errores que me muestra la excepción y la sentencia SQL para que el cliente me la mande por correo. He verificado que me explota en ambos casos.

¿Sabes de alguna documentación o página web donde hablen a fondo sobre el tema? Encontrar información sobre Delphi es mas duro que pegarle a un pobre.

De todas formas, si encuentras la solución perfecta tanto para este artículo como para otros no te cortes en decírmelo. También yo tengo ganas de que la gente me enseñe a como hacer las cosas como dios manda en Delphi.

Saludos.

Miguel Guixot dijo...

Tengo una duda: es posible meter en la sentencia sql un script que contenga ya parámetros de por sí y que delphi no los interprete.
Ejemplo script:
CREATE PROCEDURE ACTUALIZA_LINEASVENTA
AS
DECLARE VARIABLE LINEA INTEGER;
DECLARE VARIABLE ALBARAN VARCHAR(10);
DECLARE VARIABLE REFERENCIA VARCHAR(10);
begin
for select lv_numerolinea, lv_numeroalbaran, lv_articuloref, lv_articuloprov
from lineasventa
into :linea, :albaran, :referencia, :proveedor
do

El problema es que el TIBQuery, me interpreta los parámetros y los espera.

Administrador dijo...

Que yo sepa, tanto los componentes TIBQuery como TIBSQL toman como parámetro cualquier palabra que empiece por los dos puntos.

De hecho nos obliga a rellenar los params en cuanto encuentra este tipo de palabras.

Para hacer lo que tu quieres habría que utilizar un componente que permitiera mandar sentencias SQL en formato crudo sin preprocesar nada.

Jagjag dijo...

Excelente blog mano, me ha ayudado mucho gracias y sigue adelante te lo agradeceremos muchos creemelo.

Anónimo dijo...

Bueno, una aclaración: llamar al método "Prepare" no es obligatorio pero si no se llama se sobrecarga la base de datos un montón (véase ayuda de Delphi).

Si no se llama explícitamente al método Prepare, Delphi prepara y "desprepara" el query, lo que hace que Interbase reserve buffers 1000 veces (una para cada registro), que es una operación muy lenta.

Llamando explícitamente al método Prepare, Delphi ya no lo "desprepara" al cerrar el query, sinó que seguirá preparado para usarse de nuevo.

Administrador dijo...

Gracias por la información.

Julio dijo...

Muy bueno el blog. Siempre lo consulto.
Gracias por dedicar tu tiempo a compartir conocimientos

Unknown dijo...

Tengo un problema con unas funciones que he creado en MYSQL no encuentro la manera de hacerlas trabajar desde delphi, necesito pasarles parametros y recibir los parametros de salida desde las mismas, el caso es que me da error no se como manejar esto, estoy utlizando delphi xe2 y el MySql de Wamp ojala me pudieran ayudar con algun ejemplo completo gracias.

Administrador dijo...

Deberías probar a utilizar DBExpress con el controlador que lleva de MySQL.

A menos que utilices otros componentes que conecten directamente con MySQL.

Saludos.

Unknown dijo...

S.O.S He provado pero aun asi me da error llevo varios dias estancado en esto sin poder adelantar con gusto PAGARIA para que se conectaran a mi ordenador remotamente por una explicacion y ver solucionar el problema, por favor si pueden ayudarme mi correo es yosvanyisel@gmail.com. Gracias.

elohin dijo...

AYUDA URGE !!!! TENGO ESTA TAREA:

C.- Realiza un programa que lea desde un archivo de texto (arreg.txt), el contenido de 10 valores enteros (positivos y negativos), que los cuente e imprima en diferentes memos el resultado de cuantos positivos y cuantos negativos hay, además de imprimir a c/u de los elementos, según le corresponda.

elpatas dijo...

Muchas gracias!!! sigue asì, tu blog me ayuda a aprender cada dia mas, un abrazo desde Mexico.

Unknown dijo...

Me encanta Delphi al Límite, me ha ayudado a crecer como programador en el apasionante lenguaje de programación Turbo pascal y su IDE por excelencia Delphi, gracias por este espacio de comunidad.

Publicidad