SQL Server vu par Christian Robert

SQL Server : Déterminer qui a son anniversaire aujourd’hui (manipulation de date et d’index)

C'est une question toute bête me direz vous : Qui a son anniversaire aujourd'hui ? Toute bête mais qui amène à des calculs tortueux. J'avais déjà traité le sujet sur le calcul de l'âge : http://blogs.codes-sources.com/christian/archive/2007/07/02/SQL-Server-Comment-calculer-differences-dates.aspx

Dans ce cas, pourquoi ne pas retrouver les personnes ayant leur anniversaire aujourd'hui par ce biais là ? Parce que çà implique d'utiliser des fonctions sur les champs présent dans le WHERE et interdit l'usage des index… Ah, j'ai du oublié de vous dire que ma table comporte 1 million de personnes, rien que çà !

La définition de la table est la suivante :

CREATE TABLE dbo.NewP( 
    p_id int IDENTITY(1,1) NOT NULL, 
    FirstName nvarchar(50) NOT NULL, 
    LastName nvarchar(50) NOT NULL, 
    BirthDate date NULL
CONSTRAINT pk_x PRIMARY KEY NONCLUSTERED ( p_id)
GO

Ici j'utilise le type date de SQL Server 2008 pour éviter d'alourdir le code encore un peu plus. Si vous cherchez une méthode pour supprimer les composantes heures du type datetime de SQL Server, c'est par ici que çà se passe : http://blogs.codes-sources.com/christian/archive/2007/04/29/sql-server-conserver-la-date-ou-l-heure-d-un-datetime-comparaison-des-methodes.aspx

Après mûre réflexion je pense à une méthode qui devrait être intéressante, en passant par la table de nombre détaillé ici : http://blogs.codes-sources.com/christian/archive/2008/05/28/sql-server-g-n-rer-une-table-de-nombres-ou-une-fonction.aspx

Si je reformule le problème je pense que vous comprendrez mieux. Je recherche toutes les personnes dont la date de naissance est aujourd'hui moins 1 an, 2 ans, etc. D'où l'intérêt de la table de nombre. La requête s'écrira ainsi :

select *
from dbo.NewP
where BirthDate in ( 
        select DATEADD(year, -nums, cast(getdate() as DATE)) 
        from dbo.Nombres 
        where nums < 100 -- Pas plus de 100 ans, mais bon à vous de voir ! 
    )

• Renvoie 3000 lignes sur 1,5 million
• 58 sec d'exécution
• 118 sec de CPU utilisé (j'ai un dual core donc faute au parallélisme dans la requête)
• 2,5 millions de pages lues

Il est possible de mettre un BETWEEN sur le champ nums pour avoir une tranche d'âge. La requête obtenue est lisible et devrait permettre l'utilisation d'index… Enfin un bon index, j'ai donc ajouté un index clustered sur le champ BirthDate, en croisant les doigts car vue la type de requête je doutais franchement que SQL Server aille utiliser l'index dans un tel cas même si cela se révélait intéressant pour lui.

    create clustered index ix_gghg on dbo.NewP(BirthDate)

Et c'est bien le cas, pas d'utilisation d'index, car cela renvient à rechercher plusieurs plages de valeurs contigües. Les performances restent les même que la requête précedente.

Je cherche une autre piste pour récupérer mes enregistrements plus rapidement en profitant d'un index. Ici je vais créer un nouveau champ qui sera calculé et indexé. Que vais-je mettre dedans ? Tout simplement une combinaison des mois et jour des dates de naissances de tout le monde. Ensuite je créée un index là-dessus, cette fois ci les plages de valeurs seront contigüe, l'index se révélera utilisable, toujours à condition d'utiliser le bon.

    alter table dbo.NewP add anniv as cast((month(BirthDate) * 100 + DAY(BirthDate)) as smallint)

Je le convertis en smallint, pour avoir une taille d'index encore plus petite, la probabilité de dépasser la précision du 16 bits signé étant nulle ici. Puis j'ajoute un index :

create nonclustered index ix_test on dbo.NewP(anniv) include(p_id)

Il est effectivement possible de créer un index sur un champ calculé, il y a quelques règles à respecter quant aux types de fonctions utilisables. Ici la clause INCLUDE me permet d'ajouter la clef primaire de la table à l'index comme valeur et non pas comme champ clef. Il ne pourra donc pas servir à la recherche, mais uniquement être dans le SELECT.

Mon idée est d'utiliser une méthode de pagination pour présenter les résultats 10 par 10 et non pas la totalité des personnes ayant leur anniversaire ce jour (pour le faire remplacer l'index précédent par un index clustered sans include). La requête se traduit comme suit :

with paging
as
( 
    select ROW_NUMBER() over(order by p_id) as num, p_id 
    from dbo.NewP 
    where anniv = month(cast(getdate() as DATE)) * 100 + day(cast(getdate() as DATE))
)
select * from paging 
    join dbo.NewP as tb on paging.p_id = tb.p_id
where num between 1 and 10;

Les résultats sont assez exceptionnels :

• Renvoie 10 lignes sur 1,5 million (normal on pagine)
• 0 sec d'exécution
• 0 sec de CPU utilisé
• 52 pages lues

Moralité les gains sont importants surtout si la quantité de données de la table de base est importante.

Quand est ce qu'il faut avoir recours à ce type d'optimisation ? Je dirais très rarement, çà dépendra essentiellement du temps de réponse de la requête de départ et de votre volume de données. Si cette requête est essentielle pour vous et qu'il y a peu d'écriture dans la table dans ce cas oui. Evitez de faire cela si vous avez de grandes quantités d'écriture dans cette table.

Maintenant voyons les défauts de la méthode : Je ne peux pas requêter facilement sur une plage de date sans me torturer l'esprit. Les mois ne faisant pas tous 30 jours, c'est à vous de manuellement trouver les bornes du BETWEEN à appliquer sur le champ anniv !

Bon anniversaire…