• 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: Ken Anderson <email@hidden>
  • Date: Wed, 17 Sep 2008 09:42:37 -0400

I had a similar problem with Oracle a while ago. Queries run from the command line or a SQL editor took far less time than ones generated by EOF. In the end, it turned out that the problem was related to using parameter passing (since EOF always parameterized the queries... it was really comparing apples to oranges). For Oracle, I had to do this to solve the problem:

-Doracle.jdbc.V8Compatible=true

Anyway, I realize on FrontBase this doesn't help you very much, but you might want to consider that the adaptor is sending the query in a different way than you would manually...

Ken

On Sep 16, 2008, at 3:31 PM, Joshua Dubey wrote:

Hi Chuck,

I am timing the app queries based on what EOAdaptorDebugEnabled shows. In each case, only one row is returned, so I'm guessing the difference is not caused by any EOF overhead that might be going on.

For the runs against database, I am timing thusly:

Timing start;
<query as pasted from EOAdaptorDebugEnabled logs>;
timing stop;

Just ran the above of again, with freshly cut and pasted SQL to be sure, same result.

Regardless, I will see about having the schema change done.

Thanks for the prompt response.

Josh

On Sep 16, 2008, at 10:40 AM, Chuck Hill wrote:


On Sep 16, 2008, at 10:35 AM, Joshua Dubey wrote:

Hi,

I am seeing some search performance behavior I don't understand and am hoping someone can provide some insight.

We have an application that searches a very large table (millions of rows) for orders. We have options to search with wild cards, and and do case insensitive searches. Typical generated SQL for case insensitive wild card search looks like this:

SELECT <bunch of attributes>, from ORDERS T0 WHERE UPPER(T0.EMAIL_ADDRESS) LIKE UPPER('email@hidden%') ESCAPE '\;

This query can take up to a minute and a half. It takes the same amount of time whether the query is run directly against our database or using our application. This is what I would expect.


A case insensitive non wild card search produces SQL below

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?



However, running it through the app still takes about a minute and a half.


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.


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

_______________________________________________ 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: George Domurot <email@hidden>
    • Re: Question about search performance
      • From: Guido Neitzer <email@hidden>
References: 
 >Question about search performance (From: Joshua Dubey <email@hidden>)
 >Re: Question about search performance (From: Chuck Hill <email@hidden>)
 >Re: Question about search performance (From: Joshua Dubey <email@hidden>)

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