--- In ib-support@yahoogroups.com, Tim Ledgerwood <tim@s...> wrote:

> (and it seems that perrformance degrades over
> time quite quickly - 2 or 3 days - say within 5 or 6 shifts)

Tim, check gap between oldest active transaction and current one
using gstat -h. Very likely there are one or more eventually opened
not read-only transactions in your application.

> We have checked the tables - the data is there. The data in the

joined

> table is there. When we run the query that the stored procedure

contains as

> a normal select, it produces the data. When we "select * from

procedurename

> (par1, par2)", we get a null result.
> It almost seems as if the db or the client is timing the procedure

out - I

Not likely. If you got NULL result and no exception messages, it
means query was not aborted, it just could'nt find data.

> ALTER PROCEDURE "GETSALESBYDRYSTOCK"
> (
> "POSID" INTEGER,
> "SHIFTNUM" INTEGER
> )
> RETURNS
> (
> "DESCRIP" VARCHAR(50),
> "STOK_CODE" VARCHAR(13),
> "AMOUNT" FLOAT,
> "DEPT_CODE" VARCHAR(2),
> "UTOTAL" FLOAT,
> "MTOTAL" FLOAT
> )
> AS
> BEGIN
> FOR SELECT
> l.Descrip, l.Stok_Code, l.Dept_Code, s.Amount,
> SUM(l.UQuantity) AS UTotal, SUM(l.MQuantity) AS MTotal
> FROM log l JOIN STOCK S
> ON l.Stok_Code = s.Code
> WHERE l.Tran_Type LIKE '#%'
> AND Misc_Trans = 0
> AND l.Sal_Ref = 'S'
> AND l.POS_ID = :POSID
> AND SHIFTNUM = :SHIFTNUM
> GROUP BY l.Stok_Code, l.Descrip, s.Amount, l.Dept_Code
> ORDER BY l.Descrip
> INTO
> :DESCRIP, :STOK_CODE, :DEPT_CODE, :AMOUNT, :UTOTAL, MTOTAL
> DO
> BEGIN
> SUSPEND;
> END
> END
> ^

There are two lines in Where clause where columns are used without
table aliases. It is so-called ambigious query which can unpredictably
return wrong or correct results. Such a queries are prohibited in
dialect 3 databases in FB but are made temporarily acceptable in
dialect 1 to compatibility with old applications migrated from Borland
IB. It is most probable reason, especially if there are columns with
this names in both tables. Alter procedure being sure applications
which use it are not connected at this moment. If it will'nt help (I
never encountered such a behaviour, just guesses based on vague
recollections of discussions):

1. Check connection strings in all application instances and used in
interactive tool are the same sign-to-sign.
2. Check you use gds32.dll the same version as server software.
3. What is your IBX version? Early ones had nasty bug with
TIBStoredProc.ExecProc (though not with Select from SP) which in
conjunction with another bug in Borland gds32.dll provided very
strange behaviour.
4. Check plans for select itself and select from SP. If different
indices are used, there is opportunity there are broken ones.

Best regards, Alexander.