June 30, 2016 at 3:46 pm
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
July 1, 2016 at 11:41 am
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".
July 1, 2016 at 11:42 am
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