>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

> Oldest transaction 14986
> Oldest active 14987
> Oldest snapshot 14987
> Next transaction 15491
> Creation date Jun 5, 2011 22:00:58

Well, a gap of 500 isn't normally a problem, but it of course depends on what these 500 transactions have done. If almost all of them have updated every row in the table, I guess it would be a problem. With 15000 transactions in about 17 days, my guess is that the oldest transaction was started in the morning and that you ran the statistics some time after lunch.

What I would guess could be a problem, is that changes to the data made the optimizer choose a different plan for some queries. However, that is not any of the queries in your procedure since all of them typically refer to one table only and the only one that refers to several tables actually requires NATURAL on both tables (which generally is a bad idea). However, I do notice that you do select from other stored procedures, and if one of them gets a bit slower, then that slowness could multiply when done in a loop (the procedure itself might execute in 5 seconds, but running it 1000 times would require almost one and a half hour).

Regarding NATURAL, I'd recommend you to consider changing:

join clientes_seleccion
on ((clientes.id = clientes_seleccion.id)
or (:in_id = -2))

to

join clientes_seleccion
on clientes_seleccion.id =
case when (:in_id = -2) then
clientes_seleccion.id
else
clientes.id
end

This will not work if clients_seleccion.id can be NULL, but id columns tend to be NOT NULL. It would probably be a bit slower when (:in_id = -2), but it should be considerably faster if it isn't. So, do you typically run with :in_id = -2? This change would typically make an index for clientes_seleccion.id available to the query.

HTH,
Set