Thanks for that thorough information.
So if I get you right no difference in normal direct SQL, but there can be a
difference
in stored procedures?

I don't use stored procedures, so I take it in this situation I can use
either.
I have done some testing with starting with 'Z' and like 'Z%' (both in
normal, direct SQL)
and can see no difference in performance.

RBS


2011/2/2 Svein Erling Tysv�r <svein.erling.tysvaer@...>

>
>
> >Is there any difference in Firebird 1.5 between these 2 queries:
> >
> >field1 starting with 'Z'
> >field1 like 'Z%'
> >
> >or are they treated exactly the same internally?
> >The reason I ask is somebody reckons that like is a lot faster in this
> >situation.
> >Bear in mind this is Firebird 1.5.
>
> STARTING WITH can generally use indexes. I don't think there's any
> difference in the two situations you describe (I think Fb 1.5 translates the
> LIKE into STARTING WITH), but this is only true as long as you use
> constants. If your situation is slightly different:
>
> Field1 starting with :MyParam
> Field1 like :MyParam
>
> then the situation is very different. In such a situation, STARTING WITH
> can use an index, whereas LIKE cannot (at prepare time, LIKE doesn't know
> whether the parameter starts with a wildcard character or a fixed value).
>
> So generally, I'd recommend using STARTING WITH rather than LIKE.
>
> Still, in specific situations, LIKE can be quicker. That is, when the
> optimizer chooses to use the index with STARTING WITH, but where using that
> particular index actually proves to cause a slowdown. You may have seen
> other messages on this list where +0 or ||'' is used to avoid a particular
> index from being used and basically using 'field1 LIKE :MyParam' is
> identical to 'field1+0 STARTING WITH :MyParam'. Even in this situation would
> I not recommend using field1 LIKE, since field1+0 STARTING WITH tells me
> that it is a conscientious choice from the programmer, whereas LIKE might be
> due to not even considering STARTING WITH.
>
> So, generally use STARTING WITH if you know how the field starts, and LIKE
> when you're trying to match a particular pattern that is more complex. If
> you know how the field starts and have a pattern later on in the field, use
> both STARTING WITH and LIKE if you want an index for the field to be used.
>
> HTH,
> Set
>
>

[Non-text portions of this message have been removed]