Comment écrire et optimiser les procédures stockées SQL Server

[Introduction] Au cours du processus de développement d'une base de données, une logique métier et des opérations de base de données complexes sont souvent rencontrées. À ce stade, SP sera utilisé pour encapsuler les opérations de base de données. S'il y a de nombreux SP dans le projet et qu'il n'y a pas certaines normes d'écriture, cela rendra difficile la maintenance du système à l'avenir et rendra difficile la compréhension de la logique des grands SP. De plus, par exemple,

Au cours du processus de développement de la base de données, des problèmes complexes sont souvent rencontrés. Pour la logique métier et les opérations de base de données, SP sera utilisé pour encapsuler les opérations de base de données à ce stade. Si le projet comporte de nombreux SP et que l'écriture n'est pas standardisée, cela rendra difficile la maintenance du système à l'avenir et rendra difficile la compréhension de la logique des grands SP. De plus, si la quantité de données dans la base de données est élevée. grand ou le projet a des exigences de performances élevées pour les SP, vous rencontrerez C'est un problème d'optimisation, sinon la vitesse peut être très lente. Par expérience personnelle, un SP optimisé est même des centaines de fois plus efficace qu'un SP avec des performances médiocres. .

Détails :

1. Si les développeurs utilisent des tables ou des vues d'autres bibliothèques, ils doivent créer des vues dans la bibliothèque actuelle pour implémenter des opérations entre bibliothèques. directement "databse.dbo.table_name", car sp_depends ne peut pas afficher la table ou la vue inter-bases de données utilisée par le SP, ce qui n'est pas pratique pour la vérification.

2. Avant de soumettre SP, les développeurs doivent avoir utilisé set showplan on pour analyser le plan de requête et effectuer leur propre vérification d'optimisation des requêtes.

3. Pour améliorer l'efficacité du fonctionnement du programme et optimiser les applications, vous devez prêter attention aux points suivants lors de l'écriture du SP :

(a) Spécifications d'utilisation SQL. :

i. Essayez d'éviter les opérations de transactions volumineuses et utilisez la clause holdlock avec prudence pour améliorer la concurrence du système.

ii. Essayez d'éviter d'accéder de manière répétée à la ou aux mêmes tables, en particulier aux tables contenant de grandes quantités de données. Vous pouvez d'abord envisager d'extraire les données dans une table temporaire en fonction de conditions, puis d'établir une connexion.

iii. Essayez d'éviter d'utiliser des curseurs, car les curseurs sont moins efficaces. Si les données exploitées par le curseur dépassent 10 000 lignes, elles doivent être réécrites si un curseur est utilisé, essayez d'éviter les boucles de curseur. Effectuez ensuite l’opération de jointure de table.

iv. Faites attention à l'écriture des mots où. L'ordre des déclarations doit être déterminé en fonction de l'ordre de l'index et de la taille du champ. ordre cohérent avec l'ordre et la plage de l'index, du grand au petit.

v. N'effectuez pas de fonctions, d'opérations arithmétiques ou d'autres opérations d'expression sur le côté gauche de "=" dans la clause Where, sinon le système pourrait ne pas être en mesure d'utiliser l'index correctement.

vi. Essayez d'utiliser exist au lieu de select count(1) pour déterminer si un enregistrement existe. La fonction count n'est utilisée que pour compter toutes les lignes de la table, et count(1) est plus. plus pratique que count(*).

vii. Essayez d'utiliser «>=» au lieu de «>».

viii. Faites attention au remplacement entre certaines clauses or et les clauses d'union

ix. de connexion de données.

x. Faites attention à la relation entre les paramètres et les types de données dans la procédure stockée.

xi. Faites attention au volume de données des opérations d'insertion et de mise à jour pour éviter les conflits avec d'autres applications. Si la quantité de données dépasse 200 pages de données (400 000 ), le système mettra à niveau le verrou et le verrou au niveau de la page sera mis à niveau vers un verrou au niveau de la table.

(b) Spécifications d'utilisation de l'index :

i La création d'index doit être envisagée en conjonction avec l'application. les grandes tables OLTP ne doivent pas dépasser 6 index.

ii. Essayez d'utiliser les champs d'index comme conditions de requête, en particulier les index clusterisés. Si nécessaire, vous pouvez utiliser l'index nom_index pour forcer la spécification de l'index

iii. Évitez le couplage. Effectuez une analyse de table lors de l'interrogation de tables volumineuses et envisagez de créer de nouveaux index si nécessaire.

iv. Lors de l'utilisation d'un champ d'index comme condition, si l'index est un index conjoint, alors le premier champ de l'index doit être utilisé comme condition pour garantir que le système utilise l'index, sinon l'index ne sera pas utilisé.

v. Faites attention à la maintenance de l'index, reconstruisez périodiquement l'index et recompilez la procédure stockée.

(c) Spécifications d'utilisation de Tempdb :

i Essayez d'éviter d'utiliser distinct, classer par, regrouper par, avoir, rejoindre, cumuler. , car ces déclarations augmenteront la charge de tempdb.

ii. Évitez de créer et de supprimer fréquemment des tables temporaires et réduisez la consommation des ressources des tables système.

iii. Lors de la création d'une table temporaire, si la quantité de données insérées en même temps est importante, vous pouvez utiliser select into au lieu de créer une table pour éviter les journaux et améliorer la vitesse si la quantité de données est importante ; n'est pas volumineux, afin de faciliter le système. Pour les ressources de table, il est recommandé de créer d'abord une table, puis de l'insérer.

iv. Si la table temporaire contient une grande quantité de données et doit être indexée, le processus de création de la table temporaire et d'indexation doit être placé dans une procédure sous-stockée distincte pour garantir que le le système peut facilement Il est préférable d'utiliser l'index de la table temporaire.

v. Si des tables temporaires sont utilisées, toutes les tables temporaires doivent être explicitement supprimées à la fin de la procédure stockée. Commencez par tronquer la table, puis supprimez-la. Cela peut éviter un verrouillage à long terme. des tables système.

vi. Soyez prudent lors de la connexion de requêtes et de modifications entre de grandes tables temporaires et d'autres grandes tables afin de réduire la charge sur les tables système, car cette opération utilisera la table système tempdb plusieurs fois dans une seule instruction.

(d) Utilisation raisonnable de l'algorithme :

Basé sur la technologie d'optimisation SQL mentionnée ci-dessus et le contenu d'optimisation SQL dans le manuel ASE Tuning, combiné à des applications pratiques, plusieurs algorithmes sont utilisés à des fins de comparaison pour obtenir la méthode qui consomme le moins de ressources et est la plus efficace. . Des commandes de réglage ASE spécifiques sont disponibles : activer les statistiques, activer l'heure des statistiques, activer showplan, etc.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!