Sql Server does not allow for user defined constants. So we are forced to take other routes like hard-coded values, scalar udf's, settings tables, etc.
Unfortunately, all methods are not equal. You need to carefully consider your use of different methods, and test the results as all of these methods result in different approaches by the query optimizer.
We use an object factory that is tightly coupled to database tables. To simplify data access we have a single view that becomes, in essence, a partitioned table. We'll call it dbo.Object
This view looks something like this:
1
2
3
4
5
6
7
8
9
10
|
create view dbo.Object
as
select 1 as ObjectType_ID, ID, Description from dbo.[User]
union all
select 2 as ObjectType_ID, ID, Description from dbo.object2
union all
select 3 as ObjectType_ID, ID, Description from dbo.object3
union all
select 4 as ObjectType_ID, ID, Description from dbo.object4
--etc.. |
Now to query against that view, you can simply predicate on objecttype. If you check the IO statistics, it is only looking up against the one table, provided you query it properly. Don't bother looking at the Query Plan as it does not truly reflect what the server does to fetch the data.
So the idea is, we have a table which directly related concrete types in code to database object types. This way objects can very simply load themselves from the DB. Poor coding practices aside, for many applications this is a perfectly legitimate model.
The problem comes in when writing queries against these partitioned views. We were looking for a way to not have to hard-code type ID's into sprocs or queries. This is where constants would be a big help. I could potentially define a constant called @@UserType and equate it to it's corresponding database object type.
This way when writing a query to extract all user info, I could simply write:
1
|
Select * from dbo.Object where ObjectType_ID = @@UserType |
Here are the multiple ways we have tried to accomplish this, each has it's own unique performance outcomes:
First, turn IO statistics output on for your connection.
This query works properly, unfortunately we are hardcoding the ID, which is what we are trying not to do. This is the baseline. We want results identical to this.
1
|
Select * from dbo.Object where ObjectType_ID = 2 |
So lets try creating a UDF that returns that ObjectType_ID
1
2
3
4
5
6
|
create function [ObjectType].[User]()
returns int
as
begin
return(1)
end |
This query unfortunately evaluates each table, but at least it doesn't scan them
1
2
3
4
|
declare @objtype int
set @objtype = ObjectType.[User]() -- a udf that returns a value
Select * from dbo.Object where ObjectType_ID = @objtype |
This query is closer, but it is creating a working table and is evaluating for every row
1
|
Select * from dbo.Object where ObjectType_ID = ObjectType.[User]() |
I modified the UDF to take a useless param, in hopes that the deterministic decisions were based on the input param to output, but to no avail.
The results were same as above.
1
|
Select * from dbo.Object where ObjectType_ID = ObjectType.[User](0) |
I changed the UDF to take the string Type name as an input, and a simple CASE or IF statement is user to return the correct ID.
This would be optimal, as it would prevent me from having to create a UDF for each Type. I also tried it with looking up the value in a table.
Unfortunately we get the same result.
1
|
Select * from dbo.Object where ObjectType_ID = ObjectType.GetType('User') |
Lets try a subquery.
This is the worst, as it scans each table.
1
2
|
Select * from dbo.Object
where ObjectType_ID = (Select ObjectType.[User]()) |
Joining on a settings table is an option, unfortunately as we may be doing multiple joins on tables requiring these constants, we would have to do multiple joins on the settings table, which turns out to be very inefficient.
Chuck Heinzelman pointed out an intersting function to call to check if the UDF is being treated as deterministic:
1
|
select objectpropertyex(object_id('objecttype.user'), 'isdeterministic') -- returns 0 |
This returns
False
So taking Chuck's advice, I schema bound the function:
1
2
3
4
5
6
7
|
alter function [ObjectType].[User]()
returns int
with schemabinding
as
begin
return(1)
end |
Now, we try this again:
1
|
Select objectpropertyex(object_id('objecttype.user), 'isdeterministic') -- returns 1 |
Excellent, now it returns True.
Unfortunately, we get almost the same results if we re-run the queries.
I did not, however, profile it to see if it is truly evaluating the function for every row. I suspect it is not. So the use of the UDF may be not so bad if the only result is that it is using a worktable. Unfortunately, every place i use the UDF in a query, it creates another worktable.
So until we have a better CONST struct in SQL we will be forced to hardcode values for some of our queries.