Re: Question about search performance
Re: Question about search performance
- Subject: Re: Question about search performance
- From: Guido Neitzer <email@hidden>
- Date: Wed, 17 Sep 2008 08:06:12 -0600
- Thread-topic: Question about search performance
FrontBase doesn't do that. At least not in the versions I used.
cug
On 17.09.08 07:42, "Ken Anderson" <email@hidden> 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:
>>
m
>>
>> 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:
>
m
>
> 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