Réglage des performances des requêtes (SQL Server Compact)

Vous pouvez améliorer les performances de votre application SQL Server Compact 4.0 en optimisant les requêtes que vous utilisez. Les sections ci-dessous présentent les techniques qui vous permettent d'optimiser les performances des requêtes.

Amélioration des index

Créer des index utiles constitue l'une des méthodes les plus importantes pour améliorer les performances des requêtes. Des index utiles vous permettent de trouver des données en utilisant moins d'opérations d'E/S disque et de ressources système.

Pour créer des index utiles, vous devez comprendre l'utilisation des données, les types de requêtes et leur fréquence d'exécution. Vous devez également comprendre comment le processeur de requêtes peut utiliser des index pour trouver rapidement des données.

Lorsque vous décidez de créer des index, étudiez vos requêtes les plus importantes, et plus particulièrement celles dont les performances ont le plus d'impact sur les utilisateurs. Créez des index en tenant compte de ces requêtes. Une fois un index ajouté, réexécutez la requête pour déterminer si les performances ont été améliorées. Si tel n'est pas le cas, supprimez l'index.

Comme avec la plupart des techniques d'optimisation des performances, il n'existe pas de règles générales. Par exemple, les requêtes SELECT s'exécuteront éventuellement plus rapidement avec davantage d'index, alors que les opérations de manipulation de données (DML) (INSERT, UPDATE et DELETE) seront ralenties de manière significative, car davantage d'index devront être gérés avec chaque opération. Si vos requêtes sont principalement des instructions SELECT, davantage d'index peuvent être utiles. Si votre application effectue de nombreuses opérations de manipulation de données (DML), vous devez limiter le nombre d'index créés.

SQL Server Compact prend en charge les plans d'exécution de requêtes qui permettent d'évaluer et d'optimiser des requêtes. SQL Server Compact utilise le même schéma de plan d'exécution de requêtes que SQL Server 2008 R2, sauf que SQL Server Compact utilise un sous-ensemble d'opérateurs. Pour plus d'informations, consultez le schéma de plan de requête Microsoft sur https://schemas.microsoft.com/sqlserver/2004/07/showplan/.

Les quelques sections ci-dessous fournissent des informations supplémentaires sur la création d'index utiles.

Création d'index très sélectifs

L'indexation des colonnes utilisées dans la clause WHERE de vos requêtes les plus importantes améliore fréquemment les performances. Cela dépend toutefois de la sélectivité de l'index. La sélectivité est le rapport des lignes de qualification contre le nombre total des lignes. Si le rapport est faible, l'index est très sélectif. Il peut éliminer la plupart des lignes et réduire de façon significative la taille de l'ensemble des résultats. C'est par conséquent un index utile à créer, contrairement à un index non sélectif.

Les index uniques sont les index les plus sélectifs. Une seule ligne peut correspondre, ce qui est particulièrement utile pour les requêtes destinées à ne renvoyer qu'une seule ligne. Par exemple, un index sur une colonne d'ID unique vous permet de trouver rapidement une ligne particulière.

Vous pouvez évaluer la sélectivité d'un index en exécutant les procédures stockées sp_show_statistics sur les tables SQL Server Compact. Par exemple, si vous évaluez la sélectivité de deux colonnes, « Customer ID » et « Ship Via », vous pouvez exécuter les procédures stockées suivantes :

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI               0            7                   0

ANATR               0            4                   0

ANTON               0           13                   0

AROUT               0           14                   0

BERGS               0           23                   0

BLAUS               0            8                   0

BLONP               0           14                   0

BOLID               0            7                   0

BONAP               0           19                   0

BOTTM               0           20                   0

BSBEV               0           12                   0

CACTU               0            6                   0

CENTC               0            3                   0

CHOPS               0           12                   0

COMMI               0            5                   0

CONSH               0            4                   0

DRACD               0            9                   0

DUMON               0            8                   0

EASTC               0           13                   0

ERNSH               0           33                   0

(90 rows affected)

et

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1               0            320                   0

2               0            425                   0

3               0            333                   0

(3 rows affected)

Les résultats indiquent que la colonne « Customer ID » possède un degré de duplication bien inférieur. Cela signifie qu'un index sur cette colonne sera plus sélectif qu'un index sur la colonne « Ship Via ».

Pour plus d'informations sur l'utilisation de ces procédures stockées, consultez sp_show_statistics (SQL Server Compact 3.5), sp_show_statistics_steps (SQL Server Compact 3.5) et sp_show_statistics_columns (SQL Server Compact).

Création d'index de plusieurs colonnes

Les index de plusieurs colonnes sont des prolongements naturels des index de colonne unique. Ils sont utiles pour évaluer des expressions de filtre qui correspondent à un ensemble de préfixes de colonnes clés. Par exemple, l'index composite CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) permet d'évaluer les requêtes suivantes :

• ... WHERE "Last Name" = 'Doe'

• ... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'

• ... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

Toutefois, il est inutile pour la requête suivante :

• ... WHERE "First Name" = 'John'

Lorsque vous créez un index de plusieurs colonnes, vous devez placer les colonnes les plus sélectives le plus à gauche dans la clé. L'index est ainsi plus sélectif lors de l'utilisation de plusieurs expressions.

Indexation non conseillée des petites tables

Une petite table est une table dont le contenu tient dans une ou quelques pages de données. Évitez d'indexer des tables très petites, car une analyse de table est généralement plus efficace. Elle évite le chargement et le traitement des pages d'index. En ne créant pas d'index sur de très petites tables, vous évitez que l'optimiseur en choisisse un.

SQL Server Compact stocke les données dans des pages de 4 Ko. Le nombre de pages peut être calculé approximativement à l'aide de la formule ci-après, bien que le nombre réel puisse être légèrement supérieur en raison du traitement du moteur de stockage.

<somme des tailles des colonnes, en octets> * <Nbre de lignes>

<Nbre de pages> = -----------------------------------------------------------------

4096

Imaginons, par exemple, qu'une table possède le schéma suivant :

Nom de colonne	Type (taille)
ID de commande	INTEGER (4 octets)
ID de produit	INTEGER (4 octets)
Prix unitaire	MONEY (8 octets)
Quantité	SMALLINT (2 octets)
Remise	REAL (4 octets)

La table contient 2820 lignes. Selon la formule, il faut 16 pages pour stocker ses données :

<Nbre de pages> = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15.15 pages

Choix des éléments à indexer

Il est recommandé de créer des index sur des clés primaires. Il est également souvent utile de créer des index sur des clés étrangères. En effet, les clés primaires et les clés étrangères sont souvent utilisées pour joindre des tables. Des index sur ces clés permettent à l'optimisateur de requêtes d'envisager des algorithmes de jointure d'index plus efficaces. Si votre requête joint des tables en utilisant d'autres colonnes, il est souvent utile de créer des index sur ces colonnes pour la même raison.

Lorsque des contraintes de clés étrangères et de clés primaires sont créées, SQL Server Compact crée automatiquement des index pour ces dernières et tire parti de celles-ci lors de l'optimisation des requêtes. Faites en sorte que les clés primaires et les clés étrangères soient petites pour que les jointures soient exécutées plus rapidement.

Utilisation d'index avec des clauses de filtre

Des index peuvent être utilisés pour accélérer l'évaluation de certains types de clauses de filtre. Bien que toutes les clauses de filtre limitent l'ensemble de résultats final d'une requête, certaines clauses peuvent également limiter la quantité de données à analyser.

Un argument de recherche (SARG) restreint une recherche, car il spécifie une concordance exacte, une plage de valeurs ou un ensemble de deux éléments ou davantage réunis par un opérateur AND. Il peut prendre une des formes suivantes :

• Colonne opérateur <constante ou variable>

• <constante ou variable> opérateur Colonne

Les opérateurs SARG comprennent =, >, <, >=, <=, IN, BETWEEN et parfois LIKE (dans les cas des concordances des préfixes, tels que LIKE 'John%'). Un argument SARG peut contenir plusieurs conditions réunies par un opérateur AND. Il peut s'agir d'une requête qui correspond à une valeur spécifique, telle que :

• "Customer ID" = 'ANTON'

• 'Doe' = "Last Name"

Il peut également s'agir d'une requête qui correspond à une plage de valeurs, telle que :

• "Order Date" > '1/1/2002'

• "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'

• "Customer ID" IN ('ANTON', 'AROUT')

Une expression qui n'utilise pas d'opérateurs SARG n'améliore en aucun cas les performances, car le processeur de requêtes SQL Server Compact doit évaluer chaque ligne pour déterminer si elle répond à la clause de filtre. Pour cette raison, un index est inutile sur des expressions qui n'utilisent pas des opérateurs SARG. Les opérateurs non-SARG comprennent NOT, <>, NOT EXISTS, NOT IN, NOT LIKE et des fonctions intrinsèques.

Utilisation de l'optimiseur de requête

Lorsque l'optimiseur de requête SQL Server Compact détermine les méthodes d'accès aux tables de la base de données, il détermine si un index existe pour une clause SARG. Si tel est le cas, l'optimiseur évalue l'index en calculant le nombre de lignes renvoyées. Il estime ensuite la charge de travail qu'implique la recherche de lignes de qualification à l'aide de l'index. Il choisira un accès indexé si celui-ci implique une charge de travail moins importante qu'une analyse de table. Un index peut être utile si sa première colonne ou l'ensemble des préfixes des colonnes sont utilisés dans la clause SARG et que celle-ci établit une limite supérieure ou inférieure (ou les deux) pour restreindre la recherche.

Distinction entre le temps de réponse et le temps total

Le temps de réponse est le temps que prend une requête pour renvoyer le premier enregistrement. Le temps total est le temps que prend la requête pour renvoyer tous les enregistrements. Pour une application interactive, le temps de réponse est important, car il permet d'indiquer à l'utilisateur qu'une requête est en cours de traitement. Pour une application de traitement par lot, le temps total reflète le débit général. Vous devez déterminer les critères de performances de l'application et des requêtes, puis les concevoir en conséquence.

Supposons, par exemple, qu'une requête renvoie 100 enregistrements et qu'elle est utilisée pour remplir une liste avec les cinq premiers enregistrements. Dans ce cas, la durée de l'opération de renvoi des 100 enregistrements vous importe peu. Ce que vous souhaitez, c'est que la requête renvoie rapidement les quelques premiers enregistrements de sorte que vous puissiez remplir la liste.

De nombreuses opérations de requête peuvent être effectuées sans devoir stocker les résultats intermédiaires. On dit que ces opérations sont en pipeline. Les projections, les sélections et les jointures sont des exemples d'opérations en pipeline. Les requêtes implémentées avec ces opérations peuvent renvoyer des résultats immédiatement. D'autres opérations, telles que SORT et GROUP-BY, doivent utiliser toutes leurs entrées avant de renvoyer des résultats à leurs opérations parentes. On dit que ces opérations requièrent une matérialisation. Les requêtes implémentées avec ces opérations ont un délai initial en raison de la matérialisation. Après ce délai initial, elles renvoient généralement des enregistrements très rapidement.

Les requêtes qui demandent un temps de réponse doivent éviter la matérialisation. Par exemple, l'utilisation d'un index pour implémenter ORDER-BY entraîne un meilleur temps de réponse que l'utilisation du tri. La section ci-dessous décrit cette approche plus en détail.

Indexation des colonnes ORDER-BY / GROUP-BY / DISTINCT pour améliorer le temps de réponse

Les opérations ORDER-BY, GROUP-BY et DISTINCT sont toutes des types de tri. Le processeur de requêtes SQL Server Compact implémente le tri de deux façons différentes. Si les enregistrements sont déjà triés par un index, le processeur n'a besoin d'utiliser que l'index. Sinon, il doit utiliser une table de travail temporaire pour trier d'abord les enregistrements. Un tel type de tri préliminaire peut entraîner des délais initiaux importants sur les appareils dont la mémoire est limitée et la puissance du processeur peu élevée, et doit être évité si le temps de réponse est important.

Dans le contexte d'index de plusieurs colonnes, pour que ORDER-BY ou GROUP-BY envisage un index particulier, les colonnes ORDER-BY ou GROUP-BY doivent correspondre à l'ensemble de préfixes des colonnes d'index dans l'ordre exact. Par exemple, l'index CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) peut optimiser les requêtes suivantes :

• ... ORDER BY / GROUP BY "Last Name" ...

• ... ORDER BY / GROUP BY "Last Name", "First Name" ...

En revanche, il ne peut pas optimiser les requêtes suivantes :

• ... ORDER BY / GROUP BY "First Name" ...

• ... ORDER BY / GROUP BY "First Name", "Last Name" ...

Pour qu'une opération DISTINCT envisage un index de plusieurs colonnes, la liste de projection doit correspondre à toutes les colonnes d'index, bien qu'elles ne doivent pas être dans l'ordre exact. L'index précédent peut optimiser les requêtes suivantes :

• ... DISTINCT "Last Name", "First Name" ...

• ... DISTINCT "First Name", "Last Name" ...

En revanche, il ne peut pas optimiser les requêtes suivantes :

• ... DISTINCT "First Name" ...

• ... DISTINCT "Last Name" ...

Notes

Si votre requête renvoie toujours des lignes uniques, évitez de spécifier le mot clé DISTINCT, car il entraîne uniquement un délai supplémentaire.

Réécriture des sous-requêtes pour utiliser la clause JOIN

Vous pouvez parfois réécrire une sous-requête pour utiliser la clause JOIN et améliorer les performances. L'avantage de la création d'une clause JOIN réside dans le fait que vous pouvez évaluer des tables dans un ordre différent de celui défini par la requête. L'avantage de l'utilisation d'une sous-requête est qu'il n'est souvent pas nécessaire d'analyser toutes les lignes à partir de la sous-requête pour évaluer son expression. Par exemple, une sous-requête EXISTS peut renvoyer la valeur TRUE lorsqu'elle détecte la première ligne de qualification.

Notes

Le processeur de requêtes SQL Server Compact réécrit toujours la sous-requête IN pour utiliser la clause JOIN. Il n'est pas nécessaire d'essayer cette solution lorsque des requêtes contiennent la clause de sous-requête IN.

Par exemple, pour déterminer toutes les commandes qui contiennent au moins un article avec une remise de 25 pour cent ou supérieure, vous pouvez utiliser la sous-requête EXISTS suivante :

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

Vous pouvez également réécrire cette sous-requête pour utiliser la clause JOIN :

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

Utilisation limitée des jointures externes

Les jointures externes ne sont pas traitées de la même manière que les jointures internes, car l'optimiseur de requête ne tente pas de réorganiser l'ordre de jointure des tables de jointure externe comme il le fait pour les tables de jointure interne. La table externe (la table de gauche dans une jointure externe gauche et la table de droite dans une jointure externe droite) fait d'abord l'objet d'un accès, suivi par la table interne. Cet ordre de jointure fixe peut engendrer des plans d'exécution peu optimaux.

Pour plus d'informations sur une requête contenant INNER JOIN, consultez la Base de connaissances Microsoft.

Utilisation des requêtes paramétrables

Si votre application exécute une série de requêtes qui ne différent que par quelques constantes, vous pouvez améliorer les performances à l'aide d'une requête paramétrée. Par exemple, vous pouvez exécuter la requête suivante pour renvoyer les commandes effectuées par différents clients :

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

Les requêtes paramétrables génèrent de meilleures performances en compilant la requête qu'une seule fois et en exécutant le plan compilé plusieurs fois. Par programme, vous devez conserver l'objet de commande qui contient le plan de requête mis en cache. La destruction de l'objet de commande précédent et la création d'un nouvel objet entraînent la destruction du plan mis en cache et, par conséquent, une nouvelle compilation de la requête. Si vous devez exécuter plusieurs requêtes paramétrables de manière imbriquée, vous pouvez créer plusieurs objets de commande, chacun mettant en cache le plan d'une requête paramétrée. De cette manière, vous évitez que toutes ces requêtes soient recompilées.

Interrogation des données uniquement lorsque cela est nécessaire

Le processeur de requêtes SQL Server Compact est un outil puissant destiné à l'interrogation des données stockées dans votre base de données relationnelle. Toutefois, un coût intrinsèque est associé à tout processeur de requêtes. Il doit compiler, optimiser et générer un plan d'exécution avant de commencer à exécuter réellement le plan. Ce coût est d'autant plus lourd dans le cas de requêtes simples qui s'exécutent rapidement. Pour cette raison, les performances peuvent être parfois considérablement améliorées si vous implémentez vous-même la requête. Si chaque milliseconde compte pour votre composant principal, nous vous recommandons d'envisager cette solution. En revanche, les requêtes volumineuses et complexes sont toujours destinées au processeur de requêtes.

Supposons, par exemple, que vous souhaitiez rechercher l'ID client d'une série de commandes organisées par leur ID de commande. Vous pouvez effectuer cette recherche de deux manières différentes. Vous pouvez d'abord suivre ces étapes pour chaque recherche :

1. Ouverture de la table Orders

2. Recherche de la ligne à l'aide de l'« Order ID » (ID de commande) spécifique

3. Extraction du « Customer ID » (ID de client)

Vous pouvez également exécuter la requête suivante pour chaque recherche :

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

La solution qui repose sur la requête est plus simple mais plus lente que la solution manuelle, car le processeur de requêtes SQL Server Compact convertit l'instruction SQL déclarative en trois opérations que vous pourriez implémenter manuellement. Ces trois étapes sont ensuite effectuées l'une après l'autre. Le choix de la méthode à utiliser dépend du critère le plus important pour votre application, à savoir la simplicité ou les performances.