Revision f4194b70
Von Sven Schöling vor etwa 13 Jahren hinzugefügt
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
Ein Query in $::form->create_links beschleunigt