September 20, 2017 at 6:13 pm
in our Azure environment, we are using some auto-generated code in our DACPAC deployments
(code sample attached)
problem is that the code uses
Objectproperty( [o].[object_id], N'IsSystemTable' ) = 0
in the where clause
- might be better if the code used the TYPE column (type = 'U') as the sys.OBJECTS table is already being joined to....
we have alot of tables in the database and this WHERE condition takes the excution to be from seconds to hours...
would like to change the code but it is generated...
any thoughts / ideas on how to speed this up ??
thx
September 21, 2017 at 6:53 am
Not likely to happen as long as that code is being used. Functions in a WHERE clause make the query unable to use indexes, because it's no longer "SARGABLE". Unless you can force that query to operate another way, you've got a problem. If you can, you might be able to get that function to be in a SELECT into a temp table, and then you can index that and JOIN to it with the WHERE clause filtering for the desired value.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 21, 2017 at 8:10 am
I think it's very possible that o.type = 'U' would speed it up, rather than using OBJECTPROPERTY.
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".
September 21, 2017 at 8:12 am
ScottPletcher - Thursday, September 21, 2017 8:10 AMI think it's very possible that o.type = 'U' would speed it up, rather than using OBJECTPROPERTY.
I interpreted the post saying this is "generated code", as meaning it's not changeable. However, there's plenty of room for doubt on that... and I do agree with you.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 21, 2017 at 8:16 am
sgmunson - Thursday, September 21, 2017 8:12 AMScottPletcher - Thursday, September 21, 2017 8:10 AMI think it's very possible that o.type = 'U' would speed it up, rather than using OBJECTPROPERTY.I interpreted the post saying this is "generated code", as meaning it's not changeable. However, there's plenty of room for doubt on that... and I do agree with you.
Yep. Even if they can't change the code it gens, they might be able to replace the string "Objectproperty( [o].[object_id], N'IsSystemTable' ) = 0" with "o.type = 'U'" in the code before creating/altering it. May not be easy to do, but it could be worth it.
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply