Business Geek

Ce petit post pour faire découvrir ou, je l'espère,  redécouvrir, une instruction T-SQL : COALESCE. La définition de cette instruction est :

Quand je parle de cette instruction que j'utilise depuis 2001, on me dit toujours : ben t'as qu'à utiliser ISNULL... Ce n'est pas faux car ISNULL fait la même chose mais uniquement entre 2 valeurs.

Récemment, je suis tombé sur ce code T-SQL, assez illisible : 

ISNULL(matable.Col1, ISNULL(maTable.col2, ISNULL(DefTable.Col1, @ValeurParDefaut))

COALESCE(matable.Col1, maTable.col2, DefTable.Col1, @ValeurParDefaut)

Voila, ça fait la même chose mais c'est plus propre et plus simple à maintenir.

Je voudrais passer un message à tous les développeurs. Dans tous vos développements, lorsque vous avez des chaînes de connexion...

utilisez la propriété "application name" !!

En effet, quand on profile une base de données pour auditer les performances ou pour recenser les utilisateurs en vue d'un migration par exemple, cette information est importante pour voir plus facilement qui fait quoi. Sinon, vous verrez apparaître dans vos traces l'application .NET SqlClient Data Provider qui est tout simplement la valeur par défaut de ADO.NET.

Pour pallier à cela, voilà à quoi doit ressembler votre chaîne de connexion :

Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI;Application Name=www.bewise.fr

Merci d'avance pour les gens qui travaillent sur la BD.

Erratum :

J'étais fatigué (retour de TechDays ??) quand j'ai écrit cet article car il y a une solution beaucoup plus simple (que je connaissais en plus). Je n'ai pas voulu écouter la petite voix dans ma tête qui me disait "abruti, abruti" quand j'ai cité la fonction Previous. Bref, la solution est :

=RunningValue(Fields!SubTotal.Value, Sum, Nothing)

Mais je vous invite à lire la solution proposée ci-dessous car elle est utile pour de aggrégats un peu custom (et puis pour dire que je n'ai pas gratté pour rien).

Article Original

Dans cet article nous allons voir comment faire une somme cumulée dans un rapport. Le principe est simple, j'ai des ventes dans le temps et pour chaque date, je veux le cumul depuis la première date.

Reporting Services n'offre pas cette fonctionnalité en natif mais nous allons mettre en place les élements pour le suporter. Sans vouloir gâcher la surprise, je peux déjà vous annoncer que l'on va passer par du code .NET ajouté au rapport.

Pour commencer, voyons ce que nous offre Reporting Services comme fonction en rapport avec notre besoin. Il y a la fonction Previous qui permet de récupérer des données dans la ligne précédente. Cependant, cela ne marche plus à partir de la 3eme ligne car la fonction ne remonte pas plus haut.

L'idée pour résoudre cette problématique est d'utiliser une variable qui va tenir le cumul depuis la première ligne. A chaque ligne, on ajoute la valeur en cours. Pour garder une variable, on doit passer par le code .NET.

Pour ce faire on passe par la fenêtre de propriétés du rapport, dans l'onglet Code.

Oubliez le confort de l'Intellisense, vous n'êtes pas dans Visual Studio, l'éditeur ressemble plutôt à Notepad. En espérant que Reporting Services 2008 apporte des améliorations dans ce sens (comme pour Integration Services). En plus, vous êtes limité uniquement à VB.NET comme langage.

Quant au code en lui-même il est assez simple. On garde un variable Shared (static) et on fournit une petite fonction pour mettre à jour et retourner le cumul.  Ici j'ai choisi le type Decimal car je souhaite retourner une somme d'argent. Rien de plus simple :

Shared cumul As Decimal = 0.0
Public Shared Function GetCumulativeSum(ByVal currentValue As Decimal) As Decimal
  cumul += currentValue
  Return cumul
End Function

Reste à utiliser ce code dans le rapport. Pour cela, on utilise une Expression dans un élément de notre DataRegion :

=Code!GetCumulativeSum(Fields!SalesAmount.Value)

Un des problèmes est que l'on ne peut pas utiliser cette technique de cumul à différents endroits. En effet, on ne repart jamais à zéro. Plutôt que de tenter de réinitialiser le cumul dans la première ligne d'une DataRegion, on préfèrera l'utilisation d'un dictionnaire dans le Custom Code. Voici le code utilisant un dictionnaire :

Shared cumuls As System.Collections.Generic.Dictionary(Of String, Decimal) = _
     New System.Collections.Generic.Dictionary(Of String, Decimal)
Public Shared Function GetCumulativeSum(ByVal currentValue As Decimal, ByVal sumKey As String) As Decimal
  If Not cumuls.ContainsKey(sumKey) Then
    cumuls.Add(sumKey, 0.0)
  End If
  cumuls.Item(sumKey) += currentValue
  Return cumuls.Item(sumKey)
End Function

Dans le rapport, on utilisera l'expression suivante :

=Code!GetCumulativeSum(Fields!SalesAmount.Value, "salesInChart")
...
=Code!GetCumulativeSum(Fields!TaxAmount.Value, "taxInMatrix")

Note : pour faire du C#, il faudra coder la fonction dans une assembly et la référencer dans le rapport.

En attendant l'utilisation massive du champ Date (juste la date) de SQL Server 2008, il faut ruser pour récupérer une date sans les heures/minutes depuis un champ DateTime.

2 méthodes s'offrent à nous :

1. Cast(DATEDIFF(day, 0, monchampDateTime) As DateTime)
2. Convert(varchar(20), monchampDateTime, 102)

Le premier est plus performant que le second. Le second renvoie un type string.

Les vues indexées sont un excellent vecteur d'optimisation et j'en suis un des ardents défenseurs !! Je les utilise depuis SQL Server 2000 pour optimiser des requêtes un peu complexes ou fréquemment utilisées. Le but de ce post n'est pas de détailler les vues indexées mais de présenter un problème qui m'est arrivé.

Lors d'une formation SQL Server, je présente comme d'habitude l'intérêt des vues indexées et on décide de faire une petite démo avec AdventureWorks. L'idée est d'optimiser la récupération des descriptions de produits. La vue est la suivante : 

Create View [dbo].[vProdDesc]
WITH SCHEMABINDING
as
select p.name, d.description
from production.product p
    inner join Production.ProductModelProductDescriptionCulture dc
        on dc.ProductModelID = p.ProductModelID
        and cultureid = 'fr'
    inner join production.productdescription d
        on d.ProductDescriptionID = dc.ProductDescriptionID

Je pose un index UNIQUE et CLUSTERED sur la vue et pour montrer l'utilisation de l'index j'exécute un simple SELECT sur la vue :

Select * From dbo.vProdDesc

Le plan d'exécution généré montre clairement que l'index n'est pas utilisé. J'utilise donc tous les grigris SQL en ma possession : DBCC FREEPROCCACHE, sp_recompile, table hint, mais rien n'y fait.

Un petit tour dans l'aide pour voir si je n'ai pas zappé quelquechose et je tombe sur une phrase annodine :

Indexed views can be created in any edition of SQL Server 2005. In SQL Server 2005 Enterprise Edition, the query optimizer automatically considers the indexed view. To use an indexed view in all other editions, the NOEXPAND table hint must be used.

Je n'ai pourtant jamais utiliser ce hint pour activer des vues indexées. Et pourtant, en l'ajoutant à ma requête, le plan d'exécution est correct et tire bénéfice de l'index.

Select * From dbo.vProdDesc WITH (NOEXPAND)

Un coup d'oeil sur la version :

Select @@version

Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Developer Edition on Windows NT 6.0 (Build 6000: )

Conclusion : l'édition Developer est bien bridée par rapport à la version Enterprise. Et l'effet de bord à cela c'est que l'on ne peut pas utiliser de vue indexées dites "techniques" (ie. utilisées implicitement par l'optimiseur de requêtes) car on ne peut pas préciser l'option NOEXPAND si la vue n'est pas utilisée directement.

Ce que je ne comprends pas c'est qu'il me semblait toujours avoir développé sur des Developer Edition or je n'ai jamais rencontré cet effet de bord. Donc je commence à croire que c'était des Enterprise... Ah le luxe, quand tu nous tiens...