From 0630567f600b194c1e9c3e97eb8e117b73f88402 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Sven=20Sch=C3=B6ling?= <s.schoeling@linet-services.de>
Date: Thu, 21 Apr 2016 19:26:48 +0200
Subject: [PATCH] DB-Upgrade: Queries nicht in der Schleife preparen
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Datenbank mit 100k invoice Einträgen braucht Jahre sonst.
---
 sql/Pg-upgrade2/invoice_positions.pl                        | 6 ++++--
 .../orderitems_delivery_order_items_positions.pl            | 6 ++++--
 2 files changed, 8 insertions(+), 4 deletions(-)

diff --git a/sql/Pg-upgrade2/invoice_positions.pl b/sql/Pg-upgrade2/invoice_positions.pl
index f8bc9112f..d92daca6e 100644
--- a/sql/Pg-upgrade2/invoice_positions.pl
+++ b/sql/Pg-upgrade2/invoice_positions.pl
@@ -17,8 +17,10 @@ sub run {
 
 
   $query = qq|SELECT * FROM invoice ORDER BY trans_id, id|;
+  my $query2 = qq|UPDATE invoice SET position = ? WHERE id = ?|;
 
   my $sth = $self->dbh->prepare($query);
+  my $sth2 = $self->dbh->prepare($query2);
   $sth->execute || $::form->dberror($query);
 
   # set new position field in order of ids, starting by one for each invoice
@@ -32,10 +34,10 @@ sub run {
     }
     $last_invoice_id = $ref->{trans_id};
 
-    $query = qq|UPDATE invoice SET position = ? WHERE id = ?|;
-    $self->db_query($query, bind => [ $position, $ref->{id} ]);
+    $sth2->execute($position, $ref->{id});
   }
   $sth->finish;
+  $sth2->finish;
 
   $query = qq|ALTER TABLE invoice ALTER COLUMN position SET NOT NULL|;
   $self->db_query($query);
diff --git a/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl b/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl
index cda92006c..712ce835e 100644
--- a/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl
+++ b/sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl
@@ -25,8 +25,10 @@ sub run {
 
     my $order_id_col = $order_id_cols{ $table };
     $query = qq|SELECT * FROM $table ORDER BY $order_id_col, id|;
+    my $query2 = qq|UPDATE $table SET position = ? WHERE id = ?|;
 
     my $sth = $self->dbh->prepare($query);
+    my $sth2 = $self->dbh->prepare($query2);
     $sth->execute || $::form->dberror($query);
 
     # set new position field in order of ids, starting by one for each order
@@ -40,10 +42,10 @@ sub run {
       }
       $last_order_id = $ref->{ $order_id_col };
 
-      $query = qq|UPDATE $table SET position = ? WHERE id = ?|;
-      $self->db_query($query, bind => [ $position, $ref->{id} ]);
+      $sth2->execute($position, $ref->{id});
     }
     $sth->finish;
+    $sth2->finish;
 
 
     $query = qq|ALTER TABLE $table ALTER COLUMN position SET NOT NULL|;
-- 
2.20.1