Indexed views on double linked server

Question

A server SERVER_A has some data.

A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this:

CREATE view myview as
select * from 
openquery ( SERVER_A, select .... )

A server SERVER_C defines B as a linked server.

I have full access to SERVER_C, I can ask for some changes to SERVER_B, but not to SERVER_A. All my applications will access server SERVER_C, and need the data from SERVER_A. The views on SERVER_B are quite complex, and they cause timeout before returning anything.

I think that to improve the performance on these queries I need to turn the views on SERVER_B into "indexed views". To index a view, I must create a clustered index.

Questions:

1) Where should I define the clustered index? Should it be at SERVER_B, or I can somehow define it on SERVER_C?

2) Will the use of an indexed view on SERVER_B affect in any way the performance of SERVER_A even when the view is not being used?

Answer 1

You can't create an Indexed view on linked server table.

According to BOL http://msdn.microsoft.com/en-us/library/ms191432.aspx "The view must reference only base tables that are in the same database as the view."

Answer 2

To improve performance you may create a copy of data from server A on server C and update it on a schedule or use replication, if possible. Sometimes it works, if you don't need too 'live' data. With OPENQUERY whole dataset is transferred each time the query is executed.