db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brett Wooldridge <brett.wooldri...@gmail.com>
Subject Re: SELECT query takes 5 secs, what can I do?
Date Fri, 18 Sep 2009 01:07:17 GMT
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