Optimisation script sqlplus

Bonjour bonjour,

Petite demande qui va amener à deux questions, potentiellement bête, mais sûr laquelle je n'arrive pas à trouver de certitude.
Voila mon petit soucis :

Un script sql de 3 000 lignes avec une boucle de curseur principale qui appelle d'autres curseurs, fait tout plein d'opérations youpi tralala...
Dans cette boucle, après l'appel de 4-5 curseur, il y a une insertion dans une table.

Ceci amène à ma première question : Vaut-il mieux, dans le cas d'une loop sur des milliers de lignes, faire un insert de suite, ou écrire les lignes dans un fichier pour ensuite faire un sqlload dessus ? Je sais que le sql load est plus rapide que l'insert, mais après, c'est par rapport à l'écriture du fichier où je ne suis pas sûr que le gain de temps se fasse remarquer...


Dans ce script aussi, il y a pas mal de if sur des données du curseur amenant la boucle principal. Des if du style :

Code:

---

If (moncurseur.data = 0) THEN moncurseur.data = 'CC' ELSE moncurseur.data = 'VV'

---

Je ne sais pas si c'est propre à moi, mais ca me défrise un peu, je serais potentiellement passé par des clause CASE ou DECODE de suite dans le select plutôt que par des if dans le curseur.

Ma deuxième question est donc : if vs case ? Quel serait le plus rapide (si bien sûr il y a un plus rapide)

Pour info : Le script est donc un fichier .sql appelé avec sqlplus sous linux

Merci d'avance

Bisous bisous

Bonjour,

Les curseurs, c'est le mal.

Les développeurs vont dire que c'est plus lisible que du code SQL ; la preuve.

Si possible revenir à l'expression du besoin (la doc ??) et recoder ça avec un INSERT ... SELECT ...

Pour répondre à la question :
Solution 1 : remplir ligne à ligne un fichier externe à la base, et, quand le traitement est fini reverser les lignes du fichier dans la base
Solution 2 : remplir la table ligne à ligne avec
(S2.1) une transaction sur l'ensemble des lignes
(S2.2) une transaction par insert

La S1 est pour moi la pire :
-le périmètre de sécurité est élargi
-la gestion des erreurs est élargie (place sur disque, droits, suppression sauvage pendant le traitement, ...)

Du coup la S2 est moins pire mais se pose la question des transactions et de l'isolation des traitements.
-Que se passe t'il si la procédure est lancée plusieurs fois alors que la première exécution n'est pas finie ?
-Coté rollback segment la S2.2 est meilleure mais exige un "scénario de reprise" (dans ce cas, je pense qu'il est plutôt simple : relancer la procédure)
-Coté perf pure la fréquence des COMMIT est en soit une conversation
.trop fréquent = logwriter en continu
.trop peu fréquent = des redo actifs trop longtemps
Bref entre les 2 mon coeur balance.

Vérifie si ton script ne pourrait pas tout simplement se transformer en un simple INSERT ... SELECT ;)

Me souviens avoir repris ainsi quelques scripts PL/SQL qui paraissaient bien complexes avec curseurs imbriqués, tests multiples, compteurs incrémentés à chaque passage, etc et qui se sont simplifiés en une seule requête et un gain de temps de traitement de plusieurs heures.
Le développeur original n'avait en fait rien compris au SQL et traitait les tables comme des fichiers indexés dans un langage procédural classique. :aie:

Bonjour à tous et merci pour vos réponses.
En effet les curseurs, c'est mal, surtout que là, une fois sur deux la personne passe par un curseur avec paramètres, les autres fois par des fonctions... et parfois les deux sont aussi "viables" les uns que les autres...

Alors pour répondre aux questions :

Sur le périmètre de sécurité et gestions des erreurs sur l'écriture de fichier : 75% des scripts existants passent par écriture de fichiers donc la gestion de la place, des droits, tout ça, y'a pas de soucis.
Ensuite, les scripts sont lancés par un ordonnanceur donc si il y a un plantage, c'est tout qui est rollback, le script ne se relancera pas à nouveau et surtout il n'est pas possible que le script tourne plusieurs fois en même temps.

Je sais que au niveau des questions j'étais plus dans la micro optimisation mais c'est ce qui semblait le plus rapide à faire.. Pour le fait de passer pas des select....insert, je suis totalement d'accord, il faudra que je regarde, car il y a en effet tellement de curseurs, fonctions, vérifications ect que ca va être drôle à refaire...

En tout cas merci pour vos avis

Les curseurs sont beaucoup plus rapide que d'écrire un fichier sur le serveur et de le recharger avec sqlloader (ou table externe)

Dire que les curseurs c'est le mal, je ne suis pas d'accord, ça reste de l'algorithmie.
C'est sur que si les curseurs peuvent être remplacés par un INSERT SELECT .. c'est juste un mauvais développement.

Les VUES c'est le mal !! 8-)

Bonjour,

Citation:

---

Envoyé par McM

Dire que les curseurs c'est le mal, je ne suis pas d'accord, ça reste de l'algorithmie.

---

C'est exactement la même position que dire que

Code:

---

SELECT *

---

ça peut être bien, mais faut l'utiliser dans des cas précis.
Dans les faits, le nombre de personnes qui accèdent effectivement à la subtilité sont soit des relecteurs soit des génies car la réalité est que le développeur, payé au mois, doit produire son code pour hier sachant que le cahier des charges arrivera demain.

L'utilisation de curseurs revient à utiliser une logique :
1- de langage de 3ieme génération (description du process)
2- de traitement ligne à ligne
Or le langage SQL ne répond à aucun de ces 2 caractéristiques.
Produire du code interprété dans un environnement spécialisé sans respecter les caractéristiques de base, je ne vois pas en quoi ça peut être autre chose que mal.

Et je suis tout à fait d'accord pour dire que les VUES, utilisées pour de l'encapsulation/réutilisation de code c'est aller, encore une fois, à l'encontre du langage SQL.
Donc oui, les VUES et les CURSEURS sont les outils de la tentation du diable : le mal.

Ps: je crois que je vire spirituel moi là :oops:

Citation:

---

Envoyé par Michel.Priori

L'utilisation de curseurs revient à utiliser une logique :
1- de langage de 3ieme génération (description du process)
2- de traitement ligne à ligne
Or le langage SQL ne répond à aucun de ces 2 caractéristiques.
Produire du code interprété dans un environnement spécialisé sans respecter les caractéristiques de base, je ne vois pas en quoi ça peut être autre chose que mal

---

On n'est pas en SQL, mais en PL/SQL
A partir du moment où je suis en plsql, c'est que j'ai généralement besoin faire du traitement par ligne => curseur.
Le cas où je n'ai pas besoin de curseur est plutôt marginal (ou alors ce sont des fonctions simples).

Bonjour,

Citation:

---

Envoyé par McM

On n'est pas en SQL, mais en PL/SQL

---

Désolé j'ai cru qu'on parlait de Oracle Database.
Fallait le dire qu'on était dans Oracle Form.

Perso je n'ai JAMAIS trouvé d'écriture optimisée de CURSEUR qui soit plus rapide que son équivalent en écriture optimisée en SQL.

L'argument de la maintenabilité du code, est spécieux : à partir d'une certaine complexité la description de l'objectif et celle de l'algo reste incontournable (les fameux commentaires dans le code).
Et en dessous de ce seuil, seule l'expertise du relecteur est en jeux.

Bonjour,

Citation:

---

Envoyé par McM

C'est sur que si les curseurs peuvent être remplacés par un INSERT SELECT .. c'est juste un mauvais développement.

---

Citation:

---

Envoyé par McM

Le cas où je n'ai pas besoin de curseur est plutôt marginal

---

C'est moi ou c'est incohérent ?

A mais non, faut tout lire :

Citation:

---

Envoyé par McM

A partir du moment où je suis en plsql, c'est que j'ai généralement besoin faire du traitement par ligne

---

Je pense que la phrase est mal formulée et que le sens voulu est:

Citation:

---

A partir du moment où j'ai besoin de faire du traitement par ligne, c'est que j'ai généralement besoin faire du plsql

---

Et là ça change tout.

Mais au fait, c'est quand qu'on a "besoin de faire du traitement par ligne" dans un SGBD ?
(pour moi il y a 1 réponse, mais voyons les propositions)

Citation:

---

Envoyé par Michel.Priori

Perso je n'ai JAMAIS trouvé d'écriture optimisée de CURSEUR qui soit plus rapide que son équivalent en écriture optimisée en SQL.

---

Sans doute parce que la raison d'être des curseurs n'est certainement pas d'être plus rapides ou optimisés que du SQL ensembliste.

Pour des perfs qui roxent du poney bien évidemment l'ensembliste va être mettre à genoux une boucle
Par contre si l'on ne souhaite pas l'interruption complète d'un INSERT parce qu'il y a une ligne en erreur sur les 10000 présentes, le curseur peut tout à coup devenir la bonne solution à suivre pour tracer et obtenir des explications sur ce qu'il s'est passé.
Sans compter la complexité de certains contrôles que l'on souhaite intégrés et qu'il serait compliqué d'implémenter en SQL pur.

Il n'y a pas que la performance pure qui compte, il faut aussi prendre en compte l'exploitation et la lisibilité des erreurs.
Entre un INSERT qui pète à cause d'un grain de sable sans donner quelque information que ce soit et une boucle qui trace les erreurs, ça peut faire toute la différence au quotidien.

Bonsoir,

Citation:

---

Envoyé par Scriuiw

Par contre si l'on ne souhaite pas l'interruption complète d'un INSERT parce qu'il y a une ligne en erreur sur les 10000 présentes,

---

J'avoue que j'ai du mal à concevoir que l'instruction INSERT...SELECT... soit par nature atomique.
Je n'ai jamais compris en quoi l'écriture, en lot, de différentes lignes, dans la même table de destination, pouvait rendre incohérentes les données au point d'obliger à ce que l'opération soit transactionnée.
Du coup je me tord un peu la cervelle pour faire une boucle de INSERT...SELECT...LIMIT... jusqu'à ce qu'il n'y ait plus de lignes à insérer.
2 techniques ont retenues mes faveurs : le pointage et le traçage (en fonction des contraintes)

Citation:

---

Envoyé par Scriuiw

le curseur peut tout à coup devenir la bonne solution à suivre pour tracer et obtenir des explications sur ce qu'il s'est passé.

---

Oui.
Oui pour le traçage ligne à ligne.
Mais faut avouer que les ETL font ça très bien.
En dehors de l'import de données (depuis des sources douteuses), réfléchir en lot tout en jouant de la clause WHERE, est une option intéressante.

Citation:

---

Envoyé par Scriuiw

Sans compter la complexité de certains contrôles que l'on souhaite intégrés et qu'il serait compliqué d'implémenter en SQL pur.

---

Des exemples, avec données source à l'appui ?


Note :
Je suis déçu de ne pas avoir lu LE cas d'école où le CURSEUR est indispensable.
Par la suite, peut être.

Bonjour à tous

Merci pour vos réponses, avoir des réponses qui en plus font "débats" est toujours quelque chose d'instructif et de plaisant.

Pour vous donner des nouvelles, j'avais dans l'idée d'en effet passer par un insert....Select en refaisant tout et en rajoutant le hint APPEND (l'insert se fait sur une table de travail qui est truncate à chaque début de script donc ce hint est parfait pour ce que je souhaitais). Malheureusement, quand j'en ai parlé au client, il a pris peur car "cela représente trop de travail et de changement donc on fait pas"...

Il est toujours dommage de voir des personnes refuser de refaire/améliorer un truc sous prétexe que, même si il n'est pas optimal, même si il est trop lent, cela leur fait peur... C'est avec des choix comme cela qu'on se retrouve avec un SI obsolète et incapable de gérer la charge

Bisous bisous

ps : Je mets la conversation en résolue car elle l'est mais n'hésitez pas à continuer ces échanges qui sont dans le respect et instructif :)

Bonjour,

Citation:

---

Envoyé par JeanYvette

Malheureusement, quand j'en ai parlé au client, il a pris peur car "cela représente trop de travail et de changement donc on fait pas"...

---

C'est tout à votre honneur de vouloir informer le client d'une optimisation potentielle.
Mais s'il n'a rien demandé ... 'on ne touche pas au truc qui marche'
Et vouloir argumenter en 2ieme vague va être pris pour de la polémique. Très peu de chance de faire changer d'avis le décideur.
Dans ce cas on note dans un coin la date et la proposition.
Un jour, la confiance aidant, on pourra ressortir les "dossiers". ;)

A l'inverse s'il s'est plaint du fonctionnement actuel, c'est une erreur de vouloir informer des détails techniques.
La réponse attendue est : "Oui, je peux faire. Pas de problèmes."

Le fonctionnement actuel est en effet problématique et on m'a demandé de me pencher dessus.
J'ai dû en informer car il s'agit d'un script majeur pour l'entreprise et qu'il faut parfois faire des reprises dessus (pas sur le script même mais sur certaines données KO). Ce changement passant par des select...insert est trop important et donc nécessite trop de tests, et c'est sur ce point que ça bloque, ils ne veulent pas passer du temps à faire "trop de test"...

J'ai accepté, je vais faire 2-3 modifs par ci par là (y'a quand même des trucs pas très beaux dans le script que je peux changer) mais ça ne sera pas aussi optimal, tant pis

Vous devriez lire la documentation de dbms_errlog

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

begin dbms_errlog.create_error_log('TABLE_FOR_WHICH_LOGGING_INSERT_ERRORS_IS_DESiRED'); end;     insert into TABLE_FOR_WHICH_LOGGING_INSERT_ERRORS_IS_DESiRED(... les colonnes...) ( -- ... select les valeurs à insérer     ) log errors reject limit unlimited;     select * from err$_TABLE_FOR_WHICH_LOGGING_INSERT_ERRORS_IS_DESiRED; -- vous aurez la liste de rows dont le insert à échouer

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

desc err$_TABLE_FOR_WHICH_LOGGING_INSERT_ERRORS_IS_DESiRED ;     Nom NULL ? Type --------------- ------ --------------- ORA_ERR_NUMBER$ NUMBER ORA_ERR_MESG$ VARCHAR2(2000) ORA_ERR_ROWID$ UROWID ORA_ERR_OPTYP$ VARCHAR2(2) ORA_ERR_TAG$ VARCHAR2(2000) ... + colonnes de la table testée...

---

Bonjour,

Citation:

---

Envoyé par Michel.Priori

Note :
Je suis déçu de ne pas avoir lu LE cas d'école où le CURSEUR est indispensable.
Par la suite, peut être.

---

Les problèmes NP-Complets (problème du sac à dos par exemple), dés qu'il y a un peu de volume, nécessitent souvent d'être au moins partiellement itératifs.

Ils sont en théorie solvables de manière ensembliste, à condition de pouvoir s'offrir le luxe d'attendre plusieurs jours (voire années!) le résultat...