Discussion :

[t1bea]

Bonjour,

voici l'explication de mon problème, suite à un message privé.

Pour essayer de faire court :
ma base de données comporte plusieurs tables dont 3 qui sont très importantes comportant environ 500 000 et 2 millions d'enregistrements.
Les enregistrements de ces trois "grosses" tables sont identifiés par la combinaison de clés étrangères et d'une date/heure. J'avais défini cette combinaison comme clé primaire mais il semblerait que les performances avec une telle clé soient vraiment catastrophiques. J'ai donc pensé mettre un "index de ligne" en tant que clé primaire en auto-increment mais les enregistrements de ces tables sont constament supprimés/ajoutés. L'index risque donc d'exploser assez vite...
Qu'envisageriez vous comme solution pour permettre des requêtes efficaces sur de telles tables ? Les requêtes portent sur tous les éléments de la clé, compris la date/heure...
Je ne vois pas du tout comment optimiser / restructurer cette base et il faut pourtant que j'y parvienne. Alors si vos aviez une piste....

REQUETES DE CREATION DES TABLES

// 12 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE capteur
(
capteur_id			INT IDENTITY(1,1) NOT FOR REPLICATION,
donneeplq_id			INT NOT NULL FOREIGN KEY REFERENCES donneeplq (donneeplq_id),
nom		 		VARCHAR(30) NOT NULL,
nbreoctets			TINYINT DEFAULT '2',
equationmultiplicateur 		FLOAT DEFAULT '1',
equationadditionneur 		FLOAT DEFAULT '0',
voieplqdefaut			VARCHAR(4)
CONSTRAINT PK_capteur PRIMARY KEY (capteur_id)
)

// 57600 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE hydro
(
station_id 			INTEGER NOT NULL FOREIGN KEY REFERENCES station (station_id),
capteur_id			INTEGER NOT NULL FOREIGN KEY REFERENCES capteur (capteur_id),
dateheuretu			DATETIME DEFAULT GETDATE(),
dateheurelegale			DATETIME DEFAULT GETDATE(),
donneebrute			FLOAT DEFAULT '-9999',
donneetraitee			FLOAT DEFAULT '-9999'
CONSTRAINT PK_hydro PRIMARY KEY (station_id,capteur_id,dateheuretu)
)

// 450 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE hydroinstantane
(
hydroinstantane_id		INT IDENTITY(1,1) NOT FOR REPLICATION,
station_id 			INTEGER NOT NULL FOREIGN KEY REFERENCES station (station_id),
capteur_id			INTEGER NOT NULL FOREIGN KEY REFERENCES capteur (capteur_id),
dateheuretu			DATETIME DEFAULT GETDATE(),
dateheurelegale			DATETIME DEFAULT GETDATE(),
donneebrute			FLOAT DEFAULT '-9999',
donneetraitee			FLOAT DEFAULT '-9999'
CONSTRAINT PK_hydroinstantane PRIMARY KEY (hydroinstantane_id)
)

// 2 600 000 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
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)
)

// 400 000 lignes

Code :

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

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

// 34 lignes

Code :

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

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
 
CREATE TABLE pointhaut
(
pointhaut_id			INT IDENTITY(1,1) NOT FOR REPLICATION,
nom		 		VARCHAR(30),
mnemonique			VARCHAR(10),
relais_numero			INTEGER DEFAULT '1',
relais_canal			INTEGER DEFAULT '1',
base_ip				VARCHAR(15),
base_port			INTEGER DEFAULT '10001',
type				TINYINT DEFAULT '1',
relais_secours			TINYINT DEFAULT '1',
base_secours			TINYINT DEFAULT '1',
numerosecoursrtc		VARCHAR(25),
numerosecoursgsm		VARCHAR(25),
numeronumeris			VARCHAR(25),
ipadsl				VARCHAR(15),
typeantenne			VARCHAR(50),
hauteursol			FLOAT DEFAULT '0',
frequenceemission		FLOAT DEFAULT '0',
frequencereception		FLOAT DEFAULT '0',
coordonnex			INTEGER DEFAULT '0',
coordonney			INTEGER DEFAULT '0',
actif				INTEGER DEFAULT '0',
ipsupervisionentrant		VARCHAR(15),
ipsupervisionsortant		VARCHAR(15)
CONSTRAINT PK_pointhaut PRIMARY KEY (pointhaut_id)
)

// 150 lignes

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
CREATE TABLE station
(
station_id				INT IDENTITY(1,1) NOT FOR REPLICATION,
nom		 			VARCHAR(30),
mnemonique				VARCHAR(10),
codestation				INTEGER,
codehydro				VARCHAR(15),
nbreperiodesanterieures			TINYINT DEFAULT '0',
vecteurradio				INTEGER DEFAULT '-1',
vecteursatellite			INTEGER DEFAULT '-2',
vecteurtelephone			INTEGER DEFAULT '-3',
canalradio				INTEGER DEFAULT '1',
typetelephone				INTEGER DEFAULT '1',
numerotelephone				VARCHAR(25) DEFAULT '',
satellite_ip				VARCHAR(15) DEFAULT '',
satellite_port				INTEGER DEFAULT '10001',
est_substitution			INTEGER DEFAULT '0',
id_substitution				INTEGER DEFAULT '0',
substitution_active			INTEGER DEFAULT '0'
CONSTRAINT PK_station PRIMARY KEY (station_id)
)

EXEMPLES DE REQUETES D'UTILISATION

// Requête de sélection des dernières données selon la donnée capteur.donneeplq_id = 2

Code :

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

1
2
3
4
 
SELECT hydro.station_id, hydro.dateheuretu, hydro.donneetraitee, hydro.donneebrute
FROM hydro INNER JOIN capteur ON hydro.capteur_id=capteur.capteur_id
WHERE capteur.donneeplq_id = 2 AND hydro.dateheuretu >= '20070515 09:18:00' ORDER BY hydro.station_id ASC, hydro.dateheuretu ASC

// Requête d’insertion ou de modification d’une donnée hydro

Code :

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

1
2
3
4
 
IF EXISTS ( SELECT station_id FROM hydro WHERE station_id=4 AND capteur_id=2 AND dateheuretu='20070515 09:36:00') 
UPDATE hydro SET donneebrute=944, donneetraitee=944 
WHERE station_id=4 AND capteur_id=2 AND dateheuretu='20070515 09:36:00' ELSE INSERT INTO hydro (station_id, capteur_id, dateheuretu, dateheurelegale, donneebrute, donneetraitee) VALUES (4,2,'20070515 09:36:00','20070515 11:36:00',944,944)

// Requête d’insertion ou de modification d’une donnée hydro instantanée

Code :

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

1
2
 
IF EXISTS (SELECT donneebrute FROM hydroinstantane WHERE station_id = 4 AND capteur_id = 2) UPDATE hydroinstantane SET donneebrute = 944, donneetraitee = 944, dateheuretu = '20070515 09:36:00', dateheurelegale = '20070515 11:36:00' WHERE ( station_id = 4) AND (capteur_id = 2) ELSE INSERT INTO hydroinstantane (station_id, capteur_id, dateheuretu, dateheurelegale, donneebrute, donneetraitee) VALUES (4,2,'20070515 09:36:00','20070515 11:36:00',944,944)

// Requête d’insertion d’une donnée technique

Code :

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

1
2
 
INSERT INTO donneestechniques (station_id, indexdonnee, dateheuretu, dateheurelegale, donnee) VALUES (4,19,'20070515 09:36:00','20070515 11:36:00',0)

// Requête d’insertion ou de modification d’une donnée technique

Code :

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

1
2
3
4
 
IF EXISTS ( SELECT station_id FROM donneestechniques WHERE station_id=1 AND indexdonnee=20 AND dateheuretu='20070515 09:36:00') 
UPDATE donneestechniques SET donnee=-2 WHERE station_id=1 AND indexdonnee=20 AND dateheuretu='20070515 09:36:00' 
ELSE INSERT INTO donneestechniques (station_id,indexdonnee,dateheuretu,dateheurelegale, donnee) VALUES (1,20,'20070515 09:36:00','20070515 11:36:00',-2)

// Sélection de données sur 1 jour selon un capteur

Code :

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

1
2
3
4
 
SELECT hydro.dateheurelegale, hydro.station_id, hydro.donneetraitee, hydro.donneebrute
FROM (hydro INNER JOIN capteur ON hydro.capteur_id = capteur.capteur_id) INNER JOIN station ON hydro.station_id=station.station_id
WHERE capteur.donneeplq_id = 2 AND hydro.dateheurelegale BETWEEN '20070514 11:38:00' AND '20070515 11:36:00' ORDER BY hydro.dateheurelegale ASC, station.codestation ASC

EXEMPLES DE REQUETES DE MAINTENANCE DES TABLES A UNE PROFONDEUR DE DONNEES DE 20 JOURS

Code :

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

1
2
3
 
DELETE FROM hydro WHERE dateheuretu < '20070414 11:38:00'
DELETE FROM donneestechniques WHERE dateheuretu < '20070414 11:38:00'

PROBLEMES MAJEURS
Les problèmes que je trouve sont des temps de réponse du serveur très long.

Exemple : les requetes de DELETE prennent 15 minutes à 30 minutes et bloquent le serveur qui ne peut pas faire tourner l'applicatif en parallèle.

A noter aussi : la base de données est répliquée en fusion sur 2 autres serveurs (débit entre les serveurs : 128 kb/s).
Configuration logicielle des serveurs :
PRINCIPAL : W2003 serveur + SQL Serveur 2000
SECOURS : W XP + agent SQL.

Question principale :
comment optimiser mes tables pour avoir des performances meilleures sur les requetes ci dessus ?

Autre question :
Quels serveurs conseilleriez vous (marque, modèle, caractéristiques ?)

Merci de m'avoir lue jusque ici et merci pour vos réponses.

[ylarvor]

Le problème qui peut subvenir sur une table à fort renouvellement comme c'est le cas dans cette application, c'est une fragmentation des index de cle primaire.
Avez vous un plan de maintenance pour faire un REBUILD des index chaque semaine ?

[serge0934]

tu crées un index sur dateheuretu et créé un plan de maintenance journalier qui réindexe du style

DBCC DBREINDEX...
DELETE FROM hydro WHERE dateheuretu < dateadd(day,-20,getdate())
DBCC DBREINDEX...

de plus créé des proc pour faire tes mises a jour a cause de la réplication, car ce sera ton serveur qui gèrera les insert et update et plus ton client. Ca ira plus vite aussi car les Proc sont compilées
ex:

CREATE PROCEDURE upd_data
(@dateheuretu datetime,
@station_id int,
@capteur_id int,
@donneetraitee int
)
AS
BEGIN

UPDATE hydro SET donneebrute=@donneetraitee, donneetraitee=@donneetraitee
WHERE station_id=@station_id AND capteur_id=@capteur_id AND dateheuretu=@dateheuretu

if @@rowcount=0 -- si aucune donnée mise a jour
INSERT INTO hydro (station_id, capteur_id, dateheuretu, dateheurelegale, donneebrute, donneetraitee) VALUES (@station_id ,@capteur_id ,@dateheuretu,@dateheuretu,@donneetraitee,@donneetraitee)
END

[t1bea]

Il est vrai que j'ai des index dont la fragmentation logique atteinds 4*695,17 % et la densité d'analyse 12,82 %. Ca peut expliquer mes problèmes !!

je vais tester vos solutions,
merci