Dans les organisations fonctionnant sans entrepôt de données ou sans base de données analytique distincte pour la génération de rapports, la base de données de production active représente probablement l’unique source contenant les données les plus récentes et à jour. L’optimisation est alors essentielle lors de l’interrogation d’une base de données de production. Une requête inefficace peut avoir un impact négatif sur les ressources de la base de données de production, et peut ralentir les performances du service ou empêcher les autres utilisateurs d’utiliser ce dernier si elle comprend des erreurs. Par conséquent, il est important d’optimiser vos requêtes pour qu’elles aient un impact minimal sur les performances de la base de données.

1. Définir les exigences métier avant de commencer

Dans un précédent article, nous avons présenté les meilleures pratiques pour définir les exigences métier en matière d’informatique décisionnelle. Ces mêmes pratiques doivent être adoptées lors de l’optimisation des requêtes SQL, notamment les points suivants :

  • Identification des parties prenantes appropriées. Veillez à inclure toutes les parties prenantes concernées dans les décisions relatives au développement de votre requête. Assurez-vous également que l’équipe d’administrateurs de bases de données est impliquée lors de l’interrogation des bases de données de production.
  • Concentration sur les résultats opérationnels. Assurez-vous que la requête a un objectif défini et unique. Utiliser des requêtes imprécises sur la base de données de production afin de générer des rapports d’exploration ou faisant double emploi est un risque inutile.
  • Encadrement des discussions pour définir des exigences pertinentes. Définissez la fonction et la portée des rapports en identifiant son audience cible. Ainsi, la requête se concentrera sur les tables comportant le niveau de précision adéquat.
  • Développement d’exigences adaptées en posant les bonnes questions. Ces questions commencent généralement part « Qui », « Quoi », « Où », « Quand », « Pourquoi ».
  • Rédaction d’exigences très spécifiques et validation auprès des parties prenantes. Les performances de la base de données de production sont trop importantes pour avoir des exigences vagues ou ambiguës. Veillez à ce que les exigences soient aussi précises que possible, et faites-les valider par les parties prenantes avant d’exécuter la requête.

2. Définir des champs SELECT à la place de SELECT *

Lors de l’exécution de requêtes d’exploration, bon nombre de développeurs SQL utilisent SELECT * (soit « sélectionner tout ») comme requête abrégée pour interroger l’ensemble des données disponibles dans une table. Cependant, si une table contient des champs et des lignes en grand nombre, les ressources de la base de données sont mises à rude épreuve en raison de l’interrogation d’une quantité importante de données inutiles.

Définir des champs dans l’instruction SELECT indiquera à la base de données d’interroger uniquement les données requises qui permettent de répondre aux exigences métier. Étudions un exemple dans lequel les exigences métier requièrent les adresses postales des clients.

Requête inefficace :

SELECT *
FROM Customers

Il se peut que cette requête extraie d’autres données également contenues dans la table relative aux clients, telles que des numéros de téléphone, des dates d’activité, et des notes des services clientèle et commercial.

Requête efficace :

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

Cette requête extrait uniquement les informations en lien avec les adresses postales.

Afin de conserver un index de l’ensemble des tables et des noms de champ, exécutez une requête à partir d’une table système telle que INFORMATION_SCHEMA ou ALL_TAB_COLUMNS (pour MS SQL Server, plus d’informations sont disponibles ici).

3. Sélectionner d’autres champs pour éviter d’utiliser SELECT DISTINCT

SELECT DISTINCT permet de retirer facilement les éléments en double d’une requête. SELECT DISTINCT reGROUPe tous les champs dans la requête afin de créer des résultats distincts. Toutefois, une puissance de traitement considérable est nécessaire pour atteindre cet objectif. Par ailleurs, le regroupement des données peut rendre ces dernières imprécises. Afin d’éviter l’utilisation de SELECT DISTINCT, il est préférable de sélectionner davantage de champs en vue de créer des résultats uniques.

Requête inefficace et imprécise :

SELECT DISTINCT FirstName, LastName, State
FROM Customers

Cette requête ne tient pas compte des personnes portant les mêmes nom et prénom dans un même État. Les noms courants tels que Jean Martin ou Léa Durand seront regroupés, ce qui engendrera un nombre inexact d’enregistrements. Dans les bases de données plus volumineuses, un grand nombre de Jean Martin et de Léa Durand ralentira l’exécution de cette requête.

Requête efficace et précise :

SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers

L’ajout de champs supplémentaires a permis de renvoyer des enregistrements uniques sans utiliser SELECT DISTINCT. La base de données n’a pas à regrouper de champs et le nombre d’enregistrements est exact.


Voir Sisense en action :

Quality Assurance Project Status - Software Dashboard

4. Créer des jointures avec INNER JOIN plutôt qu’avec WHERE

Certains développeurs SQL préfèrent effectuer des jointures avec les clauses WHERE, telles que :

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 un total des ventes de 1 000, la requête générerait d’abord 1 000 000 de résultats, puis filtrerait les 1 000 enregistrements dans lesquels CustomerID est correctement joint. Il s’agit d’une utilisation inefficace des ressources de base de données, puisque cette dernière a réalisé 100 fois plus de recherches que nécessaire. Les jointures cartésiennes sont particulièrement problématiques dans les bases de données de grande échelle, car une jointure cartésienne de deux grandes tables crée des milliards ou des billions de résultats.

Il est préférable d’utiliser INNER JOIN pour éviter de créer une jointure cartésienne :

SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
   INNER JOIN Sales
   ON Customers.CustomerID = Sales.CustomerID

La base de données générerait seulement les 1 000 enregistrements souhaités dans lesquels CustomerID est identique.

Certains systèmes SGBD sont capables de reconnaître les jointures WHERE et de les exécuter automatiquement en tant que jointures INNER JOIN. Dans ces systèmes SGBD, il n’existe aucune différence en matière de performances entre la jointure WHERE et INNER JOIN. Cependant, tous les systèmes SGBD reconnaissent INNER JOIN. Votre administrateur de bases de données vous indiquera la meilleure méthode pour votre environnement.

5. Utiliser WHERE à la place de HAVING pour définir les filtres

À l’instar du concept susmentionné, l’objectif d’une requête efficace est d’extraire uniquement les enregistrements nécessaires de la base de données. Conformément à l’ordre des opérations SQL, les instructions HAVING sont calculées après les instructions WHERE. Une instruction WHERE est plus efficace si le but est de filtrer une requête selon des conditions.

Par exemple, supposons que 200 ventes aient été réalisées en 2016, et que nous souhaitions connaître 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 extrait 1 000 enregistrements de ventes de la table des ventes, filtre les 200 enregistrements générés en 2016, puis comptabilise les enregistrements de l’ensemble de données.

En comparaison, les clauses WHERE limitent le nombre d’enregistrements extraits :

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 extrait les 200 enregistrements de 2016, puis comptabilise les enregistrements dans l’ensemble de données. La première étape de la clause HAVING a été totalement supprimée.

HAVING doit uniquement être utilisé pour filtrer sur un champ agrégé. Dans la requête ci-dessus, nous pourrions également filtrer les clients ayant effectué plus de cinq 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

6. Utiliser des caractères génériques à la fin d’une expression uniquement

Lorsque vous recherchez des données en texte brut, telles que des villes ou des noms, les caractères génériques permettent de créer la recherche la plus vaste possible. Cependant, la recherche la plus vaste est également la plus inefficace.

Lorsqu’un caractère générique de début d’expression est utilisé, particulièrement s’il est combiné à un caractère générique de fin, la base de données doit rechercher une correspondance dans l’ensemble des enregistrements, n’importe où dans le champ sélectionné.

Examinons cette requête pour extraire les villes commençant par « Char » :

SELECT City FROM Customers
WHERE City LIKE ‘%Char%’

Cette requête extraira les résultats attendus tels que Charleston, Charlotte et Charlton, mais également des résultats inattendus tels que Cape Charles, Crab Orchard et Ri chardson.

Voici une requête plus efficace :

SELECT City FROM Customers
WHERE City LIKE ‘Char%’

Cette requête extraira uniquement les résultats attendus comme Charleston, Charlotte et Charlton.

7. Utiliser LIMIT pour effectuer un échantillonnage des résultats de la requête

Avant d’exécuter une requête pour la première fois, vérifiez que les résultats seront bénéfiques et significatifs en utilisant une instruction LIMIT. (Dans certains systèmes SGBD, les mots TOP et LIMIT sont utilisés de façon interchangeable.) L’instruction LIMIT renvoie uniquement le nombre d’enregistrements indiqué. L’utilisation d’une instruction LIMIT évite de mettre à l’épreuve la base de données de production avec une requête importante, pour au final découvrir que cette dernière doit être modifiée ou affinée.

À partir de la requête des ventes de 2016 précédente, nous allons examiner un maximum 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

L’échantillon nous permet de voir si nous obtenons un ensemble de données utilisables ou non.

8. Exécuter les requêtes analytiques pendant les heures creuses

Afin de minimiser l’impact de la requête sur la base de données de production, demandez à un administrateur de bases de données de la programmer afin qu’elle soit exécutée pendant les heures creuses. Il est préférable d’exécuter la requête lorsque le nombre d’utilisateurs simultanés est au plus bas, généralement au milieu de la nuit (entre 3 h et 5 h).

Si votre requête répond à plusieurs ou à tous les critères ci-dessous, il est préférable de l’exécuter la nuit :

  • Sélection dans des tables volumineuses (> 1 000 000 enregistrements)
  • Jointures cartésiennes ou CROSS JOIN
  • Instructions d’exécution en boucle
  • Instructions SELECT DISTINCT
  • Sous-requêtes imbriquées
  • Recherches avec des caractères génériques dans des champs de mémo ou de texte longs
  • Requêtes avec plusieurs schémas
Tags: | |