2

Well, I know it is possible to select join tables that are in different database if those database are in the same server and security group, as discussed in this question Can we use join for two different database tables?

My question now is: Does select joins in different databases create performance issues, compared to select join in the same database?

Community
  • 1
  • 1
Guilherme de Jesus Santos
  • 4,955
  • 5
  • 43
  • 68

1 Answers1

4

I do not really think so. Since they are on the same server instance. When you query:

Select * from sys.sysprocesses, that will show you all the processes that running on that particular server across all databases. So from some points, we could think in such a way that all the pending processes from each database one the same server contributed to the process step. Also, if you right click the server instance, choose Activity Monitor, you could see the Processes and System resources as well as I/O related to that Server performance, not the database performance.

And if you look at the actual or estimated execution plan graphic icons for your query from the graphic execution plan, there is no such icon for database level but table level. All the graphic icons you need could be found here: https://technet.microsoft.com/en-us/library/ms175913(v=sql.105).aspx

I think it will only have the performance issue when you join the same tables from the other server instance because even they are the same table, the logic joining from another server is sending the query and get the result back from the server, which really depends on that server performance. For example, openquery() and four-part distributed query such as [Server].[Database].[Schema].[NameOfObject]. Those query will be sent to target server from your current server, and no matter it has Where condition or not, the select * will be executed on that server, which that server system decides how quick you can get the result.

LONG
  • 4,490
  • 2
  • 17
  • 35