Hello there!!

This is my enviroment: FB2.5, Delphi 2007, TIBQuery

I have a selectable stored procedure in a very new database (very few
records) which brings me all the pending movements of a customer or the
selected ones. (i'm copying the SP above)

If I run the query with the production database. It seems to hang the
app, and the FB service takes about 50% of the CPU. It happens just when
I open the query.

If I do a backup-restore. It runs normally...

I know that it may have to do with a long running transaction issue. The
problem is that I thought that I was very carefull in that matter!!!

Here there are the procedure body, and a gStat of production database
and one before backup-restore.

Any idea? It's a transaction issue? or may be something else?

As usual: THANKS A MILLON!!!

-Sergio




*PROCEDURE*

CREATE PROCEDURE SALDO_CTA_CTE_CLI_GRAL (
in_id fk_id,
in_mostrar_cero boolean)
returns (
out_nombre descripcion collate es_es_ci_ai,
out_id fk_id,
out_fecha fecha,
out_descripcion descripcion collate es_es_ci_ai,
out_sucursal numero_chico,
out_numero numero_doc,
out_importe money,
out_saldo money,
out_saldo_tabla money)
as
begin

if (in_mostrar_cero is null) then
in_mostrar_cero = 1;

if (in_id > 0) then
begin

select nombre || ' - ' || razon_social,saldo from clientes where id
= :in_id into :out_nombre,:out_saldo_tabla;

for
select out_id, out_fecha, out_descripcion, out_sucursal,
out_numero, out_importe, out_saldo
from saldo_cta_cte_clientes(:in_id)
into :out_id, :out_fecha, :out_descripcion, :out_sucursal,
:out_numero, :out_importe, :out_saldo
do
begin
suspend;
end

end
else
begin

for select clientes.id, clientes.nombre || ' - ' ||
clientes.razon_social, clientes.saldo
from clientes
join clientes_seleccion on ((clientes.id = clientes_seleccion.id)
or (:in_id = -2))
where exists(select id from fac_ven where saldo > 0 and
fac_ven.id_clientes = clientes.id) or
exists(select id from cobros where saldo > 0 and
cobros.id_clientes = clientes.id)
order by 2
into :in_id, :out_nombre, :out_saldo_tabla
do
begin

if ((out_saldo_tabla <> 0) or (in_mostrar_cero = 1)) then
begin

for
select out_id, out_fecha, out_descripcion, out_sucursal,
out_numero, out_importe, out_saldo
from saldo_cta_cte_clientes(:in_id)
into :out_id, :out_fecha, :out_descripcion, :out_sucursal,
:out_numero, :out_importe, :out_saldo
do
begin
suspend;
end

end

end

end

end


===========


*PRODUCTION DATABASE*

Database "datos.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 15496
Page size 4096
ODS version 11.2
Oldest transaction 14986
Oldest active 14987
Oldest snapshot 14987
Next transaction 15491
Bumped transaction 1
Sequence number 0
Next attachment ID 311
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jun 5, 2011 22:00:58
Attributes force write

Variable header data:
*END*


Database file sequence:
File datos.fdb is the only file


================


*AFTER BACK-UP AND RESTORE*


Database "datos.fdb"
Database header page information:
Flags 0
Checksum 12345
Generation 307
Page size 4096
ODS version 11.2
Oldest transaction 1
Oldest active 2
Oldest snapshot 2
Next transaction 299
Bumped transaction 1
Sequence number 0
Next attachment ID 2
Implementation ID 16
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Jun 21, 2011 23:09:08
Attributes force write

Variable header data:
Sweep interval: 20000
*END*


Database file sequence:
File datos.fdb is the only file



[Non-text portions of this message have been removed]