db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject RE: Performance problem with derby
Date Thu, 01 Sep 2011 11:59:18 GMT
Yes, Squirrel does strip away the comments.  I have had this problem in the past. 

-----Original Message-----
From: Knut Anders Hatlen [mailto:knut.hatlen@oracle.com] 
Sent: Thursday, September 01, 2011 5:04 AM
To: derby-dev@db.apache.org
Subject: Re: Performance problem with derby

mogoye <sylvain.roulet@eloquant.com> writes:

> Mike Matrigali wrote:
>> 
>> Given the Sort it looks like index is not being used to avoid sorts.
>> I am not sure why that is the case.  Does adding an index on just
>> CALLSTART help or not?
>> 
>
> I try that but I've got the same results.
>
>
>
> Mike Matrigali wrote:
>> 
>> Another option that might help is you can give derby hints in your query
>> to force it to use the index.
>> 
>> So in summary I would suggest doing the following in order:
>> 1) run compress on the base table, this will make sure all the optimizer 
>> statistics are up to date, and retry to see if that helps.
>> 2) create index on just CALLSTART and retry query to see if that helps.
>> 3) drop new index and add hint to force your existing (clientid, 
>> callstart) index and see if that helps.
>> 
>> In all cases if you want feedback on the above, including the queryplan 
>> as you have done below will help.
>> 
>
> I found in debry optimisation guide a way to force index usage.
> SELECT * FROM CALLCDRACD --DERBY-PROPERTIES index=IND_CALLSTART WHERE
> clientId='ERDF' ORDER BY CALLSTART OFFSET 10 ROWS FETCH NEXT 50 ROWS ONLY;
>
> But when I execute this on SQuirel the executed request is only SELECT *
> FROM CALLCDRACD
>
> When I try to execute it from ij I've got the following error : 
> ERROR XCY04: Invalid syntax for optimizer overrides. The syntax should be --
> DERBY-PROPERTIES propertyName = value [, propertyName = value]*
>
> Is my command wrong ?

You need a newline after the optimizer directive, since "--" starts a
single-line comment. Something like:

SELECT * FROM CALLCDRACD --DERBY-PROPERTIES index=IND_CALLSTART
WHERE clientId='ERDF' ORDER BY CALLSTART OFFSET 10 ROWS FETCH NEXT 50 ROWS ONLY;

Your observations may indicate that SQuirreL stripped away the entire
comment, including the optimizer directive, before it passed the SQL
text to Derby, so it might not work as expected even with the newline.
But it should work in ij.

Hope this helps,

-- 
Knut Anders



Mime
View raw message