none
在本地和linked Server之间的查询 RRS feed

  • 问题

  • 我有一个查询用来从另外一个server上提取数据 并插入本地数据库

    这是个每日都要运行的job,一直很少有问题,最近这个 job 不工作了,导致问题的是一个查询,花很长的时间然后失败

    查询语句:
    SELECT a。*
    INTO dbo.LOCALTABLEa
    FROM LINKEDSERVER.dbo.TABLEa AS a, LOCALTABLEb AS b ON a.c1 = b.id

     LINKEDSERVER.dbo.TABLEa(c1) 上有索引,不到4,500,000条记录
    LOCALTABLEb(id)是主键, 不到20,000条记录

    错误信息:
    Server: Msg 11, Level 16, State 1, Line 1
    General network error. Check your network documentation.

    单独查询 LINKEDSERVER.dbo.TABLEa 和 LOCALTABLEb 都没有问题,速度正常


    环境: Windows 2003 SP2
    SQL Server 2000 Standard Edition (8.0.760)



    2010年2月28日 23:17

答案

全部回复

  • Sounds there was network connection issue between servers, need restart sql at least to clean it up.
    2010年3月1日 0:00
  • 我这边的 server没有做任何软硬件的修改

    对方的server 不在我的控制之下,是否做过任何变动,不太清楚,而且我也没办法去查看。

    有没有什么更明确的方法去找到根据是对方的设置或者硬件变动造成的 问题?

    谢谢回复!
    2010年3月1日 0:19
  • Reboot your server first, contact remote server admin if rebooting doesn't fix the problem.
    2010年3月1日 4:03
  • I did reboot our server but nothing changed.

    the thing is that from our end I can query the same table which is in the database of linked server with no problem if I don't JOIN it with our local table.

    However, when i JOIN them up, performance dramatically declines. It takes more than 1.5 mins to return 100 records.

    Now I tried to replace the local table with a table variable. I got 10000 records returned in 1 min.

    Hopefully it will work. I am going to try tonight.

    Still don't know the reason underneath.....


    Thank you, rmiao!


    2010年3月3日 3:37
  • Tried replace join with sub-query?

    2010年3月3日 4:10
  • Yes, I have tried sub-query but it did not work.

    I got the problem fixed now. 

    What I did is just to replace the local table with a table variable, insert the local table into the table variable and then JOIN the remote table with the table variable.

    It seems SQL Server query performance sharply decline when trying to query 2 tables which locate in the 2 different Servers ( but meanwhile I can get result quickly from a query by joining local table with another remote server, so I think there gonna be some issues with the server in the other end..) 
    2010年3月3日 23:21