Professional Web Applications Themes

badly failed optimisation on simple query - Microsoft SQL / MS SQL Server

Is there an index on RelationType? "jan" <com> wrote in message ...

  1. #1

    Default Re: badly failed optimisation on simple query

    Is there an index on RelationType?

    "jan" <com> wrote in message 

    Ilya Guest

  2. #2

    Default Re: badly failed optimisation on simple query

    is there an index on all join columns?

    The views expressed here are my own
    and not of my employer.
    "Ilya Margolin" <com> wrote in message

    Kevin Guest

  3. #3

    Default Re: badly failed optimisation on simple query


    SQL-Server depends too much on statistics when determining its query
    plan to assess your situation. You have mentioned some of the relevant
    variables, such as indexes and number of rows. Some other elements are
    not mentioned, such as table size, choice of clustered index, state of
    statistics (up to date or not, samples or full scanned), etc.

    However, a fact to keep in mind: when the estimated execution time of a
    query plan is relatively low (such as 1 or 2 seconds), SQL-Server might
    decide not to 'waste' time by investigating potentially better query
    plans. The time needed to come up with a better plan would be more than
    the time saved during execution.

    I am afraid I don't have a real explanation for the unstable behavior
    towards the query execution (as long as it is exactly the same query
    which is not run with different parameters).


    jan wrote: 
    Gert-Jan Guest

  4. #4

    Default Re: badly failed optimisation on simple query

    Hi, I'm not sure about this at all, but since association is such a large
    table, but you only want a small section, what about selecting the bits you
    want into a temp table. Try inserting FromKeyCol and ToKeyCol into a temp
    table and then joining on that...

    My hunch is that SQL server is only using the non-clustered index or doing
    something silly with its indexing on the large table, its the only way to
    explain it.

    It could be doing something like joining all the tables compeletely and then
    throwing away all the records that dont match the where condition instead od
    using the where condition to minimise the records joined...

    Also, it may be worth throwing in a few Order By's just to see if you can
    get it to use the indexes you want it to use... sometimes the silliest
    things can work in silly situations =)

    Good Luck!

    "jan" <com> wrote in message 

    Terence Guest

  5. #5

    Default Re: badly failed optimisation on simple query


    Some questions:
    How big is the [association] table?
    Does the query plan show any bookmark lookups?
    Is RelationType an integer column?

    One reason for poor optimization could be that the
    optimizer assumes from statistics that it quickly will find rows
    satisfying RelationType = 40511 and RelationType = 5241
    no matter what order the rows of the table are inspected,
    only to discover that while there may be many rows satisfying
    this condition, they are all bunched at the wrong end of the
    table in the order of the index actually used.

    Is there any chance this is what's going on here? If so,
    forcing an index can be necessary. What is the order of
    joins used by the optimizer (since you say that forcing the
    order helps)

    -- Steve Kass
    -- Drew University
    -- Ref: 33E2632B-7712-493B-BEE9-A0751790BCA1

    jan wrote:

    Steve Guest

  6. #6

    Default Re: badly failed optimisation on simple query

    Gert-Jan Strik <nl> wrote in message news:<nl>... 

    I was thinking that on a very small table - like some of these, that
    it would be worth just doing a table scan (a couple of logical reads)
    rather than use an index and got through an unecessary extra read, but
    that is applicable to very small tables so I guess it's barely worth
    optimising for and they didn't bother.

    Hadn't considered that! I suspect that the padding is the default, and
    with a clustered index as far as I understand, there should be no
    fragmentation within the file (is that right?), but the file itself
    might be fragmented. I doubt it matters here as they are mostly so
    small, but I'll look into it on the rest of our tables.

    Thanks for spotting that. The code generator is going to get a drastic
    overhaul RSN.

    I presume this is adding redundant info for the optimiser - I'll try
    this if I rrun into this behaviour again.

    As you say, there's no point spending more time than necessary on
    this, and I'll have leave this one now.

    Thanks again to all who've helped,


    > <snip>[/ref]
    jan Guest

  7. #7

    Default Re: badly failed optimisation on simple query

    > with a clustered index as far as I understand, there should be no 

    FYI: even if you have a clustered index, after some usage (and no
    reindexing), you are likely to get fragmentation and unused space.

    You can get fragmentation if SQL-Server has to split pages to insert new
    data (if the new data does not fit on the existing page). This will
    increase the table size, and add fragmentation.

    You can get unused space if you delete rows. A page will only be freed
    if all rows are removed from it. Unused space will not decrease the
    table size.

    Gert-Jan Guest

  8. #8

    Default solution! (Re: badly failed optimisation on simple query)

    "Terence Siganakis" <> wrote in message news:<3f28dbab$>...

    Hi all,

    had a bit more time, so tried to follow up Terence's suggestion,
    specifically the suggestion that it was doing a mass join against the
    association table - about 1.2 million rows - and throwing away 99.999%
    of the results.

    So I put on an index on association of (RelationType, ToKeyCol) which
    dropped the time (ignring compilation overhead) down to virtually

    It seems to me that the optimiser should have selected a portion of
    the association table by the RelationType (which is the first column
    of the clustered index, and would have produced a sub-table of only a
    few dozen items - see stats in other post) and then scanned that for a
    matching ToKeyCol. Presumably it just wasn't and went a bit barmy


    jan (somewhat happier)
    jan Guest

Similar Threads

  1. generic array query optimisation help?
    By Nicklas in forum MySQL
    Replies: 2
    Last Post: June 28th, 07:07 PM
  2. Replies: 7
    Last Post: March 9th, 02:45 PM
  3. Query-based update failed
    By Don Paolo in forum MySQL
    Replies: 2
    Last Post: December 7th, 11:19 AM
  4. warning: pg_query(): Query failed
    By Aaron Steele in forum PostgreSQL / PGSQL
    Replies: 9
    Last Post: January 10th, 10:52 PM
  5. Pivot Table: Query Failed
    By Wilson in forum ASP Components
    Replies: 0
    Last Post: August 25th, 10:31 AM


Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139