You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cloudstack.apache.org by ro...@apache.org on 2018/01/05 05:57:44 UTC
[cloudstack] branch master updated: CLOUDSTACK-10065: Optimize SQL
queries in listTemplate API to improve performance (#2260)
This is an automated email from the ASF dual-hosted git repository.
rohit pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/cloudstack.git
The following commit(s) were added to refs/heads/master by this push:
new 7253969 CLOUDSTACK-10065: Optimize SQL queries in listTemplate API to improve performance (#2260)
7253969 is described below
commit 72539690882490d44c23bef5412e3bccd93df258
Author: PranaliM <pr...@accelerite.com>
AuthorDate: Fri Jan 5 11:27:41 2018 +0530
CLOUDSTACK-10065: Optimize SQL queries in listTemplate API to improve performance (#2260)
The db queries in listTemplateAPI could be optimized to get unique results from the database which could help in reducing the listTemplate API response time.
---
.../db/src/com/cloud/utils/db/GenericDao.java | 2 +
.../db/src/com/cloud/utils/db/GenericDaoBase.java | 70 +++++++++++++++++++++-
.../db/src/com/cloud/utils/db/SqlGenerator.java | 16 +++++
.../src/com/cloud/api/query/QueryManagerImpl.java | 3 +-
server/test/com/cloud/user/MockUsageEventDao.java | 6 +-
5 files changed, 94 insertions(+), 3 deletions(-)
diff --git a/framework/db/src/com/cloud/utils/db/GenericDao.java b/framework/db/src/com/cloud/utils/db/GenericDao.java
index 121d65d..63047e7 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 @@ public interface GenericDao<T, ID extends Serializable> {
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 c5a4cd8..304a122 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.Map;
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;
@@ -1328,6 +1328,14 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
@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) {
return search(sc, filter, null, false, enableQueryCache);
}
@@ -1927,6 +1935,52 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
}
}
+ 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 @@ public abstract class GenericDaoBase<T, ID extends Serializable> extends Compone
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 e6cb9cb..5168496 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 class SqlGenerator {
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 2d6fabc..efe31cb 100644
--- a/server/src/com/cloud/api/query/QueryManagerImpl.java
+++ b/server/src/com/cloud/api/query/QueryManagerImpl.java
@@ -3383,7 +3383,8 @@ public class QueryManagerImpl extends MutualExclusiveIdsManagerBase implements Q
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 4959b83..5d8ed6c 100644
--- a/server/test/com/cloud/user/MockUsageEventDao.java
+++ b/server/test/com/cloud/user/MockUsageEventDao.java
@@ -279,6 +279,11 @@ public class MockUsageEventDao implements UsageEventDao{
}
@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 class MockUsageEventDao implements UsageEventDao{
public void saveDetails(long eventId, Map<String, String> details) {
}
-
}
--
To stop receiving notification emails like this one, please contact
['"commits@cloudstack.apache.org" <co...@cloudstack.apache.org>'].