Re: Question about search performance
Re: Question about search performance
- Subject: Re: Question about search performance
- From: Ken Anderson <email@hidden>
- Date: Wed, 17 Sep 2008 15:10:53 -0400
Joshua,
I will bet you plenty of money that it will!
Ken
On Sep 17, 2008, at 2:58 PM, Joshua Dubey wrote:
Actually, I am running on Oracle. Not sure where this Frontbase
notion started. :-)
I will look into Ken's suggestion below, that might do the trick.
-Josh
On Sep 17, 2008, at 8:00 AM, George Domurot wrote:
Joshua, some DB's "like" results are the same as their case
insensitive variety. I'm not sure about FrontBase, but it may be
worth a try.
-G
On Sep 17, 2008, at 6:42 AM, Ken Anderson wrote:
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
_______________________________________________
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
_______________________________________________
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