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/
