Post reply

CTE is not just an expression

  • June 12, 2009 at 3:06 am

    #222455

    Consider a query like this

    select K.id, K.description, coalesce(K.state, M.state, (select state from dbo.states where label='1' and master_id=K.master_id)) as AState, S.description

    from dbo.detail K join dbo.master M on M.id=K.master_id

    left outer join dbo.states S on S.state=coalesce(K.state,M.state,(select state from dbo.states where label='1' and master_id=K.master_id))

    Since sqlserver does not allow use of alias (namely AState), I had to copy the coalesce expression, so I replaced this with CTE:

    with detail0 as (select K.id, K.description, coalesce(K.state, M.state, (select state from dbo.states where label='1' and master_id=K.master_id)) as AState

    from dbo.detail K join dbo.master M on M.id=K.master_id)

    select K.*, S.description from detail0 K

    left outer join dbo.states S on S.state=K.AState

    Surprisingly, the CTE variation runs a lot faster (~100x).

    I've read many times that CTE is just what name says: expression, however, in some cases it helps the engine properly optimize the query plan. The first plan has a node with "index spool (eager spool)" 96% for the subselect expression and timing is ~half sec. The second query plan has a node with "index spool (eager spool)" 55% for the subselect expression and timing is 7 msec.

    Unless I missed something...

  • June 12, 2009 at 10:32 pm

    #1008999

    What helped it was that you got the COALESCE out of the WHERE clause.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply