Coding an Owner Prefix to Avoid Recompilation

Editor's Note: Send your SQL Server questions to Richard Waymire, Microsoft's group program manager for SQL Server management tools, at [email protected]

A sentence in your June 2001 column caught my eye: "Recompilations might be the source of the slower stored procedure speed." You recommend coding the owner prefix on all reference tables, views, and procedures. Should I incorporate this step into my coding standards to avoid suffering a performance problem? Can you give me more information about the circumstances under which recompilation occurs?

Yes, you should always code the owner prefix if you know it. Let's consider an example of why coding the owner prefix can improve query performance. When user Fred calls stored procedure, and executes the query

SELECT col1 FROM table1

the query optimizer must decide whether to retrieve fred.table1 or dbo.table1. Then, when user Mark calls the same stored procedure, the query optimizer must recompile the query plan to determine whether the user wants mark.table1 or dbo.table1. If you code the SELECT statement as

SELECT col1 FROM dbo.table1

the query optimizer encounters no ambiguity and avoids recompilation.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.