Pagination

Régulièrement, je rencontre des projets, essentiellement web, dans lesquels il y a des besoins de pagination de liste. Par exemple, un site e-commerce a besoin d’afficher une liste de produits d’une catégorie. Bien souvent, les développeurs râlent après SQL Server car il n’y aurait pas d’options comme le LIMIT de MySQL. Je vais donc vous montrer comment réaliser une pagination avec SQL Server.

Le besoin

Dans cet article, je vais partir du principe que je travaille pour un site e-commerce qui me demande d’afficher la liste des produits en fonction de la catégorie choisie. Il faudra que cette liste n’affiche que 20 articles par page et bien sur que les pages soient affichées très rapidement.

Mise en place

Pour réaliser ces tests, j’ai créé la table produits :

CREATE TABLE [dbo].[produits](
    [id] [int] NOT NULL,
    [libelle] [varchar](250) NULL,
    [prix] [decimal](9, 2) NULL,
    [categoryId] [int] NULL,
    [ordreAffichage] [int] NULL,
    [description] [varchar](500) NULL,
CONSTRAINT [PK_produits] PRIMARY KEY CLUSTERED([id] ASC))

J’ai ensuite chargé 110 000 lignes de données dans cette table répartie en 3 catégories. J’ai généré les données dans Excel en incrémentant des id et des libellés.

Données de la pagination

ALL in the DOM

Une des techniques de pagination rencontrée fréquemment sur de petits sites est la technique que j’appelle « ALL in the DOM ». Elle consiste à charger tous les produits de la catégorie dans la page et de gérer la pagination avec JavaScript. La rapidité d’intégration de cette solution est sans doute un avantage mais les performances peuvent très vite se montrer désastreuses ! En effet, si on doit surcharger la page de plusieurs centaines d’articles qui ne seront sans doute jamais consultés, l’affichage pourrait être extrêmement long. Et c’est sans compter le requêtage de la table qui devra lire beaucoup de pages (et d’IO) ainsi que la bande passante du server SQL qui devra transférer les données à l’application. Bref, cette solution est à réserver exclusivement pour des projets amateurs à faible volumétrie.

La requête exécutée sur le serveur ressemblerait à ceci :

DECLARE @CATEGORY int = 3

select id
, libelle
, prix
, categoryId
, description
from produits
where categoryId = @CATEGORY
order by ordreAffichage

Table temporaire

Une autre solution que j’ai rencontré, mais que je ne recommande pas, est d’utiliser des TOP avec une table temporaire. Une première étape consiste à créer un table temporaire qui contient toutes les lignes jusqu’à la fin de la pagination : par exemple, avec 20 produits par page pour afficher la 5ème page, la table temporaire contiendra les 100 premiers produits. Dans la 2ème étape, il suffit ensuite de sélectionner avec un top 20 les produits de la page.

DECLARE @DEBUT int = 500
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

select top (@DEBUT + @LONGUEUR) id
, libelle
, prix
, categoryId
, description
, ordreAffichage
into #t
from produits
where categoryId = @CATEGORY
order by ordreAffichage

select top (@LONGUEUR) *
from #t
order by ordreAffichage desc

drop table #t

Avec ce système, plus l’utilisateur paginera et plus les performances se dégraderont. Avec de nombreux utilisateurs, la TEMPDB pourrait grossir très vite.

CTE et Fenêtrage

Depuis la version 2005, on peut utiliser les CTE pour les requêtes récursives et avec la fonction de fenêtrage ROWNUMBER qui permet d’attribuer un rang à une ligne, nous sommes capable de ne récupérer que les lignes qui nous intéresse.

DECLARE @DEBUT int = 500
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

;WITH cte AS
(
select id
, libelle
, prix
, categoryId
, description
, ROW_NUMBER() OVER (ORDER BY ordreAffichage) as rang
from produits
where categoryId = @CATEGORY
)
select id
, libelle
, prix
, categoryId
, description
FROM cte
WHERE rang > @DEBUT AND rang <= @DEBUT + @LONGUEUR

Je pense que cette solution (ou une adaptation de celle-ci) est bonne pour les SQL Server < 2012.

OFFSET et FETCH

Avec SQL Server 2012 et 2014, OFFSET et FETCH ont fait leur apparition. Cette méthode se rapproche le plus du LIMIT connu sur MySQL.

DECLARE @DEBUT int = 80000
DECLARE @LONGUEUR int = 20
DECLARE @CATEGORY int = 3

select id
, libelle
, prix
, categoryId
, description
from produits
where categoryId = @CATEGORY
order by ordreAffichage
offset @DEBUT rows
fetch next @LONGUEUR rows only

Au niveau performance, elle est quasiment identique aux CTE + ROWNUMBER mais elle est bien plus lisible.

TVP et Cache Client

Cette méthode est plus complexe à mettre en oeuvre. En effet, il est nécessaire d’utiliser les TVP dans SQL Server et gérer un cache au niveau de l’application.
Pour utiliser une TVP, il est nécessaire de créer un type TABLE au niveau de la base de données comme ceci :

CREATE TYPE dbo.TypePagination AS TABLE
(
id int
)

Lors du premier affichage d’une catégorie, il convient de le récupérer les ID de tous les produits de la catégorie et de les stocker dans le cache de l’application.

DECLARE @CATEGORY int = 3

select id
from produits
where categoryId = @CATEGORY
order by ordreAffichage

Lors de la pagination par l’utilisateur, il convient ensuite de récupérer les 20 ID suivant :

select id
, libelle
, prix
, categoryId
, description
from produits p
where exists (select * from @TypePagination t where t.id = p.id)
order by ordreAffichage

Cette méthode est de loin la plus performante si l’indexation de la table produits est réalisée correctement. L’inconvénient est la difficulté (relative) à sa mise en place, la consommation de ressources supérieure au niveau du serveur applicatif et la lenteur du premier affichage de la liste de produit (mais on peut compenser ceci par une procédure de chargement de cache lors du reboot du serveur applicatif).

Comparatif

Afin de tester les performances, j’ai exécuté 10 fois chacune des requêtes et j’ai calculé des moyennes sur le CPU Time, l’Elapsed Time et le Logical Reads. J’ai fait ce test pour la 1ère et la 50ème page de la liste de produit.

Comparatif Performance Pagination

La méthode avec la table temporaire est de moins en moins efficace en avançant dans la pagination. De plus elle a beaucoup d’inconvénients à cause de la création de tables temporaires.

La méthode All in the DOM est catastrophique ! Surtout que dans cet exercice, je ne tiens compte que de la consommation au niveau de la base de données. Il reste ensuite à la page Web de gérer la pagination.

La technique Offset + Fetch semble être un bon compromis entre performance et facilité de développement et reste stable au fur et à mesure de l’avancée dans la pagination. Cependant la méthode utilisant CTE + fonction de fenêtrage est très proche.

La dernière méthode utilisant la TVP est la plus performante au niveau base de données. Mais comme la performance doit se mesurer dans son ensemble avec la chaine complète et qu’une partie du travail est réalisé coté applicatif, il faut tester en fonction de vos besoins.

Plan de Maintenance

Plan de maintenance – a quoi ça sert ?

Le plan de maintenance d’une instance SQL Server est l’ensemble des jobs qui permettent de garantir le bon fonctionnement des bases de données dans le temps.

Est-ce indispensable ?

Imaginez une instance de bases de données non maintenue. Sans action sur vos indexs, les performances deviendraient catastrophiques. Sans sauvegarde, vous risqueriez de perdre toutes vos données, les journaux de logs satureraient l’espace de vos disques. Et ce n’est qu’un avant-gout des problèmes qui pourraient se multiplier. Je suis donc obligé de dire que la mise en place d’un plan de maintenance est obligatoire. C’est même une des premières choses à définir lors d’une installation.

Les différentes étapes

La sauvegarde

C’est surement à la sauvegarde de nos bases de données que l’on pense en premier quand on parle de plan de maintenance. Vous devez adapter votre stratégie de sauvegarde en fonction de vos besoins. Par exemple, pour un petit site web relativement statique avec un recovery model simple, une sauvegarde FULL chaque nuit lors des heures creuses d’activité pourrait suffire. Par contre, pour un gros site e-commerce, une sauvegarde FULL quotidienne + des sauvegardes incrémentales régulières + des sauvegardes des logs toutes les 5 minutes pourrait être envisageable.

Bref, cet article n’a pas vocation à vous aider à choisir une stratégie de sauvegarde mais plutôt de ne pas oublier des étapes importantes dans votre plan de maintenance.

N’oubliez pas que le but de la sauvegarde des bases de données est de pouvoir les restaurer ! Il faut donc penser à vérifier que vos sauvegardes sont opérationnelles.

Echec restore

Je sauvegarde toujours dans un nouveau fichier, je ne suis pas vraiment adepte de plusieurs sauvegardes dans un seul fichier (bien qu’il y ait quelques avantages). Ce nouveau fichier est horodaté dans son nom. Avec ce système, je suis donc obligé de créer une étape de suppression des anciennes sauvegardes pour ne pas remplir mes disques.

J’ai souvent la question sur la sauvegarde des bases systèmes. Oui il est préférable de sauvegarder au minimum MASTER (pour le mot de passe des logins par exemple), MSDB (pour toutes les données de l’agent) et éventuellement MODEL.

En principe, je sauvegarde également régulièrement dans des fichiers SQL le script des logins, les server objects et les objets de l’agent. Ce n’est pas obligatoire, mais ça permet souvent de gagner du temps.

La vérification d’intégrité

La vérification d’intégrité des bases de données est également incontournable. Si une de vos bases est corrompue, il faut être alerté le plus rapidement possible pour réduire la perte de données.

En général, j’exécute un DBCC CHECKDB avant de faire les sauvegardes complètes. Pour réduire le coût de cette opération, on peut alterner avec un DBCC CHECKDB et l’option WITH PHYSICAL_ONLY.

Integrité de vos bases

Je recommande de conserver la dernière sauvegarde FULL effectuée juste après le dernier DBCC CHECKDB (FULL). En cas de corruption, on peut repartir d’une sauvegarde saine.

Les indexs et statistiques

A chaque insertion / suppression / mise à jour de données dans une table, ses indexs se fragmentent.

Si une maintenance des indexs et des statistiques n’est pas effectuée, les performances globales de la base de données vont se dégrader. En effet, un index fragmenté va obliger à lire plus de pages de données donc plus d’IO et plus de RAM seront nécessaires. Si les statistiques ne sont pas à jour, l’optimiseur pourrait décider d’utiliser un plan d’exécution non adapté ce qui aurait pour effet de consommer trop de ressources et de ralentir les temps d’exécution des requêtes.

Il convient donc de défragmenter régulièrement les indexs et de recalculer les statistiques. Cette tache est une partie du plan de maintenance indispensable à une base de données.

On pourrait être tenté de reconstruire chaque index mais pour une base de données utilisée 24/24h, ça pourrait ne pas coller avec les besoins métier car cette opération peut être très couteuse. L’idéal est donc d’utiliser des scripts et en général j’applique cette règle :
– Si l’index est fragmenté à plus de 30% alors je reconstruis l’index (à la reconstruction de l’index, les statistiques sont recrées donc pas besoin de refaire une mise à jour).
– si l’index est fragmenté entre 10 et 30% alors je réorganise l’index et je recalcule les statistiques.
– sinon je ne fais rien.

defrag

N’oubliez pas qu’en version Enterprise, les indexs peuvent être mis à jour ONLINE. C’est plus lent mais la base est toujours accessible.

Si un index reste toujours fragmenté et possède moins de 8 pages, inutile de s’acharner à le défragmenter…

Le nettoyage

La dernière tache indispensable dans un plan de maintenance est la suppression des historiques de sauvegardes et des jobs de l’agent. En effet, si vous ne le faites pas, la base MSDB deviendra énorme.

Nettoyer les historiquesEssayez de restaurer rapidement une base à partir d’une sauvegarde FULL + quelques sauvegardes de LOGS avec un historique de quelques mois et vous comprendrez ce dont je veux parler 😉

Conclusion

Le plan de maintenance est une des taches de base d’un DBA et devrait être mis en place sur chaque instance. Il n’existe pas un plan de maintenance type car c’est à vous d’adapter votre plan au besoin de l’entreprise.

N’oubliez pas que grâce à Kankuru, vous pouvez surveiller que vos plans fonctionnent correctement sur tous vos serveurs !

Il est possible que vos plans de maintenance contiennent des étapes supplémentaires. N’hésitez pas à les décrire dans vos commentaires.

Parcours du combattant

Migrer Iphone vers Windows Phone – mon parcours du combattant

J’utilise un iphone depuis pas mal d’années. Je m’étais juré de ne jamais changer d’OS tant j’en étais satisfait. Cependant, j’étais lassé du manque d’autonomie et du peu de choix de téléphone : en gros, il faut toujours prendre du haut de gamme. J’étais également tenté par le format phablet qu’Apple n’a pas l’air d’avoir l’intention de sortir. En tant qu’utilisateur de produits Microsoft, je voulais bien sûr tester leur fameux OS. Ce sont toutes ces raisons qui m’ont poussé à migrer d’Iphone vers Windows Phone.

Le téléphone

Je me suis laissé tenté par le Nokia 1320 (le low-cost du 1520) car le prix était faible, c’est à dire inférieur à 300€, et que le format de la SIM était du micro donc le même que celui de mon iphone 4S. Ceci me permet de revenir sur mon vieil iphone si Windows Phone ne me plait pas 😉

Nokia Lumia 1320

Le téléphone en lui même est plutôt lourd et peu pratique à cause du bouton d’appareil photo qui s’active à chaque fois. Ça doit être parfait pour les gauchers mais manque de chance, je suis droitier ! Si vraiment Windows Phone me plait, je pense en choisir un plus petit.

Récupérer mes données

Lorsque j’ai changé mon 3GS pour mon 4S, je n’ai eu qu’une sauvegarde/restauration à faire avec itunes pour retrouver toute ma configuration. La migration vers Windows Phone risque d’être plus compliquée.

Première chose à faire : copier mes contacts. Comme je n’avais pas vraiment envie d’entrer manuellement chacun de mes contacts dans mon nouveau téléphone, il a donc fallu trouver une solution. La bonne nouvelle, c’est que Nokia a pensé à tout ! Grâce à ce logiciel, en activant le Bluetooth sur mes 2 téléphones, j’ai réussi à recopier tous mes contacts.

Ensuite, les photos. Je m’en suis sorti très facilement car je sauvegardais déjà mes photos Iphone sur OneDrive.

WIndows Phone 8

La configuration de l’OS est relativement simple, je retrouve la plupart des applications que j’utilisais précédemment à part les jeux (mais je suis obligé d’avouer que je n’ai pas spécialement cherché pour le moment).

Par contre, je n’ai pas réussi à configurer certaines messageries POP3 d’OVH et 1&1. Il faudrait que je creuse encore plus avant de dire que ça ne fonctionne pas mais tout de même l’ajout d’un compte de messagerie ne devrait pas poser de problème. En plus, j’ai une dizaine de boites mail et je trouve dommage d’avoir autant de tuiles sur ma page d’accueil.

La deuxième chose que je reproche à l’OS, est le manque cruel d’un vrai centre de notification. Après 24h de téléphone éteint, j’ai reçu une vingtaine de notifications sans en avoir un résumé. Bref, il faudrait rester en permanence avec un œil sur son téléphone pour ne rien rater. Normalement, avec Windows Phone 8.1, le problème devrait être réglé mais pour le moment, ça ne me convient pas.

Dernier point, mais je pense que le problème est plutôt lié à IOS, les personnes qui m’envoyaient des iMessages depuis d’autres Iphone ne peuvent plus m’envoyer de SMS. Enfin en tout cas, je ne les reçois plus sauf s’ils pensent à me les envoyer en sms.

Conclusion

Pour le moment, je ne retrouve pas tout ce que j’attends d’un téléphone, je suis donc toujours sur mon iphone ! Je me suis inscrit au programme développeur qui permet de tester Windows Phone 8.1. Je vais le tester dans les prochains jours et peut être migrer définitivement. Dans tous les cas, je pense que j’écrirai un nouvel article pour vous tenir au courant.

Sql Server 2014

SQL Server 2014 : les nouveautés

Hier, Microsoft a officiellement lancé SQL Server 2014. Une version d’évaluation gratuite de 180 jours est disponible sur le site Microsoft.

Je pensais écrire un article complet sur toutes les améliorations qu’apporte cette nouvelle release SQL Server 2014 mais le GUSS l’avait déjà fait avant moi lors des Journées SQL Server. Je vous propose donc de visionner la vidéo de la session :

Pour moi, la fonctionnalité qui m’intéresse le plus est bien sur Hekaton. J’y vois beaucoup de possibilités de gain de performance. C’est pourquoi, je vous propose également une seconde vidéo, enregistrée en décembre lors des journées SQL Server, entièrement dédiée au In-Memory. En plus, elle est présentée par un des meilleurs speakers : Christophe Laporte :

Je pense que dans le futur, je publierai des exemples concrets d’utilisations du In-Memory…