public inbox for [email protected]
help / color / mirror / Atom feedFrom: Murtuza Zabuawala <[email protected]>
To: pgadmin-hackers <[email protected]>
Subject: PATCH: To fix the performance of delete operation (pgAdmin4)
Date: Thu, 15 Sep 2016 15:38:09 +0530
Message-ID: <CAKKotZQvEXHC_z76muv0V+f--Uskph1Oj3WHB5PfPxS2MEgHUA@mail.gmail.com> (raw)
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi,
PFA patch to fix the issue of slow performance while deleting rows from
grid.
RM#1696
*Issue:*
As per logic implemented for Backgrid, we were deleting individual rows,
means If user selects 100 rows to delete then we were rendering sql
template 100 times to generate sql for each individual rows and then
execute all 100 delete statements one by one due to which we were getting
poor performance.
Please review.
--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgadmin-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgadmin-hackers
Attachments:
[application/octet-stream] RM_1696.patch (5.3K, 3-RM_1696.patch)
download | inline diff:
diff --git a/web/pgadmin/tools/sqleditor/command.py b/web/pgadmin/tools/sqleditor/command.py
index 4b8db6e..b5b8bde 100644
--- a/web/pgadmin/tools/sqleditor/command.py
+++ b/web/pgadmin/tools/sqleditor/command.py
@@ -465,14 +465,27 @@ class TableCommand(GridCommand):
# For deleted rows
elif of_type == 'deleted':
+ is_first = True
+ rows_to_delete = []
+ keys = None
+ no_of_keys = None
for each_row in changed_data[of_type]:
- data = changed_data[of_type][each_row]
- sql = render_template("/".join([self.sql_path, 'delete.sql']),
- data=data,
- object_name=self.object_name,
- nsp_name=self.nsp_name)
- list_of_sql.append(sql)
- list_of_rowid.append(data)
+ rows_to_delete.append(changed_data[of_type][each_row])
+ # Fetch the keys for SQL generation
+ if is_first:
+ # We need to covert dict_keys to normal list in Python3
+ # In Python2, it's already a list
+ keys = list(changed_data[of_type][each_row].keys())
+ no_of_keys = len(keys)
+ is_first = False
+
+ sql = render_template("/".join([self.sql_path, 'delete.sql']),
+ data=rows_to_delete,
+ primary_key_lables=keys,
+ no_of_keys=no_of_keys,
+ object_name=self.object_name,
+ nsp_name=self.nsp_name)
+ list_of_sql.append(sql)
for i, sql in enumerate(list_of_sql):
if sql:
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
index d81e79d..cde28a7 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
@@ -2069,17 +2069,25 @@ define(
data: JSON.stringify(self.data_store),
success: function(res) {
var grid = self.slickgrid,
- data = grid.getData();;
+ data = grid.getData();
if (res.data.status) {
// Remove deleted rows from client as well
if(is_deleted) {
var rows = grid.getSelectedRows();
- // Reverse the deletion from array
- // so that when we remove it does not affect index
- rows = rows.sort().reverse();
- rows.forEach(function(idx) {
- data.splice(idx, 1);
- });
+ /* In JavaScript sorting by default is lexical,
+ * To make sorting numerical we need to pass function
+ * After that we will Reverse the order of sorted array
+ * so that when we remove it does not affect array index
+ */
+ if(data.length == rows.length) {
+ // This means all rows are selected, clear all data
+ data = [];
+ } else {
+ rows = rows.sort(function(a,b){return a - b}).reverse();
+ rows.forEach(function(idx) {
+ data.splice(idx, 1);
+ });
+ }
grid.setData(data, true);
grid.setSelectedRows([]);
}
diff --git a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql
index e5dbc9b..3a552b7 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/sql/9.1_plus/delete.sql
@@ -1,4 +1,26 @@
-{# Delete the row with primary keys #}
DELETE FROM {{ conn|qtIdent(nsp_name, object_name) }} WHERE
-{% for pk_key in data %}
-{% if not loop.first %} AND {% endif %}{{ conn|qtIdent(pk_key) }} = {{ data[pk_key]|qtLiteral }}{% endfor %};
\ No newline at end of file
+{### If there is only one primary key ###}
+{% if no_of_keys == 1 %}
+{{ conn|qtIdent(primary_key_lables[0]) }} IN
+{### If there are multiple primary keys ###}
+{% elif no_of_keys > 1 %}
+({% for each_label in primary_key_lables %}
+{% if not loop.first %}, {% endif %}{{ conn|qtIdent(each_label) }}
+{% endfor %}) IN
+{% endif %}
+(
+{### Rows to delete ###}
+{% for obj in data %}
+{% if not loop.first %}, {% endif %}
+{% if no_of_keys == 1 %}
+{{ obj[primary_key_lables[0]]|qtLiteral }}
+{% elif no_of_keys > 1 %}
+{### Here we need to make tuple for each row ###}
+(
+{% for each_label in primary_key_lables %}
+{% if not loop.first %}, {% endif %}{{ obj[each_label]|qtLiteral }}
+{% endfor %}
+)
+{% endif %}
+{% endfor %}
+);
\ No newline at end of file
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected]
Subject: Re: PATCH: To fix the performance of delete operation (pgAdmin4)
In-Reply-To: <CAKKotZQvEXHC_z76muv0V+f--Uskph1Oj3WHB5PfPxS2MEgHUA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox