Discussion :

[sagopa]

Bonjour,

J'ai créée une procédure stockée qui d'abord sélectionne une liste d'id.
Ensuite en parcourant cette liste je fait des suppressions dans des tables différentes:

SELECT le_id INTO listLeId FROM ... WHERE ...

FOR myId IN SELECT listLeId
LOOP

DELETE FROM A WHERE li_le_id = myId;

DELETE FROM B WHERE lv_le_id = myId;

DELETE FROM C WHERE le_id = myId;

END LOOP;

J'ai stocké le résultat de ma première requête dans une "variable" afin d'éviter de l'exécuter 3 fois.
Cependant, la suppression ligne par ligne prend beaucoup de temps.

Je ne connais pas les différentes méthodes en PL/SQL ; voyez-vous une solution pour me guider svp.

Note: j'utilise PostgreSQL 8.3

Merci par avance.

[punkoff]

bonjour,


Alors vous utilisez le langage SQL qui est un langage ensembliste.

Là vous faites un traitement itératif => c'est lent !


A moins d'avoir des raisons sépcifique pour faire un tel traitement, revoyez votre procedure pour ne faire que 3 requêtes de type DELETE. (et enlevez cette boucle !)

[sagopa]

En effet, c'est plus lent.

Mais dans ce cas je vais devoir faire 3 requêtes ayant une sous-requête identique.

N'est il pas possible d'éviter cela en stockant le résultat dans une sorte de variable afin d'avoir quelque chose comme ça:

MyReq = SELECT le_id INTO listLeId FROM ... WHERE ...

DELETE FROM A WHERE li_le_id IN (MyReq);
DELETE FROM B WHERE lv_le_id IN (MyReq);
DELETE FROM C WHERE le_id IN (MyReq);

[punkoff]

il faudrait stocker ce résultat dans une table temporaire si vous ne voulez pas ré-exécuter la sous-requête.

Toute autre approche impliquera un traitement itératif => lent


Concernant la table temporaire :
- faire un vacuum analyze de la table temporaire avant requêtage.
- positionner des index sur cette table temporaire.


Au final, je suis pas sur que ça en vaille le coup.

Quelle est votre requête select, ainsi que vos requête delete ?

edit: pour aller plus loin, typiquement ce genre de traitement induit un test d’existence. Ceci est optimisé au niveau du sgbd et vouloir le contourner avec un mécanisme autre ne peut être bon au niveau perf

[sagopa]

L'exécution de ces requêtes prend plus de 30 min!!

Voici le code:

CREATE OR REPLACE FUNCTION clearLogs
(
x_airlockName CHARACTER VARYING,
x_stopDateTime TIMESTAMP
)
RETURNS BOOLEAN AS $$
DECLARE

listEventsID int4[] := '{}';
listEventsGroupsID int4[] := '{}';
eventID log_events.le_id%TYPE;
eventsGroupID log_events.le_leg_id%TYPE;

BEGIN

-- delete images associated to the events to delete
DELETE FROM log_images
WHERE li_le_id IN (
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

-- then delete videos associated to the events to delete
DELETE FROM log_videos
WHERE lv_le_id IN (
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

-- then delete the events
DELETE FROM log_events
WHERE le_id IN (
SELECT le_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

-- finally delete the groups events
DELETE FROM log_events_groups
WHERE leg_id IN (
SELECT le_leg_id
FROM airlocks INNER JOIN log_events ON a_id = le_a_id
LEFT OUTER JOIN log_events_groups ON le_leg_id = leg_id
WHERE le_datetime < x_stopDateTime
AND a_name = x_airlockName
AND le_do_not_delete = false
AND (le_leg_id IS NULL
-- we keep the group event if at least 1 event should not be deleted
-- or at least 1 event is too recent according to the stop date time
OR le_leg_id NOT IN (
SELECT DISTINCT le_leg_id FROM log_events
WHERE le_leg_id IS NOT NULL
AND (le_do_not_delete = true OR le_datetime >= x_stopDateTime)
)
)
);

RETURN true;

END;
$$ LANGUAGE plpgsql;

[punkoff]

Pour des problèmes d'optimisations, il faut :
- la structure des tables + index
- que vous expliquiez le plus simplement possible votre besoin (afin que l'on puisse vérifier que la syntaxe des requêtes sql que vous utilisez est la bonne)
- le plan d'execution de vos requête (explain analyze, et ceci il le faudrait sur la requete select uniqueument et la requete entiere avec le delete)


edit : sinon un exemple d'utilisation des tables temporaires :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
do $$
declare 
val text;
curs1 cursor for explain analyze select * from t_temp where id between 2 and 1000;
 
begin
create temporary table t_temp (id int) on commit drop;
 
insert into t_temp (select generate_series(1, 10000));
create index idx_t on t_temp (id);
 
 
analyze t_temp;
 
for val in curs1 loop
raise notice '%', val;
end loop;
 
end $$;