问题描述
我正在通过链接服务器从 VIEWS 中提取大量数据.我使用的是 SQL Server 2012,链接服务器是 SQL Server 2008
I am extracting large amount of data via linked server from VIEWS. I am using SQL Server 2012 and linked server is 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
我期待 300K 行有将近 700 个 ID.以前需要几个小时,但现在需要 20 多个小时!!
I am expecting 300K rows for nearly 700+ IDs. before it used to take couple of hours but now its take more than a 20 hr!!
您能否为此 PAIN 提出任何替代解决方案?
Could you please suggest any alternative solution for this PAIN??
非常感谢!
推荐答案
当您使用由 4 部分组成的名称时,例如 [server].db.dbo.table,尤其是在 中join,很多时候整个表都通过网络复制到本地机器上,这显然不太理想.
When you use a 4-part name such as [server].db.dbo.table, especially in a join, often times the entire table is copied over the wire to the local machine, which is obviously not ideal.
更好的方法是使用 OPENQUERY -- 在源(链接服务器)处理.
A better approach is to use an OPENQUERY -- which is handled at the source (linked server).
试试:
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 过滤该行.
With this approach the linked server will return all rows for date > x, and then the local server will filter that by ID's in your local table.
当然,索引仍然会影响 SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01.
Of course, indexing will still play a factor for doing SELECT * FROM DB.TABLE.VIEW WHERE DATE>'2012-01-01.
我在大型子集上使用的另一种方法是将本地 ID 转储到远程服务器,然后远程处理它,例如:
Another approach, which I use on large subsets, is to dump the local ID's to the remote server, THEN handle it all remotely, such as:
-- 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 返回本地机器的远程查询(假设远程机器有本地作为链接).
If the local view is also large, then you may consider executing a remote query that uses an openquery back to the local machine (assuming the remote machine has the local as a link).
-- 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]