Problema con select first en Firebird

[[Al González]]

¡Hola de nuevo!

Ya me había ido a dormir, pero de pronto, entre el barullo de mis últimos pensamientos del día, reflexioné:

----------------------------------------------------
¿Por qué será que no se permite parametrizar un First?

Ah, pero podría "armar" el First n en el Select que llama al procedimiento (Select First n * From Procedimiento...). Después de todo, un procedimiento almacenado que devuelve un cursor hace un Suspend por cada fila, y si desde afuera le digo "First 10", pues sólo deberá traerme 10 y suspender definitivamente la obtención de las filas restantes.

Quizá por eso los creadores de InterBase / Firebird no permiten usar una variable o parámetro para indicar la cantidad en el interior de un procedimiento. Pareciera una forma sutil de decirnos, "¿pa' qué quieres el First parametrizado aquí? Tú pide desde afuera la cantidad que requieras." Aunque esto no suena muy convincente, quizá haya otra razón.

Ah, ¿pero qué pasaría si a ese Select exterior le añado una cláusula Order By? Temo que se va a traer todo el cursorote y ya luego tomará los First n del orden indicado, discriminando una gran cantidad de filas que ya se trajo. Eso lo volvería muy lento...
----------------------------------------------------

Entonces recordé que tenía una barra de chocolate con almendras en el congelador y aquello fue la gota que derramó la tentación.

Me levanté a la media noche para compartir estas reflexiones con ustedes y de paso hacer algunas pruebas adicionales con IB Expert y Delphi. Los resultados de esas pruebas fueron que:

1. Efectivamente, se puede utilizar First en el Select exterior para evitar el empleo de Execute Statement dentro del procedimiento.
2. Ese First sí puede tener un parámetro.
3. Por alguna razón no se permiten cláusulas First sin un valor literal dentro de procedimientos almacenados y disparadores.
4. Como era de esperarse, una cláusula Order By en el Select que llama al procedimiento hace que se obtengan todas las filas posibles de éste antes de aplicar el First de dicha sentencia Select, lo cual no es óptimo.

Conclusión para mi caso: Usaré First parametrizados en los Selects que llaman a los procedimientos. Dentro de éstos aplicaré un Order By acorde a ese First de afuera. En esos Selects exteriores evitaré el uso de Order By; con la ventaja de que, al estar utilizando CDSs (client data sets) conectados a los queries, puedo reordenar las filas en memoria según convenga para cada caso.

Esta solución podría ser útil en otros casos; todo depende de la cantidad de registros, los componentes utilizados y de qué tanto queramos variar el orden del cursor.

Empezado por jhonny

Si necesitas usar la primera sintaxis que describe Alex Peshkoff en http://fresh.t-systems-sfr.com/unix/...cute_statement estarías limitado a 32765 caracteres que es el tamaño máximo de un tipo VARCHAR o 32767 si decides usar un CHAR...

Gracias por el enlace Jhonny. Al final de ese documento se explican otras desventajas de Execute Statement (como el mayor consumo de tiempo). Veo muy útil la instrucción, pero creo que sólo la emplearé en casos muy especiales.

Un abrazo chocolatoso.

Al González.

[RolphyReyes]

Saludos.

Al González podrias si te es posible subir los select's de pruebas que utilizaste? .

Gracias de antemano.

[[egostar]]

Bueno, el hecho de usar el FIRST, solo lo considero para obtener estadísticas, no para recuperar filas de las tablas, mi caso es por ejemplo, quiero saber los N números más marcados mas no las primeras N filas, eso no me hace sentido y lo haría con validaciones WHERE, HAVING.

Salud OS.

[ariefez]

Tu sentencia esta bien solo le falta unos parentesis

Código SQL [-]
    SELECT FIRST (:PAGE_NF) SKIP (:PAGE_NF * (:PAGE_NRO - 1))

donde:
PAGE_NF Numero de registros por pagina
PAGE_NRO el numero de pagina

este es el codigo q uso, para darte un ejemplo

Código SQL [-]
/* CLIENTE - DISPONIBLES */

SET TERM ^ ;

CREATE OR ALTER PROCEDURE CLI_SELECT (
  CLIENTE TYPE OF D_CLI_NOM,
  DNI TYPE OF D_RUC,
  PAGE_NF INTEGER,
  PAGE_NRO INTEGER,
  IN_BLOCKED SMALLINT)
RETURNS (
  CLI_ID INTEGER,
  CLI_NOM VARCHAR(60),
  RUC VARCHAR(15),
  BLOCKED SMALLINT)
AS
BEGIN
  FOR
    SELECT FIRST (:PAGE_NF) SKIP (:PAGE_NF * (:PAGE_NRO - 1))
      CLI_ID, CLI_NOM, RUC, BLOCKED
    FROM
      CLIENTES
    WHERE
      0 < CLI_ID
      AND (:IN_BLOCKED = 1 OR BLOCKED = 0)
      AND (:CLIENTE IS NULL OR CLI_NOM LIKE '%' || :CLIENTE || '%')
      AND (NI IS NULL OR RUC LIKE '%' || NI || '%')
    ORDER BY CLI_NOM, RUC, CLI_ID
    INTO :CLI_ID, :CLI_NOM, :RUC, :BLOCKED
  DO SUSPEND;
END^

SET TERM ; ^

Agrego: probado en firebird 2.0 y 2.1 beta

[jhonny]

Vaya, Vaya, Vaya¡¡¡

[[egostar]]

, Funcionó perfectamente.

Muchas gracias ariefez, pues nada, cada día se aprende algo nuevo.

Así quedo finalmente mi SP

Código SQL [-]
SET TERM ^ ;

CREATE PROCEDURE NEW_PROCEDURE (
    e_limite integer,
    e_inicial date,
    e_final date)
returns (
    s_prefijo varchar(3),
    s_numero varchar(20),
    s_llamadas integer,
    s_duracion numeric(15,0))
as
begin
  /* Procedure Text */
  FOR
    select first (:e_limite) Prefijo, Numero, COUNT(*) as NumLla,
       SUM(DurEntera) as TotDur from llamadas
    where fecha between :e_inicial and :e_final
    group by prefijo, numero order by NumLla desc
  INTO :S_Prefijo, :s_numero, :s_duracion, s_llamadas DO
  suspend;
end^

SET TERM ; ^

GRANT SELECT ON LLAMADAS TO PROCEDURE NEW_PROCEDURE;

GRANT EXECUTE ON PROCEDURE NEW_PROCEDURE TO SYSDBA;

Solo hago notar que la funcion SUM() requiere de un tipo de dato Numeric, intentaba asignarlo como Integer y me daba un error de casting.

Salud OS.

[[Al González]]

¡Hola a todos!

Empezado por RolphyReyes

...podrias si te es posible subir los select's de pruebas que utilizaste?...

No sé si sirvan de mucho, y menos después de la tremenda solución dada por Ariefez, pero son los siguientes.

Select que detiene definitivamente el For Select del procedimiento spParte tras obtener las primeras 10 filas construidas por el procedimiento:

Código SQL [-]
-- Versión rápida
Select First 10 * From spParte (Null, Null, Null, Null, Null, Null)

Select que, por tener Order By, espera a que se realice todo el For Select del procedimiento spParte, para luego devolver solo 10 de las filas que construyó el procedimiento:

Código SQL [-]
-- Versión lenta
Select First 10 * From spParte (Null, Null, Null, Null, Null, Null) Order By Numero

Empezado por egostar

...usar el FIRST, solo lo considero para obtener estadísticas, no para recuperar filas de las tablas, mi caso es por ejemplo, quiero saber los N números más marcados mas no las primeras N filas, eso no me hace sentido...

¡Hey, hey, hey! Este no es mi vaso de Batman. Eliseo: En tu procedimiento sí usas First para obtener las primeras n filas. Sí, las primeras n filas que construye en el aire el propio For Select del procedimiento con ayuda de Suspend. No dije que fueran las filas (registros) de una tabla.

Empezado por ariefez

...Tu sentencia esta bien solo le falta unos parentesis

Código SQL [-]
    SELECT FIRST (:PAGE_NF)...

Agrego: probado en firebird 2.0 y 2.1 beta

Empezado por jhonny

Vaya, Vaya, Vaya¡¡¡

Otros tres vayaes . Me sorprendió de la misma forma y lo mejor de todo es que ¡también funciona en Firebird 1.5!

Gracias por el tip Ariefez, creo que has dado con la mejor solución para los casos planteados. Y además he aprendido aquello del Order By en llamadas a procedimientos.


Un primer abrazo.

Al González.

[ariefez]

Para eso estamos para compartir lo que sabemos y de pasadita tambien para aprender ...