Performances : SQL vs PL/SQL

Version imprimable

14/06/2012, 15h09
blackangel_xwing

Performances : SQL vs PL/SQL

En ce qui concerne la performance, il est préférable d'utiliser l'instruction if-then-else ou la clause where ?
Merci pour votre reponse ;)
14/06/2012, 15h25
Waldar

Ça dépend.
14/06/2012, 21h43
pachot

Bonjour,
Une clause where peut servir à filtrer des données lors de l'accès alors qu'une condition pl/sql va lire toutes les données pour les filtrer ensuite. Donc la réponse serait en faveur du where.
Cordialement,
Franck.

Je comprends que la clause where charge la mémoire avec les données filtrées tandis que l'instruction if-then else charge la mémoire avec toutes les données, c'est vrai?
Donc, le code suivant :
Code:

1 2 3 4 5 6 select case when x > 3 then 'ok' else 'not ok' end action; for r in c loop update.. set.. where.. and r.action = 'ok'
est meilleur que :
Code:

1 2 3 4 5 6 7 select case when x > 3 then 'ok' else 'not ok' end action; for r in c loop if r.action = 'ok' update.. set.. where..
Merci,
blackangel

Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 select case when x > 3 then 'ok' else 'not ok' end action; for r in c loop update.. set.. where.. and r.action = 'ok' est meilleure que : select case when x > 3 then 'ok' else 'not ok' end action; for r in c loop if r.action = 'ok' update.. set.. where..
8O8O8O8O

Et dans ton deuxième cas tu mets quoi dans la clause WHERE à ton avis ???
J'ai pas l'impression que tu aies compris ta propre question !

La différence porte sur un exemple tel que :
Code:

1 2 3 4 5 6 7 8 FOR i IN (SELECT col1, col2 FROM ma_table) LOOP IF (Col2 = 0) THEN UPDATE .. END IF;
Et celui-ci :
Code:

1 2 3 4 5 6 7 8 FOR i IN (SELECT col1, col2 FROM ma_table WHERE Col2 = 0) LOOP UPDATE ..
Le 2eme sera plus performant car le nombre de lignes sélectionnées est conditionné par la clause WHERE, en résulte (normalement) un nombre de lignes moindre à traiter

15/06/2012, 10h13
blackangel_xwing
La situation exacte est le suivante :

j'ai créé une table log :
Code:

1 2 3 4 5 6 create table log as SELECT case when date > trunc(sysdate-20) then 'ok' else 'not ok' end action, * from table_y
Je veux faire update sur la table_y en fonction de chaque cas ( et puis faire update sur log pour voir les cas traités avec succès ou échec).
Code:

1 2 3 for r in (select * from log) loop if r.action = 'ok' update table_y set.. where..
ou
Code:

1 2 for r in (select * from log) loop update.. set.. where.. and r.action = 'ok'
Je voudrais savoir si le 2eme est plus performant :)
15/06/2012, 10h25
FSiebert

Bonjour,

Citation:

Envoyé par blackangel_xwing

La situation exacte est le suivante

Je trouve votre explication encore très imprécise...

Pourquoi ne mettez-vous pas la condition sur le SELECT (cela serait plus performant) ?

Expliquez VRAIMENT et COMPLÈTEMENT ce que vous voulez faire, vous aurez une réponse plus rapide et bien plus adaptée à vos besoins.

J'essaie d'être plus explicite ;)

Je ne peut pas mettre la condition sur le select parce que je necessite de faire update sur table - source en fonction de la condition date > trunc (sysdate-20); j'utilise la table log pour voir les cas traités avec succès/échec et retenir les erreurs en cas d'échec;

La première tentative a été :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table log as
SELECT  y.*
from table_source y
 
for r in (select * from log) loop
begin
 
if r.date > trunc(sysdate-20) then 
	update table_source  y set y.exec ='D' where y.id = r.id
	update log set obs ='exécuté' where id = r.id
else
 
	update table_source  y set y.rej ='D' where y.id = r.id
	update log set obs ='rejeté' where id = r.id
end if ;
 
exception when others then
		update log set obs ='err' where id = r.id 
end;
end loop;

Mais j'utilisais "select case " parce que je pensais qu'il est plus performant, corrigez-moi si je me trompe ..

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
create table log as
SELECT
case when date > trunc(sysdate-20) then 'ok'
else 'not ok'
end action, *
from table_source y 
 
-- 1ère version
for r in (select * from log) loop
if r.action ='ok' then
	begin
		update table_source  y set y.exec ='D' where y.id = r.id
		update log set obs ='exécuté' where id = r.id
	exception when others then
		update log set obs ='err' where id = r.id 
    	end;
else 
	begin
		update table_source  y set y.rej ='D' where y.id = r.id
		update log set obs ='rejeté' where id = r.id
	exception when others then
		update log set obs ='err' where id = r.id 
	end;
end if;
end loop;
 
--la 2eme
for r in (select * from log) loop
begin
	update table_source  y set y.exec ='D' where y.id = r.id and r.action ='ok'
	    update log set obs ='exécuté' where id = r.id and r.action ='ok'
	update table_source  y set y.rej ='D' where y.id = r.id and r.action ='not ok'
	    update log set obs ='rejeté' where id = r.id and r.action ='not ok'
exception when others then
        update log set obs ='err' where id = r.id 
end;
end loop;

Quelle version est donc plus performante?

15/06/2012, 13h52
McM
Il sera toujours moins performant de faire un update qui va updater 0 ligne que de ne pas faire d'update !

Donc entre ce code qui fait 2 updates dont un qui ne va rien faire mais qu'Oracle va quand même chercher à savoir s'il y a des données
Code:

1 2 UPDATE table_source y SET y.exec ='D' WHERE y.ID = r.ID AND r.action ='ok' UPDATE table_source y SET y.rej ='D' WHERE y.ID = r.ID AND r.action ='not ok'
et ce code qui ne fait qu'un seul update suivant une variable
Code:

1 2 3 4 5 6 IF r.action = 'ok' THEN UPDATE table_source y SET y.exec ='D' WHERE y.ID = r.ID ELSE UPDATE table_source y SET y.rej ='D' WHERE y.ID = r.ID END IF;
C'est forcément le deuxième code.
15/06/2012, 13h57
Scriuiw

La version la plus performante est celle que vous n'avez pas exploitée, c'est à dire en FULL SQL.

Votre code est très facilement transposable en commande ensembliste

PS : A moins que dans votre code il n'y ait des appels à des procédures, fonctions, ou autres tests/manipulations de données que l'on ne peut voir dans votre extrait de code.
15/06/2012, 15h04
McM

Sauf pour traiter les erreurs au niveau ligne. Imagine qu'il y a un trigger sur table_source qui peut sortir en erreur, un seul update ne serait pas gérable.
15/06/2012, 16h08
Scriuiw

C'est aussi une possibilité que je n'avais pas envisagée vue la simplicité de la mise à jour.
Elle est toutefois à prendre en compte :ccool:

Code:

1 2 UPDATE table_source y SET y.exec ='D' WHERE y.ID = r.ID AND r.action ='ok' UPDATE table_source y SET y.rej ='D' WHERE y.ID = r.ID AND r.action ='not ok'
Code:

1 2 3 4 5 6 IF r.action = 'ok' THEN UPDATE table_source y SET y.exec ='D' WHERE y.ID = r.ID ELSE UPDATE table_source y SET y.rej ='D' WHERE y.ID = r.ID END IF;
Pouvez vous me dire comment traite ces deux cas les moteurs pl/sql et sql ?

Citation:

La version la plus performante est celle que vous n'avez pas exploitée, c'est à dire en FULL SQL.

FULL SQL signifie à n'utiliser pas du tout pl/sql ?

Merci beaucoup

15/06/2012, 16h34
Scriuiw

Le moteur PL/SQL ouvre un curseur et pour chaque boucle envoi une instruction au moteur SQL pour mettre à jour la ligne correspondante.

Quand je dis FULL SQL ça n'interdit pas le PL/SQL, c'est juste qu'au lieu de faire un curseur, une boucle une instruction de mise à jour, on fait une instruction SQL qui met à jour toutes les lignes.
15/06/2012, 17h10
mnitu
Citation:

Envoyé par McM

Sauf pour traiter les erreurs au niveau ligne. Imagine qu'il y a un trigger sur table_source qui peut sortir en erreur, un seul update ne serait pas gérable.

Si, facilement, avec DML Error Logging.

Le code suivant:
Code:

1 2 3 4 ... exception when others then UPDATE log SET obs ='err' WHERE id = r.id
est le bug numéro 1 en Pl/SQL.

Faire du DML en boucle
Code:

1 2 3 4 For r In ... Loop Update ... set where r.id... End Loop;
c’est le Pl/SQL de la grande mère !
15/06/2012, 17h25
McM

1: DML Error Logging c'est à partir de la 10gR2
2 : Le when others.. mouais.. .. moi je ne suis pas d'accord avec Tom Kyte.
15/06/2012, 20h59
blackangel_xwing

Citation:

Une clause where peut servir à filtrer des données lors de l'accès alors qu'une condition pl/sql va lire toutes les données pour les filtrer ensuite.

Citation:

Il sera toujours moins performant de faire un update qui va updater 0 ligne que de ne pas faire d'update !

Citation:

Le moteur PL/SQL ouvre un curseur et pour chaque boucle envoi une instruction au moteur SQL pour mettre à jour la ligne correspondante.

Excusez moi pour l'insistance...
Pouvez vous me dire comment traite ces deux cas les moteurs pl/sql et sql (l'instruction if-then-else ne charge plus le moteur pl/sql) ?

Merci beaucoup
18/06/2012, 10h03
mnitu

Citation:

Envoyé par McM

...2 : Le when others.. mouais.. .. moi je ne suis pas d'accord avec Tom Kyte.

OK très bien chacun a ses gouts. Mais analysez l’exemple que j’ai extrait et dit moi vous ne trouvez pas qu’il y a un problème avec cette approche ?
18/06/2012, 10h25
mnitu
Chaque fois quand vous utilisez une construction similaire
Code:

1 2 3 4 5 6 7 8 ... FOR r IN (SELECT * FROM log) loop begin IF r.date > trunc(sysdate-20) then UPDATE table_source y SET y.exec ='D' WHERE y.id = r.id UPDATE log SET obs ='exécuté' WHERE id = r.id
c’est à dire un curseur qui cherche des valeurs pour alimenter par la suite une autre requête SQL vous devez vous poser des questions. En fait ce type de traitement est inefficace d’une part à cause des changements de contexte entre le moteur PL/SQL et le moteur SQL et d’autre part à cause du traitement ligne par ligne qu’il suppose.
Deux solutions sont envisageables pour améliorer ce code :
- Solution SQL : utilisez une seule requête SQL, dans votre cas un seul Update ou Merge
- Solution PL/SQL : utilisez des collections c’est à dire traitement par lot et l’instruction FORALL…
17/07/2012, 12h51
thepierre

oracle = machine à sql.:ccool:

donc il faut oublier le pl/sql, qui n'est qu'un pis-aller pour transférer un programme fortran des années 1970 vers oracle avec un taux de paresse maximum. :cry:
17/07/2012, 14h15
mnitu

Vous vous trompez royalement. PL/SQL fait partie de la famille de langage ADA et n’a rien emprunté à FORTRAN. Sans PL/SQL Oracle ne sera plus là aujourd’hui. Mais comme tout outil il doit être employé judicieusement.
17/07/2012, 14h22
Pomalaix

Citation:

Envoyé par mnitu

Sans PL/SQL Oracle ne sera plus là aujourd’hui.

Ah tiens, tu peux développer cet avis ?
17/07/2012, 14h30
mnitu

A l’époque ou Oracle a décidé d’utiliser PL/SQL pour les procédures stockées, Sysbase était bien avancée sur ce point et les grignoté pas mal des marchés.

Mais imagine un seul moment Oracle sans PL/SQL …
17/07/2012, 14h55
Pomalaix

Il a quand même fallu attendre la version 6 d'Oracle (1988) pour le PL/SQL.
Mais je ne sais pas à partir de quelle version le PL/SQL est devenu un composant obligatoire. En V7, de mémoire, on pouvait se dispenser d'exécuter CATPROC.SQL à la création de la base.

C'est sûr que le PL/SQL est important, mais je crois que ce qui a fait le succès d'Oracle face à ses concurrents dans les années 90, c'est le verrouillage au niveau ligne et le mécanisme de lecture consistante multiversion basé sur les segments d'annulation.

Voici un lien vers un très bel historique des produits Oracle depuis le début :
http://oracle.com.edgesuite.net/timeline/oracle/
Ca peut être long à se charger, mais ça vaut le coup. (Noter que les "vignettes" sont cliquables).
18/07/2012, 09h39
Bluedeep

Citation:

Envoyé par mnitu

Vous vous trompez royalement. PL/SQL fait partie de la famille de langage ADA et n’a rien emprunté à FORTRAN. Sans PL/SQL Oracle ne sera plus là aujourd’hui. Mais comme tout outil il doit être employé judicieusement.

En effet, je ne vois pas du tout quelle proximité avec le FORTRAN on peut trouver à PL/SQL.
18/07/2012, 16h06
thepierre

Citation:

donc il faut oublier le pl/sql, qui n'est qu'un pis-aller pour transférer un programme fortran des années 1970 vers oracle avec un taux de paresse maximum.

[Pour ceux qui n'ont pas compris la métaphore] ceci signifie que quand il faut transférer un programme codé dans un langage des plus basiques et historiquement le plus répandu, si l'on ne veut pas faire trop d'effort, on effectue un quasi-copier-coller de ce qui a été fait vers une proc ou un script pl/sql, langage qui était également assez basique jusque la version 8 d'oracle.

Actuellement, tous les programmes pl/sql à volumétrie respectable sont revus et réécris en ensembliste, de manière à ce que le système BD d'oracle puisse travailler à plein et tel qu'il a été conçu -exécuter des requêtes sql- , et en parallélisation native. Mon boulot au quotidien...
18/07/2012, 16h27
mnitu

Il n’y avait rien dans la version PL/SQL pré Oracle 8 qui vous obligeait à mal écrire les programmes, à part l’incompréhension de ce qui se passait derrière. Ce qui reste toujours valable aujourd’hui.

Remarque: Vu votre nouvelle intervention je pense que vous avez voulu dire Cobol à la place de Fortran.
18/07/2012, 16h33
Bluedeep

Citation:

Envoyé par thepierre

[Pour ceux qui n'ont pas compris la métaphore] ceci signifie que quand il faut transférer un programme codé dans un langage des plus basiques et historiquement le plus répandu,...

Que FORTRAN ait été le premier langage, c'est un fait historique.
Mais "historiquement le plus répandu", je ne sais pas où vous avez été chercher cela .....

Quand à parler de langage "basique" pour le FORTRAN .......
18/07/2012, 16h53
thepierre

mon deuxième message faisait pourtant abstraction de toute référence à fortran...pour ne pas heurter les sensibilités...:mouarf:

Citation:

Envoyé par mnitu

Il n’y avait rien dans la version PL/SQL pré Oracle 8 qui vous obligeait à mal écrire les programmes, à part l’incompréhension de ce qui se passait derrière. Ce qui reste toujours valable aujourd’hui.

On peut évidemment écrire un beau programme en pl/sql, compréhension et/ou incompréhension fonctionnelle mise à part. Mais rien ne justifiera l'utilisation d'un ~3GL~ à la place de la langue native de la bd oracle : le sql.

Y a-t-il encore des problématiques "incodables" en sql, de nos jours??

Citation:

Envoyé par mnitu

Remarque: Vu votre nouvelle intervention je pense que vous avez voulu dire Cobol à la place de Fortran.

je ne parle pas d'un langage source en particulier, je parle des solutions du moindre effort en général pour migrer un programme d'un système vers un autre, et en particulier vers une bd oracle.

(zut, à force, on va croire que je suis amoureux d'oracle :oops:)