Trying to optimize performance of query having Hash Matches & Agreegate

  • For large amounts of rows nested loops are usually HORRIBLE performers. if you are hitting lots of data HASHING (or MERGING) is best. If you have lots of data - you might simply need better hardware. Have you done a fileIO stall and wait stats analysis to see if you need better IO throughput, better tempdb performance, more RAM, more CPU power??

    Note that you can FORCE nested loop joins in a copy of your views if you think those are better.

    Also, why the top 100 % ORDER BY in the views? a) this is a "hack" that is no longer supported and b) it is a performance killer due to sorting. Best have lots of RAM and some serious tempdb capabilities for that.

    Likewise your UNION statements. Those carry both a SORT and DISTINCT operations under the covers. As someone else said, if you KNOW there CANNOT be duplicates, or you don't care about them, use UNION ALL instead.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing post 16 (of 16 total)

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