Discussion :

[nicodev24]

Bonjour,

j'ai une table qui doit contenir des informations horodatées tournantes sur une profondeur d'un mois.
Chaque jour je supprime les informations pour garder la taille de la table à 1 mois.

Mon problème est lié à la taille de la table : 1 million d'enregistrements, et au temps mis à effectuer cette requete de suppression.

Voici la structure de la table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
CREATE TABLE donneestechniques
(
station_id 	INTEGER NOT NULL FOREIGN KEY REFERENCES station (station_id),
indexdonnee	INTEGER NOT NULL,
dateheuretu	DATETIME DEFAULT GETDATE(),
dateheurelegale	DATETIME DEFAULT GETDATE(),
donnee		FLOAT DEFAULT '-9999',
parametre	INTEGER DEFAULT'0'
CONSTRAINT PK_donneestechniques PRIMARY KEY (station_id, indexdonnee, dateheuretu, parametre)
)

Voici ma requête de suppression :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DELETE FROM donneestechniques WHERE dateheuretu < 'AAAAMMJJ HH:mm:SS.mse'

avec 'AAAAMMJJ HH:mm:SS.mse' la date d'il y a un mois.

La requete, executée une fois par jour, mets 5 minutes à s'éxécuter. Je cherche désespérement à diminuer son temps d'execution.

Avez vous des conseils sur les tables avec un nombre important d'enregistrements permettant de résoudre mon problème ?

[rudib]

Bonjour,

As-tu un index sur la colonne dateheuretu (avec cette colonne seule, ou en premier) ?
Même avec ça, peut-être SQL Server ne l'utilise pas, peux-tu poster le résultat de:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SET SHOWPLAN_TEXT
GO
DELETE FROM donneestechniques WHERE dateheuretu < 'AAAAMMJJ HH:mm:SS.mse'

Ta clé primaire est longue. Est-elle clustered ? On dirait que oui selon ton DDL. Ca peut être un problème. essaie de la mettre en NONCLUSTERED.

[nicodev24]

Bonjour,

voici le résultat des mes manips sur la table :

création d'un index sur la colonne dateheuretu


voici le détail de ma clé primaire


et voici le résultat de la requete avec la modification ci dessus :

|--Sequence
|--Index Delete(OBJECT:([frontal].[dbo].[donneestechniques].[index_2101582525]))
| |--Sort(ORDER BY:([donneestechniques].[rowguid] ASC))
| |--Table Spool
| |--Table Delete(OBJECT:([frontal].[dbo].[donneestechniques]))
| |--Top(ROWCOUNT est 0)
| |--Index Seek(OBJECT:([frontal].[dbo].[donneestechniques].[IX_dateheuretu]), SEEK:([donneestechniques].[dateheuretu] < Convert([@1])) ORDERED FORWARD)
|--Index Delete(OBJECT:([frontal].[dbo].[donneestechniques].[PK_donneestechniques]))
| |--Sort(ORDER BY:([donneestechniques].[station_id] ASC, [donneestechniques].[indexdonnee] ASC, [donneestechniques].[dateheuretu] ASC, [donneestechniques].[parametre] ASC))
| |--Table Spool
|--Index Delete(OBJECT:([frontal].[dbo].[donneestechniques].[IX_dateheuretu]))
|--Sort(ORDER BY:([donneestechniques].[dateheuretu] ASC, [Bmk1000] DESC))
|--Table Spool


Je n'ai pas encore assez de recul dans le temps pour juger des améliorations, car j'ai du supprimer des données pour d'autres tests. Dès que ma table aura repris son ancien volume, je reposterais.

Merci en tout cas de ta réponse,
Nicolas

[SQLpro]

1) pour votre clef, sachez qu'une clef composée de plusieurs colonnes est toujours une mauvaise idée. Vous auriez tout intérêt soit :
a) à ajouter un identifiant autoincrémenté et le faire devenir clef et placez vos quatre colonnes de l'ancienne clef en contrainte UNIQUE si besoin est
b) à utiliser un index non clustured (solution moins satisfaisante à mon gout.

2) effectivement rajouter un index sur la date pour que lors de la suppression SQL Server trouve direcetment le point d'entrée.

3) si des index secondaires (donc non sémantique) existe, les supprimer préalablement à la suppression et les remettre après la suppression.

Avec cela vous devriez diminuer d'une facteur de 2 à 10 les temps d'exécution.

A +

[nicodev24]

Bonjour,
j'ai bien pensé à ajouter un identifiant autoincrémenté, mais même si je le défini comme un entier de grande précision (BIGINT) comment faire pour qu'il ne se bloque pas à sa valeur maximum (après quelques mois de fonctionnement), et recycle les valeurs d'index qui ont été supprimées ?

Est ce que le type "uniqueidentifier" peut faire l'affaire ?

Merci

[serge0934]

bigint va jusqu'a 2 puissance 63 en + et - soit de

-9223372036854775808 a +9223372036854775807

soit en gros des milliards de milliards de milliards de lignes, si t'arrive au max ton disque du serveur aura explosé avant

[nicodev24]

après quelques calculs,
j'ai besoin de 200 000 lignes par jour, soit 1 milliard 480 millions de lignes pour 20 ans.

merci à tous pour vos réponses.

[SQLpro]

Donc un INT est largement suffisant car capable de couvrir 4 milliard de ligne soit 54 ans !
Avec un BIGINT, tu peut mourrir tranquille la limite sera dans : 231 928 234 millénaires...

A +