You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by GitBox <gi...@apache.org> on 2018/01/05 05:57:45 UTC
[GitHub] rhtyd closed pull request #2260: CLOUDSTACK-10065: Optimize SQL queries in listTemplate API to improve performance
rhtyd closed pull request #2260: CLOUDSTACK-10065: Optimize SQL queries in listTemplate API to improve performance
URL: https://github.com/apache/cloudstack/pull/2260
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git a/framework/db/src/com/cloud/utils/db/GenericDao.java b/framework/db/src/com/cloud/utils/db/GenericDao.java
index 121d65d6501..63047e7c699 100644
--- a/framework/db/src/com/cloud/utils/db/GenericDao.java
+++ b/framework/db/src/com/cloud/utils/db/GenericDao.java
@@ -276,4 +276,6 @@
Pair<List<T>, Integer> searchAndDistinctCount(final SearchCriteria<T> sc, final Filter filter);
Map<String, Attribute> getAllAttributes();
+
+ Pair<List<T>, Integer> searchAndDistinctCount(final SearchCriteria<T> sc, final Filter filter, final String[] distinctColumns);
}
diff --git a/framework/db/src/com/cloud/utils/db/GenericDaoBase.java b/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
index c5a4cd85dd8..304a122a0b7 100644
--- a/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
+++ b/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
@@ -44,7 +44,7 @@
import java.util.TimeZone;
import java.util.UUID;
import java.util.concurrent.ConcurrentHashMap;
-
+import com.google.common.base.Strings;
import javax.naming.ConfigurationException;
import javax.persistence.AttributeOverride;
import javax.persistence.Column;
@@ -1326,6 +1326,14 @@ protected void addJoins(StringBuilder str, Collection<JoinBuilder<SearchCriteria
return new Pair<List<T>, Integer>(objects, count);
}
+ @Override
+ @DB()
+ public Pair<List<T>, Integer> searchAndDistinctCount(final SearchCriteria<T> sc, final Filter filter, final String[] distinctColumns) {
+ List<T> objects = search(sc, filter, null, false);
+ Integer count = getDistinctCount(sc, distinctColumns);
+ return new Pair<List<T>, Integer>(objects, count);
+ }
+
@Override
@DB()
public List<T> search(final SearchCriteria<T> sc, final Filter filter, final boolean enableQueryCache) {
@@ -1927,6 +1935,52 @@ public Integer getDistinctCount(SearchCriteria<T> sc) {
}
}
+ public Integer getDistinctCount(SearchCriteria<T> sc, String[] distinctColumns) {
+ String clause = sc != null ? sc.getWhereClause() : null;
+ if (Strings.isNullOrEmpty(clause)) {
+ clause = null;
+ }
+
+ final StringBuilder str = createDistinctSelect(sc, clause != null, distinctColumns);
+ if (clause != null) {
+ str.append(clause);
+ }
+
+ Collection<JoinBuilder<SearchCriteria<?>>> joins = null;
+ if (sc != null) {
+ joins = sc.getJoins();
+ if (joins != null) {
+ addJoins(str, joins);
+ }
+ }
+
+ final TransactionLegacy txn = TransactionLegacy.currentTxn();
+ final String sql = "SELECT COUNT(*) FROM (" + str.toString() + ") AS tmp";
+
+ try (PreparedStatement pstmt = txn.prepareAutoCloseStatement(sql)) {
+ int i = 1;
+ if (clause != null) {
+ for (final Pair<Attribute, Object> value : sc.getValues()) {
+ prepareAttribute(i++, pstmt, value.first(), value.second());
+ }
+ }
+
+ if (joins != null) {
+ i = addJoinAttributes(i, pstmt, joins);
+ }
+
+ final ResultSet rs = pstmt.executeQuery();
+ while (rs.next()) {
+ return rs.getInt(1);
+ }
+ return 0;
+ } catch (final SQLException e) {
+ throw new CloudRuntimeException("DB Exception in executing: " + sql, e);
+ } catch (final Throwable e) {
+ throw new CloudRuntimeException("Caught exception in : " + sql, e);
+ }
+ }
+
public Integer getCount(SearchCriteria<T> sc) {
String clause = sc != null ? sc.getWhereClause() : null;
if (clause != null && clause.length() == 0) {
@@ -2013,4 +2067,18 @@ protected StringBuilder createDistinctIdSelect(SearchCriteria<?> sc, final boole
Integer count = getCount(sc);
return new Pair<List<T>, Integer>(objects, count);
}
+
+ @DB()
+ protected StringBuilder createDistinctSelect(SearchCriteria<?> sc, final boolean whereClause, String[] distinctColumns) {
+ final SqlGenerator generator = new SqlGenerator(_entityBeanType);
+ String distinctSql = generator.buildDistinctSql(distinctColumns);
+
+ StringBuilder sql = new StringBuilder(distinctSql);
+
+ if (!whereClause) {
+ sql.delete(sql.length() - (_discriminatorClause == null ? 6 : 4), sql.length());
+ }
+
+ return sql;
+ }
}
diff --git a/framework/db/src/com/cloud/utils/db/SqlGenerator.java b/framework/db/src/com/cloud/utils/db/SqlGenerator.java
index e6cb9cb8897..516849650f1 100644
--- a/framework/db/src/com/cloud/utils/db/SqlGenerator.java
+++ b/framework/db/src/com/cloud/utils/db/SqlGenerator.java
@@ -680,4 +680,20 @@ public String buildDistinctIdSql() {
return sql.append("SELECT DISTINCT id FROM ").append(buildTableReferences()).append(" WHERE ").append(buildDiscriminatorClause().first()).toString();
}
+
+ public String buildDistinctSql(String[] distinctColumnNames) {
+ StringBuilder sbColumn = new StringBuilder();
+
+ if (distinctColumnNames != null && distinctColumnNames.length > 0) {
+ for (String columnName : distinctColumnNames) {
+ sbColumn.append(columnName).append(", ");
+ }
+ sbColumn.delete(sbColumn.length() - 2, sbColumn.length());
+ } else {
+ sbColumn.append("*");
+ }
+
+ StringBuilder sql = new StringBuilder();
+ return sql.append("SELECT DISTINCT " + sbColumn.toString() + " FROM ").append(buildTableReferences()).append(" WHERE ").append(buildDiscriminatorClause().first()).toString();
+ }
}
diff --git a/server/src/com/cloud/api/query/QueryManagerImpl.java b/server/src/com/cloud/api/query/QueryManagerImpl.java
index 1c5c70c9852..c125b90c360 100644
--- a/server/src/com/cloud/api/query/QueryManagerImpl.java
+++ b/server/src/com/cloud/api/query/QueryManagerImpl.java
@@ -3357,7 +3357,8 @@ else if (!template.isPublicTemplate() && caller.getType() != Account.ACCOUNT_TYP
uniqueTmplPair = _templateJoinDao.searchIncludingRemovedAndCount(sc, searchFilter);
} else {
sc.addAnd("templateState", SearchCriteria.Op.IN, new State[]{State.Active, State.UploadAbandoned, State.UploadError, State.NotUploaded, State.UploadInProgress});
- uniqueTmplPair = _templateJoinDao.searchAndCount(sc, searchFilter);
+ final String[] distinctColumns = {"temp_zone_pair"};
+ uniqueTmplPair = _templateJoinDao.searchAndDistinctCount(sc, searchFilter, distinctColumns);
}
Integer count = uniqueTmplPair.second();
diff --git a/server/test/com/cloud/user/MockUsageEventDao.java b/server/test/com/cloud/user/MockUsageEventDao.java
index 4959b83a452..5d8ed6c4555 100644
--- a/server/test/com/cloud/user/MockUsageEventDao.java
+++ b/server/test/com/cloud/user/MockUsageEventDao.java
@@ -278,6 +278,11 @@ public UsageEventVO findOneBy(SearchCriteria<UsageEventVO> sc) {
return null;
}
+ @Override
+ public Pair<List<UsageEventVO>, Integer> searchAndDistinctCount(SearchCriteria<UsageEventVO> sc, Filter filter, String[] distinctColumns) {
+ return null;
+ }
+
@Override
public List<UsageEventVO> listLatestEvents(Date endDate) {
return null;
@@ -303,5 +308,4 @@ public UsageEventVO findOneBy(SearchCriteria<UsageEventVO> sc) {
public void saveDetails(long eventId, Map<String, String> details) {
}
-
}
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services