Accueil > .Net, C#, Développement > [C#-TSQL] FORCESEEK et Entity Framework

[C#-TSQL] FORCESEEK et Entity Framework

Ce billet fait suite à la petite série sur l’optimisation.
La situation est expliquée ici Optimisations C#, Entity Framework et Sql, donc je n’y reviendrais pas.

Ce présent billet va traiter du hint FORCESEEK sur SQL Server et comment l’utiliser avec Entity Framework.

Commençons donc par le début : la récupération de la donnée de référence.
J’ai besoin de une ligne parmi les 75 millions de la table. Tous les critères ne sont pas indexés ou je ne peux pas exploiter les index (cas d’index sur plusieurs colonnes).

Du coup, ma requête SQL doit ressembler à ça :

SELECT TOP 1 * 
FROM MATABLE 
WHERE COLONNE1 = 'PARAM1' 
AND COLONNE2 = 'PARAM2' 
AND COLONNE3 = 'PARAM3'

Dans la pratique, COLONNE1 et COLONNE2 sont indexés, COLONNE3 fait partis d’un index sur plusieurs colonnes, je ne peux donc pas l’exploiter.
Enfin, PARAM1 correspond à un paramètre dynamique (par exemple : le client sur lequel je fais le test) tandis que les deux autres sont figés.

Ma requête sur EF correspond à ceci :

var reference = context.MATABLE.FirstOrDefault(a => a.COLONNE1 == param1
                && "PARAM2".Equals(a.COLONNE2, StringComparison.InvariantCultureIgnoreCase)
                && "PARAM3".Equals(a.COLONNE3, StringComparison.InvariantCultureIgnoreCase));

Le SQL généré sera donc (obtenue via le SQL Server Profiler) :

exec sp_executesql
N'SELECT TOP (1) 
   -- liste de toutes les colonnes
   FROM [dbo].[MATABLE] AS [Extent1]
   WHERE ([Extent1].[COLONNE1] = @p__linq__0) 
   AND (''PARAM2'' = [Extent1].[COLONNE2]) 
   AND (''PARAM3'' = [Extent1].[COLONNE3])',
N'@p__linq__0 varchar(8000)',
@p__linq__0='PARAM1'

Et là, c’est le drame, la requête met une bonne minute pour s’exécuter avec un plan d’exécution qui fait un Clustered Index Scan :
Clustered Index Scan

Résultat : c’est pas bien.
Et là, du fond des bois, le FORCESEEK vient à la rescousse.

Pour l’utiliser avec EF, c’est pas très compliqué :

const string sql = 
   "SELECT TOP 1 * FROM MATABLE WITH (FORCESEEK) WHERE COLONNE1 = @PARAM1 AND COLONNE2 = 'PARAM2' AND COLONNE3 = 'PARAM3'";
var reference = context.ExecuteStoreQuery<MATABLE>(
   sql, 
   new SqlParameter { ParameterName = "PARAM1", Value = "ma_valeur" });

C’est déjà un peu moins sympa que du Linq normal, faut connaître (un peu) le T-SQL.

La requête SQL exécutée va donc être :

exec sp_executesql 
N'SELECT TOP 1 * FROM MATABLE WITH (FORCESEEK) WHERE COLONNE1 = ''PARAM1'' AND COLONNE2 = ''PARAM2'' AND COLONNE3 = ''PARAM3''',
N'@COLONNE1 nvarchar(7)',@COLONNE1=N'PARAM1'

Ici, une petite remarque : le PARAM1 est déjà substitué dans la requête par sa valeur réelle.

Le plan d’exécution devient donc :
Index Seek
Mais c’est surtout le temps d’exécution qui est sympa : il reste figé à 00:00:00 !

A noter aussi qu’il est très bien possible de ne pas utiliser le sp_executesql.
Mais là, c’est d’un point de vue sécurité que c’est moins bien : le sp_executesql permet de limiter l’injection SQL.
Ceci dit, le problème (dans mon cas) est moindre puisque PARAM1 correspond à une clef primaire technique.

Ainsi, on pourra procéder comme ceci :

var sql = string.Format("SELECT TOP 1 * FROM MATABLE WITH (FORCESEEK) WHERE COLONNE1 = '{0}' AND COLONNE2 = 'PARAM2' AND COLONNE3 = 'PARAM3'", PARAM1);
var reference = context.ExecuteStoreQuery<MATABLE>(sql).FirstOrDefault();

Ici, la requête exécutée sera directement :

SELECT TOP 1 * 
FROM MATABLE 
WITH (FORCESEEK) 
WHERE COLONNE1 = 'PARAM1' 
AND COLONNE2 = 'PARAM2' 
AND COLONNE3 = 'PARAM3'

Après, il est quand même important de noter que les hints ne doivent être utilisés qu’en dernier recours : SQL Server fait sa petite tambouille interne tout seul, normalement et l’utilisation des hints permet de passer outre.
Grosso modo, ça revient à lui dire comment faire son job (le genre de chose qui peut agacer un dev), alors à utiliser avec modération !

Catégories :.Net, C#, Développement
  1. Aucun commentaire pour l’instant.
  1. 29/01/2014 à 20:36

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s

%d blogueurs aiment cette page :