Archive pour janvier 2008

Vues indexées et optimisation

29 janvier 2008

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…

Requêter un cube Analysis Services depuis SSIS

28 janvier 2008

On pourrait penser que requêter un cube OLAP avec Integration Services est trivial et pourtant, avec un collègue, nous nous sommes heurtés à quelques difficultés.

Première tentative :

Naïvement, nous créons une connexion de type OLE DB Provider for Analysis Services 9.0. On la branche sur un OLE DB Source et en guise de SQL Command, je tape ma requête MDX (les ventes par catégories depuis le DataWarehouse de AdventureWorks) :

SELECT NON EMPTY { [Measures].[Sales Amount] } ON COLUMNS,NON EMPTY { ([Dim Product].[Dim Product Category].[Dim Product Category].ALLMEMBERS ) } ON ROWS FROM [AdventureWorks]

A la confirmation, l’avertissement suivant apparaît :

[OLE DB Source [1]] Warning: The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

Soit, qu’il en soit ainsi, un WSTR me plait bien, de plus que la preview me montre bien le résultat attendu. Je termine mon package en dirigeant le flux vers un bête FlatFile.

A l’exécution, le warning réapparaît mais plus grave, le package plante violemment(lançant SQLDumper.Exe au passage).

La solution :

Il faut en fait passer par .NET et utiliser les providers “managed“. On crée donc une ADO.NET Connection via un clic droit dans la zone des Connection Managers. Dans la liste des .NET Providers for OleDb, on choisit Analysis Services 9.0 (oui, le même mais en managé) et on le configure pour pointer sur notre cube.

Dans le Data Flow Task, on glisse un DataReader Source auquel on branche la connexion créée ci-dessus. Pas d’avertissement, on continue en branchant la sortie vers le fichier, on teste et ça marche immédiatement. Vous aurez peut-être juste besoin d’une conversion de type si vous n’êtes pas en UNICODE mais rien de difficile là-dedans (Data Conversion Transformation).

Merci à Julien sans qui je n’aurai jamais publié cet article (avant un moment).

 

PS : désolé, toutes les instructions sont en anglais car j’utilise une version US des produits, ce que je vous conseille même si vous êtes fan de la loi Toubon.

Affichage d’un tableau vide dans Reporting Services

28 janvier 2008

Dans les newsgroups (US), de nombreuses personnes demandent comment gérer l’absence de données dans un tableau (ou un autre type de Data Region). Voici un petit condensé des solutions techniques…

Utilisation de NoRows

C’est la méthode la plus simple, si le DataSet lié à une DataRegion de type Table est vide, il est possible d’afficher un texte à la place du tableau grâce à la propriété NoRows. Le problème est que l’on ne peut pas modifier la mise en forme du texte et que rien d’autre ne s’affiche (pas d’entêtes de colonne). Aussi, si votre tableau mesure plusieurs centimètres de haut, l’espace reste réservé au rendu, laissant souvent un espace blanc et vide.

Utilisation d’une ligne cachée

L’idée ici est de garder les entêtes de colonne mais d’ajouter le texte explicatif d’absence de données. On ajoute une ligne de Header dans la Table. On écrit le texte dedans (avec une fusion de cellules au besoin). Puis on met une expression dans la propriété Visibility afin d’afficher la ligne uniquement s’il n’y a pas de ligne dans le DataSet:

=(CountRows()>0)

Attention, on doit retourner false si on veut cacher l’élément; il faut donc réfléchir à l’envers… Cette solution ne règle toujours pas le problème de l’espace vide avant l’élément suivant.

Utilisation d’une TextBox

On utilise le principe de la propriété Visibility pour cacher entièrement la Table si elle est vide Visibility.Hidden=(CountRows()=0). On place une TextBox contenant le message au-dessus de la Table et on lui applique une expression similaire dans sa propriété Visibility pour l’afficher à la bonne condition. La Table étant cachée, il n’y a pas d’espace utilisé et inversement.

 

PS : je n’ai pas réglé le cas où on souhaite afficher les entêtes sans l’espace vide. La propriété Visible n’est pas prise en compte sur une ligne de Table, il en est de même avec la propriété CanShrink. La solution que je vois est de recréer les entêtes dans un rectangle et d’utiliser la technique de la TextBox présentée ci-dessus.

 

CREATE APPLICATION ROLE

24 janvier 2008

Tout le monde connaît l’utilisation des rôles dans la sécurité des applications. On n’y échappe pas avec SQL Server puisque l’on peut définir  (CREATE ROLE) et affecter (sp_addrolemember) des rôles au sein de sa base de données. On appelle ces rôles des : databases-roles.

SQL Server 2005 offre un autre type de rôle qui sont les : application-roles. La philosophie de ces rôles est de donner des droits à une application plutôt qu’aux utilisateurs en la mettant dans un rôle particulier. J’ai longtemps tourné autour de cette fonctionnalité, n’arrivant pas à me convaincre de son utilité. En effet, dans 99% des applications que j’approche, l’application se connecte avec sa propre identité. Donc, au niveau sécurité, je peux la configurer finement, indépendamment de ses utilisateurs (qui eux peuvent être db_owner, sysadmin ou de parfaits inconnus dans un autre contexte). En conséquence, j’avais écarté cette technique de ma boîte à outils.

C’était sans compter sur mes stagiaires du moment qui ont voulu s’en convaincre et nous avons repris la problématique du début. Donc reprenons :

Premièrement, créons un rôle d’application

CREATE APPLICATION ROLE app1 WITH PASSWORD = 'pass31', default_schema = Person

Nous parlons bien d’un rôle donc il est impossible de se connecter avec. Il nous faut donc des logins classiques. Vous pouvez garder l’authentification intégrée et autoriser les login windows ou bien en créer ou en réutiliser.

CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS --tous les utilisateurs windows
CREATE LOGIN appUser WITH PASSWORD = 'appUser' --utilisateur spécifique

La phase suivante que l’on a classiquement est de créer un utilisateur dans la base de données et de la mapper sur un login. Cette phase est maintenant inutile car on va faire en sorte que l’utilisateur soit vu comme l’application par la base de données. Cela se fait via l’éxecution d’une procédure système qui va faire du context switching.

EXEC sp_setapprole 'app1', 'pass31'

Maintenant, tout le code T-SQL est exécuté avec les droits du rôle d’application. Pour vérifier, on peut appeler la fonction USER_NAME(). Pour revenir au contexe d’éxécution précédent, il suffit de fermer la connexion ou d’appeler la procédure système sp_unsetapprole (par contre, il faudra conserver un token fourni par sp_setapprole et le redonner à sp_unsetapprole).

Il ne reste plus qu’à intégrer cette technique dans une application. Pour cela, on communique avec la base de données de façon classique sauf qu’en début de connexion, on doit faire le changement de contexte comme présenté dans le code ci-dessous. A noter qu’avec une architecture correcte (ie. une vraie couche d’accès aux données), on masque aisément cette tuyauterie technique.

using (SqlConnection cnn = new SqlConnection(
    string.Format("data source=(local);initial catalog=Lab;user id={0};password={1}", txtLogin.Text, txtPassword.Text)))
{
  cnn.Open();

  SqlCommand cmd = cnn.CreateCommand();
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.CommandText = "sp_setapprole";
  cmd.Parameters.AddWithValue("@rolename", txtAppRole.Text);
  cmd.Parameters.AddWithValue("@password", "pass31");
  cmd.ExecuteNonQuery();

  SqlDataAdapter adpater = new SqlDataAdapter("select top 50 firstname, lastname, emailaddress from person.contact", cnn);
  adpater.Fill(contactsDataSet);
  cnn.Close();
}

Ici, c’est bien un utilisateur autorisé sur le serveur qui se connecte mais les droits sont ceux de l’application. L’énorme avantage que je vois (mais comment ai-je pu passer à côté) est le fait que si l’application tombe entre de mauvaises mains, sans l’étape Login, elle ne pourra pas atteindre le niveau base de données, même si le rôle d’application a tous les droits.
Aussi, si je récupère un login/mot de passe, je peux juste me connecter au serveur mais je ne peux pas accéder aux bases. Inversement avec le rôle, avec lequel on ne peut pas se connecter.

Passons quand même en revue les inconvénients. Le principal (et le seul) est que je base toute l’authentification de mon application sur SQL Server et donc je dois créer des login pour tous les utilisateurs. Bien sûr, avec un peu d’architecture, on doit pouvoir rendre cela plus souple et élégant.

Conclusion, grâce aux rôles d’application et au context switching, on découple fortement la paire login/user et on rationnalise la gestion de la sécurité des applications dans la base de données.

Merci Mathieu d’avoir insisté ;-)

Hiérarchies non-naturelles avec Analysis Services

22 janvier 2008

Ce webcast traite des hiérarchies dans les dimensions sous Analysis Services. On aborde la cas particulier des hiérarchies non-naturelles avec un exemple trivial d’une dimension Année-Mois-Semaine-Jour. Cette hiérarchie a la spécificité d’avoir une relation many-to-many entre 2 niveaux (mois-semaine). Voici une solution pour traiter le cas sans créer d’erreur de calcul dans le cube l’utilisant.

Hiérarchies non-naturelles avec Analysis Services
Hiérarchies non-naturelles avec Analysis Services

TechDays 2008

20 janvier 2008

L’annonce des TechDays 2008 n’a pas dû vous échapper étant donné que c’est relayé sur la toile depuis des mois. J’en remet une louche en faisant un peu de pub pour la session que je co-anime avec Sébastien Pertus (un autre Bewisien dont je vous recommande le blog) :

Gestion des textes et des images avec SQL Server 2005 et 2008 : XML, FTS, FileStream, Gestion des BLOB…

C’est une session sur SQL Server 2008 et plus particulièrement sur la fonctionnalité FILESTREAM. Je vous invite à vous inscrire aux TechDays en cliquant sur le lien ci-dessous. Pour rappel, les TechDays se tiennent à Paris au palais des Congrès les 11, 12 et 13 février. La session aura lieu le dernier jour.

TechDays 2008

Ajax: UpdatePanel Injection

16 janvier 2008

Je vais vous présenter une technique utile pour optimiser la gestion des UpdatePanel dans ASP.NET Ajax : l’injection d’UpdatePanel.
[Le code source est disponible en fin d'article]

La problématique :

Dans une page se trouve une liste d’éléments présentés dans un contrôle. Prenons un contrôle template comme le Repeater par exemple (bien que je préfère créer ma propre liste templatée). Une action sur un élément doit changer son état et l’affichage doit refléter ce changement en conséquence. L’image ci-dessous montre l’exemple utilisé.

Avec un PostBack classique, on doit reconstruire tout l’arbre de contrôle pour pouvoir rendre la liste modifiée. Une optimisation (même si on ne gagne que de la bande passante) consisterait à placer le template de l’élément dans un UpdatePanel mais on ne gagne pas le temps de Render car la page reconstruit quand même l’arbre de contrôle (CreateChildControl) comme présenté dans la trace ASP.NET suivante.

arbre de controle du repeater

En plus, à moins que vous ne l’ayez prévu dans votre code, il y aura un nouvel appel à la base de données pour recharger la liste.

L’objectif :

Ne pas reconstruire l’ensemble de l’arbre (le Repeater) mais construire juste le template de l’élément modifié. On envoie le HTML généré au client via AJAX pour mettre à jour le DOM.

La solution :

On va utiliser un faux UpdatePanel qui représente la partie à modifier et qui sera plus haut dans la hiérarchie de contrôles (à la racine par exemple). On intercepte l’événement dans la page, on effectue la modification visuelle (après avoir fait son traitement métier bien évidemment) dans ce faux UpdatePanel et on va le réinjecter côté client à la place du vrai UpdatePanel à modifier. Bien entendu, on ne recharge pas la liste d’éléments.

Comment faire ? :

Premièrement, il faut placer la partie à mettre à jour selon cette technique dans un UserControl (ASCX) pour factoriser; je rappelle qu’il y a les instances dans le Repeater et la fausse instance à la racine. Dans notre cas c’est le bloc jaune (l’élément) que j’exporte. Cet export nous oblige à réifier l’affichage d’un élément en lui créant par exemple des propriétés publiques correspondant aux infos à afficher. On peut aussi le rendre autonome afin qu’il sache les retrouver à partir de l’identifiant de l’élément mais là je rentre dans le détail. Il faudra aussi faire de même avec les événements que l’élément peut lancer.

Deuxièmement, on place un instance de ce contrôle dans l’ItemTemplate du Repeater et à la racine de la page, dans un UpdatePanel. Pour le second, on veillera à mettre Visible à False afin de ne pas perturber l’affichage normal. Je vous rappelle que ce second élément n’est là que pour simuler un autre UpdatePanel, c’est un contrôle “technique“.

<ItemTemplate>
 <Custom:Department runat="server" ID="DeptBlock" IsSelected='<%#Eval("DepartmentID").ToString()==ElemID %>'
   ElemGroup='<%#Eval("GroupName") %>' ElemName='<%#Eval("Name") %>' ElemID='<%#Eval("DepartmentID") %>' />
</ItemTemplate>

Troisièment, désactiver les raffraichissements automatiques des UpdatePanel pour prendre la main avec les attributs suivants :

ChildrenAsTriggers="false" UpdateMode="Conditional"

Quatrièment, il ne reste plus qu’à modifier l’événement déclencheur (j’ai choisi ici ItemCommand du Repeater). Les étapes sont :

  • désactiver le Repeater pour prendre la main
  • remplir le faux UpdatePanel
  • faire croire au système que c’est le bon UpdatePanel que l’on envoie
  • L’envoyer côté client

protected void DepartmentList_ItemCommand(object source, RepeaterCommandEventArgs e)
{
 //on ne rebind PLUS la liste
 // DepartmentList.DataBind();
 //et on la "désactive" sinon, elle a tendance à se construire toute seule
 DepartmentList.DataSource = null;
 DepartmentList.DataSourceID = null;

 //en revanche, on configure le FakeUpdatePanel
 //...ici, je mets des données bidon mais je devrais charge mon élément individuel
 FakeDepElem.ElemGroup = "FAKE MIS A JOUR";
 FakeDepElem.ElemName = "Fake Dept";
 FakeDepElem.ElemID = e.CommandArgument.ToString();
 FakeDepElem.IsSelected = true;
 //on rend l'élément visible sinon la méthode Render ne retournera rien
 FakeDepElem.Visible = true;

 //ICI, c'est le point technique, on change l'ID du faux UpdatePanel
 //le fait de changer l'identifiant de l'update panel va permettre de cibler un autre updatepanel coté client.
 FakeElementUP.ID = e.CommandName;
 //on envoie le panel au client
 FakeElementUP.Update();
}

Le point le plus important est le remplacement de l’identifiant de mon faux UpdatePanel. Une fois renvoyé au client, la partie JavaScript d’Ajax cherche l’UpdatePanel spécifié et le remplace, sans se rendre compte de l’altération. La capture faite avec Fiddler ci-dessous montre ce que renvoie Ajax au client. Une entête identifie le bloc (l’UpdatePanel) à mettre à jour suivi du code HTML à remplacer. C’est cette entête finalement que nous avons altéré.

632|updatePanel|DepartmentList_ctl03_DeptBlock_ElementUP|

Verification avec Fiddler

Dans ce code, vous voyez que j’utilise e.CommandName. Ce choix est arbitraire. Pour fonctionner, le handler de l’événement a besoin à minima des informations suivantes :

  • l’identifiant de l’élément (ou un moyen de le retrouver)
  • l’identifiant de l’UpdatePanel à mettre à jour

SelectBtn.CommandArgument = ElemID;
SelectBtn.CommandName = ElementUP.UniqueID;

Moi je les passe explicitement dans la commande mais on peut imaginer de les retrouver avec le paramètre source ou par un autre moyen.

J’ai pu mettre en oeuvre cette technique massivement dans un site web grand public de courses en ligne que nous (Bewise) avons réalisé pour un client. Je peux donc vous dire qu’elle a été validée à tous les niveaux : performances, maintenabilité, fiabilité, sécurité. Bien évidement, elle est beaucoup plus complexe que cela car beaucoup plus riche fonctionnellement (je dis cela pour ceux qui peuvent rester sur leur faim).

Effets de bord :

Si la Trace est active, vous risquez de voir apparaître une erreur précisant qu’il y a des doublons dans les ID des contrôles et le module de Trace ne sait pas le gérer (et donc lève une exception). Bizarrement, l’erreur n’est plus levée si la pile est pleine (requestLimit). Si vous avez plus d’infos sur ce comportement non déterministe ou un moyen de contournement…

Code Source :

Le code source utilise juste une connexion à la base AdventureWorks de Microsoft. Vous aurez juste à changer la chaîne de connexion dans le web.config et le nom du serveur web dans les propriétés du projet. Le code est téléchargeable ici

Intellisense dans SQL Server 2008

5 janvier 2008

Bon d’accord, ce n’est pas vraiment dans SQL Server 2008 mais bien dans l’éditeur de T-SQL SSMS (Management Studio).

Comme pour Visual Studio, elle marche pour les mot-clés mais aussi de façon contextuelle dans les clauses SELECT, FROM, WHERE, etc.

 

Bonne année !!

5 janvier 2008

Comme il est de coutume, je ne déroge pas aux voeux de la nouvelle année. Que 2008 soit une année flamboyante pour SQL Server et la Business Intelligence


Suivre

Get every new post delivered to your Inbox.

Joignez-vous à 203 followers