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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Performance Tuning » Improve performance of View created...

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

Rate Topic

Display Mode

Topic Options

Author

Message

sql server developer

Posted Thursday, August 23, 2012 5:45 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 4:07 PM
Points: 127, Visits: 359

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 »

Permissions