Subscribe Feed (RSS)Prefix tables with dbo. in your SQL
May 29th, 2007 by Sameer | Filed under SQL.Here’s a SQL power tip - to speed up your query and stored procedure executions a bit. Prefix your tables and stored procedures with dbo.
Select * from dbo.[Users] where UserID = @UserID
or
EXEC dbo.UsersLookup ….
By prefixing with dbo, Our database makes one less call. Normally, when you do not use the dbo keyword, on a query such as Select * from Users where UserID = @UserID, it will first check the user’s schema to see if that table exists for them.
i.e. if you are logged on as server administrator (sa), it will attempt to do a
Select * from sa.Users where UserID = @UserID
Then, if that table does not exist under sa, it will try again under dbo (main schema)
Normally, this is 1 extra redundant call that you want to avoid.
This helps the database engine encourage re-use and caching of queries. The database engine will have an easier time recognizing that this is the same query that was just called, and will be more likely to cache and re-use your execution and decrease execution run time.
