procedimientos - Oracle PL/SQL-¿Las excepciones NO_DATA_FOUND son malas para el rendimiento del procedimiento almacenado?
raise application error oracle (12)
Estoy escribiendo un procedimiento almacenado que necesita tener mucho acondicionamiento. Con el conocimiento general de la codificación C # .NET de que las excepciones pueden perjudicar el rendimiento, siempre evité usarlas en PL / SQL también. Mi condicionamiento en este proceso almacenado gira principalmente en torno a si existe o no un registro, lo que podría hacer de dos maneras:
SELECT COUNT(*) INTO var WHERE condition;
IF var > 0 THEN
SELECT NEEDED_FIELD INTO otherVar WHERE condition;
....
-o-
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND
....
El segundo caso me parece un poco más elegante, porque entonces puedo usar NEEDED_FIELD, que debería haber seleccionado en la primera declaración después de la condición en el primer caso. Menos código. Pero si el procedimiento almacenado se ejecutará más rápido usando el COUNT (*), entonces no me importa escribir un poco más para compensar la velocidad de procesamiento.
¿Algún consejo? ¿Me estoy perdiendo otra posibilidad?
EDITAR debería haber mencionado que todo esto ya está anidado en FOR LOOP. No estoy seguro de si esto hace una diferencia con el uso del cursor, ya que no creo que pueda DECLARAR el cursor como una selección en FOR LOOP.
¡Si es importante, realmente necesita comparar ambas opciones!
Habiendo dicho eso, siempre he usado el método de excepción, ya que es mejor usar solo la base de datos una vez.
@DCookie
Solo quiero señalar que puedes dejar de lado las líneas que dicen
EXCEPTION
WHEN OTHERS THEN
RAISE;
Obtendrá el mismo efecto si deja el bloque de excepción juntos, y el número de línea informado para la excepción será la línea donde realmente se lanza la excepción, no la línea en el bloque de excepción donde se volvió a generar.
Como SELECT INTO supone que se devolverá una única fila, puede usar una declaración del formulario:
SELECT MAX(column)
INTO var
FROM table
WHERE conditions;
IF var IS NOT NULL
THEN ...
El SELECT le dará el valor si uno está disponible, y un valor de NULL en lugar de una excepción NO_DATA_FOUND. La sobrecarga introducida por MAX () será mínima a cero, ya que el conjunto de resultados contiene una sola fila. También tiene la ventaja de ser compacto en relación con una solución basada en cursor, y no ser vulnerable a problemas de concurrencia como la solución de dos pasos en la publicación original.
El recuento (*) nunca aumentará la excepción porque siempre devuelve recuento real o 0 - cero, sin importar qué. Yo usaría el conteo.
En lugar de tener bucles de cursor anidados, un enfoque más eficiente sería usar un bucle de cursor con una combinación externa entre las tablas.
BEGIN
FOR rec IN (SELECT a.needed_field,b.other_field
FROM table1 a
LEFT OUTER JOIN table2 b
ON a.needed_field = b.condition_field
WHERE a.column = ???)
LOOP
IF rec.other_field IS NOT NULL THEN
-- whatever processing needs to be done to other_field
END IF;
END LOOP;
END;
La primera (excelente) respuesta declarada -
El método con count () no es seguro. Si otra sesión elimina la fila que cumplió con la condición después de la línea con el recuento (*), y antes de la línea con la selección ... en, el código generará una excepción que no será manejada.
No tan. Dentro de una unidad de trabajo lógica, Oracle es totalmente coherente. Incluso si alguien confirma la eliminación de la fila entre un recuento y un selecto Oracle, para la sesión activa obtendrá los datos de los registros. Si no puede, obtendrá un error de "instantánea demasiado antigua".
No usaría un cursor explícito para hacer esto. Steve F. ya no recomienda a las personas que usen cursores explícitos cuando se puede usar un cursor implícito.
El método con count(*) no es seguro. Si otra sesión elimina la fila que cumplió con la condición después de la línea con el count(*) , y antes de la línea con la select ... into , el código generará una excepción que no será manejada.
La segunda versión de la publicación original no tiene este problema, y generalmente se prefiere.
Dicho esto, hay una sobrecarga menor usando la excepción, y si está 100% seguro de que los datos no cambiarán, puede usar el count(*) , pero recomiendo que no lo haga.
Ejecuté estos puntos de referencia en Oracle 10.2.0.1 en Windows de 32 bits . Solo estoy mirando el tiempo transcurrido. Hay otros arneses de prueba que pueden proporcionar más detalles (como recuentos de enganches y memoria utilizada).
SQL>create table t (NEEDED_FIELD number, COND number);
Tabla creada.
SQL>insert into t (NEEDED_FIELD, cond) values (1, 0);
1 fila creada
declare
otherVar number;
cnt number;
begin
for i in 1 .. 50000 loop
select count(*) into cnt from t where cond = 1;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 1;
else
otherVar := 0;
end if;
end loop;
end;
/
Procedimiento PL / SQL completado con éxito.
Transcurrido: 00: 00: 02.70
declare
otherVar number;
begin
for i in 1 .. 50000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 1;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
Procedimiento PL / SQL completado con éxito.
Transcurrido: 00: 00: 03.06
Puede que esté superando a un caballo muerto aquí, pero marqué el cursor con el cursor en bucle, y eso funcionó tan bien como el método no_data_found:
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
for foo_rec in (select NEEDED_FIELD from t where cond = 0) loop
otherVar := foo_rec.NEEDED_FIELD;
end loop;
otherVar := 0;
end;
end loop;
end;
Procedimiento PL / SQL completado con éxito.
Transcurrido: 00: 00: 02.18
Sí, te estás perdiendo usando los cursores
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
OPEN foo_cur;
FETCH foo_cur INTO foo_rec;
IF foo_cur%FOUND THEN
...
END IF;
CLOSE foo_cur;
EXCEPTION
WHEN OTHERS THEN
CLOSE foo_cur;
RAISE;
END ;
es cierto que esto es más código, pero no utiliza EXCEPCIONES como control de flujo que, habiendo aprendido la mayor parte de mi PL / SQL del libro de programación PL / SQL de Steve Feuerstein, creo que es algo bueno.
Si esto es más rápido o no, no sé (hago muy poco PL / SQL hoy en día).
Stephen Darlington hace un buen punto, y puedes ver que si cambias mi punto de referencia para usar una tabla de tamaño más realista si llenero la tabla a 10000 filas usando lo siguiente:
begin
for i in 2 .. 10000 loop
insert into t (NEEDED_FIELD, cond) values (i, 10);
end loop;
end;
Luego vuelva a ejecutar los puntos de referencia. (Tuve que reducir el recuento de bucles a 5000 para obtener tiempos razonables).
declare
otherVar number;
cnt number;
begin
for i in 1 .. 5000 loop
select count(*) into cnt from t where cond = 0;
if (cnt = 1) then
select NEEDED_FIELD INTO otherVar from t where cond = 0;
else
otherVar := 0;
end if;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.34
declare
otherVar number;
begin
for i in 1 .. 5000 loop
begin
select NEEDED_FIELD INTO otherVar from t where cond = 0;
exception
when no_data_found then
otherVar := 0;
end;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.10
El método con la excepción es ahora más del doble de rápido. Entonces, para casi todos los casos, el método:
SELECT NEEDED_FIELD INTO var WHERE condition;
EXCEPTION
WHEN NO_DATA_FOUND....
es el camino a seguir. Dará resultados correctos y generalmente es el más rápido.
Una alternativa al código de @ Steve.
DECLARE
CURSOR foo_cur IS
SELECT NEEDED_FIELD WHERE condition ;
BEGIN
FOR foo_rec IN foo_cur LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
El ciclo no se ejecuta si no hay datos. Los bucles Cursor FOR son el camino a seguir, ayudan a evitar muchas tareas domésticas. Una solución aún más compacta:
DECLARE
BEGIN
FOR foo_rec IN (SELECT NEEDED_FIELD WHERE condition) LOOP
...
END LOOP;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END ;
Lo cual funciona si conoce la declaración de selección completa en tiempo de compilación.
no tiene que usar abrir cuando está utilizando para bucles.
declare
cursor cur_name is select * from emp;
begin
for cur_rec in cur_name Loop
dbms_output.put_line(cur_rec.ename);
end loop;
End ;
o
declare
cursor cur_name is select * from emp;
cur_rec emp%rowtype;
begin
Open cur_name;
Loop
Fetch cur_name into Cur_rec;
Exit when cur_name%notfound;
dbms_output.put_line(cur_rec.ename);
end loop;
Close cur_name;
End ;