Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision 0630567f

Von Sven Schöling vor mehr als 8 Jahren hinzugefügt

  • ID 0630567f600b194c1e9c3e97eb8e117b73f88402
  • Vorgänger 42bf1ae9
  • Nachfolger 83b760fb

DB-Upgrade: Queries nicht in der Schleife preparen

Datenbank mit 100k invoice Einträgen braucht Jahre sonst.

Unterschiede anzeigen:

sql/Pg-upgrade2/invoice_positions.pl
17 17 

  		




  18
  18
  
    

  		




  19
  19
  
    
  $query = qq|SELECT * FROM invoice ORDER BY trans_id, id|;


  		




  
  20
  
    
  my $query2 = qq|UPDATE invoice SET position = ? WHERE id = ?|;


  		




  20
  21
  
    

  		




  21
  22
  
    
  my $sth = $self->dbh->prepare($query);


  		




  
  23
  
    
  my $sth2 = $self->dbh->prepare($query2);


  		




  22
  24
  
    
  $sth->execute || $::form->dberror($query);


  		




  23
  25
  
    

  		




  24
  26
  
    
  # set new position field in order of ids, starting by one for each invoice


  		




  ......




  32
  34
  
    
    }


  		




  33
  35
  
    
    $last_invoice_id = $ref->{trans_id};


  		




  34
  36
  
    

  		




  35
  
  
    
    $query = qq|UPDATE invoice SET position = ? WHERE id = ?|;


  		




  36
  
  
    
    $self->db_query($query, bind => [ $position, $ref->{id} ]);


  		




  
  37
  
    
    $sth2->execute($position, $ref->{id});


  		




  37
  38
  
    
  }


  		




  38
  39
  
    
  $sth->finish;


  		




  
  40
  
    
  $sth2->finish;


  		




  39
  41
  
    

  		




  40
  42
  
    
  $query = qq|ALTER TABLE invoice ALTER COLUMN position SET NOT NULL|;


  		




  41
  43
  
    
  $self->db_query($query);


  		












  

    
      sql/Pg-upgrade2/orderitems_delivery_order_items_positions.pl
    
  





  25
  25
  
    

  		




  26
  26
  
    
    my $order_id_col = $order_id_cols{ $table };


  		




  27
  27
  
    
    $query = qq|SELECT * FROM $table ORDER BY $order_id_col, id|;


  		




  
  28
  
    
    my $query2 = qq|UPDATE $table SET position = ? WHERE id = ?|;


  		




  28
  29
  
    

  		




  29
  30
  
    
    my $sth = $self->dbh->prepare($query);


  		




  
  31
  
    
    my $sth2 = $self->dbh->prepare($query2);


  		




  30
  32
  
    
    $sth->execute || $::form->dberror($query);


  		




  31
  33
  
    

  		




  32
  34
  
    
    # set new position field in order of ids, starting by one for each order


  		




  ......




  40
  42
  
    
      }


  		




  41
  43
  
    
      $last_order_id = $ref->{ $order_id_col };


  		




  42
  44
  
    

  		




  43
  
  
    
      $query = qq|UPDATE $table SET position = ? WHERE id = ?|;


  		




  44
  
  
    
      $self->db_query($query, bind => [ $position, $ref->{id} ]);


  		




  
  45
  
    
      $sth2->execute($position, $ref->{id});


  		




  45
  46
  
    
    }


  		




  46
  47
  
    
    $sth->finish;


  		




  
  48
  
    
    $sth2->finish;


  		




  47
  49
  
    

  		




  48
  50
  
    

  		




  49
  51
  
    
    $query = qq|ALTER TABLE $table ALTER COLUMN position SET NOT NULL|;


  		












Auch abrufbar als:  Unified diff