• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Question about search performance
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Question about search performance


  • Subject: Re: Question about search performance
  • From: Chuck Hill <email@hidden>
  • Date: Tue, 16 Sep 2008 12:22:48 -0700


On Sep 16, 2008, at 12:00 PM, Guido Neitzer wrote:

On 16.09.08 11:40, "Chuck Hill" <email@hidden> wrote:

SELECT <bunch of attributes>, from ORDERS T0 WHERE
UPPER(T0.EMAIL_ADDRESS) LIKE UPPER('email@hidden') 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('guido@%');
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@hidden) Help/Unsubscribe/Update your Subscription: This email sent to email@hidden
  • Follow-Ups:
    • Re: Question about search performance
      • From: Guido Neitzer <email@hidden>
References: 
 >Re: Question about search performance (From: Guido Neitzer <email@hidden>)

  • Prev by Date: Re: Question about search performance
  • Next by Date: Re: Question about search performance
  • Previous by thread: Re: Question about search performance
  • Next by thread: Re: Question about search performance
  • Index(es):
    • Date
    • Thread