SQL关联服务器查询非常慢

Question

我正在通过联接服务器从视图中提取大量数据。我使用的是SQL Server 2012，而联接服务器是SQL Server 2008。

我的选择语句是：

SELECT * INTO MY_LOCAL_TABLE
FROM 
(    SELECT * FROM LINKEDSERVER.DB.TABLE.VIEW
     WHERE DATE>'2012-01-01' AND ID IN (SELECT ID FROM MY_LOCAL_VIEW) 
) Q

我预计会有将近700个ID的300K行数据。之前只需要几个小时，但现在需要超过20个小时！！

您能否提供任何这种问题的替代解决方案？

非常感谢您的帮助！

- arm

---

这些视图是否被索引了？如果没有，它们是否包含子查询？如果没有，您可以为这些视图添加索引。 - Pricey

如果视图之间相互调用，尤其是在链接服务器上，这总是一个性能问题。 - HLGEM

是的，视图已经正确地建立了索引。如果我使用OPENQUERY，会有什么区别吗？或者使用SSIS包？ - arm

在这种情况下的另一个问题可能是与链接服务器<2012 SP1的权限有关。请查看此链接和列出的第1个问题 - “权限不足”。链接：https://thomaslarock.com/2013/05/top-3-performance-killers-for-linked-server-queries/ - Mr Moose

Answer 1

当您使用类似 [server].db.dbo.table 这样的四部分名称，特别是在 join 中时，往往会将整个表格复制到本地机器上，显然这并不理想。

更好的方法是使用 OPENQUERY -- 这是在源（链接服务器）处处理的。

请尝试：

SELECT *
FROM OPENQUERY([LINKEDSERVER], 'SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01')
AND ID IN (SELECT ID FROM MY_LOCAL_VIEW) 

采用这种方法，关联服务器会返回日期大于x的所有行，然后本地服务器会通过本地表中的ID进行过滤。

当然，索引仍然在执行SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01'时起到了作用。

另一种我在大型子集上使用的方法是将本地ID转储到远程服务器，然后全部在远程处理，例如：

    -- copy local table to linked server by executing remote query 
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'SELECT ID INTO db.dbo.tmpTable FROM [SERVER].DB.DBO.MY_LOCAL_VIEW'
    EXEC(@SQL) AT [LINKEDSERVER]

   -- index remote table?!?
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'CREATE INDEX [IXTMP] ON db.dbo.tmpTable (ID)'
    EXEC(@SQL) AT [LINKEDSERVER]

    -- run query on local machine against both remote tables
    SELECT *
    -- INTO sometable
    FROM OPENQUERY([LINKEDSERVER], 'SELECT * 
                                    FROM DB.TABLE.VIEW
                                    WHERE DATE>''2012-01-01''
                                    AND ID IN (SELECT ID FROM db.dbo.tmpTable)')

    -- now drop remote temp table of id's
    DECLARE @SQL NVARCHAR(MAX)
    SET @SQL = 'DROP TABLE db.dbo.tmpTable'
    EXEC(@SQL) AT [LINKEDSERVER]

如果本地视图也很大，那么您可以考虑执行一个远程查询，使用 openquery 返回到本地机器（假设远程机器将本地作为链接）。

如果本地视图也很大，那么您可以考虑执行一个远程查询，使用 openquery 返回到本地机器（假设远程机器将本地作为链接）。

-- copy local table to linked server by executing remote query 
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = 'SELECT ID INTO db.dbo.tmpTable FROM OPENQUERY([SERVER], ''SELECT ID FROM DB.DBO.MY_LOCAL_VIEW'')'
EXEC(@SQL) AT [LINKEDSERVER]

- Dave C

---

+1 确实是一个不错的方法。我之前不知道这个（我的意思是 openquery 因素）。 - Rahul

嗯...这对我来说似乎是个好主意。我不确定我是否有权限在链接服务器上创建临时表，但我一定会尝试你的建议，因为链接服务器包含超过50K个ID！谢谢。 - arm

可以将ID放入真正的临时表(#table)中--每个人都应该有权创建它们，并一次性运行所有操作。您也可以在tempdb中创建用户表，不一定在工作数据库中。 - Dave C

谢谢你，我真的很感激！ - arm

如果您无法在tempdb中创建表格，则可以将所有ID连接在一起，并在openquery中使用'SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01 and ID in (' + @concatednatedListOfIds + ')'。您说列表并不是那么长。如果您的列表非常长，那么这可能不是一个好主意。 - Brad

Answer 2

其他人已经提到了索引。所以我不再赘述。如果您可以更改内部查询，建议另选一种选项。

 SELECT * FROM LINKEDSERVER.DB.TABLE.VIEW
 WHERE DATE>'2012-01-01' AND ID IN (SELECT ID FROM MY_LOCAL_VIEW)

使用内部联接（inner join）进行“joined”查询，因为您说有700多个inlist元素。 试一试。

   SELECT lnv.* FROM LINKEDSERVER.DB.TABLE.VIEW lnv
   inner join MY_LOCAL_VIEW mcv
   on lnv.ID = mcv.ID
   and lnv.DATE > '2012-01-01'

- Rahul

---

跨服务器连接经常导致整个表复制到本地计算机，这只适用于非常小的表，您可以承担此情况。 - Dave C

@JiggsJedi，这是非常正确的（再次提醒，我在我的答案中已经提到过...这是一个值得尝试的建议），但如果连接的表不够大，这将大多数时间提高性能（根据我的观察）。 - Rahul

Answer 3

你还可以尝试将查询语句包装在一个EXEC语句中，以避免将比必要更多的数据复制到你的服务器上。

EXEC('SELECT * FROM DB.TABLE.VIEW
     WHERE DATE>'+''''+'2012-01-01'+''''+' AND ID IN (SELECT ID FROM MY_LOCAL_VIEW)') AT LINKEDSERVER

- Rafael Llopis