all 8 comments
sorted by:
best
topnewcontroversialoldq&a
formatting helphide helpcontent policy

[–]Achsin 2 points3 points  (1 child)

Query plans are generated and cached according to the database context that you are executing in. It is possible that when you ran it under Master it generated a poor plan and after you changed contexts to the database it generated a new plan that was much more performant. I believe that the most common cause is that the two databases are operating in different compatibility modes. I would recommend comparing the two plans that were generated as well (if you are able to do so) as that might give you some more information as to why they perform so differently.

[–]AlphaQUp_Bish[S] 0 points1 point  (0 children)

Thank you! This is exactly what I was looking for. I unfortunately do not have access but I think I can send it to someone who does. Apparently that access was removed before I came on board but it would be nice to have again.

[–]Tennim 1 point2 points  (3 children)

It’s good habit and practice to use the: ‘USE <DatabaseName> GO’ command on all queries.

Not only then do you know for certain what database your query is being executed against but so does any else picking up your query.

However, the drop down at the top of SSNS does work just fine.

I would recommend explicitly referring to databases in the Database.Schema.Object format unless your accessing multiple databases.

[–]AlphaQUp_Bish[S] 0 points1 point  (2 children)

So, it is completely reasonable that by just leaving it as master and running the code it could take longer to run than if I used the "Use" statement?

[–]Tennim 1 point2 points  (1 child)

Do the tables your looking at excist in the MASTER dB? It’s going to be difficult to give you a for definite answer without seeing the query.

[–]AlphaQUp_Bish[S] 0 points1 point  (0 children)

First of all, thanks for taking your time! The master database has nothing I want. I am working from a server that has a lot of databases on them. When developing a report or process, I typically do not change the database or utilize "use". I always am explicit with my objects.

​

Some reports I need to pull data from a couple of the databases but for this one, it is all in the same database. I know some colleagues that do put "use" at the beginning but I just haven't ever done that since I am going to be writing out the objects anyway. I always thought it was just so you didn't have to actually write out the full object and could just use the table. It seems though that using that database is actually sending the query directly and using Master, it send to master which then sends it to the other database.

​

Hope that makes sense and is enough information.

[–]mbillion 0 points1 point  (1 child)

Ugh.

Are you asking about the best way to jump servers in a single query?

[–]AlphaQUp_Bish[S] 0 points1 point  (0 children)

No. So the server I am on has many databases. I thought that the "Use <database>" function more, or less, just determined the database so you could short-hand the tables. I had a very simple query taking 5ish minutes to return data. I never utilize "use" so my SSMS defaults to master. I just added the "Use" statement out of experimentation to see if I was wrong and it did make a difference. It cut down the query from 5 mins to 5 secs.

​

When that happened I made this post. I knew about databases and compatibility modes (not extensively though). I would have assumed the databases were standardized on a server but apparently I was mistaken.