How optimize queries with fully qualified names in t-sql?

Question

When I call:

select * from Database.dbo.Table where NAME = 'cat'

It takes:

200 ms

And when I change database to Database in Management Studio and call it without fully qualified name it's much faster:

select * from Table where NAME = 'cat'

It takes:

17 ms

Is there any way to make fully qualified queries faster without changing database?

Answer 1

Given that those two times are so different, it might just be quicker the second time because SQL has cached the query plan from the first time you ran it. Did you run those two right after each other? Even the same query with nothing changed will be much quicker the second time you run it.

If you want to get accurate execution times for the queries, you need to make SQL forget the query plans it has cached after each run. I guess restarting SQL is one way of doing that.

Answer 2

Most likely the difference is because the studio opens new connection to the database. To provide it - try to put 2 fully qualified sql statements - you should only see the difference for the first one. The second should run in the same connection

Answer 3

It occurred that the solution was to change Auto Close on this database to false.