db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Øyvind Harboe <oyvind.har...@zylin.com>
Subject Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Date Wed, 15 Apr 2015 06:08:30 GMT
Hi Mike,

see answers below inline:

On Wed, Apr 15, 2015 at 2:03 AM, mike matrigali <mikemapp1@gmail.com> wrote:
> Do many of the queries share a similar structure to the query you describe
> in point 5?

Yes, but few have hundreds of arguments. When they do, these queries,
unsurprisingly, take a long time though.

> Can you give an order of magnitude on the number of expected rows in t2 from
> that query, also
> maybe an estimate of overall size of the database tables involved in all the
> queries.

We have ca. 6000 main records and there are ca. 1000 'field' records.

The report defines a group of fields that have to be enumerated
one-by-one and all cases have to be checked if they are in that group.

There are 6000 main records X 1000 fields so a potential of 6m links.
If we created direct links between all the cases and the fields(6m
isn't the end of the world), then that table would have to be dumped
anytime the fields change.

> Have you, or are you willing to look at query plans:
>     http://wiki.apache.org/db-derby/PerformanceDiagnosisTips
> If you are able to post a query plan for that query I would be willing to
> look at it to see
> if derby is doing a scan or if the optimizer is using the expected
> "multi-probe" join strategy.

I'll dig into the query plan, I've looked at the other things there,
but nothing yielded appreciable results.

> Is it option to change the query?

Our application sits on top of Apache Cayenne ORM. It is Apache
Cayenne ORM that formulates the queries. This gives us limited and
indirect control of the actual SQL queries.

Systems like PostgreSQL shine because they are more forgiving to the
situations that happen when there are many layers between the query
and the database engine.

> 2) I have seen issues with large in-lists (which also should equate to large
> number of OR's as described below), where
>     compilation cost is high, and optimizer chooses scan vs the probes.

Large in-lists crashed so I had to split it into multiple queries:


> I don't know if it is an option for you, but I have seen others change
> queries of this form into a join on either a real or
> temp table depending on if the values are actually variable or not.

Perhaps we can discover something that Apache Cayenne ORM can use?

Our application is stuck on the current version of Cayenne due to some
PITA technical debt that needs to be paid off.

Øyvind Harboe - Can Zylin Consulting help on your project?

View raw message