From f4194b703f9557168c2407f5b5a7009ed4db9b9d Mon Sep 17 00:00:00 2001
From: =?utf8?q?Sven=20Sch=C3=B6ling?= <s.schoeling@linet-services.de>
Date: Mon, 26 Sep 2011 17:50:09 +0200
Subject: [PATCH] Ein Query in $::form->create_links beschleunigt

---
 SL/Form.pm | 27 ++++++++++++++++++++++-----
 1 file changed, 22 insertions(+), 5 deletions(-)

diff --git a/SL/Form.pm b/SL/Form.pm
index 2116adb55..a23bb4337 100644
--- a/SL/Form.pm
+++ b/SL/Form.pm
@@ -2886,11 +2886,28 @@ sub create_links {
     }
 
     # now get the account numbers
-    $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id
-                FROM chart c, taxkeys tk
-                WHERE (c.link LIKE ?) AND (c.id = tk.chart_id) AND tk.id =
-                  (SELECT id FROM taxkeys WHERE (taxkeys.chart_id = c.id) AND (startdate <= $transdate) ORDER BY startdate DESC LIMIT 1)
-                ORDER BY c.accno|;
+#    $query = qq|SELECT c.accno, c.description, c.link, c.taxkey_id, tk.tax_id
+#                FROM chart c, taxkeys tk
+#                WHERE (c.link LIKE ?) AND (c.id = tk.chart_id) AND tk.id =
+#                  (SELECT id FROM taxkeys WHERE (taxkeys.chart_id = c.id) AND (startdate <= $transdate) ORDER BY startdate DESC LIMIT 1)
+#                ORDER BY c.accno|;
+
+#  same query as above, but without expensive subquery for each row. about 80% faster
+    $query = qq|
+      SELECT c.accno, c.description, c.link, c.taxkey_id, tk2.tax_id
+        FROM chart c
+        -- find newest entries in taxkeys
+        INNER JOIN (
+          SELECT chart_id, MAX(startdate) AS startdate
+          FROM taxkeys
+          WHERE (startdate <= $transdate)
+          GROUP BY chart_id
+        ) tk ON (c.id = tk.chart_id)
+        -- and load all of those entries
+        INNER JOIN taxkeys tk2
+           ON (tk.chart_id = tk2.chart_id AND tk.startdate = tk2.startdate)
+       WHERE (c.link LIKE ?)
+      ORDER BY c.accno|;
 
     $sth = $dbh->prepare($query);
 
-- 
2.20.1