Développeurs, arrêtez de partir en boucle...

Vous n'allez pas tomber de votre chaise : "faire des boucles dans un langage de programmation, plutôt qu'un SELECT, pour insérer des données dans une table a un cout". Et ne parlons même pas de les ramener sur un client pour les remettre en base de données.

Cet article ne vous apprendra surement rien comme souvent. Il illustre, en revanche, assez bien, ce prix à payer. Un exemple a parfois plus de vertu qu'une longue discussion.

Evidemment ça dépend de votre machine, de vos structures, des volumes manipulés et de beaucoup d'autres facteurs ; faîtes donc ces tests vous même. Ça ne vous prendra pas plus de 5'. Et, si après ça, vous arrivez à transformer une seule boucle en SELECT, vous aurez contribué, vous aussi, à améliorer ce monde...

Scripts de test

Ne pipons pas les dés dès le départ. Pour être le plus conservateur possible, vous créez une simple table sans index ni contraintes. Vous pourrez ensuite être plus méchant...

create table T1 (id number, text varchar2(1000)) ;
set timing on

Votre premier exemple consiste à insérer 10000 lignes dans la table à partir d'un select. Evidemment, le temps d'exécution du select n'est pas ce qu'on veut mesurer. L'ordre ci-dessous est généralement assez performant pour illustrer le propos, même si pour 1 millions de lignes, il y aurait plus efficace :

insert into T1
  (select rownum id, 
           rpad('X',1000) text 
     from dual 
  connect by level <= 10000);
rollback;

Comparons avec le même "programme" avec une boucle en PL/SQL. 

begin
   for i in (select rownum id, 
                    rpad('X',1000) text 
               from dual 
            connect by level <= 10000) loop
      insert into T1 values (i.id, i.text);
   end loop;
end;
/
rollback;

Mais c'est vrai, le PL/SQL est un langage interprété alors pourquoi ne pas le compiler ?

create or replace procedure insert_5000 is
begin
   for i in (select rownum id, 
                    rpad('X',1000) text 
               from dual 
            connect by level <= 10000) loop
      insert into T1 values (i.id, i.text);
   end loop;
end;
/
alter procedure insert_5000 
  compile plsql_code_type=native;
exec insert_5000
rollback;

et pour finir, juste pour fixer les idées et bien qu'elle soit souvent inutilisable, considérez l'optimisation ci-dessous :

alter table T1 nologging;
insert /*+ append */ into T1 
   (select rownum id, 
           rpad('X',1000) text 
     from dual 
  connect by level <= 10000);
rollback;

Résultats

Voici donc les résultats moyens que j'ai obtenu, sur un serveur Linux x86_64 en 11.2.0.3 après une dizaine d'exécution par scénario.

Scénario	Temps (secondes)
INSERT (SELECT)	0.3
Boucle PL/SQL	1.0
Boucle PL/SQL compilée	1.0
INSERT (SELECT /*+ APPEND */)	0.05

Il ne s'agit pas d'en faire une règle. Pensez-y la prochaine fois que vous écrivez FOR ou WHILE en PL/SQL...