Appliquer un CTE récursif sur les lignes d'une table groupée (SQL server 2005)

Question

J'ai un tableau (ROOMUSAGE) contenant les heures d'entrée et de sortie des chambres regroupées par PERSONNE et par CHAMBRE. Cela ressemble à ceci :

PERSONKEY | ROOMKEY | CHECKIN           | CHECKOUT         | ROW
----------------------------------------------------------------
1         | 8       |  13-4-2010 10:00  | 13-4-2010 11:00  | 1
1         | 8       |  13-4-2010 08:00  | 13-4-2010 09:00  | 2

1         | 1       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1
1         | 1       |  13-4-2010 14:00  | 13-4-2010 15:00  | 2
1         | 1       |  13-4-2010 13:00  | 13-4-2010 14:00  | 3

13        | 2       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1
13        | 2       |  13-4-2010 15:00  | 13-4-2010 16:00  | 2

Je veux sélectionner uniquement les lignes consécutives pour chaque regroupement PERSONKEY, ROOMKEY. Le tableau résultant souhaité est donc le suivant :

PERSONKEY | ROOMKEY | CHECKIN           | CHECKOUT         | ROW
----------------------------------------------------------------
1         | 8       |  13-4-2010 10:00  | 13-4-2010 11:00  | 1

1         | 1       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1
1         | 1       |  13-4-2010 14:00  | 13-4-2010 15:00  | 2
1         | 1       |  13-4-2010 13:00  | 13-4-2010 14:00  | 3

13        | 2       |  13-4-2010 15:00  | 13-4-2010 16:00  | 1

Je veux éviter d'utiliser des curseurs et j'ai donc pensé utiliser un CTE récursif. Voici ce que j'ai trouvé :

;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW)
as (select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU
    where RU.ROW = 1

    union all

    select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU inner join CTE on RU.ROW = CTE.ROW + 1
    where CTE.CHECKIN = RU.CHECKOUT
      and CTE.PERSONKEY = RU.PERSONKEY
      and CTE.ROOMKEY = RU.ROOMKEY)

Cela fonctionne bien pour les très petits ensembles de données (moins de 100 enregistrements), mais c'est inutilisable pour les grands ensembles de données.

Je pense que je devrais d'une manière ou d'une autre appliquer le cte de manière récursive sur chaque regroupement PERSONKEY, ROOMKEY de ma table ROOMUSAGE mais je ne sais pas comment faire.

Toute aide serait très appréciée,

A la vôtre !

Answer 1

Quelques éléments à essayer

• Au lieu de se joindre à chaque et en filtrant les résultats dans votre clause where, pourriez-vous essayer de réduire le nombre d'enregistrements directement dans la jointure pour accélérer les choses ?
• Ajoutez un index de recouvrement sur PersonKey, RoomKey, CheckOut & Row et voyez si cela améliore les performances.

Déclaration SQL

;with CTE (PERSONKEY, ROOMKEY, CHECKIN, CHECKOUT, ROW)
as (select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU
    where RU.ROW = 1

    union all

    select RU.PERSONKEY,
           RU.ROOMKEY, 
           RU.CHECKIN,
           RU.CHECKOUT,
           RU.ROW
    from ROOMUSAGE RU 
         inner join CTE on CTE.ROW + 1 = RU.ROW
                           and CTE.CHECKIN = RU.CHECKOUT
                           and CTE.PERSONKEY = RU.PERSONKEY
                           and CTE.ROOMKEY = RU.ROOMKEY
)