Do many of the queries share a similar structure to the query you describe in
point 5?
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.
Is the target system running embedded or client/server, derby should best
perform embedded,
though the number of rows being returned is pretty small so may not matter.
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.
Is it option to change the query? Derby does have 2 known issues when it comes
to queries
of this form:
1) derby compile time is often longer than other systems. priority was given to
optimizing execution
hoping that usual case was either prepared statement or internally cached
statement plans. I think logStatementText
or RUNTIMESTATISTICS can provide compile vs execution time in derby.
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.
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.
On 4/13/2015 12:54 PM, Øyvind Harboe wrote:
5. There are some queries that stick out as a sore thumb below, e.g. the one which returns 1292 rows in table 1 below. I'm loathe
to reproduce the query here because it is *LONG*. It takes a few hundred arguments to match against a list of primary keys. The
SQL statement contains a few hundred of these " .. OR (t2.field = ?) OR (t2.field = ?) OR (t2.field = ?) ...". Stored queries are
not an option here because the query is generated by Java code at the Cayenne ORM level. This query can't be simplified because
the report is checking if the records are in one out of 322 specific categories where each category is a 'field' record as alluded
to above.
Table 1.
Jetty Tomcat
Rows time/ms time/ms
13 0 1
13 0 0
11 0 0
13 1 1
13 1 0
0 1 1
1292 2662 2625
757 262 240
100 188 193
121 163 152
818 190 211
85 164 174
41 132 133
17 140 139
118 134 133
21 144 143
58 273 131
1 133 136
1340 292 323
226 157 161
7 0 1
34 1 1
0 0 0
0 1 0
4809 50 52
91 2 1
61 1 1
0 0 0
0 1 0
1292 2684 2572
757 253 242
100 190 196
121 161 153
818 209 200
85 167 184
41 131 128
17 141 147
118 135 142
21 144 151
58 133 133
1 133 139
1340 294 311
226 156 161
7 1 0
34 0 0
0 0 0
0 0 0
4809 67 51
91 1 1
61 1 0
0 0 0
13 1 5
11 1 1
Øyvind Harboe - Can Zylin Consulting help on your project?
http://www.zylin.com/
--
email: Mike Matrigali - [email protected]
linkedin: https://www.linkedin.com/in/MikeMatrigali
