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 23:15:29 GMT
Hi Mike,

after mulling over the feedback, I failed to explain my yellow rubber duck
why I can't introduce some new tables here to avoid the pathologically long
OR statements that match main records against fields.

Basically I had to check if a main record was under a category or any of
it's subcategories. This meant first creating a list of all categories,
then a very, very long select statement to match against all those
categories. The customer has really gone to town on creating an enormous
number of categories whereas the system was originally designed to have a
few dozen categories and hence performance broke down.

The solution was to create an extra table where each category has a map
from itself to all it's children. This way I can immediately tell if a main
record is under a category or any of the subcategories with a join
statement.

Here are the before/after numbers for PostgreSQL. As you can see the
pathologically slow Derby statement(2561ms) has been fixed.

It's also interesting to note that these optimizations do have no effect on
PostgreSQL(in the noise).

If I'm able to get this improvement to production quality(there are a few
pesky issues in the app), then I avoid the performance implosion, so even
if PostgreSQL is >5x faster, there are will then be bigger fish to fry in
the app and we avoid the deployment issues with PostgreSQL.


        PostgreSQL/ms   Derby/ms
Rows    Befores After   Before  After
13      0       0       0       1
0       5       13      0       1
1292    42      46      2561    231
757     44      46      237     223
100     16      13      194     218
121     24      20      157     212
818     46      48      197     244
85      13      11      168     212
41      17      13      135     217
17      10      17      145     223
118     23      25      156     225
21      12      9       151     213
58      17      25      139     212
1       15      13      139     226
1340    62      61      297     220
226     18      36      170     211
7       1       0       0       0
34      4       5       1       1
0       5       4       1       1
0       1       1       0       0
4809    76      69      59      55
91      5       5       1       1
61      10      5       1       1
0       3       6       0       1
0       5       7       0       0
1292    42      48      2566    222
757     45      40      238     217
100     13      17      195     221
121     20      15      175     240
818     40      45      199     217
85      20      11      169     211
41      18      11      135     209
17      10      16      143     211
118     19      16      141     213
21      9       17      153     212
58      22      24      138     217
1       12      11      137     284
1340    48      59      384     227
226     18      16      165     218
7       1       0       1       1
34      7       4       1       1
0       5       3       0       1
0       1       1       0       0
4809    73      68      53      58
91      6       5       1       1
61      6       5       1       2
0       5       4       1       1
13      0       0       1       0
11      0       0       1       0

20031   914     934     9907    6333



-- 
Øyvind Harboe - Can Zylin Consulting help on your project?
http://www.zylin.com/

Mime
View raw message