Hello,

but my SQL isn't a prepared SQL, I build the SQL directly with the
searchstring used in the like and the index isn't still used.

I take the IBPlanAnalyser and copy both SQLs directly into it with no
params and so on and the = 'TEST' runs in 3-4 seconds (most time is
used for preparing it regarding to IBPLanAnalyzer) but the
LIKE 'TEST%' runs for minutes. I don't use parameter....

I although tested the SQL with STARTING and it runs 3-4 minutes too.

Maybe the SQL-Statement is to complex for firebird? Why?

Thanks

Stefan

--- In firebird-support@yahoogroups.com, "Leyne, Sean" <Sean@...>
wrote:

>
> Stefan,
>
> > I have a very large SQL which does a search on a table while

joining

> > over several other tables. All tables contains millions of rows.

The

> > SQL does a search on a table WORD with column name='TEST'. All
> > entries in the column are in uppercase and indexed. The SQL runs

in 3

> > seconds when I search name='TEST' but if I search for name
> > like 'TEST%' it runs 15 minutes. The plan analyszer shows that in

the

> > second case no index is used. I teste it fpr 2.0.3 and 2.1. Uner
> > Oracle 9i or SQL-Server 2005 both selects runs in about some

seconds.

> >
> > What is the problem in Firebird?
>
> Arguably there is no problem with Firebird, but in your test.
>
> Firebird will use indexes for these SQL statements:
>
> SELECT ... WHERE NAME = 'TEST'
>
> SELECT ... WHERE NAME LIKE 'TEST%'
>
>
> Firebird will also use indexes for a prepared query like:
>
> SELECT ... WHERE NAME = :Parm
>
>
> But Firebird will *not* use an index for a prepared query like:
>
> SELECT ... WHERE NAME LIKE :Parm
>
>
> This is a known/documented limitation.
>
>
> Sean
>