Projekt

Allgemein

Profil

« Zurück | Weiter » 

Revision cf263726

Von Moritz Bunkus vor fast 10 Jahren hinzugefügt

  • ID cf2637263435860d7ef0fd5f5d408dc8ee1366cd
  • Vorgänger b3a1f678
  • Nachfolger c3ab29e6

CVars-Lösch-Queries deutlich effizienter gestaltet

PostgreSQL kann Queries à la »DELETE … WHERE … IN (SELECT…)« nicht gut
optimieren und erzeugt dafür exponentielle Laufzeit. Viel schneller ist,
eine Vorselektierung mit normalen JOINs zu nutzen und nachher beim
DELETE ein WHERE EXIST (…) mit Bezug auf die zu löschende Tabelle
einzusetzen.

Unterschiede anzeigen:

sql/Pg-upgrade2/delete_cvars_on_trans_deletion.sql
4 4 

  		




  5
  5
  
    
-- 1. Alle benutzerdefinierten Variablen löschen, für die es keine


  		




  6
  6
  
    
-- Einträge in den dazugehörigen Tabellen mehr gibt.


  		




  
  7
  
    
CREATE TEMPORARY TABLE cvars_to_delete AS


  		




  
  8
  
    
SELECT cv.id


  		




  
  9
  
    
FROM custom_variables cv


  		




  
  10
  
    
LEFT JOIN custom_variable_configs cfg ON (cv.config_id = cfg.id)


  		




  7
  11
  
    

  		




  8
  12
  
    
-- 1.1. Alle CVars für Artikel selber (sub_module ist leer):


  		




  9
  
  
    
DELETE FROM custom_variables


  		




  10
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))


  		




  11
  
  
    
  AND (COALESCE(sub_module, '') = '')


  		




  12
  
  
    
  AND NOT EXISTS (SELECT id FROM parts WHERE parts.id = custom_variables.trans_id);


  		




  
  13
  
    
LEFT JOIN parts p ON (


  		




  
  14
  
    
      (cfg.module = 'IC')


  		




  
  15
  
    
  AND ((cv.sub_module IS NULL) OR (cv.sub_module = ''))


  		




  
  16
  
    
  AND (cv.trans_id = p.id))


  		




  13
  17
  
    

  		




  14
  18
  
    
-- 1.2. Alle CVars für Angebote/Aufträge, Lieferscheine, Rechnungen


  		




  15
  
  
    
-- (sub_module gesetzt):


  		




  16
  
  
    
DELETE FROM custom_variables


  		




  17
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))


  		




  18
  
  
    
  AND (sub_module = 'orderitems')


  		




  19
  
  
    
  AND NOT EXISTS (SELECT id FROM orderitems WHERE orderitems.id = custom_variables.trans_id);


  		




  20
  
  
    

  		




  21
  
  
    
DELETE FROM custom_variables


  		




  22
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))


  		




  23
  
  
    
  AND (sub_module = 'delivery_order_items')


  		




  24
  
  
    
  AND NOT EXISTS (SELECT id FROM delivery_order_items WHERE delivery_order_items.id = custom_variables.trans_id);


  		




  25
  
  
    

  		




  26
  
  
    
DELETE FROM custom_variables


  		




  27
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'IC'))


  		




  28
  
  
    
  AND (sub_module = 'invoice')


  		




  29
  
  
    
  AND NOT EXISTS (SELECT id FROM invoice WHERE invoice.id = custom_variables.trans_id);


  		




  
  19
  
    
-- (cv.sub_module gesetzt):


  		




  
  20
  
    
LEFT JOIN orderitems oi ON (


  		




  
  21
  
    
      (cfg.module    = 'IC')


  		




  
  22
  
    
  AND (cv.sub_module = 'orderitems')


  		




  
  23
  
    
  AND (cv.trans_id   = oi.id))


  		




  
  24
  
    

  		




  
  25
  
    
LEFT JOIN delivery_order_items doi ON (


  		




  
  26
  
    
      (cfg.module    = 'IC')


  		




  
  27
  
    
  AND (cv.sub_module = 'delivery_order_items')


  		




  
  28
  
    
  AND (cv.trans_id   = doi.id))


  		




  
  29
  
    

  		




  
  30
  
    
LEFT JOIN invoice i ON (


  		




  
  31
  
    
      (cfg.module    = 'IC')


  		




  
  32
  
    
  AND (cv.sub_module = 'invoice')


  		




  
  33
  
    
  AND (cv.trans_id   = i.id))


  		




  30
  34
  
    

  		




  31
  35
  
    
-- 1.3. Alle CVars für Kunden/Lieferanten:


  		




  32
  
  
    
DELETE FROM custom_variables


  		




  33
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'CT'))


  		




  34
  
  
    
  AND NOT EXISTS (SELECT id FROM customer WHERE customer.id = custom_variables.trans_id UNION SELECT id FROM vendor WHERE vendor.id = custom_variables.trans_id);


  		




  
  36
  
    
LEFT JOIN customer c ON (


  		




  
  37
  
    
      (cfg.module  = 'CT')


  		




  
  38
  
    
  AND (cv.trans_id = c.id))


  		




  
  39
  
    

  		




  
  40
  
    
LEFT JOIN vendor v ON (


  		




  
  41
  
    
      (cfg.module  = 'CT')


  		




  
  42
  
    
  AND (cv.trans_id = v.id))


  		




  35
  43
  
    

  		




  36
  44
  
    
-- 1.4. Alle CVars für Ansprechpersonen:


  		




  37
  
  
    
DELETE FROM custom_variables


  		




  38
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'Contacts'))


  		




  39
  
  
    
  AND NOT EXISTS (SELECT cp_id FROM contacts WHERE contacts.cp_id = custom_variables.trans_id);


  		




  
  45
  
    
LEFT JOIN contacts cp ON (


  		




  
  46
  
    
      (cfg.module  = 'Contacts')


  		




  
  47
  
    
  AND (cv.trans_id = cp.cp_id))


  		




  40
  48
  
    

  		




  41
  49
  
    
-- 1.5. Alle CVars für Projekte:


  		




  
  50
  
    
LEFT JOIN project pr ON (


  		




  
  51
  
    
      (cfg.module  = 'Projects')


  		




  
  52
  
    
  AND (cv.trans_id = pr.id))


  		




  
  53
  
    

  		




  
  54
  
    
-- Nun diejenigen Zeilen nehmen, für die es in keiner der verknüpften


  		




  
  55
  
    
-- Tabellen eine korrespondierende Zeile gibt.


  		




  
  56
  
    
WHERE (p.id     IS NULL)


  		




  
  57
  
    
  AND (oi.id    IS NULL)


  		




  
  58
  
    
  AND (doi.id   IS NULL)


  		




  
  59
  
    
  AND (i.id     IS NULL)


  		




  
  60
  
    
  AND (c.id     IS NULL)


  		




  
  61
  
    
  AND (v.id     IS NULL)


  		




  
  62
  
    
  AND (cp.cp_id IS NULL)


  		




  
  63
  
    
  AND (pr.id    IS NULL);


  		




  
  64
  
    

  		




  42
  65
  
    
DELETE FROM custom_variables


  		




  43
  
  
    
WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = 'Projects'))


  		




  44
  
  
    
  AND NOT EXISTS (SELECT id FROM project WHERE project.id = custom_variables.trans_id);


  		




  
  66
  
    
WHERE EXISTS (


  		




  
  67
  
    
  SELECT ctd.id


  		




  
  68
  
    
  FROM cvars_to_delete ctd


  		




  
  69
  
    
  WHERE ctd.id = custom_variables.id


  		




  
  70
  
    
);


  		




  45
  71
  
    

  		




  46
  72
  
    
-- 2. Triggerfunktionen erstellen, die die benutzerdefinierten


  		




  47
  73
  
    
-- Variablen löschen.


  		












Auch abrufbar als:  Unified diff