Menu
▾
▴
[Hyperic-hq-discuss] hq r12889 - in branches/HQ_4_1/src/org/hyperic: hibernate/dialect hq/measurement/server/session
|
From: <sc...@hy...> - 2009-03-27 19:34:59
|
Author: scottmf Date: 2009-03-27 12:34:48 -0700 (Fri, 27 Mar 2009) New Revision: 12889 URL: http://svn.hyperic.org/?view=rev&root=Hyperic+HQ&revision=12889 Modified: branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialect.java branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialectUtil.java branches/HQ_4_1/src/org/hyperic/hibernate/dialect/MySQL5InnoDBDialect.java branches/HQ_4_1/src/org/hyperic/hibernate/dialect/Oracle9Dialect.java branches/HQ_4_1/src/org/hyperic/hibernate/dialect/PostgreSQLDialect.java branches/HQ_4_1/src/org/hyperic/hq/measurement/server/session/DataManagerEJBImpl.java Log: [HHQ-2906] [HHQ-1474] backed out r12216, sql changes made the problem worse. Modified: branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialect.java =================================================================== --- branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialect.java 2009-03-27 19:20:07 UTC (rev 12888) +++ branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialect.java 2009-03-27 19:34:48 UTC (rev 12889) @@ -112,6 +112,16 @@ long begin, long end, String table) throws SQLException; + public Map getAggData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException; + + public Map getCountData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException; + /** * If true, tells the ComboGenerator to use the SequenceGenerator, else * uses the MultipleHiLoPerTableGenerator Modified: branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialectUtil.java =================================================================== --- branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialectUtil.java 2009-03-27 19:20:07 UTC (rev 12888) +++ branches/HQ_4_1/src/org/hyperic/hibernate/dialect/HQDialectUtil.java 2009-03-27 19:34:48 UTC (rev 12889) @@ -77,6 +77,7 @@ } public static Map getAggData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, long begin, long end, String table) throws SQLException { // Keep track of the "last" reported time @@ -85,12 +86,20 @@ ResultSet rs = null; PreparedStatement astmt = null; StopWatch timer = new StopWatch(); + + StringBuffer iidsConj = new StringBuffer( + DBUtil.composeConjunctions("instance_id", iids.length)); + DBUtil.replacePlaceHolders(iidsConj, iids); + + StringBuffer tidsConj = new StringBuffer( + DBUtil.composeConjunctions("template_id", tids.length)); + DBUtil.replacePlaceHolders(tidsConj, tids); final String aggregateSQL = "SELECT template_id," + minMax + " MAX(timestamp) " + " FROM " + table + "," + TAB_MEAS + - " WHERE timestamp BETWEEN ? AND ? AND measurement_id = id " + - " GROUP BY template_id"; + " WHERE timestamp BETWEEN ? AND ? AND measurement_id = id AND " + + iidsConj + " AND " + tidsConj + " GROUP BY template_id"; try { // Prepare aggregate SQL @@ -133,15 +142,25 @@ } public static Map getCountData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, long begin, long end, String table) throws SQLException { ResultSet rs = null; PreparedStatement astmt = null; StopWatch timer = new StopWatch(); + StringBuffer iidsConj = new StringBuffer( + DBUtil.composeConjunctions("instance_id", iids.length)); + DBUtil.replacePlaceHolders(iidsConj, iids); + + StringBuffer tidsConj = new StringBuffer( + DBUtil.composeConjunctions("template_id", tids.length)); + DBUtil.replacePlaceHolders(tidsConj, tids); + final String countSQL = "SELECT template_id, COUNT(id) FROM " + TAB_MEAS + - " WHERE EXISTS (SELECT 1 FROM " + table + + " WHERE " + iidsConj + " AND " + tidsConj + + " AND EXISTS (SELECT measurement_id FROM " + table + " WHERE timestamp BETWEEN ? AND ? AND measurement_id = id) " + " GROUP BY template_id"; try { @@ -193,8 +212,10 @@ DBUtil.replacePlaceHolders(iidsConj, iids); final String lastSQL = - "SELECT value FROM " + table + ", " + TAB_MEAS + - " WHERE template_id = ? AND timestamp = ? AND id = measurement_id"; + "SELECT value FROM " + table + ", " + + "(SELECT id FROM " + TAB_MEAS + + " WHERE template_id = ? AND " + iidsConj + ") ids " + + "WHERE id = measurement_id AND timestamp = ?"; for (Iterator it = lastMap.entrySet().iterator(); it.hasNext(); ) { Map.Entry entry = (Map.Entry) it.next(); @@ -203,8 +224,8 @@ Long lastTime = (Long) entry.getValue(); // Now get the last timestamp - if (_log.isDebugEnabled()) { - _log.debug("getAggregateData() for tid=" + tid + " lastTime=" + + if (_log.isTraceEnabled()) { + _log.trace("getAggregateData() for tid=" + tid + " lastTime=" + lastTime + ": " + lastSQL); } @@ -232,9 +253,9 @@ DBUtil.closeResultSet(logCtx, rs); DBUtil.closeStatement(logCtx, lstmt); } - if (_log.isDebugEnabled()) { - _log.debug("getAggregateData(): Statement query elapsed " + - "time: " + timer); + if (_log.isTraceEnabled()) { + _log.trace("getAggregateData(): Statement query elapsed " + + "time: " + timer.getElapsed()); } } return resMap; Modified: branches/HQ_4_1/src/org/hyperic/hibernate/dialect/MySQL5InnoDBDialect.java =================================================================== --- branches/HQ_4_1/src/org/hyperic/hibernate/dialect/MySQL5InnoDBDialect.java 2009-03-27 19:20:07 UTC (rev 12888) +++ branches/HQ_4_1/src/org/hyperic/hibernate/dialect/MySQL5InnoDBDialect.java 2009-03-27 19:34:48 UTC (rev 12889) @@ -276,6 +276,15 @@ return rtn; } + public Map getCountData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException + { + return HQDialectUtil.getCountData(conn, minMax, resMap, tids, iids, + begin, end, table); + } + public Map getAggData(Connection conn, String minMax, Map resMap, Integer[] tids, Integer[] iids, long begin, long end, String table) Modified: branches/HQ_4_1/src/org/hyperic/hibernate/dialect/Oracle9Dialect.java =================================================================== --- branches/HQ_4_1/src/org/hyperic/hibernate/dialect/Oracle9Dialect.java 2009-03-27 19:20:07 UTC (rev 12888) +++ branches/HQ_4_1/src/org/hyperic/hibernate/dialect/Oracle9Dialect.java 2009-03-27 19:34:48 UTC (rev 12889) @@ -132,6 +132,24 @@ iids, begin, end, table); } + public Map getAggData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException + { + return HQDialectUtil.getAggData(conn, minMax, resMap, tids, + iids, begin, end, table); + } + + public Map getCountData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException + { + return HQDialectUtil.getCountData(conn, minMax, resMap, tids, iids, + begin, end, table); + } + public boolean usesSequenceGenerator() { return true; } Modified: branches/HQ_4_1/src/org/hyperic/hibernate/dialect/PostgreSQLDialect.java =================================================================== --- branches/HQ_4_1/src/org/hyperic/hibernate/dialect/PostgreSQLDialect.java 2009-03-27 19:20:07 UTC (rev 12888) +++ branches/HQ_4_1/src/org/hyperic/hibernate/dialect/PostgreSQLDialect.java 2009-03-27 19:34:48 UTC (rev 12889) @@ -126,6 +126,24 @@ iids, begin, end, table); } + public Map getAggData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException + { + return HQDialectUtil.getAggData(conn, minMax, resMap, tids, + iids, begin, end, table); + } + + public Map getCountData(Connection conn, String minMax, Map resMap, + Integer[] tids, Integer[] iids, + long begin, long end, String table) + throws SQLException + { + return HQDialectUtil.getCountData(conn, minMax, resMap, tids, iids, + begin, end, table); + } + public boolean usesSequenceGenerator() { return true; } Modified: branches/HQ_4_1/src/org/hyperic/hq/measurement/server/session/DataManagerEJBImpl.java =================================================================== --- branches/HQ_4_1/src/org/hyperic/hq/measurement/server/session/DataManagerEJBImpl.java 2009-03-27 19:20:07 UTC (rev 12888) +++ branches/HQ_4_1/src/org/hyperic/hq/measurement/server/session/DataManagerEJBImpl.java 2009-03-27 19:34:48 UTC (rev 12889) @@ -51,7 +51,6 @@ import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.hyperic.hibernate.dialect.HQDialect; -import org.hyperic.hibernate.dialect.HQDialectUtil; import org.hyperic.hibernate.Util; import org.hyperic.hq.common.SystemException; import org.hyperic.hq.common.server.session.ServerConfigManagerEJBImpl; @@ -1912,14 +1911,14 @@ try { conn = DBUtil.getConnByContext(getInitialContext(), DATASOURCE_NAME); + HQDialect dialect = Util.getHQDialect(); List measids = MeasTabManagerUtil.getMeasIds(conn, tids, iids); String table = getDataTable(begin, end, measids.toArray()); - Map lastMap = HQDialectUtil.getAggData(conn, minMax, resMap, begin, - end, table); + Map lastMap = dialect.getAggData(conn, minMax, resMap, tids, + iids, begin, end, table); if (count) { - resMap = HQDialectUtil.getCountData(conn, minMax, resMap, begin, - end, table); - HQDialect dialect = Util.getHQDialect(); + resMap = dialect.getCountData(conn, minMax, resMap, tids, iids, + begin, end, table); return dialect.getLastData(conn, minMax, resMap, lastMap, iids, begin, end, table); } |