sql server 2000 Pb d'exécution de procédure archivage

Version imprimable

Bonjour tout le monde,

Cette procédure archive toutes les prospections (table prospections) datant de plus de 6 ans ainsi que toutes les actions (table praction) relatives à ces prospections . Je ne comprend pas pourquoi l’exécution ne se finalise pas au bout de 25 min (et plus).Pourtant niveau syntaxe la procédure semble correcte.

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
 
CREATE PROC dbo.ArchiveDonnées
(
    @CutOffDate datetime = NULL
)
AS
BEGIN
    SET NOCOUNT ON
 
    IF @CutOffDate IS NULL 
    BEGIN
        SET @CutOffDate = DATEADD (yyyy, -6, CURRENT_TIMESTAMP)
 
    END
 
END
 
    BEGIN TRAN
 
        INSERT INTO Migration.dbo.migprospection
        SELECT * 
        FROM recette_670.dbo.prospection
        WHERE datefin < @CutOffDate
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers Migration.dbo.migprospection', 16, 1)
            RETURN -1
        END
 
        INSERT INTO Migration.dbo.migpraction
        SELECT *
        FROM recette_670.dbo.praction
        WHERE fk_prospect IN
        (
            SELECT pk_prospect
            FROM dbo.prospection
            WHERE datefin < @CutOffDate
        )
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers Migration.dbo.migpraction', 16, 1)
 
            RETURN -1
        END
 
        DELETE dbo.praction
        WHERE fk_prospect IN
        (
            SELECT pk_prospect
            FROM dbo.prospection
            WHERE datefin < @CutOffDate
         )
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers recette_670.dbo.praction', 16, 1)
            RETURN -1
        END
 
        DELETE dbo.prospection
        WHERE Datefin < @CutOffDate
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers recette_670.dbo.prospection', 16, 1)
            RETURN -1
        END
 
    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN
        RETURN 0
    ENd
GO

Quelqu'un peut m'éclairer ?

MERCI

13/10/2009, 12h30
verbal34

Bonjour,

le fait que votre requête d'archivage ne se termine pas au bout de 25 minutes ne veut pas dire forcément qu'il y a problème!

Le temps d'archivage dépends de beaucoup de paramètres comme la config du serveur, le nombre de lignes à archiver (insert dans la base d'archive et delete de la base cible), la taille de la base de données....

cela dépend aussi du nombre de connexions à vos bases de données au moment de l'archivage (journée, nuit, week end...).

au bout de plusieurs heures d'exécution cela peut être inquiétant. Personnellement, sur une base de +-10Go, avec archivage de presque 60% de données (SQL Server 2000) mes requêtes mettent 2H pour effectuer l'archivage.
13/10/2009, 14h26
doudou8mc

Après quelques minute d'attentes ,voici l'erreur :

Citation:

"Serveur : Msg 547, Niveau 16, État 1, Procédure ArchiveDonnées, Ligne 64
Conflit entre l'instruction DELETE et la contrainte COLUMN REFERENCE 'FK__PRPRODUIT__FK_PR__75A278F5'. Le conflit est survenu dans la base de données 'recette_670', table 'PRPRODUIT', column 'FK_PROSPECT'.

Serveur : Msg 50000, Niveau 16, État 1, Procédure ArchiveDonnées, Ligne 70
une erreur est survenue lors de la copie des données vers recette_670.dbo.prospection
L'instruction a été arrêtée.
Procédure stockée*: recette_670.dbo.ArchiveDonnées
Code de retour = -1"

Il est vrai qu'il existe une table PRPRODUIT ds laquelle est référencée la clé étrangère "PK_PROSPECT" (clé primaire de table prospection).Est ce que celà empêche la copie de lignes ed la table prospection vers la base d'archive ? Et si c'est le cas , y'a t-il un moyen d'y remédier et comment ?

MERCI
13/10/2009, 15h39
verbal34

Citation:

Envoyé par doudou8mc

Il est vrai qu'il existe une table PRPRODUIT ds laquelle est référencée la clé étrangère "PK_PROSPECT" (clé primaire de table prospection).Est ce que celà empêche la copie de lignes ed la table prospection vers la base d'archive ? Et si c'est le cas , y'a t-il un moyen d'y remédier et comment ?
MERCI

Non, cela n'empêche pas la copie de des données de la table recette_670.dbo.prospection vers la table Migration.dbo.migprospection, par contre ça empêche la suppression des lignes de cette table (recette_670.dbo.prospection) qui sont référencées par la table recette_670.dbo.PRPRODUIT.

Il faudra faire l'archivage (copie ensuite delete) dans l'ordre.

Dans ce cas là, il faudra archiver aussi les données de la table recette_670.dbo.PRPRODUIT et éventuellement d'autres tables qui possèdent des références de clefs étrangères vers TOUTES les clefs primaires de TOUTES les tables à archiver.
13/10/2009, 16h33
doudou8mc

ok merci ; la séquencement de l'archivage est bon (copie puis suppression); par contre , en effet , je pense aussi qu'il faudra créer toutes les tables liées à prospection par une contrainte référentielle.Je voulaisn éviter de passer par là.

MERCI en tout cas

Bonjour,

Veillez à utiliser des jointures plutôt que le prédicat IN, et à mettre le noms de vos colonnes plutôt que * dans vos SELECT :

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
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
CREATE PROC dbo.ArchiveDonnées
(
    @CutOffDate DATETIME = NULL
)
AS
BEGIN
    SET NOCOUNT ON
 
    IF @CutOffDate IS NULL 
    BEGIN
        SET @CutOffDate = DATEADD (yyyy, -6, CURRENT_TIMESTAMP)
 
    END
 
    BEGIN TRAN 
        INSERT INTO Migration.dbo.migprospection
        SELECT colonnesDeLaTableProspection
        FROM recette_670.dbo.prospection
        WHERE datefin < @CutOffDate
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers Migration.dbo.migprospection', 16, 1)
            RETURN -1
        END
 
        INSERT INTO Migration.dbo.migpraction
        SELECT colonnesDeLaTablePraction
        FROM recette_670.dbo.praction AS PRAC
        JOIN dbo.prospection AS PROS
			ON PRAC.fk_prospect = PROS.pk_prospect
		WHERE PROS.datefin < @CutOffDate
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers Migration.dbo.migpraction', 16, 1)
 
            RETURN -1
        END
 
        DELETE FROM dbo.praction
        FROM dbo.praction AS PRAC
        JOIN dbo.prospection AS PROS
			ON PRAC.fk_prospect = PROS.pk_prospect
		WHERE PROS.datefin < @CutOffDate
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers recette_670.dbo.praction', 16, 1)
            RETURN -1
        END
 
        DELETE FROM dbo.prospection
        WHERE Datefin < @CutOffDate
 
        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('une erreur est survenue lors de la copie des données vers recette_670.dbo.prospection', 16, 1)
            RETURN -1
        END
 
    IF @@TRANCOUNT > 0
    BEGIN
        COMMIT TRAN
        RETURN 0
    END
GO

IN n'est pas SARGable, même si ici SQL Server est suffisamment intelligent pour produire lui-même la jointure.

Utiliser * est bien pour vous, pour avoir un aperçu de ce que contiennent vos tables.
Mais si vous utilisez cela dans un module SQL (procédure, fonction, trigger, vue), alors le moteur de base de données est à chaque fois obligé d'interroger les tables de métadonnées pour connaître la liste des colonnes ainsi que leur type, ce dont on peut le dispenser facilement ;)

@++ ;)

14/10/2009, 15h04
doudou8mc

Merci du conseil. Si vous avez d'autres astuces pour optimiser les temps d'exécution je suis preneur, car c'est un problème pour moi qui suis débutant.
14/10/2009, 15h35
elsuket

A moindre coût, je ne peux que nous conseiller de lire le site de SQLPro et ses très bons papiers.

Ensuite vous pouvez acheter des livres (vous ou votre entreprise), comme celui de SQLPro (encore lui !), et l'excellente liste qu'il a constitué.

Globalement les livres SQL Server de chez APress sont de bonne qualité.
Sachez néanmoins que vous ne trouverez là que les principes de l'optimisation (mais c'est déjà énorme), car celle-ci ne s'append pas : elle s'acquiert ... avec l'expérience ;)

@++ ;)