Guess I'm a tard.

  • This seems simple enough.

    SELECT DISTINCT orderID, orderNo, source FROM eventitems_rob WHERE event = 'OrderCreated'

    taking two minutes to run..

    I add a nonclustered on event..no help.

    What am I missing?

  • krypto69 (1/17/2009)


    What am I missing?

    An attached execution plan, along with the table definition, the index definitions and, if possible, an estimate of the number of rows in the table, the number that satisfy the where clause and the number that the query (with distinct) returns.

    Edit: Is there blocking?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • In fact, you'd probably hurt performance with just an index on Event, depending on where the cluster index is on the table (if there's a clustered index) because you're likely getting a bookmark lookup. That doesn't even mention the fact that the DISTINCT clause is going to cause aggregation. Like Gail says, it's hard to say more without an execution plan.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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