Par contre, plutôt que de dropper les index (ce qui peut être catastrophique si d'autres personnes utilisent la table en même temps), Il vaut mieux
- soit les désactiver lors de la mise à jour s'il n'y a pas de PrimaryKey sur la table (uniquement des index unique ou simple, clusterés ou non)
- soit désactiver leur mise à jour (plus lent, mais ça marche sur les PK)
Voir la doc de "ALTER INDEX" pour plus d'infos.
A noter que là j'ai voulu faire des petits tests sous SQL Server 2005 et... C'est très étrange pour les résultats !
La piste des index pourrait bien être à écarter sérieusement ! En effet, sous SQL Server 2005 Express tout du moins, les résultats sont totalement en contradiction avec l'idée reçue des index qui ralentissent les requêtes de INS/DEL
create table test (id numeric, label varchar(50));
create unique index uix_test on test (id);
declare @i numeric
set @i = 1
while @i < 10000
begin
insert into test (id, label) values (@i, 'Label ' + cast(@i as varchar))
select @i = @i + 1
end;
|
=> 1 minute pile
Sans index :
create table test (id numeric, label varchar(50));
declare @i numeric
set @i = 1
while @i <= 10000
begin
insert into test (id, label) values (@i, 'Label ' + cast(@i as varchar))
select @i = @i + 1
end;
|
=> 1 minute pile
Avec une PK active
drop table test
create table test (id numeric primary key, label varchar(50));
declare @i numeric
set @i = 1
while @i <= 10000
begin
insert into test (id, label) values (@i, 'Label ' + cast(@i as varchar))
select @i = @i + 1
end;
|
=> 1 minute pile
Cette partie du test est à mettre de côté, car il semblerait que ce soit le T-SQL qui soit très lent.
Ensuite :
insert into test select id + 10000, label from test;
insert into test select id + 20000, label from test;
insert into test select id + 40000, label from test;
insert into test select id + 80000, label from test;
insert into test select id + 160000, label from test;
insert into test select id + 320000, label from test;
insert into test select id + 640000, label from test;
insert into test select id + 1280000, label from test;
insert into test select id + 2560000, label from test;
insert into test select id + 5120000, label from test;
insert into test select id + 10240000, label from test;
|
=> environ 2x plus rapide avec la PK active que si je désactive la PK puis que je la recompute ! (30 minutes contre 60 minutes -temps estimé, j'ai arrêté en plein milieu ça me gonflait-)
Truc marrant aussi : avec ce type de requête, j'ai pu insérer 20 millions de lignes en quelques minutes alors que le T-SQL mettait un temps pas possible pour en insérer 10k 
Test des DELETE donc :
begin transaction;
delete test where id between 1000000 and 5000000;
rollback;
|
=> 3 minutes 1 seconde
Je désactive la mise à jour de la PK ici, plutôt que de la supprimer : en effet, il sera plus facile de retrouver des lignes mises à jour dans un index qui ne se met plus à jour (mais non marqué comme pollué) plutôt que sans index du tout !
begin transaction;
alter index PK__test__72C60C4A on test set(STATISTICS_NORECOMPUTE=on);
delete test where id between 1000000 and 5000000;
alter index PK__test__72C60C4A on test set(STATISTICS_NORECOMPUTE=off);
alter index PK__test__72C60C4A on test rebuild;
rollback;
|
=> 5 minutes 1 seconde
Bref, c'est sans appel : c'est bien plus rapide avec ne serait-ce que la PK activée !
Test avec un second index :
create index ix_test on test (label);
|
10 minutes 26 secondes 
=> Hors de question de le droper donc, puis de le recréer... A moins que le DELETE ne mette substanciellement plus de 10 minutes !
begin transaction;
delete test where id between 1000000 and 5000000;
rollback;
|
=> 9 minutes 54 secondes
Donc il est impensable de gagner du temps en supprimant l'index puis en le recréant !
Bon, un test plus probant, qui utilise l'index en question...
begin transaction;
delete test where label like 'Label 100%';
rollback;
|
=> 3 secondes
begin transaction;
alter index ix_test on test set(STATISTICS_NORECOMPUTE=on);
delete test where label like 'Label 100%';
alter index ix_test on test set(STATISTICS_NORECOMPUTE=off);
alter index ix_test on test rebuild;
rollback;
|
=> 15 minutes 23 secondes (lol)
begin transaction;
alter index ix_test on test disable;
delete test where label like 'Label 100%';
alter index ix_test on test rebuild;
rollback;
|
=> J'ai stoppé au bout de 1 minute 30 secondes (c'est vraiment plus lent, ça sert à rien de se faire du mal)
=> 0 secondes 
A noter que le temps final doit être pas mal faussé par la transaction, mais ça me gavait de réinsérer les lignes effacées 
Dans les deux cas, le temps lié à la transaction doit être le même pour les deux cas.
A noter aussi que mon serveur, c'est un peu Agecanonix. Donc faut pas se fier à sa lenteur pour tirer des conclusions quant à la rapidité globale des traîtements, avec un serveur récent, ça doit même pas être le 10°... (et dire que je l'ai payé la peau du cul mon joli serveur 
)
J'aime bien la tronche de mon log des transactions aussi maintenant : près de 4 Go 
(disque physique séparé, donc aucune incidence sur les temps de traîtements)
PS : Je serais plutôt curieux d'avoir l'avis d'un expert (DBA) sur mon test, parceque c'est vrai que les résultats vont à l'encontre totale de ce à quoi on a toujours été habitué ! Je serais curieux aussi de voir ce que ça donne avec d'autres SGBD, parceque là... Moi je vote et je dis que c'est chelou
DANS TOUS LES CAS :
DB2 supporte très certainement les PARTITIONS, d'autant plus qu'il est souvent utilisé pour des datawarehouse énormes (plusieurs To par table), et donc où le partitionnement semble inévitable.
Sous SQL Server 2005, le partitionnement accélère les insertions/mises à jours/select/delete (possibilité de //ser les traîtements d'une même requête entre les partitions).
Mais surtout, une partition met moins de 1 seconde à être virée d'une table. Donc des partitions changant avec le critère qui te permet d'isoler les 6 millions de lignes (je suppose, le mois d'une date par exemple) et zou ! Ton problème est résolu : quand tu fais le ménage, tu shootes simplement les partitions qui contiennent les lignes à supprimer ! Le temps d'éxécution devrait être très proche de celui d'un "drop table", c'est à dire plus proche de 0 secondes que d'autrechose
Message édité par MagicBuzz le 23-11-2006 à 00:42:46
, j'avais jamais pensé à utiliser le FETCH FIRST x ROWS ONLY avec un DELETE...