optimization scripts T-SQL

Version imprimable

16/01/2008, 11h32
plutonium719

optimization scripts T-SQL

Bonjour,

Quels conseils pouvez-vous me donner pour optimizer du code T-SQL ?
Ya-t-il des outils ?
16/01/2008, 12h20
ylarvor

INDEX

L'outil principal pour optimiser la durée des requêtes est la mise en place et la maintenance d'index.
lorsque tu utilises un outil de modelisation comme POWER AMC qui gènèrent les index en même temps que les tables.
Il existe un outil d'optimisation par indexation sur sql serveur 2005 (l'assistant parametrage du moteur de bases de données sql serveur 2005).
Vous pouvez aussi comme beaucoup, creer manuellement vos index ( CREATE INDEX )
Cela depend de la volumetrie de vos table, pour moins de 1000 lignes, aucune indexation n'est nécessaire.
Quel est la durée de vos requête ?
16/01/2008, 12h29
plutonium719

La durée des reqêtes est environ de 3h.
Je pensais plus aussi à une meilleure structuration du code. Ce qui permettrai là aussi de gagner du temps.
16/01/2008, 12h33
Thecr0w

Bonjour,

Je suis actuellement aussi à la recherche d'optimisation de T-SQL

j'ai un serveur qui doit traitrer des tables pouvant allez jusqu'a 3 millions de lignes

bien sur mes tables sont indexées correctement.

ma requete ouvre un curseur dans la table et fait des insert ou update dans d'autre table (dispatching)

aujourd'hui pour traiter 300 mille ligne ma requete prend 120 minutes environ
pourtant le serveur n'est utilisé qu'a 50% de ses capacités

Une petite idée pour pouvoir optimiser les temps de traitement?

Cordialement.
16/01/2008, 12h33
ylarvor

3H, c'est un batch ?

Il faut structurer ton code pour que les index jouent leur role, ne pas utiliser de fonction, prefere les vues par exemple...

Cours SQL Pro :
http://www.sqlspot.com/INDEXATION-un...l-exemple.html
http://sqlpro.developpez.com/cours/s...ivation-index/
http://sqlpro.developpez.com/cours/s...r_avoidCursor/
forum sql pro:
http://www.developpez.net/forums/sho...d.php?t=473651
mon blog :
http://sqlserveur.blogspot.com/search/label/tuning
http://sqlserveur.blogspot.com/search/label/Index
16/01/2008, 13h34
Babyneedle

Bonjour,

Lorsqu'on travaille avec de gros objets, la stratégie d'indexation n'est plus suffisante.

Si vous travaillez sous 2005, je vous conseille d'explorer ces quelques stratégies (à moins que ce ne soit déjà fait).

- Si ce n'est pas déjà fait, apprenez à lire dans son ensemble et tous ses détails le plan d'exécution. Aussi, il est impératif de savoir travailler avec le Profiler.

- Le partionnement des tables et index si vos instances se trouvent sur des serveurs multiprocesseurs.

- Évaluation de la complexité et le volume des requêtes afin d'ajuster le parallelisme permis sur les instances. Plus les requêtes, en moyenne sont complexes, plus il faut laisser de place au paralellisme. Si, par exemple, vous roulez en boucle la même requête complexe (ie beaucoup de sous-requêtes) plusieurs millions de fois alors que le reste du traffic se compose de petites requêtes simples et que votre serveur a 8 processeurs hyperthreadés, vous pourriez, par exemple, établir le niveau de parallellisme à 4.

- Réévaluation du niveau de sécurité RAID des disques du serveur. Souvent, le niveau de sécurité est redondant quand la stratégie de backups est solide. J'ai déjà vu ce simple changement amener un gain de 200% en performance. Quand les processeurs et la mémoire ne sont pas utilisés pleinement, c'est souvent le i/o qui est bottleneck...

- Utiliser des stratégies de staging. Vous pouvez avoir des index hyper performants pour la lecture de vos tables. Par contre, quand vient le temps de modifier les données, ces mêmes index peuvent ralentir le processus. Il faut donc parfois transporter une partie ou la totalité des données dans une table, en bulk, dans une table optimisée pour ce processus et ensuite retransporter dans notre table primaire, toujours en prenant avantage du bulk, nos données transformées.
16/01/2008, 13h48
SQLpro

Code:

ma requete ouvre un curseur dans la table et fait des insert ou update dans d'autre table (dispatching)

Un curseur n'est pas optimisable. Evitez TOUT curseur. Ne faites jamais que du code ensembliste (requêtes). Il n'y a aucune raison de faire des curseurs surtout depuis la version 2005. Dans le pire des cas c'est que la base est mal modélisée.

Lisez les articles que j'ai écrit sur l'optimsation dans SQL Server :
1) http://www.sqlspot.com/Voulez-vous-optimiser-votre.html
2) http://sqlpro.developpez.com/cours/s...r_avoidCursor/
3) http://www.sqlspot.com/INDEXATION-un...l-exemple.html
4) http://sqlpro.developpez.com/cours/optimiser/

Les pertes de perfomances sont liées :
à 60% à un mauvais modèle de données : respecte t-il les formes normales ?
à 20% à une mauvaise indexation : les index sont-ils pertinents et efficace ? Les accès se font-il dans plus de 80% des cas sur des index ?
à 10% sur les réglages du serveur
à 10% sur la structure des fichiers de la base : fichiers de tailles fixes, agrégats sur disques physique, répartition des espaces de stockage...

A +

Remplacer le curseur

il est donc possible de remplacer le curseur suivant par une simple requete ?

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
	SELECT @vchaine='DECLARE c_pharma CURSOR FOR'
			+' SELECT DISTINCT cd_qps,'
 
 
		IF (LEFT(@vnm_tab_nor,6)='memo_s')
		 BEGIN
			SELECT @vchaine=@vchaine+'dt_fic AS dt_trv'
		 END
		ELSE
		IF (LEFT(@vnm_tab_nor,6)='memo_m')
		 BEGIN
			SELECT @vchaine=@vchaine+'DBO.cal_dt_sta(dt_mvt,0) AS dt_trv'
		 END
		ELSE
		IF (LEFT(@vnm_tab_nor,6)='memo_o')
		 BEGIN
			SELECT @vchaine=@vchaine+'DBO.cal_dt_sta(ISNULL(dt_env,dt_cre),0) AS dt_trv'
		 END
		ELSE
		 BEGIN
			SELECT @vchaine=@vchaine+'DBO.cal_dt_sta(dt_ven,0) AS dt_trv'
		 END
	SELECT @vchaine=@vchaine	
			+' FROM '
			+  @vnm_table
			+' WITH (NOLOCK) ORDER BY cd_qps,dt_trv'
	EXEC (@vchaine)
        OPEN c_pharma
 
 
-- Lecture du premeire enregistrement
	FETCH c_pharma INTO @vcd_qps,@vdt_trv
 
 
	SET @vresultat=0
 
-- Debut de la boucle de lecture du curseur
	WHILE (@@fetch_status=0)
	   BEGIN

16/01/2008, 15h12
SQLpro

Impossible de vous le dire sans que vous fournissiez la description sous forme DDL des tables en jeu et la finalité du traitement (jeu de données et résultat attendu).

A +
16/01/2008, 16h35
plutonium719

Quand on lace une requête et que l'on inclue le plan d'exécution réel, est-ce que la requête prend plus de temps ou c'est égal ?
17/01/2008, 16h13
plutonium719

SQLPRo,

J'ai utilisé le Profiler comme tu as dis plus haut.
J'utilise malheureusement un curseur (pas le choix !!! Peux pas faire autrement :( )

Dedans à un moment il me met "Sort Warning"

ça correspond à quoi ? Il faut lui rajouter un Order by ?
17/01/2008, 18h22
plutonium719

me répond à moi-même ...

le nombre de résultats triés est trop important.