On Sep 16, 2008, at 12:00 PM, Guido Neitzer wrote:
On 16.09.08 11:40, "Chuck Hill" <[EMAIL PROTECTED]> wrote:
SELECT <bunch of attributes>, from ORDERS T0 WHERE
UPPER(T0.EMAIL_ADDRESS) LIKE UPPER('[EMAIL PROTECTED]') ESCAPE '\;
Here is what I don't understand: If I run the above query directly
against our database, it takes about 12 seconds.
I am somewhat surprised it would run that fast. Are you certain you
are timing this exact query?
Why? If there is a case insensitive index on that column it should
be able
to use it as it is just a begins with search.
I don't think the optimizer will match the collation to the UPPER
functions in the query. So this will table scan. I did not see why a
table scan with a trailing wildcard would be more than a minute slower
than this.
It should come back in milliseconds ...
just tested a similar query here on my MacBook Pro:
didev=# select contact_id from contact where lower(email) like
lower('[EMAIL PROTECTED]');
contact_id
------------
...
(14 rows)
Time: 1,058 ms
I have my indexes on lower, not on upper (PostgreSQL). For more rows
it
might be a bit slower ...
select contact_id from contact where lower(email) like lower('gu%');
...
(2974 rows)
Time: 14,406 ms
So, I'd say in either case is something wrong. Maybe there is an index
missing or the index is not set up for being case insensitive? The
table I
used has only two millions rows, but I have only a MacBook Pro with
the
internal 5k4 harddrive, so any decent deployment system should be WAY
faster. More than a minute sounds like a full table scan on a
machine with
slow I/O and 12s sounds like caches kicking in.
Can anyone explain why this might be from an EOF perspective?
Not immediately. But I would just avoid this whole issue and make
the
column collate case insensitively:
ALTER COLUMN "ORDERS"."EMAIL_ADDRESS" TO COLLATE
INFORMATION_SCHEMA.CASE_INSENSITIVE;
Both will be much faster.
As it will use the index. How is FrontBase than handling case
sensitive
searches (if they are needed)?
Unless there is a need for case sensitive ones, I just make the column
collate case insensitively. Of course, you still want an index on
that column.
Btw: can you have different indexes on the
same column in FrontBase, one case sensitive one not? That would
help both
cases, if the planner is using the correct one.
I think you can, but I have not run into the case where both types of
searches were desired.
Chuck
--
Chuck Hill Senior Consultant / VP Development
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [EMAIL PROTECTED]
