db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: Query optimization
Date Fri, 17 Jul 2009 09:54:37 GMT
kashyup <varun@quantum4d.com> writes:

> Hello All,
> I have a certain query and just want feedback/suggestions for the difference
> in Derby and Oracle.
>
> Query Time
> 2-3 secs in Embedded DB  (using SQuirrel)
> .2 secs in Oralce (using Oracle SQL Developer)
[...]
> THE QUERY
>
> select hentity0_.endpointentity0, hentity1_.*
> from ENTITY hentity0_, ENTITY hentity1_ 
> where hentity0_.E in ('T', 'R') 
> and hentity1_.E in ('T', 'R') 
> and hentity0_.endpointentity1=hentity1_.entity_global_id 
> and hentity0_.rtype='Metabase/S:default/R:schema.DisplayEntityRelType' 
> and (hentity0_.endpointentity0 in (a list of 500 ENTITY_GLOBAL_IDs))
>
> Some Additional Info:
>
> Selectivity (unique/total rec.) of columns:
> rtype - .002 
> endpointentity0 - .24
> endpointentity1 - .38
>
> Does the Indexes looks good? or do I need some additional indexing?

Hi Kashyup,

Derby sometimes has problems with outdated selectivity/cardinality
statistics on non-unique indexes. So the first thing I would have tried,
was to execute this statement and see if it helps:

CALL SYSCS_UTIL.SYSCS_UPDATE_STATISTICS('YOURSCHEMA', 'ENTITY', NULL)

There's also a chapter called "Selectivity and cardinality statistics"
in the tuning guide dedicated to this topic -
http://db.apache.org/derby/docs/10.5/tuning/.

If that doesn't help, the tuning guide also has a section about getting
and analyzing the statement execution plan, which may help tracking down
the issue.

-- 
Knut Anders

Mime
View raw message