Post reply

Query seems very slow for what its doing

  • June 12, 2009 at 4:15 am

    #1008487

    Thanks to both for your help. Will experiment and let you know the outcome.

    Regards,

  • June 12, 2009 at 7:31 am

    #1008620

    This part of the code is going to cause problems:

    WHERE

    p.RegionID = COALESCE(@RegionID, p.RegionID)

    AND

    p.PriceBracketID = COALESCE(@PriceBracketID, p.PriceBracketID)

    If you get index use ata ll on this table, it's going to be an index scan instead of an index seek.

    For max performance on a catch-all query, as much of a pain as it is, you'd be better off with ad hoc queries.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • June 12, 2009 at 10:11 pm

    #1008995

    I'm not sure why people think they need to do everything in a single query. Use the input parameters to get only the data you need from the properties table and jam it into a temp table. Then, you join to that relatively small temp table and things will move on rather smartly.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 3 posts - 16 through 17 (of 17 total)

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