Mercurial > dive4elements > river

comparison artifacts/src/main/java/org/dive4elements/river/artifacts/model/FixingsOverview.java @ 6847:b0c88ac9f63b

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixing Analysis: Significantly (8-10x) improved the performance of the generation of the overview data. The data is now prefetched in batches.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sat, 17 Aug 2013 20:08:51 +0200
parents 5f52a8079625
children b55111e13acd
comparison
equal deleted inserted replaced
6846:b87b20c71407 6847:b0c88ac9f63b
26 import org.hibernate.type.StandardBasicTypes; 26 import org.hibernate.type.StandardBasicTypes;
27 27
28 import org.w3c.dom.Document; 28 import org.w3c.dom.Document;
29 import org.w3c.dom.Element; 29 import org.w3c.dom.Element;
30 30
31 31 import org.dive4elements.river.utils.BatchLoader;
32 /** Generate Fixings Table chart. */ 32
33
34 /** Generate Fixings Table overview data structure to be stored in cache. */
33 public class FixingsOverview 35 public class FixingsOverview
34 implements Serializable 36 implements Serializable
35 { 37 {
36 private static Logger log = Logger.getLogger(FixingsOverview.class); 38 private static Logger log = Logger.getLogger(FixingsOverview.class);
37 39
54 " description " + 56 " description " +
55 "FROM wsts " + 57 "FROM wsts " +
56 "WHERE" + 58 "WHERE" +
57 " river_id = :river_id AND kind = 2"; 59 " river_id = :river_id AND kind = 2";
58 60
59 /** All columns from given wst. */ 61 public static final String SQL_FIXING_COLUMNS_BATCH =
60 public static final String SQL_FIXING_COLUMNS = 62 "SELECT " +
61 "SELECT" + 63 "wc.wst_id AS wst_id," +
62 " wc.id AS wst_column_id," + 64 "wc.id AS wst_column_id," +
63 " ti.start_time AS start_time," + 65 "ti.start_time AS start_time," +
64 " wc.name AS name " + 66 "wc.name AS name " +
65 "FROM wst_columns wc" + 67 "FROM wst_columns wc " +
66 " JOIN time_intervals ti ON wc.time_interval_id = ti.id " + 68 "JOIN time_intervals ti ON wc.time_interval_id = ti.id " +
67 "WHERE" + 69 "WHERE " +
68 " wc.wst_id = :wst_id " + 70 "wc.wst_id IN ($IDS) " +
69 "ORDER BY position"; 71 "ORDER BY wc.wst_id, position";
70 72
71 public static final String SQL_FIXING_COLUMN_Q_RANGES = 73 public static final String SQL_FIXING_COLUMN_Q_RANGES_BATCH =
72 "SELECT" + 74 "SELECT " +
73 " wqr.q AS q," + 75 "wcqr.wst_column_id AS wst_column_id," +
74 " r.a AS start_km," + 76 "wqr.q AS q," +
75 " r.b AS stop_km " + 77 "r.a AS start_km," +
76 "FROM wst_column_q_ranges wcqr" + 78 "r.b AS stop_km " +
77 " JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id" + 79 "FROM wst_column_q_ranges wcqr " +
78 " JOIN ranges r ON wqr.range_id = r.id " + 80 "JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id " +
79 "WHERE" + 81 "JOIN ranges r ON wqr.range_id = r.id " +
80 " wcqr.wst_column_id = :column_id " + 82 "WHERE " +
81 "ORDER BY r.a"; 83 "wcqr.wst_column_id IN ($IDS) " +
82 84 "ORDER BY wcqr.wst_column_id, r.a";
83 public static final String SQL_FIXING_COLUMN_KM_RANGE = 85
84 "SELECT" + 86 public static final String SQL_FIXING_COLUMN_KM_RANGE_BATCH =
85 " MIN(position) AS start_km," + 87 "SELECT " +
86 " MAX(position) AS stop_km " + 88 "wst_column_id," +
87 "FROM" + 89 "MIN(position) AS start_km," +
88 " wst_column_values " + 90 "MAX(position) AS stop_km " +
89 "WHERE" + 91 "FROM " +
90 " wst_column_id = :column_id"; 92 "wst_column_values " +
91 93 "WHERE " +
94 "wst_column_id IN ($IDS) " +
95 "GROUP BY wst_column_id";
96
97 public static final class KMRangeLoader extends BatchLoader<double []> {
98
99 public KMRangeLoader(List<Integer> columns, Session session) {
100 super(columns, session, SQL_FIXING_COLUMN_KM_RANGE_BATCH);
101 }
102
103 @Override
104 protected void fill(SQLQuery query) {
105 query
106 .addScalar("wst_column_id", StandardBasicTypes.INTEGER)
107 .addScalar("start_km", StandardBasicTypes.DOUBLE)
108 .addScalar("stop_km", StandardBasicTypes.DOUBLE);
109
110 List<Object []> ranges = query.list();
111 for (Object [] r: ranges) {
112 Integer cid = (Integer)r[0];
113 double [] vs = new double [] { (Double)r[1], (Double)r[2] };
114 cache(cid, vs);
115 }
116 }
117 } // class KMRangeLoader
118
119 public static final class ColumnQRangeLoader extends BatchLoader<List<double []>> {
120
121 public ColumnQRangeLoader(List<Integer> columns, Session session) {
122 super(columns, session, SQL_FIXING_COLUMN_Q_RANGES_BATCH);
123 }
124
125 @Override
126 protected void fill(SQLQuery query) {
127 query
128 .addScalar("wst_column_id", StandardBasicTypes.INTEGER)
129 .addScalar("q", StandardBasicTypes.DOUBLE)
130 .addScalar("start_km", StandardBasicTypes.DOUBLE)
131 .addScalar("stop_km", StandardBasicTypes.DOUBLE);
132
133 int lastId = Integer.MIN_VALUE;
134 List<double []> column = new ArrayList<double []>();
135
136 List<Object []> ranges = query.list();
137 for (Object [] r: ranges) {
138 int cid = (Integer)r[0];
139
140 if (cid != lastId && !column.isEmpty()) {
141 cache(lastId, column);
142 column = new ArrayList<double []>();
143 }
144 column.add(new double [] {
145 (Double)r[1],
146 (Double)r[2],
147 (Double)r[3]
148 });
149
150 lastId = cid;
151 }
152
153 if (!column.isEmpty()) {
154 cache(lastId, column);
155 }
156 }
157 } // class ColumnQRangeLoader
158
159 /** Helper class to store data from batching fixing columns. */
160 private static final class FixColumn {
161 int columnId;
162 Date startTime;
163 String name;
164
165 FixColumn(int columnId, Date startTime, String name) {
166 this.columnId = columnId;
167 this.startTime = startTime;
168 this.name = name;
169 }
170 } // class FixColumn
171
172 public static final class FixColumnLoader extends BatchLoader<List<FixColumn>> {
173
174 public FixColumnLoader(List<Integer> columns, Session session) {
175 super(columns, session, SQL_FIXING_COLUMNS_BATCH);
176 }
177
178 @Override
179 protected void fill(SQLQuery query) {
180 query
181 .addScalar("wst_id", StandardBasicTypes.INTEGER)
182 .addScalar("wst_column_id", StandardBasicTypes.INTEGER)
183 .addScalar("start_time", StandardBasicTypes.DATE)
184 .addScalar("name", StandardBasicTypes.STRING);
185
186 int lastId = Integer.MIN_VALUE;
187 List<FixColumn> cols = new ArrayList<FixColumn>();
188
189 List<Object []> columns = query.list();
190 for (Object [] c: columns) {
191 int wid = (Integer)c[0];
192
193 if (wid != lastId && !cols.isEmpty()) {
194 cache(lastId, cols);
195 cols = new ArrayList<FixColumn>();
196 }
197 cols.add(new FixColumn(
198 (Integer)c[1],
199 (Date) c[2],
200 (String) c[3]));
201
202 lastId = wid;
203 }
204 if (!cols.isEmpty()) {
205 cache(lastId, cols);
206 }
207 }
208 } // class FixColumnLoader
92 209
93 public static class QRange extends Range { 210 public static class QRange extends Range {
94 211
95 protected double q; 212 protected double q;
96 213
253 } 370 }
254 } // for all gauges 371 } // for all gauges
255 } // for all Q ranges 372 } // for all Q ranges
256 } 373 }
257 374
258 public void loadKmRange(SQLQuery query) { 375 public void loadKmRange(KMRangeLoader loader) {
259 query.setInteger("column_id", columnId); 376
260 377 double [] range = loader.get(columnId);
261 List<Object []> kms = query.list(); 378
262 379 if (range == null) {
263 if (kms.isEmpty()) {
264 log.warn("No km range for column " + columnId + "."); 380 log.warn("No km range for column " + columnId + ".");
265 } 381 return;
266 else { 382 }
267 Object [] obj = kms.get(0); 383 start = range[0];
268 start = (Double)obj[0]; 384 end = range[1];
269 end = (Double)obj[1];
270 }
271 } 385 }
272 386
273 public void loadQRanges( 387 public void loadQRanges(
274 SQLQuery query, 388 ColumnQRangeLoader loader,
275 GaugeFinder gaugeFinder 389 GaugeFinder gaugeFinder
276 ) { 390 ) {
277 query.setInteger("column_id", columnId); 391 List<double []> qrs = loader.get(columnId);
278 List<Object []> list = query.list(); 392 if (qrs == null) {
279 393 log.warn("No q ranges found for column " + columnId);
280 List<QRange> qRanges = new ArrayList<QRange>(list.size()); 394 return;
281 395 }
282 for (Object [] row: list) { 396
283 double q = (Double)row[0]; 397 List<QRange> qRanges = new ArrayList<QRange>(qrs.size());
284 double start = (Double)row[1]; 398
285 double end = (Double)row[2]; 399 for (double [] qr: qrs) {
400 double q = qr[0];
401 double start = qr[1];
402 double end = qr[2];
403
286 QRange qRange = new QRange(start, end, q); 404 QRange qRange = new QRange(start, end, q);
287 if (qRange.clip(this)) { 405 if (qRange.clip(this)) {
288 qRanges.add(qRange); 406 qRanges.add(qRange);
289 } 407 }
290 } 408 }
312 this.wstId = wstId; 430 this.wstId = wstId;
313 this.description = description; 431 this.description = description;
314 columns = new ArrayList<Column>(); 432 columns = new ArrayList<Column>();
315 } 433 }
316 434
317 public void loadColumns(SQLQuery query) { 435 public void allColumnIds(List<Integer> cIds) {
318 query.setInteger("wst_id", wstId);
319 List<Object []> list = query.list();
320 for (Object [] row: list) {
321 int columnId = (Integer)row[0];
322 Date startTime = (Date) row[1];
323 String name = (String) row[2];
324 columns.add(new Column(columnId, startTime, name));
325 }
326 }
327
328 public void loadColumnsKmRange(SQLQuery query) {
329 for (Column column: columns) { 436 for (Column column: columns) {
330 column.loadKmRange(query); 437 cIds.add(column.columnId);
438 }
439 }
440
441 public void loadColumns(FixColumnLoader loader) {
442 List<FixColumn> fcs = loader.get(wstId);
443 if (fcs == null) {
444 log.warn("No columns for wst " + wstId);
445 return;
446 }
447 for (FixColumn fc: fcs) {
448 columns.add(new Column(fc.columnId, fc.startTime, fc.name));
449 }
450 }
451
452 public void loadColumnsKmRange(KMRangeLoader loader) {
453 for (Column column: columns) {
454 column.loadKmRange(loader);
331 } 455 }
332 } 456 }
333 457
334 public void adjustExtent(Range extent) { 458 public void adjustExtent(Range extent) {
335 for (Column column: columns) { 459 for (Column column: columns) {
336 extent.extend(column); 460 extent.extend(column);
337 } 461 }
338 } 462 }
339 463
340 public void loadColumnsQRanges( 464 public void loadColumnsQRanges(
341 SQLQuery query, 465 ColumnQRangeLoader loader,
342 GaugeFinder gaugeFinder 466 GaugeFinder gaugeFinder
343 ) { 467 ) {
344 for (Column column: columns) { 468 for (Column column: columns) {
345 column.loadQRanges(query, gaugeFinder); 469 column.loadQRanges(loader, gaugeFinder);
346 } 470 }
347 } 471 }
348 472
349 /** 473 /**
350 * @param allColumns[out] Columns will be put here. 474 * @param allColumns[out] Columns will be put here.
425 fixings.add(fixing); 549 fixings.add(fixing);
426 } 550 }
427 } 551 }
428 552
429 protected void loadFixingsColumns(Session session) { 553 protected void loadFixingsColumns(Session session) {
430 SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMNS) 554
431 .addScalar("wst_column_id", StandardBasicTypes.INTEGER) 555 FixColumnLoader loader = new FixColumnLoader(
432 .addScalar("start_time", StandardBasicTypes.DATE) 556 allFixingIds(),
433 .addScalar("name", StandardBasicTypes.STRING); 557 session);
434 558
435 for (Fixing fixing: fixings) { 559 for (Fixing fixing: fixings) {
436 fixing.loadColumns(query); 560 fixing.loadColumns(loader);
437 } 561 }
562 }
563
564 protected List<Integer> allFixingIds() {
565 List<Integer> ids = new ArrayList<Integer>(fixings.size());
566 for (Fixing fixing: fixings) {
567 ids.add(fixing.getId());
568 }
569 return ids;
570 }
571
572 protected List<Integer> allColumnIds() {
573 List<Integer> cIds = new ArrayList<Integer>();
574 for (Fixing fixing: fixings) {
575 fixing.allColumnIds(cIds);
576 }
577 return cIds;
438 } 578 }
439 579
440 protected void loadFixingsColumnsKmRange(Session session) { 580 protected void loadFixingsColumnsKmRange(Session session) {
441 SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_KM_RANGE) 581
442 .addScalar("start_km", StandardBasicTypes.DOUBLE) 582 KMRangeLoader loader = new KMRangeLoader(
443 .addScalar("stop_km", StandardBasicTypes.DOUBLE); 583 allColumnIds(),
584 session);
444 585
445 for (Fixing fixing: fixings) { 586 for (Fixing fixing: fixings) {
446 fixing.loadColumnsKmRange(query); 587 fixing.loadColumnsKmRange(loader);
447 } 588 }
448 } 589 }
449 590
450 protected void loadFixingsColumnsQRanges( 591 protected void loadFixingsColumnsQRanges(
451 Session session, 592 Session session,
452 GaugeFinder gaugeFinder 593 GaugeFinder gaugeFinder
453 ) { 594 ) {
454 SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_Q_RANGES) 595
455 .addScalar("q", StandardBasicTypes.DOUBLE) 596 ColumnQRangeLoader loader = new ColumnQRangeLoader(
456 .addScalar("start_km", StandardBasicTypes.DOUBLE) 597 allColumnIds(),
457 .addScalar("stop_km", StandardBasicTypes.DOUBLE); 598 session);
458 599
459 for (Fixing fixing: fixings) { 600 for (Fixing fixing: fixings) {
460 fixing.loadColumnsQRanges(query, gaugeFinder); 601 fixing.loadColumnsQRanges(loader, gaugeFinder);
461 } 602 }
462 } 603 }
463 604
464 protected void adjustExtent() { 605 protected void adjustExtent() {
465 for (Fixing fixing: fixings) { 606 for (Fixing fixing: fixings) {
http://dive4elements.wald.intevation.org