Eli Mesika has uploaded a new change for review.
Change subject: core: QueryData2 generates slow SQL for...
......................................................................
core: QueryData2 generates slow SQL for...
QueryData2 generates slow SQL for AuditLog with no parameters
This patch fixes two issues
When the inner query condition of getting the events is empty it omits
the inner query.
i.e.
Instead of
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and
audit_log_id IN (SELECT audit_log.audit_log_id FROM audit_log ) and
not deleted) ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1
OFFSET (1 -1) LIMIT 100
Perform:
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not
deleted) ORDER BY audit_log_id DESC ,audit_log_id DESC ) as T1 OFFSET
(1 -1) LIMIT 100;
In addition, this patch fixes the redundancy in the SORT , so final
query is :
SELECT * FROM (SELECT * FROM audit_log WHERE ( audit_log_id > 0 and not
deleted) ORDER BY audit_log_id DESC) as T1 OFFSET
(1 -1) LIMIT 100;
Change-Id: Id148adcc734c5742548c74ea754fb70252155e34
Signed-off-by: Eli Mesika <emes...@redhat.com>
---
M
backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
1 file changed, 22 insertions(+), 13 deletions(-)
git pull ssh://gerrit.ovirt.org:29418/ovirt-engine refs/changes/27/17527/1
diff --git
a/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
b/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
index 5e0aa67..dec087f 100644
---
a/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
+++
b/backend/manager/modules/searchbackend/src/main/java/org/ovirt/engine/core/searchbackend/SyntaxChecker.java
@@ -900,23 +900,32 @@
// adding a secondary default sort by entity name
StringBuilder sortExpr = new StringBuilder();
sortExpr.append(sortByPhrase);
- sortExpr.append(",");
- sortExpr.append(mSearchObjectAC.getDefaultSort(searchObjStr));
+ if (
sortByPhrase.indexOf(mSearchObjectAC.getDefaultSort(searchObjStr)) < 0) {
+ sortExpr.append(",");
+ sortExpr.append(mSearchObjectAC.getDefaultSort(searchObjStr));
+ }
// only audit log search supports the SearchFrom which enables
getting records starting from a certain
// audit_log_id, this is done to make search queries from the
client more efficient and eliminate the client
// from registering to such queries and comparing last data with
previous.
- String inQuery =
- (primeryKey.equals("audit_log_id")
- ?
- StringFormat.format("SELECT * FROM %1$s WHERE (
%2$s > %3$s and %2$s IN (%4$s) and not deleted",
- tableNameWithOutTags,
- primeryKey,
- syntax.getSearchFrom(),
- innerQuery)
- :
- StringFormat.format("SELECT * FROM %1$s WHERE (
%2$s IN (%3$s)", tableNameWithOutTags,
- primeryKey, innerQuery));
+ String inQuery = "";
+ if (primeryKey.equals("audit_log_id")) {
+ if (wherePhrase.toString().equals("")) {
+ inQuery = StringFormat
+ .format("SELECT * FROM %1$s WHERE ( %2$s > %3$s
and not deleted",
+ tableNameWithOutTags, primeryKey,
+ syntax.getSearchFrom());
+ } else {
+ inQuery = StringFormat
+ .format("SELECT * FROM %1$s WHERE ( %2$s > %3$s
and %2$s IN (%4$s) and not deleted",
+ tableNameWithOutTags, primeryKey,
+ syntax.getSearchFrom(), innerQuery);
+ }
+ } else {
+ inQuery = StringFormat.format(
+ "SELECT * FROM %1$s WHERE ( %2$s IN (%3$s)",
+ tableNameWithOutTags, primeryKey, innerQuery);
+ }
retval =
StringFormat.format(Config.<String>
GetValue(ConfigValues.DBSearchTemplate),
sortExpr.toString(),
--
To view, visit http://gerrit.ovirt.org/17527
To unsubscribe, visit http://gerrit.ovirt.org/settings
Gerrit-MessageType: newchange
Gerrit-Change-Id: Id148adcc734c5742548c74ea754fb70252155e34
Gerrit-PatchSet: 1
Gerrit-Project: ovirt-engine
Gerrit-Branch: master
Gerrit-Owner: Eli Mesika <emes...@redhat.com>
_______________________________________________
Engine-patches mailing list
Engine-patches@ovirt.org
http://lists.ovirt.org/mailman/listinfo/engine-patches
