From: <sc...@hy...> - 2009-11-19 18:58:22
|
Author: scottmf Date: 2009-11-19 10:58:10 -0800 (Thu, 19 Nov 2009) New Revision: 13970 URL: http://svn.hyperic.org/?view=rev&root=Hyperic+HQ&revision=13970 Modified: branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/AvailabilityDataDAO.java branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/MeasurementDAO.java Log: [HHQ-3524] batching measurement queries with maximum of 1000 to avoid oracle limit and hibernate bug Modified: branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/AvailabilityDataDAO.java =================================================================== --- branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/AvailabilityDataDAO.java 2009-11-19 18:57:16 UTC (rev 13969) +++ branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/AvailabilityDataDAO.java 2009-11-19 18:58:10 UTC (rev 13970) @@ -25,12 +25,13 @@ package org.hyperic.hq.measurement.server.session; +import java.util.ArrayList; +import java.util.Arrays; import java.util.Collections; import java.util.Comparator; import java.util.HashMap; import java.util.Iterator; import java.util.List; -import java.util.ArrayList; import java.util.Map; import java.util.TreeSet; @@ -39,6 +40,8 @@ import org.hibernate.Query; import org.hibernate.type.IntegerType; import org.hyperic.dao.DAOFactory; +import org.hyperic.hibernate.Util; +import org.hyperic.hibernate.dialect.HQDialect; import org.hyperic.hq.authz.server.session.Resource; import org.hyperic.hq.dao.HibernateDAO; import org.hyperic.hq.measurement.MeasurementConstants; @@ -64,7 +67,7 @@ "(" + TOTAL_TIME + ") * rle.availVal"; public AvailabilityDataDAO(DAOFactory f) { - super(AvailabilityDataDAO.class, f); + super(AvailabilityDataRLE.class, f); } List findLastAvail(List mids, long after) { @@ -215,7 +218,9 @@ */ List getHistoricalAvails(Integer[] mids, long start, long end, boolean descending) { - String sql = new StringBuilder() + final List rtn = new ArrayList(mids.length); + final List list = Arrays.asList(mids); + final String sql = new StringBuilder() .append("FROM AvailabilityDataRLE rle") .append(" WHERE rle.availabilityDataId.measurement in (:mids)") .append(" AND rle.endtime > :startime") @@ -223,12 +228,16 @@ .append(" ORDER BY rle.availabilityDataId.measurement,") .append(" rle.availabilityDataId.startime") .append(((descending) ? " DESC" : " ASC")).toString(); - return getSession() - .createQuery(sql) - .setLong("startime", start) - .setLong("endtime", end) - .setParameterList("mids", mids, new IntegerType()) - .list(); + for (int i=0; i<list.size(); i+=BATCH_SIZE) { + final int last = Math.min(i+BATCH_SIZE, list.size()); + rtn.addAll(getSession() + .createQuery(sql) + .setLong("startime", start) + .setLong("endtime", end) + .setParameterList("mids", list.subList(i, last), new IntegerType()) + .list()); + } + return rtn; } /** Modified: branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/MeasurementDAO.java =================================================================== --- branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/MeasurementDAO.java 2009-11-19 18:57:16 UTC (rev 13969) +++ branches/HQ_4_2/src/org/hyperic/hq/measurement/server/session/MeasurementDAO.java 2009-11-19 18:58:10 UTC (rev 13970) @@ -40,7 +40,6 @@ import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.criterion.Restrictions; -import org.hibernate.dialect.Dialect; import org.hibernate.type.IntegerType; import org.hyperic.dao.DAOFactory; import org.hyperic.hibernate.Util; @@ -474,33 +473,55 @@ } List findMeasurements(Integer[] tids, Integer[] iids) { + final IntegerType iType = new IntegerType(); // sort to take advantage of query cache - final List iidList = Arrays.asList(iids); - final List tidList = Arrays.asList(tids); + final List iidList = new ArrayList(Arrays.asList(iids)); + final List tidList = new ArrayList(Arrays.asList(tids)); Collections.sort(tidList); Collections.sort(iidList); - final String sql = new StringBuilder() + final String sql = new StringBuilder(256) .append("select m from Measurement m ") .append("join m.template t ") .append("where m.instanceId in (:iids) AND t.id in (:tids)") .toString(); - return getSession().createQuery(sql) - .setParameterList("iids", iidList, new IntegerType()) - .setParameterList("tids", tidList, new IntegerType()) - .setCacheable(true) - .setCacheRegion("Measurement.findMeasurements") - .list(); + final List rtn = new ArrayList(iidList.size()); + final int batch = BATCH_SIZE/2; + for (int xx=0; xx<iidList.size(); xx+=batch) { + final int iidEnd = Math.min(xx+batch, iidList.size()); + for (int yy=0; yy<tidList.size(); yy+=batch) { + final int tidEnd = Math.min(yy+batch, tidList.size()); + rtn.addAll(getSession().createQuery(sql) + .setParameterList("iids", iidList.subList(xx, iidEnd), iType) + .setParameterList("tids", tidList.subList(yy, tidEnd), iType) + .setCacheable(true) + .setCacheRegion("Measurement.findMeasurements") + .list()); + } + } + return rtn; } List findAvailMeasurements(Integer[] tids, Integer[] iids) { - String sql = new StringBuilder() + final IntegerType iType = new IntegerType(); + final List iidList = Arrays.asList(iids); + final List tidList = Arrays.asList(tids); + final String sql = new StringBuilder(256) .append("select m from Measurement m ") .append("join m.template t ") .append("where m.instanceId in (:iids) AND t.id in (:tids) AND ") .append(ALIAS_CLAUSE).toString(); - return getSession().createQuery(sql) - .setParameterList("iids", iids) - .setParameterList("tids", tids).list(); + final List rtn = new ArrayList(iidList.size()); + final int batch = BATCH_SIZE/2; + for (int xx=0; xx<iidList.size(); xx+=batch) { + final int iidEnd = Math.min(xx+batch, iidList.size()); + for (int yy=0; yy<tidList.size(); yy+=batch) { + final int tidEnd = Math.min(yy+batch, tidList.size()); + rtn.addAll(getSession().createQuery(sql) + .setParameterList("iids", iidList.subList(xx, iidEnd), iType) + .setParameterList("tids", tidList.subList(yy, tidEnd), iType).list()); + } + } + return rtn; } /** |