db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrew Bruno <andrew.br...@gmail.com>
Subject Re: SELECT query takes 5 secs, what can I do?
Date Fri, 18 Sep 2009 01:30:09 GMT
agree! thx

On Fri, Sep 18, 2009 at 11:07 AM, Brett Wooldridge
<brett.wooldridge@gmail.com> wrote:
> Glad the updated statistics worked for you.  The specificity of messageId
> seemed extremely high, and given only 1 million rows should have been
> returning sub-second results.  You don't need indexes on either of the other
> two fields, they won't improve performance and will slow insertion speed.
> Brett
>
> On Fri, Sep 18, 2009 at 9:31 AM, Andrew Bruno <andrew.bruno@gmail.com>
> wrote:
>>
>> Hey all,
>>
>> Sorry been flat chat for a while, but I gotta say that the query
>> improved dramatically when the stats updated.
>>
>> When I created the index, I didnt wait for the stats to be updated.
>>
>> I came back the next day, and the query was running in sub seconds.
>> Now ExchangeSync can run well again on this 3G database.
>>
>> I want to thank everyone for the awesome support, including some of
>> those left or right field ideas!
>>
>> Cheers
>> Andrew
>>
>>
>> On Thu, Sep 17, 2009 at 7:26 PM, Knut Anders Hatlen <Knut.Hatlen@sun.com>
>> wrote:
>> > Brett Wooldridge <brett.wooldridge@gmail.com> writes:
>> >
>> >> You can force Derby to update statistics with this command:
>> >>
>> >> alter table <table-name> compress [sequential]
>> >>
>> >> Note this command itself might take a long time -- dozens of minutes --
>> >> but in
>> >> a system like yours you could get away with running it once or month or
>> >> so at
>> >> some off-peak time.
>> >
>> > In Derby 10.5 you have a cheaper way of updating the index cardinality
>> > statistics. This statement will update the statistics for all the
>> > indexes on columns in MYSCHEMA.MYTABLE without doing an expensive
>> > compress:
>> >
>> >  CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('MYSCHEMA', 'MYTABLE', NULL)
>> >
>> > http://db.apache.org/derby/docs/10.5/ref/rrefupdatestatsproc.html
>> >
>> > --
>> > Knut Anders
>> >
>
>

Mime
View raw message