Poor SQL : l’éléphant bleu du SQL

Via le mailing de Brent Ozar & Coje recommande vivement cette newsletter à quiconque bosse de près ou de loin sur SQL Server – un petit outil sympa qui reformate vos requêtes T-SQL directement dans votre navigateur web : Poor SQL. Ça marche plutôt bien, le voilà d’ailleurs déjà qui atterrit dans ma barre de favoris!

Et pour ceux que ça intéresse, j’en profite pour vous rappeler l’existence du Blitz de Brent Ozar, script SQL qui permet de prendre en main en 60 minutes les serveurs SQL dont vous venez d’hériter.

Gestion des dates en Transact SQL

Ceci est un article technique (bases de données SQL), n’hésitez pas à l’ignorer si ça ne vous intéresse pas 🙂

Depuis quelques jours nous galérons avec mon équipe à identifier une anomalie de traitement plutôt pénible dans l’ETL qui charge tous les soirs le chiffre d’affaire.

Nous avons enfin identifié l’erreur: le compte de service qui exécute le chargement le soir est configuré en américain sur la production, alors qu’il est en français sur les plateformes de développement. C’est dommage lorsqu’une date de bascule au 1er mai est interprété comme le 5 janvier en production. En effet le 05/01/2011 devient 01/05/2011 avec un format de notation américain.

Sachant que changer le paramétrage d’un compte de service en production peut prendre 3 ans de conflits politiques entre les différentes équipes (pour 2 minutes d’implémentation), nous avons décidé de faire évoluer les requêtes SQL pour qu’elles n’utilisent plus aucune conversion implicite.  L’objectif c’est de rendre le code insensible aux paramètres de langue.

En gros en SQL ça veut dire que ça c’est interdit: CAST(‘2011-05-01’ AS DATETIME), de même que référencer directement une date en chaîne de caractère : WHERE MonDateTime = ’05/01/2011′

A la place on doit faire ça : CONVERT(DATETIME,’01/05/2011′,103), ou ça : CONVERT(DATETIME,’2011-05-01′,102). En utilisant CONVERT, on peut préciser le format de la date de manière explicite: 103 pour JJ/MM/AAAA et 102 pour AAAA-MM-DD.

Ne laisser aucune conversion implicite (date ou autre) est définitivement une bonne pratique qu’il ne faut pas oublier. Pour les dates, c’est aussi une info qu’il faut savoir transmettre aux utilisateurs et MOA qui requètent les bases en SQL.

Pour plus de détail:

Recherche textuelle sur SQL Server, c’est dur!

Pour faire suite à l’article d’avant-hier, celui sur la déformation de notre vision du monde que cause les outils que l’on emploie, je voulais vous parler de la recherche textuelle sur la plateforme SQL Server. Désolé si certains sont déçus mais oui c’est un point technique 🙂

Alors pourquoi faire le lien entre cette problématique et la recherche textuelle ?

Dans une base de données on a une obsession : tout mettre dans des tables composées de lignes et des colonnes. Naturellement dans ce modèle l’unité de traitement minimale est la cellule, l’intersection d’une ligne et d’une colonne.

Très bien, mais que faire quand la cellule contient une entité unique, comme demandée par la modélisation, mais que cette entité se décompose de manière complexe ? Je pense par exemple à un descriptif produit ou un commentaire client. Comment exploiter une information humaine, un avis, une description, comprise dans une chaîne de caractère, avec des outils qui ne savent pas vraiment travailler à ce niveau de granularité ?

Et bien on fait comme on peut, mais en général ce n’est pas très joli !

Pour parler d’une situation précise, je monte actuellement une solution décisionnelle qui stocke et analyse pratiquement toutes les informations concernant le parc informatique d’un grand groupe. On pourrait penser que sur ce domaine fonctionnel on n’aurait pas de surprises dans les données: que du technique ou du numérique. Et bien détrompez-vous, remonter l’ensemble des applications installées sur les postes, sur un parc de 30’000 machines, dans 10 langues (vive l’Unicode), ça donne 3 millions de lignes à brasser par jour…

Pour vous donner un exemple : j’ai environ 1500 valeurs distinctes par jour d’applications qui contiennent le mot ‘Microsoft’, dont 500 qui contiennent le mot ‘Office’… Là dedans je dois retrouver les différentes éditions d’Office 2003 et 2007 pour faire le suivi du licensing.

Miam !

Le minimum qu’on puisse dire c’est que j’ai un problème de qualité de données. L’approche que je préfère sur ce genre de problème c’est d’utiliser les outils décisionnels pour instaurer un cycle d’amélioration des données (Tip 131):

  1. On récupère tout dans le datawarehouse avec un premier cycle d’import
  2. On flag ce qui n’est pas bon / pas encore revu
  3. On génère des rapports pour que les opérationnels puissent corriger les systèmes sources et / ou proposer des nouvelles règles d’alimentation
  4. On implémente les changements, puis retour à l’étape 1

Bien ! Le problème c’est que pour faire l’étape 2 il faut pouvoir interagir sur la donnée : est-ce que mon descriptif d’application contient ‘Office’ ? Enfin, est ce qu’il contient ‘%Office’, ‘Office%’, ‘% Office %’, ‘%Office%’… La différence ?

  • Office’ : le seul résultat qui passe c’est « Office »
  • %Office’ : « Office », « LibreOffice », « Microsoft Office »
  • Office%’ : « Office », « Office 2003 », « OfficeCracker »
  • % Office %’ : « Microsoft Office 2003», la subtilité est dans les espaces autour du mot
  • %Office%’ : toutes les possibilités

Presque facile pour Microsoft Office, mais pas pour Adobe et toutes les variations de flash, ni pour IBM, ni Mozilla, ni pour toutes les autres en fait…Et ‘0ffice’ ? ‘Offisse’ ? On peut les oublier pour des champs fournis par les éditeurs eux-même, mais pour une recherche dans des champs saisis à la main ?

Pour pouvoir traiter tous ces cas de figure on va utiliser des critères de recherche compliqués, conditionnels, qui impliquent des traitements unitaires multiples sur chacune des cellules les unes après les autres: les performances s’écroulent. En temps de réponse on passe de moins d’une seconde sur une table bien indexée à plusieurs minutes sur une recherche en ‘%…’. En fait c’est une particularité dans la manière qu’ont les bases de données relationnelles de stocker et traiter l’information qui force une lecture complète de la table à chaque fois.

On retrouve cette limite là dans tous nos outils :

Ça plus le fait qu’il faille créer toutes les règles de gestion une par une de manière exhaustive. Vivement que les IA viennent nous filer un coup de main !

Malheureusement pour le moment il n’existe pas de solution magique, une recherche textuelle étendue sur une base de données relationnelles classique aura forcément un coût important en performance et/ou un résultat foireux. La prochaine fois que vous verrez un site web équipé d’un moteur de recherche bancal, ne cherchez pas pourquoi… Et c’est là que le bas blesse: à force de galérer avec ce type d’opérations on ne les propose plus, on ne les implémente plus, et on en vient à penser qu’elles ne sont pas possibles.

Pourtant Google y arrive, et sur des volumes de données autrement plus important! C’est d’ailleurs pour ça que pour l’avenir j’ai espoir dans la prochaine génération de bases avec moteur de stockage en colonne / vertical. Va bien y avoir un génie quelque part qui va nous révolutionner les recherches textuelles là-dessus!

SQL Server 2008 SP2 et autres sucreries

La nouvelle de la journée : le SQL Server 2008 SP2 sort aujourd’hui! Il est détaillé sur le Team Blog, et se télécharge par là.

J’en profite pour faire le relai d’une sélection d’articles techniques que j’ai collectionné ces 2 derniers mois:

SSRS

  • Importer des cartes autres que celle des USA dans les rapports, via Kasper de Jonge.

SSIS

  • Minimiser les dégâts lorsqu’on effectue un produit cartésien (cross join) dans un flux de données, via Todd McDermid.
  • Une belle remise à plat sur l’utilisation des variables dans les Script Tasks et Script Components, via Todd McDermid encore.
  • Un composant à tester: un nouveau Data Conversion, via Todd McDermid toujours.
  • Une petite piqure de rappel sur les Events et Event Handlers, via le Database Journal.
  • Importer les valeurs d’un result set ADO dans un chaîne, séparées par des virgules. C’est un peu tortueux, mais c’est parfait pour générer des requêtes SQL à la volée. Via SqlServerCentral (enregistrement gratuit)

SSAS

  • MDX : Existing, Count et Filter via Thomas Ivarsson. Il n’explique pas assez à mon gout, mais en jouant avec ses requêtes on comprend bien.

T-SQL

  • Implémenter des tests unitaires automatisés dans Visual Studio 2010 pour le développement des procédures stockées. Ça c’est un sujet à creuser, via Jamie Thomson.
  • Utiliser le MERGE dans SQL Server 2008, via Robert Sheldon de Simple Talk. J’ai honte mais je n’ai pas encore eu le temps de vraiment regarder… Bouh!
  • Dédoublonner un data set grâce à la clause PARTITION BY. Pas sur que ça nous soit très utile en BI cependant, via SqlServerCentral (enregistrement gratuit)

ASP.NET

SharePoint

  • Un livre blanc sur l’authentification Kerberos dans SharePoint 2010, via Kasper de Jonge.