Microsoft SQLServer 2000 - Accélérer les insertions en désactivant les index

CREATE VIEW dbo.V_A_INDEX_DETAILS_IXD
AS

SELECT DISTINCT TOP 100 PERCENT WITH TIES
       u.name AS IXD_SCHEMA_NAME,
       o.name AS IXD_TABLE_NAME,
       i.name AS IXD_INDEX_NAME,
       CONSTRAINT_TYPE AS IXD_CONSTRAINT_TYPE,
       CASE
          WHEN i.indid = 0 THEN 'TABLE'
          WHEN i.indid = 1 THEN 'CLUSTER'
          WHEN i.indid BETWEEN 2 AND 254 THEN 'HEAP'
          WHEN i.indid = 255 THEN 'TXTEIMAGE'
       END AS IXD_INDEX_TYPE,
       INDEXPROPERTY(o.id, i.name, 'IsUnique') AS IXD_IS_UNIQUE,
       INDEXPROPERTY(o.id, i.name, 'IndexFillFactor') AS IXD_FILL_FACTOR,
       c.name AS IXD_COL_NAME,
       DATA_TYPE + '('+
       CAST(COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) AS VARCHAR(16))
           + COALESCE(', '+CAST(NULLIF
           (NUMERIC_SCALE, 0) AS VARCHAR(16)) , '') +')' AS IXD_COL_TYPE,
       k.keyno AS IXD_COL_IDX_ORDER,
       CASE
          WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.keyno, 'IsDescending' ) = 0 THEN 'ASC'
          WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.keyno, 'IsDescending' ) = 1 THEN 'DESC'
          WHEN INDEXKEY_PROPERTY (o.id , i.indid , k.keyno, 'IsDescending' ) IS NULL THEN ''
       END AS IXD_COL_DATA_ORDER,
       INDEXPROPERTY(o.id, i.name, 'IsRowLockDisallowed')  AS IXD_ROW_LOCK_DISALLOWED,
       INDEXPROPERTY(o.id, i.name, 'IsPageLockDisallowed') AS IXD_PAGE_LOCK_DISALLOWED

FROM   dbo.sysindexes i
       INNER JOIN dbo.sysobjects o
             ON i.id = o.id
       INNER JOIN dbo.sysusers u
             ON o.uid = u.uid
       INNER JOIN dbo.sysindexkeys k
             ON o.id = k.id
             and i.indid = k.indid
       INNER JOIN dbo.syscolumns c
             ON k.colid = c.colid
             and o.id = c.id
       INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
             ON u.name  = ISC.TABLE_SCHEMA
             AND o.name = ISC.TABLE_NAME
             AND c.name = ISC.COLUMN_NAME
       LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TCT
             ON  u.name = TCT.CONSTRAINT_SCHEMA
             AND i.name = TCT.CONSTRAINT_NAME

WHERE i.status &amp; 64 <> 64

ORDER  BY IXD_SCHEMA_NAME, IXD_TABLE_NAME, IXD_INDEX_NAME, IXD_COL_IDX_ORDER 

GO

-- table mère du processsus
CREATE TABLE dbo.T_A_MAINTENANCE_PROCESS_MPC
(MPC_ID            INT NOT NULL IDENTITY CONSTRAINT  PK_MPC PRIMARY KEY,
 MPC_ID_PRECEDENT  INT NULL 
    FOREIGN KEY REFERENCES T_A_MAINTENANCE_PROCESS_MPC (MPC_ID),
 MPC_SQL_USER      sysname DEFAULT CURRENT_USER, 
 MPC_SYS_USER      sysname DEFAULT SYSTEM_USER,
 MPC_SPID          INT,          
 MPC_DATE_TIME     DATETIME DEFAULT CURRENT_TIMESTAMP,
 MPC_HOST_NAME     NCHAR(128),
 MPC_APPLI         NCHAR(128),
 MPC_NETNAME       NCHAR(128),
 MPC_NETADR        NCHAR(12),
 MPC_PROCESS       VARCHAR(512),
 MPC_SUCCESS       BIT DEFAULT 0)
GO

-- table fille de détail du processus lié à la désindexation/réindexation
CREATE TABLE dbo.T_A_MAINTENANCE_INDEX_MIX
(MIX_ID             INT NOT NULL IDENTITY CONSTRAINT PK_MIX PRIMARY KEY,
 MPC_ID             INT NOT NULL 
    FOREIGN KEY REFERENCES T_A_MAINTENANCE_PROCESS_MPC (MPC_ID),
 MIX_DATE_TIME      DATETIME  DEFAULT CURRENT_TIMESTAMP,
 MIX_OPERATION      CHAR(5) CONSTRAINT CK_MIX_OPR 
    CHECK (MIX_OPERATION IN ('DROP', 'RESET')),
 MIX_SCHEMA_NAME    sysname,
 MIX_TABLE_NAME     sysname,
 MIX_INDEX_NAME     sysname,
 MIX_UNIQUE         BIT DEFAULT 0, 
 MIX_CLUSTER        BIT DEFAULT 0, 
 MIX_COL_ORDER_LIST VARCHAR(2160),
 MIX_INDEX_WITH     VARCHAR(200),
 MIX_NO_ROW_LOCK    BIT DEFAULT 0,
 MIX_NO_PAGE_LOCK   BIT DEFAULT 0,
 MIX_ON_FILE        sysname NULL,
 MIX_COMPLETE       BIT DEFAULT 0) 
GO

CREATE FUNCTION F_A_INDEX_LIST_COL_ORDER
       (@SCHEMA_NAME sysname,
        @TABLE_NAME sysname,
        @INDEX_NAME sysname)
RETURNS VARCHAR(2160)
AS

BEGIN

DECLARE @RETVAL VARCHAR(2160)
SET @RETVAL = ''

SELECT @RETVAL = @RETVAL +
       c.name  + ' ' +
       CASE 
          WHEN INDEXKEY_PROPERTY 
          (o.id , i.indid , k.keyno , 'IsDescending' )  = 0 THEN 'ASC'
          WHEN INDEXKEY_PROPERTY 
          (o.id , i.indid , k.keyno , 'IsDescending' ) = 1 THEN 'DESC'
          WHEN INDEXKEY_PROPERTY 
          (o.id , i.indid , k.keyno , 'IsDescending' ) IS NULL THEN ''
       END +', '
   
FROM   sysindexes i
       INNER JOIN sysobjects o
             ON i.id = o.id
       INNER JOIN sysusers u
             ON o.uid = u.uid
       INNER JOIN sysindexkeys k
             ON o.id = k.id
             and i.indid = k.indid
       INNER JOIN syscolumns c
             ON k.colid = c.colid
             and o.id = c.id
       INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
             ON u.name  = ISC.TABLE_SCHEMA
             AND o.name = ISC.TABLE_NAME
             AND c.name = ISC.COLUMN_NAME

WHERE  u.name = @SCHEMA_NAME
  AND  o.name = @TABLE_NAME
  AND  i.name = @INDEX_NAME

ORDER  BY k.keyno

IF LEN(@RETVAL) >= 2
   SET @RETVAL = SUBSTRING(@RETVAL, 1, LEN(@RETVAL) - 1)

RETURN @RETVAL

END

GO

CREATE PROCEDURE dbo.P_A_MAINTENANCE_DROP_INDEXES
                 @SCHEMA_NAME sysname = 'dbo', 
                 @TABLE_NAME  sysname,
                 @CREATE_ON   sysname = 'PRIMARY',
                 @MPC_ID      INT = NULL OUTPUT 
AS

DECLARE @ROWS INT, @ERROR INT

SET @MPC_ID = NULL

-- RETURN NULL ON NULL INPUT (norme SQL)
IF @TABLE_NAME IS NULL OR @CREATE_ON IS NULL OR @SCHEMA_NAME IS NULL
BEGIN
   RAISERROR ('Entrée NULL détectée dans au moins un des paramètres de la procédure
                    P_A_MAINTENANCE_DROP_INDEXES. Abandon immédiat', 16, 1)
   RETURN
END

-- schéma.table n'existe pas dans le catalogue présent
IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.TABLES
               WHERE  TABLE_SCHEMA = @SCHEMA_NAME
                 AND  TABLE_NAME = @TABLE_NAME)
BEGIN
   RAISERROR ('Le couple schéma.table : %s.%s, 
           n''existe pas dans le catalogue courant', 16, 1, @SCHEMA_NAME, @TABLE_NAME)
   RETURN
END

-- vérification file group
IF @CREATE_ON <> 'PRIMARY'
   IF NOT EXISTS (SELECT *
                  FROM   sysfilegroups
                  WHERE   groupname = @CREATE_ON)
BEGIN
   RAISERROR ('Le groupe de fichier %s, n''existe pas dans la liste des groupe
                   de fichiers pour cette base de données', 16, 1, @CREATE_ON)
   RETURN
END

-- les tables de maintenance existent-elles dans la base ?
DECLARE @T1 sysname, @T2 sysname, @SC sysname
SET @T1 = 'T_A_MAINTENANCE_INDEX_MIX' 
SET @T2 = 'T_A_MAINTENANCE_PROCESS_MPC'
SET @SC = 'dbo'
IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.TABLES
               WHERE  TABLE_SCHEMA = @SC
                 AND  TABLE_NAME = @T1 
                 AND  EXISTS (SELECT *
                              FROM   INFORMATION_SCHEMA.TABLES
                              WHERE  TABLE_SCHEMA = @SC
                                AND  TABLE_NAME = @T2))
BEGIN
   RAISERROR ('Au moins l''une des tables de travail (%s.%s, %s.%s)_
                   n''existe pas dans la liste objets du 
                   catalogue', 16, 1, @SC, @T1, @SC, @T2)
   RETURN
END

BEGIN TRANSACTION

-- insertion des informations de maintenance
INSERT INTO dbo.T_A_MAINTENANCE_PROCESS_MPC (MPC_SPID, MPC_HOST_NAME, MPC_APPLI,
MPC_NETNAME, MPC_NETADR,  MPC_PROCESS )
SELECT spid,     hostname,      program_name, nt_username, net_address, 
'Suppression des index non cluster et non unique 
(dont clef) table '+ @SCHEMA_NAME+'.'+@TABLE_NAME
FROM   master.dbo.sysprocesses p
WHERE  p.spid = @@SPID
IF @@ERROR <> 0
   GOTO LBL_ERROR       

-- reprise de l'indentifiant autogénéré pour report dans table fille
SET @MPC_ID = @@IDENTITY

-- insertion des informations sur les index
INSERT INTO dbo.T_A_MAINTENANCE_INDEX_MIX (MPC_ID, MIX_OPERATION, 
MIX_SCHEMA_NAME, MIX_TABLE_NAME, MIX_INDEX_NAME,_
 MIX_UNIQUE, MIX_CLUSTER, MIX_INDEX_WITH, MIX_COL_ORDER_LIST, 
 MIX_NO_ROW_LOCK, MIX_NO_PAGE_LOCK, MIX_ON_FILE)
SELECT @MPC_ID, 'DROP', @SCHEMA_NAME, @TABLE_NAME, i.name, 
       INDEXPROPERTY(o.id, i.name, 'IsUnique'), 
       INDEXPROPERTY(o.id, i.name, 'IsClustered'),
-- propriété PAD
       CASE 
          WHEN INDEXPROPERTY(o.id, i.name, 'IsPadIndex') = 1 THEN ' PAD_INDEX '
          ELSE ''
       END +
-- propriété FILL FACTOR
       CASE 
          WHEN  INDEXPROPERTY(o.id, i.name, 'IndexFillFactor') > 0
                THEN 'FILLFACTOR = ' + CAST(INDEXPROPERTY
                (o.id, i.name, 'IndexFillFactor') AS VARCHAR(3))
          ELSE NULL
       END,
       dbo.F_A_INDEX_LIST_COL_ORDER (@SCHEMA_NAME, @TABLE_NAME, i.name),
       INDEXPROPERTY(o.id, i.name, 'IsRowLockDisallowed'),
       INDEXPROPERTY(o.id, i.name, 'IsPageLockDisallowed'),
       NULLIF(@CREATE_ON, 'PRIMARY')
FROM   dbo.sysindexes i
       INNER JOIN dbo.sysobjects o
             ON i.id = o.id
       INNER JOIN dbo.sysusers u
             ON o.uid = u.uid
WHERE i.status &amp; 64 <> 64 -- sauf les index "stat"
  AND  u.name = @SCHEMA_NAME
  AND  o.name = @TABLE_NAME
  AND  INDEXPROPERTY(o.id, i.name, 'IsUnique') <> 1
  AND  INDEXPROPERTY(o.id, i.name, 'IsClustered') <> 1
SELECT @ERROR = @@ERROR, @ROWS = @@ROWCOUNT
IF @ERROR <> 0
   GOTO LBL_ERROR       

-- aucun index secondaire n'est présent dans la table
IF @ROWS = 0
BEGIN
   SET @MPC_ID = NULL
   GOTO LBL_ERROR
   RETURN
END

-- suppression des index
DECLARE @SQL VARCHAR(8000)

SET @SQL = 'DROP INDEX'

SELECT @SQL = @SQL + ' ' + MIX_SCHEMA_NAME + '.' + 
        MIX_TABLE_NAME +'.' + MIX_INDEX_NAME +','
FROM   dbo.T_A_MAINTENANCE_INDEX_MIX
WHERE  MPC_ID = @MPC_ID
  AND  MIX_UNIQUE = 0
  AND  MIX_CLUSTER = 0
IF @@ERROR <> 0
   GOTO LBL_ERROR       

SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) -1)

EXEC (@SQL)
IF @@ERROR <> 0
   GOTO LBL_ERROR       

UPDATE dbo.T_A_MAINTENANCE_INDEX_MIX
SET    MIX_DATE_TIME = CURRENT_TIMESTAMP,
       MIX_COMPLETE = 1
WHERE  MPC_ID = @MPC_ID

UPDATE dbo.T_A_MAINTENANCE_PROCESS_MPC
SET    MPC_SUCCESS = 1
WHERE  MPC_ID = @MPC_ID

COMMIT TRANSACTION

RETURN

LBL_ERROR:
ROLLBACK TRANSACTION
RAISERROR('Maintenance d''index : opération inachevée. Veuillez prendre 
connaissance des informations des tables dbo.T_A_MAINTENANCE_PROCESS_MPC (et 
dbo.T_A_MAINTENANCE_INDEX_MIX) pour les entrées 
d''identifiant %i.', 18, 1, @MPC_ID) WITH LOG

GO

CREATE PROCEDURE P_MAINTENANCE_RESET_INDEXES 
                 @MPC_ID INTEGER
AS

-- RETURN NULL ON NULL INPUT (norme SQL)
IF @MPC_ID IS NULL
BEGIN
   RAISERROR ('Entrée NULL détectée dans au moins un des paramètres de la 
   procédure P_MAINTENANCE_RESET_INDEXES. Abandon immédiat', 16, 1)
   RETURN
END

-- les tables de maintenance existent-elles dans la base ?
DECLARE @T1 sysname, @T2 sysname, @SC sysname
SET @T1 = 'T_A_MAINTENANCE_INDEX_MIX' 
SET @T2 = 'T_A_MAINTENANCE_PROCESS_MPC'
SET @SC = 'dbo'
IF NOT EXISTS (SELECT *
               FROM   INFORMATION_SCHEMA.TABLES
               WHERE  TABLE_SCHEMA = @SC
                 AND  TABLE_NAME = @T1 
                 AND  EXISTS (SELECT *
                              FROM   INFORMATION_SCHEMA.TABLES
                              WHERE  TABLE_SCHEMA = @SC
                                AND  TABLE_NAME = @T2))
BEGIN
   RAISERROR ('Au moins l''une des tables de travail (%s.%s, %s.%s) n''existe pas 
               dans la liste objets du catalogue', 16, 1, @SC, @T1, @SC, @T2)
   RETURN
END

-- pas d'entrée dans la table T_A_MAINTENANCE_PROCESS_MPC
IF NOT EXISTS(SELECT *
              FROM   dbo.T_A_MAINTENANCE_PROCESS_MPC
              WHERE  MPC_ID = @MPC_ID)
BEGIN
   RAISERROR ('pas d''entrée pour la clef de valeur %i dans la 
       table T_A_MAINTENANCE_PROCESS_MPC. Abandon immédiat', 16, 1, @MPC_ID)
   RETURN
END

-- pas d'entrée dans la table T_A_MAINTENANCE_INDEX_MIX
IF NOT EXISTS(SELECT *
              FROM   dbo.T_A_MAINTENANCE_INDEX_MIX
              WHERE  MPC_ID = @MPC_ID)
BEGIN
   RAISERROR ('pas d''entrée pour la clef étrangère de valeur %i 
       dans la table T_A_MAINTENANCE_INDEX_MIX. Abandon immédiat', 16, 1, @MPC_ID)
   RETURN
END

-- informer la table dbo.T_A_MAINTENANCE_PROCESS_MPC avec chainage
DECLARE @MPC_ID_NEW INT

INSERT INTO dbo.T_A_MAINTENANCE_PROCESS_MPC 
               (MPC_ID_PRECEDENT, MPC.MPC_SPID, MPC_HOST_NAME, MPC_APPLI,    
               MPC_NETNAME, MPC_NETADR,  MPC_PROCESS                         )
SELECT DISTINCT MPC.MPC_ID,       spid,         hostname,      program_name, 
nt_username, net_address, 'Réindexation de la table '+ MIX_SCHEMA_NAME+'.'+MIX_TABLE_NAME
FROM   dbo.T_A_MAINTENANCE_PROCESS_MPC MPC
       INNER JOIN dbo.T_A_MAINTENANCE_INDEX_MIX MIX
             ON MPC.MPC_ID = MIX.MPC_ID
       CROSS JOIN master.dbo.sysprocesses s
WHERE  s.spid = @@SPID
  AND  MPC.MPC_ID = @MPC_ID
IF @@ERROR <> 0
   GOTO LBL_ERROR

SET @MPC_ID_NEW = @@IDENTITY

DECLARE @SQL NVARCHAR(4000)
DECLARE @MIX_ID INT
DECLARE @NO_PG_LCK BIT, @NO_RW_LCK BIT
DECLARE @IDX_COMPOSITE_NAME NVARCHAR(257)
DECLARE @I INT
SET @I = 256

-- au travail, sans curseur
WHILE EXISTS(SELECT *
             FROM   dbo.T_A_MAINTENANCE_INDEX_MIX
             WHERE  MPC_ID = @MPC_ID
               AND  MIX_OPERATION = 'DROP')
BEGIN

-- sélection du premier index à remettre
   SELECT TOP 1 @MIX_ID = MIX_ID, @NO_PG_LCK = MIX_NO_PAGE_LOCK , @NO_RW_LCK = MIX_NO_ROW_LOCK ,
                @IDX_COMPOSITE_NAME = '[' + MIX_TABLE_NAME + '].[' + MIX_INDEX_NAME+']',
                @SQL = 'CREATE NONCLUSTERED INDEX ' + MIX_INDEX_NAME +
                       ' ON [' + MIX_SCHEMA_NAME + '].[' + MIX_TABLE_NAME+'] (' +
                       MIX_COL_ORDER_LIST + ') ' + COALESCE(' WITH ' + MIX_INDEX_WITH +' ', '') +
                       ' ON ' + COALESCE(MIX_ON_FILE, '[PRIMARY]')
   FROM   dbo.T_A_MAINTENANCE_INDEX_MIX
   WHERE  MPC_ID = @MPC_ID
     AND  MIX_OPERATION = 'DROP'

-- exécution de la réindexation
   EXEC (@SQL)
   IF @@ERROR = 0
-- succès on informe avec complète = 1
      UPDATE dbo.T_A_MAINTENANCE_INDEX_MIX
      SET    MIX_OPERATION = 'RESET',
             MIX_COMPLETE  = 1,
             MIX_DATE_TIME = CURRENT_TIMESTAMP
      WHERE  MIX_ID = @MIX_ID
   ELSE
-- échec on informe avec complète = 0
      UPDATE dbo.T_A_MAINTENANCE_INDEX_MIX
      SET    MIX_OPERATION = 'RESET',
             MIX_COMPLETE  = 0,
             MIX_DATE_TIME = CURRENT_TIMESTAMP
      WHERE  MIX_ID = @MIX_ID

-- si @NO_PG_LCK = 1 replacer l'interdiction d'utiliser le verrou de page dans l'index
   IF @NO_PG_LCK = 1
      EXEC sp_indexoption @IDX_COMPOSITE_NAME, 'DisAllowPageLocks' , '1'
-- si @NO_RW_LCK = 1 replacer l'interdiction d'utiliser le verrou de ligne dans l'index
   IF @NO_RW_LCK = 1
      EXEC sp_indexoption @IDX_COMPOSITE_NAME, 'DisAllowRowLocks' , '1'

-- a ton bouclé indéfiniment ? (max 256 index)
      SET @I = @I - 1
      IF @I = 0
         BREAK

END

-- tous les index ont-ils été recréés ?
IF EXISTS (SELECT *         
           FROM   dbo.T_A_MAINTENANCE_INDEX_MIX
           WHERE  MPC_ID = @MPC_ID
             AND  MIX_COMPLETE  = 0)
-- non : il en existe au moins 1 qui n'a pas été remis
   GOTO LBL_ERROR

-- oui : on met les pendules à l'heure dans la table dbo.T_A_MAINTENANCE_PROCESS_MPC 
UPDATE dbo.T_A_MAINTENANCE_PROCESS_MPC 
SET    MPC_SUCCESS = 1
WHERE  MPC_ID = @MPC_ID_NEW
RETURN

LBL_ERROR:
RAISERROR('Maintenance d''index : opération inachevée.
            Veuillez prendre connaissance des informations des tables 
            dbo.T_A_MAINTENANCE_PROCESS_MPC (et dbo.T_A_MAINTENANCE_INDEX_MIX) pour 
            les entrées d''identifiant %i et %i', 18, 1, @MPC_ID, @MPC_ID_NEW) WITH LOG

GO