cloudstack-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From raj...@apache.org
Subject [1/2] git commit: updated refs/heads/master to fb4e6ed
Date Tue, 27 Oct 2015 06:49:24 GMT
Repository: cloudstack
Updated Branches:
  refs/heads/master 4fe56daf4 -> fb4e6ed6b


CLOUDSTACK-8917 : Instance tab takes long time to load with 12K active VM (total vms: 190K)

modified sql that is used for retrieving vm count .


Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo
Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/c28a58a8
Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/c28a58a8
Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/c28a58a8

Branch: refs/heads/master
Commit: c28a58a8ff4ddde7b86e151ffee35ad26645e584
Parents: 3ded3e9
Author: Sudhansu <sudhansu.sahu@citrix.com>
Authored: Mon Sep 28 16:24:26 2015 +0530
Committer: Sudhansu <sudhansu.sahu@citrix.com>
Committed: Mon Sep 28 16:24:26 2015 +0530

----------------------------------------------------------------------
 .../db/src/com/cloud/utils/db/GenericDao.java   |  7 ++
 .../src/com/cloud/utils/db/GenericDaoBase.java  | 79 ++++++++++++++++++++
 .../db/src/com/cloud/utils/db/SqlGenerator.java |  6 ++
 .../com/cloud/api/query/QueryManagerImpl.java   |  2 +-
 4 files changed, 93 insertions(+), 1 deletion(-)
----------------------------------------------------------------------


http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/framework/db/src/com/cloud/utils/db/GenericDao.java
----------------------------------------------------------------------
diff --git a/framework/db/src/com/cloud/utils/db/GenericDao.java b/framework/db/src/com/cloud/utils/db/GenericDao.java
index cb401cd..121d65d 100644
--- a/framework/db/src/com/cloud/utils/db/GenericDao.java
+++ b/framework/db/src/com/cloud/utils/db/GenericDao.java
@@ -268,5 +268,12 @@ public interface GenericDao<T, ID extends Serializable> {
      */
     Pair<List<T>, Integer> searchAndCount(SearchCriteria<T> sc, Filter
filter);
 
+    /**
+     * @param sc
+     * @param filter
+     * @return
+     */
+    Pair<List<T>, Integer> searchAndDistinctCount(final SearchCriteria<T>
sc, final Filter filter);
+
     Map<String, Attribute> getAllAttributes();
 }

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
----------------------------------------------------------------------
diff --git a/framework/db/src/com/cloud/utils/db/GenericDaoBase.java b/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
index e75646a..29adbe2 100644
--- a/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
+++ b/framework/db/src/com/cloud/utils/db/GenericDaoBase.java
@@ -135,6 +135,7 @@ public abstract class GenericDaoBase<T, ID extends Serializable>
extends Compone
     protected Map<String, Object> _discriminatorValues;
     protected String _selectByIdSql;
     protected String _count;
+    protected String _distinctIdSql;
 
     protected Field _idField;
 
@@ -212,6 +213,7 @@ public abstract class GenericDaoBase<T, ID extends Serializable>
extends Compone
         final SqlGenerator generator = new SqlGenerator(_entityBeanType);
         _partialSelectSql = generator.buildSelectSql(false);
         _count = generator.buildCountSql();
+        _distinctIdSql= generator.buildDistinctIdSql();
         _partialQueryCacheSelectSql = generator.buildSelectSql(true);
         _embeddedFields = generator.getEmbeddedFields();
         _insertSqls = generator.buildInsertSqls();
@@ -1300,6 +1302,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) {
+        List<T> objects = search(sc, filter, null, false);
+        Integer count = getDistinctCount(sc);
+        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);
     }
@@ -1841,6 +1851,64 @@ public abstract class GenericDaoBase<T, ID extends Serializable>
extends Compone
         return builder.create();
     }
 
+    public Integer getDistinctCount(SearchCriteria<T> sc) {
+        String clause = sc != null ? sc.getWhereClause() : null;
+        if (clause != null && clause.length() == 0) {
+            clause = null;
+        }
+
+        final StringBuilder str = createDistinctIdSelect(sc, clause != null);
+        if (clause != null) {
+            str.append(clause);
+        }
+
+        Collection<JoinBuilder<SearchCriteria<?>>> joins = null;
+        if (sc != null) {
+            joins = sc.getJoins();
+            if (joins != null) {
+                addJoins(str, joins);
+            }
+        }
+
+        // we have to disable group by in getting count, since count for groupBy clause will
be different.
+        //List<Object> groupByValues = addGroupBy(str, sc);
+        final TransactionLegacy txn = TransactionLegacy.currentTxn();
+        final String sql = "SELECT COUNT(*) FROM (" + str.toString() + ") AS tmp";
+
+        PreparedStatement pstmt = null;
+        try {
+            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);
+            }
+
+            /*
+            if (groupByValues != null) {
+                for (Object value : groupByValues) {
+                    pstmt.setObject(i++, value);
+                }
+            }
+             */
+
+            final ResultSet rs = pstmt.executeQuery();
+            while (rs.next()) {
+                return rs.getInt(1);
+            }
+            return 0;
+        } catch (final SQLException e) {
+            throw new CloudRuntimeException("DB Exception on: " + pstmt, e);
+        } catch (final Throwable e) {
+            throw new CloudRuntimeException("Caught: " + pstmt, e);
+        }
+    }
+
     public Integer getCount(SearchCriteria<T> sc) {
         String clause = sc != null ? sc.getWhereClause() : null;
         if (clause != null && clause.length() == 0) {
@@ -1911,6 +1979,17 @@ public abstract class GenericDaoBase<T, ID extends Serializable>
extends Compone
     }
 
     @DB()
+    protected StringBuilder createDistinctIdSelect(SearchCriteria<?> sc, final boolean
whereClause) {
+        StringBuilder sql = new StringBuilder(_distinctIdSql);
+
+        if (!whereClause) {
+            sql.delete(sql.length() - (_discriminatorClause == null ? 6 : 4), sql.length());
+        }
+
+        return sql;
+    }
+
+    @DB()
     protected Pair<List<T>, Integer> listAndCountIncludingRemovedBy(final SearchCriteria<T>
sc, final Filter filter) {
         List<T> objects = searchIncludingRemoved(sc, filter, null, false);
         Integer count = getCount(sc);

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/framework/db/src/com/cloud/utils/db/SqlGenerator.java
----------------------------------------------------------------------
diff --git a/framework/db/src/com/cloud/utils/db/SqlGenerator.java b/framework/db/src/com/cloud/utils/db/SqlGenerator.java
index befe34b..864ee73 100644
--- a/framework/db/src/com/cloud/utils/db/SqlGenerator.java
+++ b/framework/db/src/com/cloud/utils/db/SqlGenerator.java
@@ -663,4 +663,10 @@ public class SqlGenerator {
 
         return sql.append("SELECT COUNT(*) FROM ").append(buildTableReferences()).append("
WHERE ").append(buildDiscriminatorClause().first()).toString();
     }
+
+    public String buildDistinctIdSql() {
+        StringBuilder sql = new StringBuilder();
+
+        return sql.append("SELECT DISTINCT id FROM ").append(buildTableReferences()).append("
WHERE ").append(buildDiscriminatorClause().first()).toString();
+    }
 }

http://git-wip-us.apache.org/repos/asf/cloudstack/blob/c28a58a8/server/src/com/cloud/api/query/QueryManagerImpl.java
----------------------------------------------------------------------
diff --git a/server/src/com/cloud/api/query/QueryManagerImpl.java b/server/src/com/cloud/api/query/QueryManagerImpl.java
index 6994b27..e90f213 100644
--- a/server/src/com/cloud/api/query/QueryManagerImpl.java
+++ b/server/src/com/cloud/api/query/QueryManagerImpl.java
@@ -1021,7 +1021,7 @@ public class QueryManagerImpl extends ManagerBase implements QueryService,
Confi
             sc.setParameters("displayVm", 1);
         }
         // search vm details by ids
-        Pair<List<UserVmJoinVO>, Integer> uniqueVmPair = _userVmJoinDao.searchAndCount(sc,
searchFilter);
+        Pair<List<UserVmJoinVO>, Integer> uniqueVmPair = _userVmJoinDao.searchAndDistinctCount(sc,
searchFilter);
         Integer count = uniqueVmPair.second();
         if (count.intValue() == 0) {
             // handle empty result cases


Mime
View raw message