I have a problem with a data retrieval query that uses three SQL Views, each based upon a remote table via a Linked Server object. The problem is that the query, a simple 3 table (actually view) join, that returns the top 200 rows, is taking over 3 minutes
The SQL server on which the Views are defined is linked to a Progress Database server, the Progress database on this server is also being used by a mission critical OLTP system. Each view is defined in a similar way, like this :
CREATE VIEW [VIEW_A] AS SELECT * FROM OPENQUERY(REMOTE_SVR,’SELECT * FROM [REMOTE_TABLE]’)
The REMOTE_SVR referenced above is the name of the Linked Server object, its connection to the actual remote server is via an ODBC driver.
I have looked at the query plan generated by the SQL Server and it is showing that 3
Remote Scans are taking place and appear to be bringing the full contents of the 3 remote tables across the network to be processed on the SQL Server. Where they are placed in a worktable before the join is performed.
Is this correct? Is it the case that the Views are nothing more than a direct connection thru to the associated tables on the Progress Server, and so for SQL Server to action the join it must first pull all the rows from each of the 3 tables on the remote
(linked) server? If this is the case I’m guessing the performance hit on the remote server and the network are potentially very significant. Not to mention the slow query itself, and the hit on the SQL Server resources. If were a complex query returning a
large result set the performance hit could be massive.
I would really appreciate some comments, hopefully to confirm if I’m right or wrong in thinking this is bad news. I am not currently in a position to redesign this setup as it is provided by a 3<sup>rd</sup> party, but If I can show the design
is inappropriate then I will have more influence, confirming my thoughts is the first step.