Matthieu MEZIL

4 en 1 : EF n’a pas vocation à mettre les DBA au chômage, Velocity, SQL CLR et SQL Dependency

Imaginons une base avec une table Cars avec en PK un Id (nvarchar(8)) basé sur le Regex [1-9][0-9]{1,2}[A-Z]{2,3}[0-9]{2} où les deux derniers chiffres correspondent à un numéro de région.

Dans la base l’incrémentation est faite de la manière suivante : 10AA[région], 11AA[région], …, 999AA[région], 10AB[région], …, 999ZZ[région], 10AAA[région], …

Cette table peut contenir des centaines de millions de lignes.

Dans un premier temps, nous allons écrire une requête LINQ To Entities permettant de connaître les régions étant passés sur trois lettres :

var q1 = (from c in context.Cars
          where c.Id.Contains("AAA")
          select c.Id.Substring(c.Id.Length - 2)).Distinct();

Cette requête va nous générer la requête SQL suivante :

SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT

Naturellement, j’aurais plutôt écrit cela :

SELECT DISTINCT
      RIGHT(Id, 2) AS [C1]
      FROM [Cars]
      WHERE [Id] LIKE N'%AAA%'

Mais le plan d’exécution est le même !

Imaginons que le DBA impose l’utilisation du RIGHT car il suit les requêtes exécutées avec le profiler et qu’il veut se simplifier la vie. Comme il ne faut jamais contrarié un DBA , nous allons donc le faire.

Cependant, le Right n’existe pas en C#. Par contre, il existe en ESQL. Pour cela, nous allons donc utiliser une nouveauté de EF4 : les CSDL Functions.

Dans le CSDL, vous pouvez rajouter le code suivant :

<Function Name="GetRegion" ReturnType="String">
  <Parameter Name="car" Type="Self.Car" />
  <DefiningExpression>
    Right(car.Id, 2)
  </DefiningExpression>
</Function>

Ensuite, nous allons rajouter une extension method afin de pouvoir utiliser cette CSDL Function dans nos requêtes LINQ To Entities :

public static class CarExtension
{
    [EdmFunction("CarsModel", "GetRegion")] 
    public static string GetRegion(this Car car)
    {
        throw new NotImplementedException("Only used by LINQ To Entities");
    }
}

Je peux maintenant écrire ma requête LINQ comme ceci :

Cette requête sera alors traduire par la requête TSQL suivante :

SELECT
[Distinct1].[C1] AS [C1]
FROM ( SELECT DISTINCT
      RIGHT([Extent1].[Id], 2) AS [C1]
      FROM [dbo].[Cars] AS [Extent1]
      WHERE [Extent1].[Id] LIKE N'%AAA%'
)  AS [Distinct1]

Cool !

Maintenant l’idée est de récupérer le dernier id par région. A ma connaissance, il n’est pas possible de faire cela proprement en SQL. En effet, je ne pense pas que SQL Server gère les expressions régulières. Or on a besoin de cette fonctionnalité pour déterminer quel est le dernier id.

Aussi pour faire ceci, je vais utiliser une requête LINQ To Object. L’idée “naturelle” serait d’écrire quelque chose comme ça :

Cependant, dans ce cas, il faut s’attendre à un OutOfMemoryException. En effet, avec cette requête, tous les enregistrements de la base seront chargés en mémoire et on peut avoir des centaines de millions de lignes en base.

Aussi, l’idée dans un premier temps va être de procéder par dichotomie sans charger en mémoire des ids inutiles.

var ids = (from c in context.Cars
           select c.GetRegion()).Distinct().AsEnumerable().ToDictionary(region => region, region => GetLastId(region, "", (new[] { "" }.Union(Enumerable.Range('A', 26).Select(i => char.ConvertFromUtf32(i)))), 0));

private static string GetLastId(string region, string letters, IEnumerable<string> possibleChars, int index)
{
    if (!possibleChars.Skip(1).Any()) // Count() == 1
    {
        letters += possibleChars.First();
        if (++index == 3)
        {
            using (var context = new CarsContainer())
            {
                return
                    (from c in context.Cars
                     where c.Id.EndsWith(letters + region)
                     orderby c.Id.IndexOf(letters) descending, c.Id descending
                     select c.Id).FirstOrDefault();
            }
        }
        return GetLastId(region, letters, Enumerable.Range('A', 26).Select(i => char.ConvertFromUtf32(i)), index);
    }
    else
    {
        var possibleCharsList = possibleChars.ToList();
        int middle = possibleCharsList.Count / 2;
        using (var context = new CarsContainer())
        {
            string idBeginning = string.Format("10{0}{1}{2}", letters, possibleCharsList[middle], "AA".Substring(index));
            if ((from c in context.Cars
                 where c.Id.StartsWith(idBeginning) && c.Id.EndsWith(region)
                 select c.Id).Any())
                return GetLastId(region, letters, possibleCharsList.Skip(middle), index);
            return GetLastId(region, letters, possibleCharsList.Take(middle), index);
        }
    }
}

L’exécution de cette requête est particulièrement longue (26 minutes et 18 secondes dans mon test avec 562 012 347 enregistrements).

Quelles sont les pistes d’améliorations ?

Le CPU utilisé par la base est à 100% et la lenteur provient uniquement de la base. Il est donc inutile d’envisager la parallélisassions du code.

On pourrait envisager de redimensionner le serveur de base de données mais ce n’est pas le but de ce post.

Dans notre cas, le DBA (je précise que je ne suis pas un DBA) va sûrement vouloir changer le schéma de la table Cars afin de séparer en 3 colonnes l’Id. Cependant, dans certains cas, il peut être intéressant de conserver la colonne Id qui restera, dans ce cas, la PK. En effet, imaginons que l’on ait déjà des applications existantes. Il faudrait pouvoir rajouter ces colonnes sans les impacter et tout en faisant en sorte que quand ces applications existantes rajoutent un enregistrement, cela renseigne ces colonnes de façon transparente.

On va donc se retrouver avec quatre colonnes (toutes not nullable) :

• Id
• Number
• Letters
• Region

Pour les renseigner, on va utiliser des fonctions CLR :

public class CarsFunction
{
    [SqlFunction]
    public static SqlInt16 GetNumber(SqlString id)
    {
        return new SqlInt16(short.Parse(Regex.Match(id.Value, "^[0-9]{2,3}").Value));
    } 

    [SqlFunction]
    public static SqlString GetLetters(SqlString id)
    {
        return new SqlString(Regex.Match(id.Value, "[A-Z]{2,3}").Value);
    } 

    [SqlFunction]
    public static SqlInt16 GetRegion(SqlString id)
    {
        return new SqlInt16(short.Parse(Regex.Match(id.Value, "[0-9]{2}$").Value));
    }
}

Ensuite, dans la base, on va enregistrer l’assembly et les fonctions :

CREATE ASSEMBLY CarsFunctionsAssembly
FROM 'D:\documents\visual studio 2010\Projects\CarsFunctions\CarsFunctions\bin\Debug\CarsFunctions.dll'
GO   

CREATE FUNCTION GetNumber(@id AS nvarchar(8)) RETURNS smallint AS EXTERNAL NAME CarsFunctionsAssembly.CarsFunction.GetNumber
GO

CREATE FUNCTION GetLetters(@id AS nvarchar(8)) RETURNS nvarchar(3) AS EXTERNAL NAME CarsFunctionsAssembly.CarsFunction.GetLetters
GO

CREATE FUNCTION GetRegion(@id AS nvarchar(8)) RETURNS smallint AS EXTERNAL NAME CarsFunctionsAssembly.CarsFunction.GetRegion

Maintenant que nous avons nos fonctions, nous allons revenir sur la création de nos trois colonnes. A la place de créer des colonnes “normales”, nous allons en faire des colonnes calculées. Ceci peut se faire avec le designer SSMS en renseignant la Formule de Computed Column Specification à dbo.GetNumber(Id) (resp dbo.GetLetters(Id), dbo.GetRegion(Id)). Dans notre cas, l’idée n’est pas de recalculer à chaque fois ces valeurs mais de les stocker une bonne fois pour toute. Du coup, on va passer la propriété “Is Persisted” à true.

Le problème c’est que pour cela, il faut que la fonction soit déterministe. C’est le cas de notre fonction. En effet, elle retourne toujours la même valeur pour le même Id. Cependant, vu que c’est une fonction CLR, SQL Server ne peut pas le savoir. Il va donc falloir le préciser explicitement.

Pour cela, nous allons modifier notre code comme ceci :

[SqlFunction(IsDeterministic = true, IsPrecise = true)]
public static SqlInt16 GetNumber(SqlString id)
{
    return new SqlInt16(short.Parse(Regex.Match(id.Value, "^[0-9]{2,3}").Value));
}

Maintenant nous pouvons persister ces colonnes.

Attention, si on importe la base dans l’edmx, il va définir StoreGeneratedPattern="Computed" sur les les colonnes Number, Letters et Region. Cela implique donc qu’à chaque update, il va recharger les propriétés Number, Letters et Region. Dans notre cas, nous savons que le calcul ne dépend que de la clé. Avec Entity Framework, il n’est pas possible de modifier la clé. Par conséquent, il est intéressant de modifier le StoreGeneratedPattern de Computed à Identity.

Cool, nous avons donc régler le problème de l’INSERT.

Concentrons nous maintenant sur ce qui nous intéresse : notre requête.

Plus besoin d’utiliser une recherche dichotomique, une simple requête suffit :

var ids = (from c in context.Cars
           group c by c.Region into g
           select new
           {
               Region = g.Key,
               LastId = (from c in g
                         orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                         select c.Id).FirstOrDefault() 
           }).AsEnumerable().ToDictionary(region => region.Region, region => region.LastId);

Nous venons de passer de 26 minutes et 18 secondes à 1 minute et 42 secondes !

C’est mieux mais ce n’est pas encore fini.

Au niveau de la base, nous allons maintenant passer le cluster de la table Cars sur la colonne Region (à la place de la colonne Id).

Dans ce cas, le temps d’exécution de ma requête tombe à 12 secondes ! (plus de 26 minutes à 12 secondes, dans ces cas-là, le client ne regrette généralement pas d’avoir pris des journées de conseil ).

Attention cependant ! Ceci est certes très intéressant dans le cas présent mais il faudra bien étudier l’ensemble des requêtes des différentes applications utilisant la base avant de prendre la décision de changer le cluster.

Nous venons de démontrer que le DBA reste un rôle important dans la réussite d’un projet.

Comme je vous l’ai dit, je ne suis pas DBA. Cependant, mon expérience m’a permis d’acquérir quelques notions que j’ai mis en place ici. De plus, j’ai la chance d’avoir quelques DBA dans mes contacts . J’en profite d’ailleurs pour les remercier pour m’avoir aider à acquérir ces notions.

Maintenant, imaginons que l’on veuille garder en cache le dernier id par région. Pour cela, nous allons peut-être passer notre dictionnaire ids dans une variable statique. Très bien mais il reste deux problèmes à traiter :

• Imaginons une application N-Tiers. Si notre application est déployée sur plusieurs serveurs frontaux, il est dommage de devoir calculer ce cache pour chacun d’eux.
• Si une application externe rajoute un enregistrement en base, comment tenir notre dictionnaire à jour ?

Pour résoudre le premier problème, nous allons utiliser Velocity. Pour le second, nous utiliserons SQLDependency.

Velocity est un cache distribué permettant beaucoup de choses très intéressantes comme le load balancing par exemple. Ce cache distribué sera partagé par l’ensemble de nos serveurs frontaux. Cela signifie donc qu’il ne sera plus utile de générer un Dictionnaire de cache par frontal.

Que devons nous faire pour utiliser Velocity ? Premier point : le télécharger et l’installer

Une fois installé, il va falloir ajouter les références suivantes dans notre projet :

• CacheBaseLibrary.dll
• CASBase.dll
• CASMain.dll
• ClientLibrary.dll
• FabricCommon.dll

Ensuite, il va falloir démarrer le cluster Velocity. Dans cette CTP, toutes les commandes d’administration se font par la console “Administration Tool - Microsoft Distributed Cache” en ligne de commande. Pour démarrer votre cluster, il faut taper la ligne de commande suivante :

start-cachecluster

Ensuite, dans le fichier de config, on va rajouter les lignes qui vont bien :

<configSections>
  <section name="dataCacheClient" type="Microsoft.Data.Caching.DataCacheClientSection, CacheBaseLibrary" allowLocation="true" allowDefinition="Everywhere"/>
</configSections>
<dataCacheClient deployment="routing">
  <localCache isEnabled="true" sync="TTLBased" ttlValue="60000"/>
  <hosts>
    <host name="MATTHIEU-PRO" cachePort="22233" cacheHostName="DistributedCacheService"/>
    <host name="MATTHIEU-LAPTOP1" cachePort="22233" cacheHostName="DistributedCacheService"/>
    <host name="MATTHIEU-LAPTOP2" cachePort="22233" cacheHostName="DistributedCacheService"/>
    <host name="MATTHIEU-PC1" cachePort="22233" cacheHostName="DistributedCacheService"/>
    <host name="MATTHIEU-PC2" cachePort="22233" cacheHostName="DistributedCacheService"/>
  </hosts>
</dataCacheClient>

Ensuite, il suffit dans notre application (sur le Tiers serveur) de requêter le cache.

return new DataCacheFactory().GetDefaultCache().GetObjectsInRegion("LastImmatPerRegion").ToDictionary(keyValuePair => short.Parse(keyValuePair.Key), keyValuePair => (string)keyValuePair.Value);

Avec ces données en cache, notre requête s’exécute en seulement une trentaine de milli-secondes.

Maintenant, il nous reste un dernier point : initialiser ce cache et le maintenir à jour.

Pour le renseigner, c’est facile, il suffit de se baser sur la requête écrite plus haut :

_cache = new DataCacheFactory().GetDefaultCache();
try
{
    _cache.RemoveRegion(LAST_IMMAT_PER_REGION);
}
catch
{
}
_cache.CreateRegion(LAST_IMMAT_PER_REGION, false);
foreach (var car in from c in context.Cars
                    group c by c.Region into g
                    select new
                    {
                        Region = g.Key,
                        LastId = (from c in g
                                  orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                                  select c.Id).FirstOrDefault()
                    })
    _cache.Put(car.Region.ToString(), car.LastId, LAST_IMMAT_PER_REGION);

Maintenant pour suivre les évolutions, c’est une autre histoire. Mon idée est d’utiliser une SQLDependency. Le problème c’est qu’on ne peut pas, à priori, récupérer les modifications apportées avec les SQLDependency. Aussi mon idée est de rajouter un Trigger sur ma table Cars qui renseignera une table temporaire.

Pour commencer, nous allons créer la table CarsModificationsTmp. Elle comprendra six colonnes :

• TmpId (int Identity(1,1), PK),
• CarId (nvarchar(8), not nullable)
• Number (smallint, not nullable)
• Letters (nvarchar(3), not nullable)
• Region (smallint, not nullable),
• Deleted (bit, not nullable, default=0)

Ensuite, il faut définir les triggers sur la table Cars :

CREATE TRIGGER CarsInserted
ON Cars
FOR INSERT
AS
BEGIN
  DECLARE @Id AS nvarchar(8)
  DECLARE @Number AS smallint
  DECLARE @Letters AS nvarchar(3)
  DECLARE @Region AS smallint
  SELECT @Id = Id, @Number = Number, @Letters = Letters, @REGION = Region FROM Inserted
  INSERT INTO CarsModificationsTmp(CarId, Number, Letters, Region) VALUES(@Id, @Number, @Letters, @Region)
END
GO
 
CREATE TRIGGER CarsDeleted
ON Cars
FOR DELETE
AS
BEGIN
  DECLARE @Region AS smallint
  DECLARE @Id AS nvarchar(8)
  DECLARE @Number AS smallint
  DECLARE @Letters AS nvarchar(3)
  SELECT @Id = Id, @Number = Number, @Letters = Letters, @REGION = Region FROM Deleted
  INSERT INTO CarsModificationsTmp(CarId, Number, Letters, Region, Deleted) VALUES(@Id, @Number, @Letters, @Region, 1)
END

Enfin, nous allons compléter le renseignement du cache en gérant notre SQL Dependency.

private void Load()
{
    using (var context = new CarsContainer())
    {
        _cache = new DataCacheFactory().GetDefaultCache();
        try
        {
            _cache.RemoveRegion(LAST_IMMAT_PER_REGION);
        }
        catch
        {
        }
        _cache.CreateRegion(LAST_IMMAT_PER_REGION, false);
        foreach (var car in from c in context.Cars
                            group c by c.Region into g
                            select new
                            {
                                Region = g.Key,
                                LastId = (from c in g
                                          orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                                          select c.Id).FirstOrDefault()
                            })
            _cache.Put(car.Region.ToString(), car.LastId, LAST_IMMAT_PER_REGION);

Le problème c’est que l’appel à SqlDependency_OnChange est asynchrone et il peut y avoir plusieurs appels en parallèle avec les problèmes que cela pose. Le DataCache de Velocity est thread-safe. Cependant, avec le code précédent, la concurrence ne nous permet pas d’affimer que l’on a le dernier id dans le cache. Afin de ne pas avoir de bugs liés au parallélisme, nous allons poser un lock.

if (cm.Deleted)
{
    lock (_lockObject)
    {
        if (cacheItem != null && cacheItem.Value.ToString() == cm.CarId)
        {
            var newLastId = (from c in contextModifications.Cars
                             where c.Region == cm.Region
                             orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                             select c.Id).FirstOrDefault();
            if (newLastId == null)
                _cache.Remove(cm.Region.ToString(), LAST_IMMAT_PER_REGION);
            else
                _cache.Put(cm.Region.ToString(), newLastId, LAST_IMMAT_PER_REGION);
        }
    }
}
else
{
    string letters;
    int lettersCompare = 0;
    lock (_lockObject)
    {
        if (cacheItem == null || (letters = Regex.Match(cacheItem.Value.ToString(), "[A-Z]{2,3}").Value).Length < cm.Letters.Length || (letters.Length == cm.Letters.Length && ((lettersCompare = String.Compare(letters, cm.Letters)) < 0 || lettersCompare == 0 && short.Parse(Regex.Match(cacheItem.Value.ToString(), "^[0-9]{2,3}").Value) < cm.Number)))
            _cache.Put(cm.Region.ToString(), cm.CarId, LAST_IMMAT_PER_REGION);
    }
}

Il peut encore y avoir un problème lors du SaveChanges. En effet, avec la concurrence, on peut essayer de supprimer un DataRow déjà supprimé ce qui engendrera une OptimisticConcurrencyException. L’approche que nous allons adopter est la suivante. Si le SaveChanges lève cette exception, on va détacher toutes les entités qui posent problème (ie : qui ont déjà été supprimées).

if (any)
    for (; ; )
        try
        {
            contextModifications.SaveChanges();
            break;
        }
        catch (OptimisticConcurrencyException ex)
        {
            foreach (var ose in ex.StateEntries)
                contextModifications.Detach(ose.Entity);
        }

Notre solution est maintenant thread-safe et fonctionne sans problème. Cependant, nous pouvons apporter une amélioration. Si on a deux notifications de changement en parallèle qui ne portent pas sur la même région, il est inutile et dommage de devoir attendre que la première soit finie pour traiter la seconde (ce que nous faisons avec notre lock unique). Aussi, nous allons utiliser un dictionnaire de lock. Cependant, un dictionnaire n’est pas thread-safe ! Il faudrait utiliser un autre object pour locker le dictionnaire. Avec .NET 4, on a une nouvelle classe : ConcurrentDictionary :

private ConcurrentDictionary<short, object> _concurrentDictionary = new ConcurrentDictionary<short,object>();

Maintenant, nous allons modifier notre code pour utiliser notre dictionnaire afin d’avoir un lock par région :

object lockRegion = _concurrentDictionary.GetOrAdd(cm.Region, new object());
if (cm.Deleted)
{
    lock (lockRegion)
    {
        if (cacheItem != null && cacheItem.Value.ToString() == cm.CarId)
        {
            var newLastId = (from c in contextModifications.Cars
                             where c.Region == cm.Region
                             orderby c.Letters.Length descending, c.Letters descending, c.Number descending
                             select c.Id).FirstOrDefault();
            if (newLastId == null)
                _cache.Remove(cm.Region.ToString(), LAST_IMMAT_PER_REGION);
            else
                _cache.Put(cm.Region.ToString(), newLastId, LAST_IMMAT_PER_REGION);
        }
    }
}
else
{
    string letters;
    int lettersCompare = 0;
    lock (lockRegion)
    {
        if (cacheItem == null || (letters = Regex.Match(cacheItem.Value.ToString(), "[A-Z]{2,3}").Value).Length < cm.Letters.Length || (letters.Length == cm.Letters.Length && ((lettersCompare = String.Compare(letters, cm.Letters)) < 0 || lettersCompare == 0 && short.Parse(Regex.Match(cacheItem.Value.ToString(), "^[0-9]{2,3}").Value) < cm.Number)))
            _cache.Put(cm.Region.ToString(), cm.CarId, LAST_IMMAT_PER_REGION);
    }

Voilà, notre travail est (enfin) terminé.

Je pense qu’il illustre assez bien le fait qu’il est possible de faire beaucoup d’optimisations à condition, comme souvent voire comme toujours, de suffisamment maîtriser les différentes technos disponibles.

Publié vendredi 20 novembre 2009 10:16 par Matthieu MEZIL