Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Improve performance of View created referencing table on a different server using OPENROWSET Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 5:45 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:41 AM
Points: 151, Visits: 447
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!
Post #1349399
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse