SP with 3 parameters as delimited list - Goal 500 milliseconds

  • Did you fix anything?? Looks mostly the same to me, just fatter tables and now you have spills on the big HASH join and a bajillion row nested loop.

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

  • If you:

    1) always, or

    2) almost always, or

    3) most critically

    search these tables using RECORD_TYPE_CD, as in this query, then you should cluster those tables first on RECORD_TYPE_CD. That alone might solve your performance issue here (and help most other queries as well). [But if the _UID columns are GUIDs, you'll still have table fragmentation issues, of course.]

    Also, don't use COLLATE to convert the data column in the table; instead, if you need to, convert the static values in the @lists table. That is, instead of:

    OI.ORDER_NUM collate Latin1_General_BIN IN (select value from @lists where paramname = '@ORDER_NUM')))

    use:

    OI.ORDER_NUM IN (select value /* Collate clause here if required */ from @lists where paramname = '@ORDER_NUM')))

    Same for BOR_ORDER_NUM and BOR_ORDER_GROUP_ID (esp. ID -- that column's a numeric data type, right?).

    As other(s) have noted, please post the table definitions.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • If you:

    1) always, or

    2) almost always, or

    3) most critically

    search these tables using RECORD_TYPE_CD, as in this query, then you should cluster those tables first on RECORD_TYPE_CD. That alone might solve your performance issue here (and help most other queries as well). [But if the _UID columns are GUIDs, you'll still have table fragmentation issues, of course.]

    Also, don't use COLLATE to convert the data column in the table; instead, if you need to, convert the static values in the @lists table. That is, instead of:

    OI.ORDER_NUM collate Latin1_General_BIN IN (select value from @lists where paramname = '@ORDER_NUM')))

    use:

    OI.ORDER_NUM IN (select value /* Collate clause here if required */ from @lists where paramname = '@ORDER_NUM')))

    Same for BOR_ORDER_NUM and BOR_ORDER_GROUP_ID (esp. ID -- that column's a numeric data type, right?).

    As other(s) have noted, please post the table definitions.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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