Post reply

Why does Query optimiser get this so wrong?

  • October 31, 2012 at 9:30 am

    #393509

    Hi guys,

    I have a query

    select

    C.caseno,

    X.caseno

    from

    cpcases C

    inner join

    caseexceptions x on X.caseno = C.caseno

    If I put SELECT TOP NNNN into the query and look at the estimated query plan it shows index scans and some paralalism (sp?) and takes about 4 seconds to run for 300,000 records. As soon as I up the Top NNN to 320,000 it introduces a Bitmap and the query performance drops off a cliff (killed the query after 15 minutes).

    Looking it up on the MSDN site it suggests using a JOIN HINT

    http://msdn.microsoft.com/en-us/library/ms173815(v=sql.90).aspx

    select

    C.caseno,

    X.caseno

    from

    cpcases c

    inner LOOP join

    caseexceptions x on X.caseno = C.caseno

    now the query returns me 1.1M rows in 16 seconds - 😀

    What would cause the optimiser to get this so wrong on first run

    If I let it run to term with the original query would the actual query plan be updated to reflect the join hint

  • October 31, 2012 at 9:46 am

    #1554678

    Statistics off? Can't say without seeing the actual exec plan.

    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 2 posts - 1 through 1 (of 1 total)

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