Mercurial > dive4elements > river
diff artifacts/src/main/java/org/dive4elements/river/artifacts/model/FixingsOverview.java @ 6847:b0c88ac9f63b
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 |
line wrap: on
line diff
--- a/artifacts/src/main/java/org/dive4elements/river/artifacts/model/FixingsOverview.java Sat Aug 17 12:17:42 2013 +0200 +++ b/artifacts/src/main/java/org/dive4elements/river/artifacts/model/FixingsOverview.java Sat Aug 17 20:08:51 2013 +0200 @@ -28,8 +28,10 @@ import org.w3c.dom.Document; import org.w3c.dom.Element; +import org.dive4elements.river.utils.BatchLoader; -/** Generate Fixings Table chart. */ + +/** Generate Fixings Table overview data structure to be stored in cache. */ public class FixingsOverview implements Serializable { @@ -56,39 +58,154 @@ "WHERE" + " river_id = :river_id AND kind = 2"; - /** All columns from given wst. */ - public static final String SQL_FIXING_COLUMNS = - "SELECT" + - " wc.id AS wst_column_id," + - " ti.start_time AS start_time," + - " wc.name AS name " + - "FROM wst_columns wc" + - " JOIN time_intervals ti ON wc.time_interval_id = ti.id " + - "WHERE" + - " wc.wst_id = :wst_id " + - "ORDER BY position"; + public static final String SQL_FIXING_COLUMNS_BATCH = + "SELECT " + + "wc.wst_id AS wst_id," + + "wc.id AS wst_column_id," + + "ti.start_time AS start_time," + + "wc.name AS name " + + "FROM wst_columns wc " + + "JOIN time_intervals ti ON wc.time_interval_id = ti.id " + + "WHERE " + + "wc.wst_id IN ($IDS) " + + "ORDER BY wc.wst_id, position"; - public static final String SQL_FIXING_COLUMN_Q_RANGES = - "SELECT" + - " wqr.q AS q," + - " r.a AS start_km," + - " r.b AS stop_km " + - "FROM wst_column_q_ranges wcqr" + - " JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id" + - " JOIN ranges r ON wqr.range_id = r.id " + - "WHERE" + - " wcqr.wst_column_id = :column_id " + - "ORDER BY r.a"; + public static final String SQL_FIXING_COLUMN_Q_RANGES_BATCH = + "SELECT " + + "wcqr.wst_column_id AS wst_column_id," + + "wqr.q AS q," + + "r.a AS start_km," + + "r.b AS stop_km " + + "FROM wst_column_q_ranges wcqr " + + "JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id " + + "JOIN ranges r ON wqr.range_id = r.id " + + "WHERE " + + "wcqr.wst_column_id IN ($IDS) " + + "ORDER BY wcqr.wst_column_id, r.a"; - public static final String SQL_FIXING_COLUMN_KM_RANGE = - "SELECT" + - " MIN(position) AS start_km," + - " MAX(position) AS stop_km " + - "FROM" + - " wst_column_values " + - "WHERE" + - " wst_column_id = :column_id"; + public static final String SQL_FIXING_COLUMN_KM_RANGE_BATCH = + "SELECT " + + "wst_column_id," + + "MIN(position) AS start_km," + + "MAX(position) AS stop_km " + + "FROM " + + "wst_column_values " + + "WHERE " + + "wst_column_id IN ($IDS) " + + "GROUP BY wst_column_id"; + public static final class KMRangeLoader extends BatchLoader<double []> { + + public KMRangeLoader(List<Integer> columns, Session session) { + super(columns, session, SQL_FIXING_COLUMN_KM_RANGE_BATCH); + } + + @Override + protected void fill(SQLQuery query) { + query + .addScalar("wst_column_id", StandardBasicTypes.INTEGER) + .addScalar("start_km", StandardBasicTypes.DOUBLE) + .addScalar("stop_km", StandardBasicTypes.DOUBLE); + + List<Object []> ranges = query.list(); + for (Object [] r: ranges) { + Integer cid = (Integer)r[0]; + double [] vs = new double [] { (Double)r[1], (Double)r[2] }; + cache(cid, vs); + } + } + } // class KMRangeLoader + + public static final class ColumnQRangeLoader extends BatchLoader<List<double []>> { + + public ColumnQRangeLoader(List<Integer> columns, Session session) { + super(columns, session, SQL_FIXING_COLUMN_Q_RANGES_BATCH); + } + + @Override + protected void fill(SQLQuery query) { + query + .addScalar("wst_column_id", StandardBasicTypes.INTEGER) + .addScalar("q", StandardBasicTypes.DOUBLE) + .addScalar("start_km", StandardBasicTypes.DOUBLE) + .addScalar("stop_km", StandardBasicTypes.DOUBLE); + + int lastId = Integer.MIN_VALUE; + List<double []> column = new ArrayList<double []>(); + + List<Object []> ranges = query.list(); + for (Object [] r: ranges) { + int cid = (Integer)r[0]; + + if (cid != lastId && !column.isEmpty()) { + cache(lastId, column); + column = new ArrayList<double []>(); + } + column.add(new double [] { + (Double)r[1], + (Double)r[2], + (Double)r[3] + }); + + lastId = cid; + } + + if (!column.isEmpty()) { + cache(lastId, column); + } + } + } // class ColumnQRangeLoader + + /** Helper class to store data from batching fixing columns. */ + private static final class FixColumn { + int columnId; + Date startTime; + String name; + + FixColumn(int columnId, Date startTime, String name) { + this.columnId = columnId; + this.startTime = startTime; + this.name = name; + } + } // class FixColumn + + public static final class FixColumnLoader extends BatchLoader<List<FixColumn>> { + + public FixColumnLoader(List<Integer> columns, Session session) { + super(columns, session, SQL_FIXING_COLUMNS_BATCH); + } + + @Override + protected void fill(SQLQuery query) { + query + .addScalar("wst_id", StandardBasicTypes.INTEGER) + .addScalar("wst_column_id", StandardBasicTypes.INTEGER) + .addScalar("start_time", StandardBasicTypes.DATE) + .addScalar("name", StandardBasicTypes.STRING); + + int lastId = Integer.MIN_VALUE; + List<FixColumn> cols = new ArrayList<FixColumn>(); + + List<Object []> columns = query.list(); + for (Object [] c: columns) { + int wid = (Integer)c[0]; + + if (wid != lastId && !cols.isEmpty()) { + cache(lastId, cols); + cols = new ArrayList<FixColumn>(); + } + cols.add(new FixColumn( + (Integer)c[1], + (Date) c[2], + (String) c[3])); + + lastId = wid; + } + if (!cols.isEmpty()) { + cache(lastId, cols); + } + } + } // class FixColumnLoader public static class QRange extends Range { @@ -255,34 +372,35 @@ } // for all Q ranges } - public void loadKmRange(SQLQuery query) { - query.setInteger("column_id", columnId); - - List<Object []> kms = query.list(); + public void loadKmRange(KMRangeLoader loader) { - if (kms.isEmpty()) { + double [] range = loader.get(columnId); + + if (range == null) { log.warn("No km range for column " + columnId + "."); + return; } - else { - Object [] obj = kms.get(0); - start = (Double)obj[0]; - end = (Double)obj[1]; - } + start = range[0]; + end = range[1]; } public void loadQRanges( - SQLQuery query, - GaugeFinder gaugeFinder + ColumnQRangeLoader loader, + GaugeFinder gaugeFinder ) { - query.setInteger("column_id", columnId); - List<Object []> list = query.list(); + List<double []> qrs = loader.get(columnId); + if (qrs == null) { + log.warn("No q ranges found for column " + columnId); + return; + } - List<QRange> qRanges = new ArrayList<QRange>(list.size()); + List<QRange> qRanges = new ArrayList<QRange>(qrs.size()); - for (Object [] row: list) { - double q = (Double)row[0]; - double start = (Double)row[1]; - double end = (Double)row[2]; + for (double [] qr: qrs) { + double q = qr[0]; + double start = qr[1]; + double end = qr[2]; + QRange qRange = new QRange(start, end, q); if (qRange.clip(this)) { qRanges.add(qRange); @@ -314,20 +432,26 @@ columns = new ArrayList<Column>(); } - public void loadColumns(SQLQuery query) { - query.setInteger("wst_id", wstId); - List<Object []> list = query.list(); - for (Object [] row: list) { - int columnId = (Integer)row[0]; - Date startTime = (Date) row[1]; - String name = (String) row[2]; - columns.add(new Column(columnId, startTime, name)); + public void allColumnIds(List<Integer> cIds) { + for (Column column: columns) { + cIds.add(column.columnId); } } - public void loadColumnsKmRange(SQLQuery query) { + public void loadColumns(FixColumnLoader loader) { + List<FixColumn> fcs = loader.get(wstId); + if (fcs == null) { + log.warn("No columns for wst " + wstId); + return; + } + for (FixColumn fc: fcs) { + columns.add(new Column(fc.columnId, fc.startTime, fc.name)); + } + } + + public void loadColumnsKmRange(KMRangeLoader loader) { for (Column column: columns) { - column.loadKmRange(query); + column.loadKmRange(loader); } } @@ -338,11 +462,11 @@ } public void loadColumnsQRanges( - SQLQuery query, - GaugeFinder gaugeFinder + ColumnQRangeLoader loader, + GaugeFinder gaugeFinder ) { for (Column column: columns) { - column.loadQRanges(query, gaugeFinder); + column.loadQRanges(loader, gaugeFinder); } } @@ -427,23 +551,40 @@ } protected void loadFixingsColumns(Session session) { - SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMNS) - .addScalar("wst_column_id", StandardBasicTypes.INTEGER) - .addScalar("start_time", StandardBasicTypes.DATE) - .addScalar("name", StandardBasicTypes.STRING); + + FixColumnLoader loader = new FixColumnLoader( + allFixingIds(), + session); for (Fixing fixing: fixings) { - fixing.loadColumns(query); + fixing.loadColumns(loader); } } + protected List<Integer> allFixingIds() { + List<Integer> ids = new ArrayList<Integer>(fixings.size()); + for (Fixing fixing: fixings) { + ids.add(fixing.getId()); + } + return ids; + } + + protected List<Integer> allColumnIds() { + List<Integer> cIds = new ArrayList<Integer>(); + for (Fixing fixing: fixings) { + fixing.allColumnIds(cIds); + } + return cIds; + } + protected void loadFixingsColumnsKmRange(Session session) { - SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_KM_RANGE) - .addScalar("start_km", StandardBasicTypes.DOUBLE) - .addScalar("stop_km", StandardBasicTypes.DOUBLE); + + KMRangeLoader loader = new KMRangeLoader( + allColumnIds(), + session); for (Fixing fixing: fixings) { - fixing.loadColumnsKmRange(query); + fixing.loadColumnsKmRange(loader); } } @@ -451,13 +592,13 @@ Session session, GaugeFinder gaugeFinder ) { - SQLQuery query = session.createSQLQuery(SQL_FIXING_COLUMN_Q_RANGES) - .addScalar("q", StandardBasicTypes.DOUBLE) - .addScalar("start_km", StandardBasicTypes.DOUBLE) - .addScalar("stop_km", StandardBasicTypes.DOUBLE); + + ColumnQRangeLoader loader = new ColumnQRangeLoader( + allColumnIds(), + session); for (Fixing fixing: fixings) { - fixing.loadColumnsQRanges(query, gaugeFinder); + fixing.loadColumnsQRanges(loader, gaugeFinder); } }