On Apr 20, 2012, at 4:51 AM, Werner wrote:
> On 20/04/2012 10:36, Werner wrote:
>> I use Firebird SQL, and "like" has a pretty bad performance compared to
>> "starting with", as the later uses an index if one is available.
>>
>> When using "someklass.somecolumn.startswith('value') SA uses "like", what
>> would have to be done to use "starting with" instead for the Firebird
>> dialect?
>>
> Hhm,
>
> The performance issue should be solved with 2.1+, so did some more search and
> testing.
>
> SA generates something like this when using "startswith" - correct?
>
> select name from cepagesyn where NAME like 'merlot' || '%%';
>
> If I run the above in IBExpert (the db tool I use to admin) it is very slow
> as the plan is "CEPAGESYN NATURAL"
>
> if I use:
>
> select name from cepagesyn where NAME like 'merlot%';
>
> PLAN (CEPAGESYN INDEX (IX_CEPAGESYN_NAME))
>
> So, could the FB dialect be tweaked to do the concatenation in Python instead
> of how it is done now?
>
> Werner
the concatenation in SQL is there to handle the case of the argument not being
a literal string, i.e. another SQL expression like a column. There also might
be some edges to it related to escape characters, not sure.
To really handle this we'd have to adjust the mechanism of startswith,
endswith, contains to move their evaluation out to the compiler, instead of
hardwiring them to the concatenation. This is probably a good idea in any
case though might be a bit destabilizing, might be better for 0.8. In the FB
case it would render "STARTING WITH".
for the moment as a workaround I'd employ your own startswith() function, or
use mycol.op('starting with')(value).
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
