Cinq façons d'améliorer vos performances SQL
Cet article est extrait de l'article MSDN « Cinq façons d'améliorer les performances SQL » et propose comment améliorer l'efficacité de fonctionnement des applications basées sur SQL Server. Il est fortement recommandé. Pour certains systèmes d'application à fort trafic, la façon d'améliorer et d'améliorer les instructions SQL est très importante et constitue un bon point de rupture.
*L'article comprend principalement le contenu suivant (si vous êtes intéressé, veuillez visiter directement l'URL suivante pour lire le document complet en chinois et en anglais) :
1. Retourner l'IDENTITÉ depuis INSÉRER
SELECT @@IDENTITY
2, vue intégrée et table temporaire
Tables temporaires – Les tables temporaires dans tempdb peuvent amener les requêtes à effectuer des opérations d'E/S et des accès au disque lourds, et les tables temporaires peuvent consommer beaucoup de ressources.
Vues en ligne : utilisez des vues en ligne au lieu de tables temporaires. Une vue en ligne est simplement une requête qui peut être jointe dans la clause FROM. Si vous avez simplement besoin de joindre des données à d'autres requêtes, vous pouvez essayer d'utiliser des vues en ligne pour économiser des ressources.
3. Évitez LEFT JOIN et NULL
Les LEFT JOIN sont très gourmands en ressources car ils contiennent des données qui correspondent à des données NULL (inexistantes). Dans certains cas, cela est inévitable, mais le coût peut être très élevé. LEFT JOIN consomme plus de ressources que INNER JOIN, donc si vous pouvez réécrire la requête pour qu'elle n'utilise aucun LEFT JOIN, vous obtiendrez une très bonne récompense.
Une technique pour accélérer les requêtes qui utilisent LEFT JOIN consiste à créer un type de données TABLE, à insérer toutes les lignes de la première table (la table à gauche de LEFT JOIN), puis à mettre à jour le type de données TABLE avec les valeurs de le deuxième tableau. Cette technique est un processus en deux étapes, mais peut faire gagner beaucoup de temps par rapport à un LEFT JOIN standard. Une bonne règle consiste à essayer différentes techniques et à enregistrer le temps requis pour chacune jusqu'à ce que vous obteniez la requête la plus performante pour votre application.
DECLARE @tblMonths TABLE (sMonth VARCHAR(7))
4, utilisation flexible du produit cartésien
J'entrerai dans les détails de cette technique et préconiserai l'utilisation de produits cartésiens dans certains cas. Pour une raison quelconque, les produits cartésiens (CROSS JOIN) suscitent beaucoup de critiques et les développeurs sont souvent avertis de ne pas les utiliser du tout. Dans de nombreux cas, ils consomment trop de ressources pour être utilisés efficacement. Mais comme tout outil SQL, ils peuvent être précieux s’ils sont utilisés correctement.
L'un des exemples de codes mérite d'être suivi :
-- Le produit cartésien renvoie tous les clients pour tous les mois. Le produit cartésien multiplie essentiellement la première table par la deuxième table, produisant un ensemble de lignes contenant le nombre de lignes de la première table multiplié par le nombre de lignes de la deuxième table. Par conséquent, le produit cartésien renvoie 12 (tous les mois) * 81 (tous les clients) = 972 lignes à la table @tblFinal. Les dernières étapes consistent à mettre à jour la table @tblFinal avec les totaux de ventes mensuels pour chaque client pour cette plage de dates et à sélectionner l'ensemble final de lignes.
DÉCLARE @tblMonths TABLE (sMonth VARCHAR(7))
DÉCLARE @tblCustomers TABLE (CustomerID CHAR(10),
Nom de l'entreprise VARCHAR(50),
Nom du contact VARCHAR(50))
DÉCLARE @tblFinal TABLE (sMonth VARCHAR(7),
ID client CHAR(10),
Nom de l'entreprise VARCHAR(50),
Nom du contact VARCHAR(50),
mSalesMONEY)
DÉCLARE @dtStartDate DATETIME,
@dtEndDate DATEHEURE,
@dtDate DATEHEURE,
@i INTEGER
SET @dtEndDate = '5/5/1997'
SET @dtEndDate = DATEADD(DD, -1, CAST(CAST((MONTH (@dtEndDate) + 1) AS
VARCHAR(2)) + '/01/' + CAST(YEAR(@dtEndDate) AS VARCHAR(4)) + '23:59:59' AS DATETIME))
SET @dtStartDate = DATEADD(MM, -1 * 12, @dtEndDate)
- Mettez tous les mois dans le premier tableau
ENSEMBLE @i = 0
PENDANT (@i < 12)
COMMENCER
FIXER @dtDate = DATEADD(mm, -1 * @i, @dtEndDate)
INSERT INTO @tblMonths SELECT CAST(YEAR (@dtDate) AS VARCHAR(4)) + '-' +
CAS
QUAND MOIS (@dtDate) < 10
PUIS '0' + CAST(MONTH (@dtDate) AS VARCHAR(2))
AUTRE CAST (MOIS (@dtDate) COMME VARCHAR (2))
FIN COMME sMois
ENSEMBLE @i = @i + 1
FIN
-- Récupérez tous les clients qui ont réalisé des ventes au cours de cette période dans le tableau "y".
INSÉRER DANS @tblCustomers
SÉLECTIONNER DISTINCT
c.ID client,
c.Nom de l'entreprise,
c.Nom du contact
DES clients c
Commandes INNER JOIN o ON c.CustomerID = o.CustomerID
O.OrderDate ENTRE @dtStartDate ET @dtEndDate
INSÉRER DANS @tblFinal
SELECT m.sMois,
c.ID client,
c.Nom de l'entreprise,
c.Nom du contact,
0
FROM @tblMonths m CROSS JOIN @tblCustomers c
UPDATE @tblFinal SET
mSales = mesdonnées.mSales
DE @tblFinal f INNER JOIN
(
SELECT c.CustomerID,
CAST(ANNÉE(o.OrderDate) AS VARCHAR(4)) + '-' +
CAS QUAND MOIS(o.OrderDate) < 10
PUIS '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
AUTRE CAST(MOIS(o.OrderDate) AS VARCHAR(2))
FIN LE sMois,
SUM(od.Quantity * od.UnitPrice) AS mVentes
DES clients c
Commandes INNER JOIN o ON c.CustomerID = o.CustomerID
INNER JOIN [Détails de la commande] od ON o.OrderID = od.OrderID
O.OrderDate ENTRE @dtStartDate ET @dtEndDate
GROUPER PAR
c.ID client,
CAST(ANNÉE(o.OrderDate) AS VARCHAR(4)) + '-' +
CAS QUAND MOIS(o.OrderDate) < 10
PUIS '0' + CAST(MONTH(o.OrderDate) AS VARCHAR(2))
AUTRE CAST(MOIS(o.OrderDate) AS VARCHAR(2))
FIN
) mes données sur f.CustomerID = mes données.CustomerID ET f.sMonth =
mesdonnées.sMois
SELECT f.sMois,
f.IDClient,
f.Nom de l'entreprise,
f.ContactName,
f.mVentes
DE @tblFinal f
COMMANDER PAR
f.Nom de l'entreprise,
f.sMois
5. Ramassez les pièces manquantes et rattrapez les pièces manquantes.
Voici quelques autres techniques courantes qui peuvent contribuer à améliorer l’efficacité des requêtes SQL. Supposons que vous souhaitiez regrouper tous les vendeurs par région et sous-totaliser leurs ventes, mais que vous souhaitiez uniquement que les vendeurs soient marqués comme actifs dans la base de données. Vous pouvez regrouper les vendeurs par région et éliminer ceux qui ne sont pas actifs à l'aide de la clause HAVING, ou vous pouvez le faire dans la clause WHERE. Faire cela dans la clause WHERE réduit le nombre de lignes à regrouper, c'est donc plus efficace que de le faire dans la clause HAVING. Le filtrage basé sur les conditions de ligne dans la clause HAVING force la requête à regrouper les données qui seraient supprimées dans la clause WHERE.
Un autre conseil d'efficacité consiste à utiliser le mot-clé DISTINCT pour rechercher des rapports distincts pour les lignes de données au lieu d'utiliser la clause GROUP BY. Dans ce cas, SQL utilisant le mot-clé DISTINCT est plus efficace. Veuillez utiliser GROUP BY uniquement lorsque vous devez calculer des fonctions d'agrégation (SUM, COUNT, MAX, etc.). De plus, si votre requête renvoie toujours une ligne unique, n'utilisez pas le mot clé DISTINCT. Dans ce cas, le mot clé DISTINCT ajoute uniquement une surcharge système.
－－－－－－－－－－－－－－－－－－－
URL chinoise :
http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx
URL en anglais :
http://msdn.microsoft.com/msdnmag/issues/02/07/DataPoints/