Re: Stored Procedure bug in Informix 7.3
Posted in 1999
Topics: Performance & Tuning, Stored Procedures & SPL
The SQL optimizer cannot determine whether or not it can use the optimizer
because it
does not know if the incoming parameter begins with a matching character. For
example, if
C is '013%', then the index can be used but if it is '%123', the index cannot
be used. The
access plan has already been built by the time the parameters are sent and so
it cannot use the
index.
MSN wrote:
> Hi,
>
> I have the following code in my stored procedure:
>
> "where A.lmatter matches ('013169' || '*') "
>
> This code above runs in less then 2 seconds
>
> if I replace it with;
>
> "where A.lmatter matches (C || '*') "
>
> Where C is a parameter. The program runs for more then 1 minute. Any idea ?
> Please help.
>
> create procedure test113(c char(6), i integer)
> RETURNING CHAR(12), Integer, CHAR(20);>
> DEFINE matter Char(12);
> DEFINE Invoice Integer;
> DEFINE BillDate Date;
>
> FOREACH
> Select *
> into matter, Invoice, BillDate
> from PW_PHMainMTD24 A>
> where A.linvoice=1929
> --and A.lmatter matches ('013169' || '*')
> and A.lmatter matches c || '*'
>
> RETURN matter, invoice, matter;
>
> END FOREACH
>
> END PROCEDURE;
>
> execute procedure test113('013169', 1929);