MS SQL Server optimizer and varying table and field aliases

Question

We have a lot of quieries for which we append a random alias at the end of field and table names (due to a custom ORM implementation that might be hard to change). The queries are like the following (though substantially more complex, most of the time):

SELECT fooA.field1 as field1B, 
       fooA.field2 as field1C 
  FROM foo as fooA

The suffixes A, B and C are randomly generated (and longer than one character). Will this hurt performance of our queries (i.e. will the optimizer not be able to recognize repeated queries due to the random part)? We mainly use SQL Server 2005.

Answer 1

Yes, the optimizer will need to reparse and recompile your query each time, since the query hash will change.

Answer 2

The Query Optimizer Engine uses the Execution plan which goes by ObjectId - the aliases are purely for programming purposes, but are not used during execution. So I do not think that performance will be affected by using different aliases or by using small or long aliases.

Quassnoi makes a good point about the rehashing of the query. Although the query performance itself is not impacted, the overall performance environment will be impacted.

Answer 3

If these are ad-hoc queries, then each will get compiled and cached as a separate query. In SQL' for a query to match to a cached plan, the text must be identical (down to case and white-space)

If you've got lots of queries that differ only in the table aliases then you're going to have very poor plan reuse, lots of compiles and a very big plan cache.

It's only stored procedures that match to cached plans by objectid