public inbox for [email protected]
help / color / mirror / Atom feedFrom: Murtuza Zabuawala <[email protected]>
To: Dave Page <[email protected]>
Cc: pgadmin-hackers <[email protected]>
Subject: Re: PATCH: To fix the performance of delete operation (pgAdmin4)
Date: Fri, 16 Sep 2016 17:04:18 +0530
Message-ID: <CAKKotZSW7SsQ6StbcZHyOh68jwVF381=r=dH7hJyeUuNtCVpwQ@mail.gmail.com> (raw)
In-Reply-To: <CA+OCxozdc=Dz80uBEYeJ0B4cj9j8GeWbZSkaoy0QqexAWY1q4g@mail.gmail.com>
References: <CAKKotZQvEXHC_z76muv0V+f--Uskph1Oj3WHB5PfPxS2MEgHUA@mail.gmail.com>
<CA+OCxozdc=Dz80uBEYeJ0B4cj9j8GeWbZSkaoy0QqexAWY1q4g@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgadmin-hackers>
Hi Dave,
Please find updated patch.
Regards,
Murtuza
--
Regards,
Murtuza Zabuawala
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Fri, Sep 16, 2016 at 3:31 PM, Dave Page <[email protected]> wrote:
> Can you rebase this please?
>
> Thanks.
>
> On Thu, Sep 15, 2016 at 11:08 AM, Murtuza Zabuawala
> <[email protected]> wrote:
> > 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
> >
>
>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: 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_v2.patch (5.1K, 3-RM_1696_v2.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 f306520..74502c7 100644
--- a/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
+++ b/web/pgadmin/tools/sqleditor/templates/sqleditor/js/sqleditor.js
@@ -2149,12 +2149,20 @@ define(
// 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 the 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], [email protected]
Subject: Re: PATCH: To fix the performance of delete operation (pgAdmin4)
In-Reply-To: <CAKKotZSW7SsQ6StbcZHyOh68jwVF381=r=dH7hJyeUuNtCVpwQ@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