August 31, 2010 at 9:26 am
Hello all,
Will using the 4 part naming convention for objects on the local instance cause unnecessary overhead?
I'm seeing a lot of stored procedures that do not do any work over a linked server, yet the objects are referenced using the 4 part naming convention, even when the object resides in the same DB as the proc and the schema is simply dbo. I ran profiler and didn't see a difference in logical IO between the 4 part and using just the object name. However, when I use a server name that is not the local instance and is not a linked server, I obviously get an error and logical IO for that call is 2 reads. So that leads me to believe that there may be overhead involved. How can I better see potential overhead?
Aside from being annoying, I was curious if there were any other pitfalls around this practice. Or, is this a recommended practice? I suspect that it is not, but would like your thoughts.
Thank you!
August 31, 2010 at 9:31 am
believe it or not, NOT declaring objects with 4 part naming actually increases the overhead, admittedly by a very very tiny bit; The SQL Engine has to lookup (or infer) the full name eventually;
just look at the xml of the execution plan of any statement....your SQL is converted from SELECT * FROM SOMETABLE to fully qualified four part names prior to execution...it's gonna get done whether you do it or let the SQL Engine do it.
the convenience of having easily readable short names by far outweighs any benefit of performance for me to bother with 4 part names;
if i' KNOW i'm in a database, i don't need to preface every object with the whole 4 part name.
I only add servername/dbname/schemaname when needed.
I've only really tripped over issues when i had multiple schemas with the same object name; the dbo.object gets selected by default (assuming it exists) if there are multi objects....so stuff with schemas have caused me a headache or two.
Lowell
August 31, 2010 at 9:43 am
Hey thanks for the info Lowell. I would have guessed the opposite - that the optimizer first checks the current db/schema and then if not found there, keep checking on up the chain.
I did take a look at two simple select count(*) queries, one with the 4 part name and the other with just the object name. I wasn't able to see what you mean by the statement is converted to the 4 part name. I do see something along those lines in the object node, child of RelOp node:
<RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1110" EstimateIO="0.00460648" EstimateCPU="0.001378" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00598448" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList />
<IndexScan Ordered="0" ForcedIndex="0" NoExpandHint="0">
<DefinedValues />
<Object Database="[server]" Schema="[dbo]" Table="
" Index="[PK_tablekey]" />
</IndexScan>
</RelOp>
Is that what you're talking about? Thanks again!
August 31, 2010 at 9:59 am
yes that was what i was refering to, i could/should have been more precise, sorry;
with a COUNT, the OutputList has no values, select a field instead to see stuff in that XML collection.
you can see in the OutputList, as well as DefinedValues lists that all the objects eventually get 4-parted; when you get some stuff with joins on them(ColumnReference) , you'll see the execution plan has the join info 4 parted as well; as far as i know, everntually everything except constants (*SELECT 'Yes') get defined eventually.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply