cloudstack-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "ASF GitHub Bot (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (CLOUDSTACK-10065) Optimize SQL queries in listTemplate API to improve performance.
Date Fri, 05 Jan 2018 05:58:01 GMT

    [ https://issues.apache.org/jira/browse/CLOUDSTACK-10065?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16312519#comment-16312519
] 

ASF GitHub Bot commented on CLOUDSTACK-10065:
---------------------------------------------

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


> Optimize SQL queries in listTemplate API to improve performance. 
> -----------------------------------------------------------------
>
>                 Key: CLOUDSTACK-10065
>                 URL: https://issues.apache.org/jira/browse/CLOUDSTACK-10065
>             Project: CloudStack
>          Issue Type: Bug
>      Security Level: Public(Anyone can view this level - this is the default.) 
>            Reporter: Pranali Mande
>
> *Description*:
> The db queries could be optimized to get unique results from the database which could
help in reducing the listTemplate API response time.



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)

Mime
View raw message