Select TOP – déterminisme et ORDER BY

Pour faire suite au post de Christian sur le Select TOP, je voudrais apporter ma pierre à l’édifice du TOP. Et militer pour la présence du ORDER BY.

Le mot clé TOP qui cermet de retourner les n premières lignes d’un jeux de résultat peut être utilisée sans option ORDER BY. Cependant, je déconseille cette pratique, à fortiori en production.

En effet, un telle requête n’est pas déterministe. Les enregistrements renvoyés peuvent varier !!! Je m’explique.

Le “SELECT TOP N * FROM ma_table” , donc sans ordre ORDER BY, va renvoyer les enregistrement dans un ordre arbitraire ce qui n’est pas vraiment souhaitable. Enfin, un ordre arbitraire, pas tout a fait comme nous allons le voir mais cela reste néanmoins à éviter en production.

Voici une table tout a fait classique :

CREATE TABLE [dbo].[Orders](
    [orderid] [int] NOT NULL,
    [custid] [char](11) NOT NULL,
    [empid] [int] NOT NULL,
    [shipperid] [varchar](5) NOT NULL,
    [orderdate] [datetime] NOT NULL,
    [filler] [char](155) NOT NULL,
CONSTRAINT [PK_Orders] PRIMARY KEY NONCLUSTERED
    (    [orderid] ASC ) WITH (PAD_INDEX  = OFF,
        STATISTICS_NORECOMPUTE  = OFF,   
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS  = ON,
        ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];

Notez la présence de la contrainte Primary Key implémentée avec un index non cluster.

On créé un index cluster sur la date de commande (orderdate) :

CREATE CLUSTERED INDEX [idx_cl_od] ON [dbo].[Orders]
( [orderdate] ASC )
WITH ( PAD_INDEX  = OFF,
        STATISTICS_NORECOMPUTE  = OFF,   
        SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF,
        DROP_EXISTING = OFF,
        ONLINE = OFF,
        ALLOW_ROW_LOCKS  = ON,
        ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY];

Cette table contient 1.000.000 d’enregistrement.

Si j’exécute la requête suivante :

image

J’obtiens 3 enregistrements, dont les numéros de commande (Clé primaire) ne se suivent pas.

Si je recherche les pages de données où sont stockés mes enregistrements j’obtiens :

SELECT TOP 3 plc.*, o.*
FROM [Performance].[dbo].[Orders] AS o
Cross Apply sys.fn_physLocCracker (%%physloc%%) AS plc

image

Mes enregistrements sont tous stockés dans la même page de donnée, la numéro 63.032.

Si je relance cette même requête en triant par numéro de commande :

image

Je constate cette fois ci que les enregistrements ne sont pas consécutifs.

Même requête avec un tri par date de commande :

image

Je retrouve les 3 enregistrements de mon SELECT d’origine.

Ainsi donc, le mot clé TOP ne revoie pas tout à fait les n premiers enregistrements de manière aléatoire, mais il semble suivre l’ordre lié à l’index cluster. Rien a voir donc avec la notion de clé primaire c’est bien lié à l’index cluster.

Creusons encore. Un bout de code permettant d’afficher le contenu d’une page de données :

DBCC TraceOn (3604);
DBCC Page (Performance, 1, 63032, 3);
DBCC TraceOff (3604);

Vous aurez reconnu le numéro de page impliqué dans la recherche de mon TOP 3 sans ORDER BY. Je tronque le résultat de manière à aller à l’essentiel :

Page @0x10D78000

m_pageId = (1:63032)                 m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 49     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594041139200                                
Metadata: PartitionId = 72057594040090624                                 Metadata: IndexId = 1
Metadata: ObjectId = 117575457       m_prevPage = (0:0)                   m_nextPage = (1:63033)
pminlen = 186                        m_slotCnt = 40                       m_freeCnt = 40
m_freeData = 8072                    m_reservedCnt = 0                    m_lsn = (550:2007:15)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = -826209790             

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED          
PFS (1:56616) = 0x40 ALLOCATED   0_PCT_FULL                               DIFF (1:6) = NOT CHANGED
ML (1:7) = NOT MIN_LOGGED           

Slot 0 Offset 0x60 Length 196

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 196                   
Memory Dump @0x6709C060

Slot 0 Column 0 Offset 0x0 Length 4 Length (physical) 0

UNIQUIFIER = 0                      

Slot 0 Column 5 Offset 0x4 Length 8 Length (physical) 8

orderdate = 2004-12-02 00:00:00.000 

Slot 0 Column 1 Offset 0xc Length 4 Length (physical) 4

orderid = 160                       

Slot 0 Column 2 Offset 0x10 Length 11 Length (physical) 11

custid = C0000004833                

Slot 0 Column 3 Offset 0x1b Length 4 Length (physical) 4

empid = 370                         

Slot 0 Column 4 Offset 0xc3 Length 1 Length (physical) 1

shipperid = I                       

Slot 0 Column 6 Offset 0x1f Length 155 Length (physical) 155

filler = a                                                                                                      

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (b200f5ebc8c9)       
Slot 1 Offset 0x124 Length 200

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 200                   
Memory Dump @0x6709C124

      

Slot 1 Column 0 Offset 0xc3 Length 4 Length (physical) 4

UNIQUIFIER = 1                      

Slot 1 Column 5 Offset 0x4 Length 8 Length (physical) 8

orderdate = 2004-12-02 00:00:00.000 

Slot 1 Column 1 Offset 0xc Length 4 Length (physical) 4

orderid = 150                       

Slot 1 Column 2 Offset 0x10 Length 11 Length (physical) 11

custid = C0000002199                

Slot 1 Column 3 Offset 0x1b Length 4 Length (physical) 4

empid = 292                         

Slot 1 Column 4 Offset 0xc7 Length 1 Length (physical) 1

shipperid = G                       

Slot 1 Column 6 Offset 0x1f Length 155 Length (physical) 155

filler = a                                                                                                      

Slot 1 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (b20057d13a9b)       
Slot 2 Offset 0x1ec Length 200

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 200                   
Memory Dump @0x6709C1EC

               

Slot 2 Column 0 Offset 0xc3 Length 4 Length (physical) 4

UNIQUIFIER = 2                      

Slot 2 Column 5 Offset 0x4 Length 8 Length (physical) 8

orderdate = 2004-12-02 00:00:00.000 

Slot 2 Column 1 Offset 0xc Length 4 Length (physical) 4

orderid = 420                       

Slot 2 Column 2 Offset 0x10 Length 11 Length (physical) 11

custid = C0000004498                

Slot 2 Column 3 Offset 0x1b Length 4 Length (physical) 4

empid = 366                         

Slot 2 Column 4 Offset 0xc7 Length 1 Length (physical) 1

shipperid = A                       

Slot 2 Column 6 Offset 0x1f Length 155 Length (physical) 155

filler = a                                                                                                      

Dans l’entête de la page nous avons la mention “m_prevPage = (0:0)” indiquant qu’il n’y a pas de page précédent celle ci. Nous sommes donc sur la première page de donnée de la table.

J’aurais tout aussi bien pu utiliser DBCC IND (‘Performance’, ‘Orders’, 1);
Cette commande me revoie la liste des pages avec leur chainage.
L’extrait correspondant à la page 63.032

PageFID PagePID IAMFID IAMPID ObjectID IndexID PartitionNumber PartitionID iam_chain_type PageType IndexLevel NextPageFID NextPagePID PrevPageFID PrevPagePID
1 63032 1 1819 117575457 1 1 7,21E+16 In-row data 1 0 1 63033 0 0

On voit clairement qu’il n’y a pas de page précédente, colonne PrevPagePID égal à zéro.

Cette première page contient donc les premiers enregistrements dans l’ordre de l’index cluster. Cette colonne étant une date, rien ne dit qu’a postériori, je ne vais pas ajouter d’enregistrement devant se “placer” avant ma commande la plus ancienne (récupération de données depuis une vieille application …). Et là, les données renvoyées ne seront pas celles que nous venons de voir. D’où le non déterminisme du TOP utilisé dans ORDER BY. Si j’ajoute une commande au 31/12/2003, sachant que j’ai de l’espace dans ma page de donnée tout se passe bien, sinon Page/split … Mon enregistrement est bien ajouté en fin de page, mais la table d’offsets effectue son travail de tri (le slot 0 a un décalage supérieur au slot 20) :

image

image

J’espère que vous êtes convaincus du non déterminisme du TOP utilisé sans ORDER BY. Certes cela renvoie les enregistrements dans l’ordre de l’index cluster, mais imaginez même que pour une raison quelconque vous ayez à changer d’index cluster sur une table. Le résultat de votre SELECT n’aurait plus rien à voir…

Quelles seraient donc les raisons (valables, hum hum …) qui pourraient nous pousser à exécuter sciemment ce genre de requête ? Voici mes suggestions, libre à vous de commenter, réfuter ou ajouter des propositions :

  • Savoir si la table contient des données …
  • Avoir un aperçu rapide de la structure de la table …
  • Et une option un peu plus pertinente : la création d’une table ayant une structure identique à la première … Pour cette dernière option, je recommande d’utiliser “ SELECT TOP (0) * INTO ma_table_copy FROM ma_table”. La structure sera copiée et vous n’aurez pas “réellement” exécuté la requête, même si dans l’ordre d’exécution le TOP apparaît tardivement (avant dernière position, suivi par le prédicat ORDER BY, sachant que le TOP va s’exécuter avec, si celui ci est présent), il ne génère pas de lectures disque car cela revient à faire une recherche sur des données système.

image

image

Cela se traduit pas un CONSTANT SCAN …

image

D’où une petite économie en lectures disque et en verrouillage …. Mais certainement un bon réflexe a acquérir !

A propos Christophe

Consultant SQL Server Formateur certifié Microsoft MVP SQL Server MCM SQL Server 2008
Cet article a été publié dans SQL Server. Ajoutez ce permalien à vos favoris.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s