Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision f4194b70

Von Sven Schöling vor etwa 13 Jahren hinzugefügt

  • ID f4194b703f9557168c2407f5b5a7009ed4db9b9d
  • Vorgänger c90d3558
  • Nachfolger 7dce1e6b

Ein Query in $::form->create_links beschleunigt

Unterschiede anzeigen:

SL/Form.pm
2886 2886
    }
2887 2887

  
2888 2888
    # now get the account numbers
2889
    $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id
2890
                FROM chart c, taxkeys tk
2891
                WHERE (c.link LIKE ?) AND (c.id = tk.chart_id) AND tk.id =
2892
                  (SELECT id FROM taxkeys WHERE (taxkeys.chart_id = c.id) AND (startdate <= $transdate) ORDER BY startdate DESC LIMIT 1)
2893
                ORDER BY c.accno|;
2889
#    $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id
2890
#                FROM chart c, taxkeys tk
2891
#                WHERE (c.link LIKE ?) AND (c.id = tk.chart_id) AND tk.id =
2892
#                  (SELECT id FROM taxkeys WHERE (taxkeys.chart_id = c.id) AND (startdate <= $transdate) ORDER BY startdate DESC LIMIT 1)
2893
#                ORDER BY c.accno|;
2894

  
2895
#  same query as above, but without expensive subquery for each row. about 80% faster
2896
    $query = qq|
2897
      SELECT c.accno, c.description, c.link, c.taxkey_id, tk2.tax_id
2898
        FROM chart c
2899
        -- find newest entries in taxkeys
2900
        INNER JOIN (
2901
          SELECT chart_id, MAX(startdate) AS startdate
2902
          FROM taxkeys
2903
          WHERE (startdate <= $transdate)
2904
          GROUP BY chart_id
2905
        ) tk ON (c.id = tk.chart_id)
2906
        -- and load all of those entries
2907
        INNER JOIN taxkeys tk2
2908
           ON (tk.chart_id = tk2.chart_id AND tk.startdate = tk2.startdate)
2909
       WHERE (c.link LIKE ?)
2910
      ORDER BY c.accno|;
2894 2911

  
2895 2912
    $sth = $dbh->prepare($query);
2896 2913

  

Auch abrufbar als: Unified diff