Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002
    Posts
    1

    Question Unanswered: SQL optimisation question...

    I'm currently looking at optimising some slow sql and one of the changes suggested is to replace the line:

    where a.ORDER_NUM >= 53320

    with

    where ISNULL(a.ORDER_NUM, a.ORDER_NUM) >= 53320


    Can anybody suggest why this would be more efficient.
    I'd assume that because it runs the ISNULL function that it would be less efficient, but it does appear to work!

  2. #2
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    Frankly, I think that the advice pretends to
    force ASE *not* to use an index on ORDER_NUM.
    Perhaps there's a better access path that
    ASE had better using instead.

    I can imagin a situation where ORDER_NUM
    grows at high speed, so histogram
    statistics keep soon outdated. ASE's
    optimizer may think 53320 is so close
    to the maximum than the condition
    ORDER_NUM >= 53320
    is supposed to retrieve few rows; that
    is, ASE thinks ORDER_NUM is a good
    choice for an index access. But this
    could be wrong because now there are
    thousands of rows with ORDER_NUM
    higher than 53320

    Regards,
    Mariano Corral

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •