Post reply

My problem 'LIKE' Child

  • January 17, 2009 at 9:57 am

    #205540

    I'm getting an index seek on this, but it's taking up about 70 percent of my query time.

    UPDATE #events SET orderNo = ei.orderNo

    FROM #events e

    JOIN eventItems_rob ei ON ei.eventValue = e.serviceID

    WHERE e.orderNo = '[NONE]' AND ei.eventTime BETWEEN @fromDate AND @thruDate AND ei.orderNo <> '[NONE]'

    AND (ei.event = 'ServiceAddedToOrder' OR ei.event LIKE 'Search-%')

    AND ISNUMERIC(ei.eventValue) = 1

    Guessing it's cause of the like?

    I have a non clustered on orderno,event,eventime,eventvalue.

    Any ideas, I can't use a literal in place of the like.

    Should I try making it a clustered?

  • January 17, 2009 at 10:05 am

    #928698

    Please post the table and index definitions and the execution plan (saved as a .sqlplan file, zipped and attached to your post)

    I could guess without that info but it would be just a guess and probably wrong.

    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

  • January 17, 2009 at 12:08 pm

    #928719

    K, will do thanks Gail.

  • January 17, 2009 at 12:39 pm

    #928721

    Here it is..

    Thanks again Gail.

  • January 18, 2009 at 11:36 am

    #928944

    Ok.....

    Since the OrderNo (in event items) is a != in the query, it's absolutely senseless to have it as the leading column. It means that SQL has to do a range scan on that index for that, and can't seek on any of the other columns.

    Try making an index on event items that has Event as the leading column. The column is a guess, but try Event time as the second column, Order No as the third and put event value in the include columns. It's pointless in the key because it's used within a function.

    You can try switching the order of the 2nd and 3rd column, and see which is faster. I think the one I've given should be, but it's a guess based on insufficient info. You can also try adding Service Id to the index, as the second column, followed by Event time and order no. It may be useful, since it's the join column, but I don't think so, not with so many inequalities floating around.

    You also need an index on Event. It's a table scan right now. Try OrderNo, ServiceID

    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

Viewing 5 posts - 1 through 4 (of 4 total)

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