Michael,
On 20/04/2012 12:21, Michael Bayer wrote:
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
As always thanks for your quick feed back.
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".
Would be great if that makes it into 0.8.
for the moment as a workaround I'd employ your own startswith() function, or
use mycol.op('starting with')(value).
That works great for the case I had a performance issue with.
Thanks a lot
Werner
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
