Den 2012-10-31 10:45 skrev Svein Erling Tysv�r s�h�r:

>
> I tried manually LIKE with and without the addition of STARTING WITH
> and tried what I thought was likely to have worse performance with
> STARTING WITH - when the parameter starts with a wildcard. With
> STARTING WITH, my query executed in 5.4 seconds, without it 4.6
> seconds. So, in my simple test, the additional overhead was about 17%.
> The other extreme, with the wildcard at the end, STARTING WITH
> executed in 0.3 seconds, vs 4.5 seconds without STARTING WITH, i.e.
> STARTING WITH is 15 times quicker. So, what determines whether such an
> addition will be harmful or benefitial, is (no surprise) what the
> parameter looks like.
>

Interesting to see! Thanks! Just one question: in the first case,
wildcard at beginning, did you write starting with '%xyz' or did you
extract the prefix up to the first wildcard, i.e. an empty string:
starting with ''? It was the latter I intended.

> To me, a question regarding such an addition is whether one should add
> a 17% overhead to people being careful about their programming to
> delight those that are more sloppy in their programming with huge
> speed increases? I'd say no, anyone is free to add STARTING WITH to
> their own queries and I don't like the idea of forcing it upon those
> that doesn't need it. Mind you, it could be a sensible addition to
> component sets and I wouldn't mind if e.g. IBO added a Boolean
> property LikeAddsStartingWith to TIB_Cursors, that - if TRUE - did
> what you want Firebird itself to do.
>

Ann said that it would require a more advanced analysis than the query
optimizer currently support, and for a good reason. OK. So the
discussion is purely academic.

>
> Myself, I rarely use LIKE in my queries, particularly not as the only
> limiting factor on huge tables (if you have other, noticeably more
> selective indexes available for the query, there's no point in adding
> STARTING WITH), and am not amongst those that are likely to benefit or
> be harmed from your proposed addition to Firebird.
>

Same for me usually: I have no problem with preparsing a search pattern
in my app and adapting the query as needed. On occasion I might stumble
opun framwork generated SQL that might benefit, but there would probably
be workarounds there too.

Kjell

--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64



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