How to improve performance with a join between 2 table from 2 SQL Servers

I am making a ASP.NET web application that involves 2 SQL Server(A & B).

I created a view in SQL server A pointing to the table in SQL Server B. I found out my application will run REALLY slow when accessing such a view. so I try to avoid using them. But in the case of 2 table joining from 2 different SQL Servers, I have no choice.

Can anyone help me with this?

Thanks!

No you don't need a View which is a query rewrite but you need the OpenRowset function. Try the link below for more.

http://msdn2.microsoft.com/en-us/library/ms190312.aspx

|||that is exactly how I create my view(I used openrowset to create it). So what you suggest is just same thing as what I am doing. It is so slow, I don't know how to improve it.|||

Can anybody have any suggestion except using "OpenRowset" function? Because it is slow and it is what I am using.

I found out that the Collate Character set of the 2 SQL Server is different, will this affect the performance? If so, how could I change it?

Thanks!

|||

When you join between 2 seperate servers, it is possible that all of the data from one servers table is moving to the other server. Possibly even data that is not ultimately required by your join. This is what i would suspect as the cause of the performance issue.

Ive seen similar issue with Access applications ive supported when the Access db was joining a local Access table to a linked sql table. It usually worked ok if one of the tables was very small, but if both tables were large, the application would virtually grind to a halt.

My solution to speed it up was to prefetch exactly the data i needed from the remote server into a temp table in my local server. Then i would join to the temp table.

If i couldnt construct a query to get exactly what i needed, i would added as many constraints as possible to try to eliminate as much unnecessary data as possible from being pulled from the remote server.