November 12, 2014 at 7:37 am
Hi!
I have a database from which I would like to move some tables(log tables) to another database.
However, some queries will need to query both databases. Based on some IDs, they will need to get some detailed info from the main database.
I will access both databases with same user.
Is there a big difference in performance when acessing from one database to another if the database is in the same instance?
And if the database is in a difference instance or in a different server?
Is this a good/bad practice?
I hope someone can provide some insights in this topic.
Thanks!
November 12, 2014 at 7:46 am
Linked servers typically perform worse than queries in the same instance.
Personally I try to avoid them at all costs.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 12, 2014 at 8:55 am
Even between two different databases in the same instance?
November 12, 2014 at 8:59 am
That is not a linked server. If you are on the same instance, and querying two separate databases, you should see very good performance.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
November 12, 2014 at 10:21 am
Koen Verbeeck (11/12/2014)
Linked servers typically perform worse than queries in the same instance.Personally I try to avoid them at all costs.
While that is probably true - linked servers tend to be a way of life in my world.
Where you can have a MAJOR impact on performance is to make sure you pay attention to where you execute the code from. In the case described, you want to execute the code on the "To" server, and pull the content you need using the link. In other words, if you're modifying data on server B based on data from server A, be sure to strcutre your query so that it is running on server B. Trying to run an UPDATE or insert on Server B "over the wire" from the context of Server A (i.e. "pushing" the change) is pretty much guaranteed to give you the absolute worse possible performance.
Using a Pull can be okay performance-wise in some scenarios assuming the network between the two is performant enough.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 13, 2014 at 4:40 am
sql.querying (11/12/2014)
Even between two different databases in the same instance?
It depends. One thing that the optimizer takes into account is foreign key constraints. Since you can't have those across databases, you could see poorer performance than for the same query within a single database. That's just one example. I suspect there are a few others if I thought about it for a while.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 13, 2014 at 6:16 am
Good point, the one that you mentioned. Thanks!
November 14, 2014 at 2:13 am
OK just out of curiosity
We have a similar scenario
A query that runs an inner join on a select statement on a linked server
If the linked server is unavoidable,what would some tips be to improve performance,I'm not directly involved with this but my colleague asked my opinion.
I suggested to either CTE or convert to a view the select statement on the remote server
November 14, 2014 at 4:02 am
Resender (11/14/2014)
OK just out of curiosityWe have a similar scenario
A query that runs an inner join on a select statement on a linked server
If the linked server is unavoidable,what would some tips be to improve performance,I'm not directly involved with this but my colleague asked my opinion.
I suggested to either CTE or convert to a view the select statement on the remote server
A CTE won't do anything to help performance. It's just a query.
Using OPENQUERY and passing parameters through that in order to put as much of the filtering work onto the remote server as possible is one of the ways I've seen the most performance improvements when working with linked servers.
By the way, you'll get more responses if you posted your own question. The only people who will likely see this are the ones who have responded to this thread.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2014 at 7:52 am
Grant Fritchey (11/13/2014)
sql.querying (11/12/2014)
Even between two different databases in the same instance?It depends. One thing that the optimizer takes into account is foreign key constraints. Since you can't have those across databases, you could see poorer performance than for the same query within a single database. That's just one example. I suspect there are a few others if I thought about it for a while.
Thanks Grant one more time for your answer.
Can you writte down other disadvantages of querying between two databases in the same instance, besides foreign key constraints?
Or if you could indicate me some articles to read about, I would appreciate.
Thanks!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply