Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Improve performance of View created referencing table on a different server using OPENROWSET


Improve performance of View created referencing table on a different server using OPENROWSET

Author
Message
sql server developer
sql server developer
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 472
Hi All,

I would like to improve the performance of view created referencing table on a different server using OPENROWSET

I’ve created a view say, ViewA in ServerA that referances TableB in ServerB.

CREATE VIEW [dbo].[ViewA]
AS
SELECT Col1,Col2,Col3 FROM
OPENROWSET('SQLOLEDB','ServerB';'Login';'Pass',
'SELECT Col1,Col2,Col3 FROM DB.dbo.TableA
')

The view is running slow so inorder to improve performance I tried to create an index and ended up with the below error, saying cannot create index without SCHEMABINDING
CREATE UNIQUE CLUSTERED INDEX IX_ViewA ON [dbo].[ViewA] (Col1)

Error:
Msg 1939, Level 16, State 1, Line 1
Cannot create index on view 'ViewA' because the view is not schema bound.

When I try to create schema binding using ‘WITH SCHEMABINDING’ getting below error

Error:
Msg 1054, Level 15, State 3, Procedure vPRSummaryWBSList, Line 23
Syntax 'Openrowset/Openquery/Opendatasource' is not allowed in schema-bound objects.

Now my questions how can I improve the performance of the view created referencing table on a different server using OPENROWSET if I cannot have SCHEMABINDING which is required for INDEX?

I really appreciate if someone could assist in improving the performance of this view

Thanks in advance!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search