I have a problem with a stored procedure. It is a simple procedure, that should read 1 record by passing the unique id of the record as parameter to the SP. Example:
create table test (id integer not null primary key, field1 integer, field2 integer) create or alter stored procedure mysp (myid) returns (result integer) as declare variable i1 integer; declare variable i2 integer; begin select field1, field2 from test where id = :myid into :i1, :i2; result = :i1 + :i2; end The query select * from mysp (1) does as expected and the performance analysis shows that 1 record of the table test was red indexed. After a while (weeks or month), the same statement shows in the performance analysis a multiple of the amount of re3cords in the table. In my real problem there are about 49000 records in the table and the performance analysis shows the non indexed read of about 950000 records. Then I just do another create or alter of the procedure and it works fine again. So there must be something going wrong with the precompiled SP after a while that gets fixed by just recompile it again. Is there a known issue that could explain that? Could it be, that there has to be a recompile of all stored procedures after doing some metadata changes or after a while of using the database and increment the amount of data? Is there a feature to recompile all SPs without doing a create or alter for all SPs? I know there is one in ibexpert, which we use, but we tried the recompile once and there where strange results with the characterset, so we do not use it any more. I hope my post makes sence and anyone could help me. kind regards Helmut
