patternsqlMinor
Indexed views on double linked server
Viewed 0 times
viewsindexeddoubleserverlinked
Problem
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:
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?
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?
Solution
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."
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."
Context
StackExchange Database Administrators Q#80125, answer score: 5
Revisions (0)
No revisions yet.