Comment optimiser l'utilisation de la clause "OR" lorsqu'elle est utilisée avec des paramètres (SQL Server 2008)

Question

Je me demande s'il existe un moyen judicieux de réécrire la requête suivante de manière à ce que les index sur les colonnes soient utilisés par l'optimiseur ?

CREATE PROCEDURE select_Proc1
    @Key1 int=0,
    @Key2 int=0
AS
BEGIN
    SELECT key3
    FROM Or_Table
    WHERE (@key1 = 0 OR Key1 = @Key1) AND
          (@key2 = 0 OR Key2 = @Key2)
END
GO

Selon cet article Comment optimiser l'utilisation de la clause "OR" lorsqu'elle est utilisée avec des paramètres ? par Preethiviraj Kulasingham :

Même si les colonnes du WHERE sont couvertes par des index, SQL Server n'est pas en mesure d'utiliser ces index. Cela soulève la question de savoir si quelque chose "bloque" l'utilisation des index. La réponse à cette question est oui - les coupables sont les paramètres et la clause OR état.

Les paramètres ne sont pas couverts par des index, ce qui signifie que SQL Server ne peut utiliser aucun des index pour évaluer les paramètres suivants @key1=0 (une condition qui s'applique également @key2=0 ).

En effet, cela signifie que SQL Server ne peut pas utiliser d'index pour évaluer la clause @key1=0 OR Key1= @key1 (en tant que OR est l'union des lignes couvertes par les deux conditions). Le même principe s'applique à l'autre clause (concernant la clé2). Cela conduit SQL Server à conclure qu'aucun index ne peut être utilisé pour extraire les lignes, ce qui l'amène à utiliser la meilleure approche suivante : un balayage d'index en grappe.

Comme vous le voyez, l'optimiseur SQL n'utilisera pas d'index sur les colonnes si les prédicats sont OR ée dans le cadre de la WHERE clause. Une solution à ce problème consiste à séparer les requêtes comportant une clause IF pour toutes les combinaisons possibles de paramètres.

Ma question est la suivante : que faire si les combinaisons possibles sont plus nombreuses que trois ou quatre ? La rédaction d'une requête distincte pour chaque combinaison ne semble pas être une solution rationnelle.

Existe-t-il une autre solution pour résoudre ce problème ?

Answer 1

SQL Server n'est pas très performant dans l'optimisation de la OR prédicats.

Utilisez ceci :

SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 = 0
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 = 0
        AND @key2 <> 0
        AND key2 = @key2
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key2 = 0
        AND @key1 <> 0
        AND key1 = @key1
UNION ALL
SELECT  key3
FROM    or_table
WHERE   @key1 <> 0
        AND @key2 <> 0
        AND key1 = @key1
        AND key2 = @key2

SQL Server consultera les valeurs des variables avant d'exécuter les requêtes et optimisera les requêtes redondantes.

Cela signifie qu'une seule requête sur quatre sera effectivement exécutée.

Answer 2

MSSQL 2008 dispose d'une syntaxe d'optimisation de la simplification des conditions, la voici

 Where (@key1 =0 OR Key1 =@Key1) AND
      (@key2 =0 OR Key2 =@Key2) option(recompile)

Cela permettra d'optimiser l'utilisation des constantes

Answer 3

Avez-vous essayé une fonction à valeur de tableau ?

CREATE FUNCTION select_func1 (  
    @Key1 int=0,
    @Key2 int=0
)
RETURNS TABLE 
AS RETURN (
    Select key3
    From Or_Table
    Where (@key1 =0 OR Key1 =@Key1) AND
          (@key2 =0 OR Key2 =@Key2)
)

select * from select_func1(1,2)

Answer 4

Oui - prudent l'utilisation d'un sql dynamique résoudra ce problème. Il y a deux façons de procéder :

1. Si vous êtes un "puriste" des procs stockés, composez une chaîne de requête personnalisée à l'intérieur d'un proc stocké et exécutez la chaîne. La requête spécifique peut alors être écrite dynamiquement à chaque exécution pour n'inclure que les critères pertinents.

2. Si vous êtes flexible quant à l'emplacement de ce code SQL, vous pouvez (encore une fois avec précaution) composer la chaîne de requête dans votre application et la transmettre au serveur.

   Le danger, bien sûr, réside dans l'injection SQL. Il faut donc faire très attention à la manière dont les données sont transmises par le client à l'instruction SQL dynamique.

Articles très complets d'Erland Sommarskog :

• Conditions de recherche dynamique en T-SQL
• La malédiction et les bénédictions du SQL dynamique