A commonly overlooked little performance tip in SQL is addressing your objects (like tables, views, functions, procedures, etc.) using the schema (aka, the owner) name in front of the object.
For example, usually people will write a simple SMS query like this:
SELECT
sys.resourceID, sys.name0
FROM
v_R_System AS sys
WHERE
sys.Client0 = 1
AND sys.Obsolete0 = 0
When the SQL engine goes out to find the data here, it has to first determine what schema the v_R_System view belongs to which is admittedly pretty fast, but all things being equal takes a little more processing:
Is there a jnelson.v_R_System view? No.
Is there a dbo.v_R_System view? Yes.
OK, pull data from there.
Next, after the query is run, the way I understand it is that SQL will cache the query plan separately for each user that runs the exact same query. So there's no sharing of the cached execution plan and everyone incurs the query compilation cost.
So, the solution is to always include the schema when addressing your objects so that when the SQL engine goes out to find the execution plan it can say, "Oh, someone's already pulled that same data, lemme just get that for you..."
Here's how the above snippet should be rewritten then - the schema/owner shown in RED:
SELECT
sys.resourceID, sys.name0
FROM
dbo.v_R_System AS sys
WHERE
sys.Client0 = 1
AND sys.Obsolete0 = 0
One last note, if you address the same object multiple times in your query, make SURE you put the schema in front of ALL instances of ALL objects or you will still get hit with that query compilation.
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
