Home Forums SQL Server 2008 SQL Server 2008 Performance Tuning Query of 100 mil rows with multiple parameters RE: Query of 100 mil rows with multiple parameters

  • March 4, 2014 at 11:11 am

    #1693999

    Jeff Moden (3/2/2014)

    I guess my first recommendation would be to make the PK a nonclustered index. It's currently way too wide to be of any practical use as a clustered index and will cause BTree of the index on quotetype column to bloat quite badly (remember that all the columns of a clustered index are added to all nonclustered indexes) making it read many more pages per lookup than it should need to. I would put a new clustered index on year and week because that seems to be predominate in all of your queries.

    Yes - but I'd try PortFrom and PortTo as cluster keys because they may be more selective and also more common as leading edge in other indexes - promoting MERGE or NLIJ's over hash joins.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]