From 7028cd4c52dfd93379eeb4c01f0036cd1bdef1c8 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Sven=20Sch=C3=B6ling?= <s.schoeling@linet-services.de>
Date: Mon, 5 Feb 2018 14:24:25 +0100
Subject: [PATCH] AM::_get_warehouse: schnelleres Query
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Das alte war extrem langsam für Installationen mit vielen Lagerplätzen
---
 SL/AM.pm | 14 ++++++++------
 1 file changed, 8 insertions(+), 6 deletions(-)

diff --git a/SL/AM.pm b/SL/AM.pm
index 7616edbe5..a8a6d3d7d 100644
--- a/SL/AM.pm
+++ b/SL/AM.pm
@@ -1305,12 +1305,14 @@ sub get_warehouse {
   map { $form->{$_} = $ref->{$_} } keys %{ $ref };
 
   $query = <<SQL;
-    SELECT b.*,
-      (   EXISTS(SELECT i.bin_id FROM inventory i WHERE i.bin_id = b.id LIMIT 1)
-       OR EXISTS(SELECT p.bin_id FROM parts     p WHERE p.bin_id = b.id LIMIT 1))
-      AS in_use
-    FROM bin b
-    WHERE b.warehouse_id = ?
+   SELECT b.*, use.in_use
+     FROM bin b
+     LEFT JOIN (
+       SELECT DISTINCT bin_id, TRUE AS in_use FROM inventory
+       UNION
+       SELECT DISTINCT bin_id, TRUE AS in_use FROM parts
+     ) use ON use.bin_id = b.id
+     WHERE b.warehouse_id = ?;
 SQL
 
   $form->{BINS} = selectall_hashref_query($form, $dbh, $query, conv_i($form->{id}));
-- 
2.20.1