Apply a recursive CTE on grouped table rows (SQL server 2005).

Question

Hi all,

I have a table (ROOMUSAGE) containing the times people check in and out of rooms grouped by PERSONKEY and ROOMKEY. It looks like this:

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

I want to select just the consecutive rows for each PERSONKEY, ROOMKEY grouping. So the desired resulting table is:

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

I want to avoid using cursors so I thought I would use a recursive CTE. Here is what I came up with:

;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)

This worked OK for very small datasets (under 100 records) but it's unusable on large datasets.

I'm thinking that I should somehow apply the cte recursevely on each PERSONKEY, ROOMKEY grouping on my ROOMUSAGE table but I am not sure how to do that.

Any help would be much appreciated,

Cheers!

Answer 1

Some things to try

• Instead of joining with every row and filtering the results in your where clause, could you try if reducing the amount of records directly in the join speeds things up?
• Add a covering index on PersonKey, RoomKey, CheckOut & Row and see if it improves performance.

SQL Statement

;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
)