Artem Yevtushenko | Builder Education |7 min read |24 avril 2019
SQL est l’un des outils de traitement des données les plus puissants qui soient. Dans SQL Superstar, nous vous donnons des conseils pratiques pour vous aider à tirer le meilleur parti de ce langage polyvalent et à créer des requêtes magnifiques et efficaces.
Si vous fonctionnez sans entrepôt de données ou sans base de données analytique distincte pour le reporting, la base de données de production en direct est probablement votre seule source de données récentes et actualisées. Lors de l’interrogation d’une base de données de production, l’optimisation est essentielle. Une requête inefficace épuisera les ressources de la base de données de production et entraînera un ralentissement des performances ou une perte de service pour les autres utilisateurs si la requête contient des erreurs. Il est vital que vous optimisiez vos requêtes pour un impact minimal sur les performances de la base de données.
Définir d’abord les exigences métier
Nous avons couvert les meilleures pratiques pour définir les exigences métier pour la BI ailleurs. Assurez-vous définitivement d’appliquer ces pratiques lorsque vous optimisez les requêtes SQL, notamment :
- Identifier les parties prenantes pertinentes. Assurez-vous que toutes les parties nécessaires sont dans la discussion pour développer votre requête. Lorsque vous interrogez des bases de données de production, assurez-vous que l’équipe DBA est incluse.
- Focus sur les résultats commerciaux. Donnez à la requête un objectif défini et unique. Taxer la base de données de production pour des rapports exploratoires ou dupliqués est un risque inutile.
- Cadrer la discussion pour des exigences optimales. Définissez la fonction et la portée du rapport en identifiant son public cible. Cela permettra de concentrer la requête sur les tables avec le bon niveau de détail.
- Prenez de bonnes questions. Suivez les 5 W’s : Qui ? Quoi ? Où ? Quand ? Pourquoi ?
- Écrivez des exigences très spécifiques et confirmez-les avec les parties prenantes. Les performances de la base de données de production sont trop critiques pour avoir des exigences peu claires ou ambiguës. Assurez-vous que les exigences sont aussi spécifiques que possible et confirmez-les avec toutes les parties prenantes avant d’exécuter la requête.
SELECTIONNER les champs au lieu d’utiliser SELECT *
Lors de l’exécution de requêtes exploratoires, de nombreux développeurs SQL utilisent SELECT * (lire » select all « ) comme raccourci pour interroger toutes les données disponibles dans une table. Cependant, si une table comporte de nombreux champs et de nombreuses lignes, cela taxe les ressources de la base de données en interrogeant beaucoup de données inutiles.
L’utilisation de l’instruction SELECT oriente la base de données vers l’interrogation des seules données dont vous avez besoin pour répondre aux exigences métier. Voici un exemple où les besoins métier demandent les adresses postales des clients.
Inefficace:
SELECT *
FROM Customers
Cette requête peut tirer d’autres données également stockées dans la table client, comme les numéros de téléphone, les dates d’activité et les notes des ventes et du service client.
Efficace:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
Cette requête est beaucoup plus propre et ne tire que les informations requises pour les adresses postales.
Pour conserver un index de tous les noms de tables et de champs, exécutez une requête à partir d’une table système telle que INFORMATION_SCHEMA ou ALL_TAB_COLUMNS (pour MS SQL Server, lisez ceci).
Débutez dès aujourd’hui avec SQL:
Évitez SELECT DISTINCT
SELECT DISTINCT est un moyen pratique de supprimer les doublons d’une requête. SELECT DISTINCT fonctionne en regroupant tous les champs de la requête pour créer des résultats distincts. Cependant, pour atteindre cet objectif, une grande puissance de traitement est nécessaire. En outre, les données peuvent être regroupées au point d’être inexactes. Pour éviter d’utiliser SELECT DISTINCT, sélectionnez davantage de champs afin de créer des résultats uniques.
Inefficace et inexact:
SELECT DISTINCT FirstName, LastName, State
FROM Customers
Cette requête ne tient pas compte du fait que plusieurs personnes d’un même état ont le même prénom et le même nom de famille. Les noms populaires tels que David Smith ou Diane Johnson seront regroupés, ce qui entraînera un nombre inexact d’enregistrements. Dans les bases de données plus importantes, un grand nombre de David Smith et de Diane Johnson entraînera une exécution lente de cette requête.
Efficace et précise:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
En ajoutant plus de champs, des enregistrements non dupliqués ont été renvoyés sans utiliser SELECT DISTINCT. La base de données n’a pas à regrouper de champs, et le nombre d’enregistrements est exact.
Voyez Sisense en action :
Explorer le tableau de bord
Créer des jointures avec INNER JOIN (et non WHERE)
Certains développeurs SQL préfèrent réaliser des jointures avec des clauses WHERE, telles que les suivantes :
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
Ce type de jointure crée une jointure cartésienne, également appelée produit cartésien ou CROSS JOIN.
Dans une jointure cartésienne, toutes les combinaisons possibles des variables sont créées. Dans cet exemple, si nous avions 1 000 clients avec 1 000 ventes totales, la requête générerait d’abord 1 000 000 de résultats, puis filtrerait pour les 1 000 enregistrements où CustomerID est correctement joint. Il s’agit d’une utilisation inefficace des ressources de la base de données, puisque celle-ci a effectué 100x plus de travail que nécessaire. Les jointures cartésiennes sont particulièrement problématiques dans les bases de données à grande échelle, car une jointure cartésienne de deux grandes tables pourrait créer des milliards ou des trillions de résultats.
Pour éviter de créer une jointure cartésienne, utilisez plutôt la jointure INNER JOIN:
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
La base de données ne générerait que les 1 000 enregistrements souhaités où CustomerID est égal.
Certains systèmes de SGBD sont capables de reconnaître les jointures WHERE et de les exécuter automatiquement en tant que INNER JOIN à la place. Dans ces systèmes SGBD, il n’y aura aucune différence de performance entre une jointure WHERE et un INNER JOIN. Cependant, INNER JOIN est reconnu par tous les systèmes de SGBD. Votre DBA vous conseillera sur ce qui est le mieux dans votre environnement.
Utiliser WHERE au lieu de HAVING pour définir des filtres
Le but d’une requête efficace est de tirer uniquement les enregistrements requis de la base de données. Selon l’ordre des opérations SQL, les instructions HAVING sont calculées après les instructions WHERE. Si l’intention est de filtrer une requête en fonction de conditions, une instruction WHERE est plus efficace.
Par exemple, supposons que 200 ventes ont été réalisées au cours de l’année 2016, et nous voulons interroger le nombre de ventes par client en 2016.
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
Cette requête tirerait 1 000 enregistrements de ventes de la table Ventes, puis filtrerait les 200 enregistrements générés au cours de l’année 2016, et enfin compterait les enregistrements dans le jeu de données.
En comparaison, les clauses WHERE limitent le nombre d’enregistrements tirés :
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
Cette requête tirerait les 200 enregistrements de l’année 2016, puis compterait les enregistrements dans le jeu de données. La première étape de la clause HAVING a été complètement éliminée.
HAVING ne doit être utilisé que pour filtrer sur un champ agrégé. Dans la requête ci-dessus, nous pourrions en outre filtrer les clients ayant plus de 5 ventes à l’aide d’une instruction HAVING.
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5
>>Kit de démarrage gratuit : Démarrez dès aujourd’hui avec notre kit de démarrage SQL gratuit
Utiliser les jokers à la fin d’une phrase uniquement
Lors de la recherche de données en clair, telles que des villes ou des noms, les jokers créent la recherche la plus large possible. Cependant, la recherche la plus large est aussi la recherche la plus inefficace.
Lorsqu’un caractère générique de tête est utilisé, surtout en combinaison avec un caractère générique de fin, la base de données a pour tâche de rechercher dans tous les enregistrements une correspondance n’importe où dans le champ sélectionné.
Considérez cette requête pour tirer les villes commençant par ‘Char’:
SELECT City FROM Customers
WHERE City LIKE ‘%Char%’
Cette requête tirera les résultats attendus de Charleston, Charlotte et Charlton. Cependant, elle tirera également des résultats inattendus, tels que Cape Charles, Crab Orchard et Richardson.
Une requête plus efficace serait :
SELECT City FROM Customers
WHERE City LIKE ‘Char%’
Cette requête tirera uniquement les résultats attendus de Charleston, Charlotte et Charlton.
Utiliser LIMIT pour échantillonner les résultats d’une requête
Avant d’exécuter une requête pour la première fois, assurez-vous que les résultats seront souhaitables et significatifs en utilisant une instruction LIMIT. (Dans certains systèmes de SGBD, le mot TOP est utilisé de manière interchangeable avec LIMIT.) L’instruction LIMIT ne renvoie que le nombre d’enregistrements spécifié. L’utilisation d’une instruction LIMIT évite de taxer la base de données de production avec une requête volumineuse, pour découvrir ensuite que la requête a besoin d’être modifiée ou affinée.
Dans la requête sur les ventes de 2016 présentée ci-dessus, nous allons examiner une limite de 10 enregistrements :
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10
Nous pouvons voir par l’échantillon si nous avons un ensemble de données utilisable ou non.
Exécutez votre requête pendant les heures creuses
Afin de minimiser l’impact de vos requêtes analytiques sur la base de données de production, demandez à un DBA de programmer l’exécution de la requête à une heure creuse. La requête doit être exécutée lorsque les utilisateurs simultanés sont au nombre le plus bas, ce qui est généralement le milieu de la nuit (3 à 5 heures du matin).
Plus votre requête présente les critères suivants, plus elle devrait être candidate à une exécution nocturne :
- Sélectionner dans de grandes tables (>1 000,000 enregistrements)
- Les jointures cartésiennes ou CROSS JOIN
- Les instructions en boucle
- Les instructionsSELECT DISTINCT
- Les sous-requêtes imbriquées
- Les recherches par caractères génériques dans les champs de texte long ou mémo. champs
- Des requêtes à schémas multiples
Des requêtes en toute confiance
Avec ces conseils en tête (plus quelques autres trucs et astuces SQL en poche), vous devriez être en mesure de construire des requêtes efficaces, belles requêtes qui fonctionneront sans problème et renverront les informations qui changent le jeu dont votre équipe a besoin.
Obtenez le kit de démarrage SQL gratuit ici:
.