SQL Server vu par Christian Robert

SQL Server : Renvoyer les données aléatoirement Tri aléatoire ou tirage aléatoire

Comment réaliser un tri aléatoire d'une table ou récupérer de la même façon une partie seulement d'une table ?

La première hypothèse serait d'utiliser la fonction RAND() de SQL Server qui génère un nombre aléatoire compris entre 0 et 1.

SELECT *
FROM Person.Address
ORDER BY RAND()

Même en exécutant plusieurs cette requête le résultat reste identique et non trié aléatoirement (le jeu de données est renvoyé dans l'ordre physique où il se trouve dans la base de données).

La raison est que le moteur réalise toujours une évaluation de la fonction une fois pour toute, pour la portée de la requête. Pour s'en convaincre, exécutez la variante de la requête ci-dessus :

SELECT *, RAND()
FROM Person.Address

La dernière colonne contient pour chaque enregistrement la même valeur. Plusieurs raisons à cela : conserver la consistance des données au travers de la requête, et conserver de bonnes performances. Peut de fonctions sont des exceptions à cette règles, voyons en une : NEWID()

NEWID() renvoie une valeur binaire de 16 octets générée par un générateur pseudo aléatoire. Cette valeur est belle est bien aléatoire et se veut unique (à 99,9999%), elle fait donc exception à la règle ci-dessus.

Testons notre requête avec cette variante :

SELECT *
FROM Person.Address
ORDER BY NEWID()

Le résultat est cette fois bien aléatoire et change à chaque exécution. Pour réaliser un tirage d'un certains nombres d'enregistrement par cette méthode, il faudra lui ajouter la clause TOP (à noter que les parenthèses sont optionnelles) :

SELECT TOP(1) *
FROM Person.Address
ORDER BY NEWID()

Cette méthode bien que faisant exactement ce que nous souhaitons peut se révéler gourmande en ressources dans la mesure où le moteur va être obligé de calculer un GUID pour chaque ligne dans la table, imaginez donc une table de 100 millions d'enregistrements.

SQL Server 2005 introduit une nouvelle fonction permettant de lister plus efficacement des données de manière aléatoire :

SELECT *
FROM Person.Address TABLESAMPLE(300 ROWS)

Cette fonction prend 2 types d'arguments, le nombre d'enregistrement souhaité ou le pourcentage d'enregistrements souhaités. Bien que très efficace cette fonction souffre tout de même d'un problème :

SELECT *
FROM Person.Address TABLESAMPLE(1 ROWS)

Testez cette requête plusieurs fois d'affiler et vous constaterez que de temps en temps vous aurez des résultats sous forme de plusieurs lignes, mais pas une, ou rien !

Le comportement de TABLESAMPLE est de filtrer au niveau des pages de la table (bloc de 8ko comprenant les données). Cette méthode bien que plus efficace que la notre, donne des effets surprenants, cela peut être gênant tout dépend de ce que vous souhaitez faire.

D'où l'idée de combiner les 2 :

    SELECT TOP(1) * 
    FROM Sales.SalesOrderDetail TABLESAMPLE(300 ROWS) 
    ORDER BY NEWID();

Ce qui fonctionne comme attendu… Attention le chiffres tu TABLESAMPLE est à déterminer de manière à ce qu'il renvoie toujours au moins un enregistrement, sinon la requête présente ne renverra aucuns résultats.

Et les performances dans tout çà (la table de test comprend 121000 enregistrements) ?

Requête à base de TOP et ORDER NEWID() seule :

• 1356 pages lues
• 249 ms de CPU consommé
• Sur 1000 exécutions 129 s de temps d'exécution

Requête à base de TOP et ORDER NEWID() + TABLESAMPLE :

• 3 pages lues
• 0 ms de CPU consommé
• Sur 1000 exécutions 750 ms de temps d'exécution

Clairement le TABLESAMPLE remporte la palme ici.

Bons tris…