2

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?

4

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."

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.