这两个的效果是一样的吗？

Question

第一：

我在远端电脑上通过DBlink操作另外一个数据库中的数据，而且也全是远程数据库中的表的Join。

第二：

在第二个数据库中建立一个存储过程执行相应的操作，然后再远端调用这个存储过程。

这两个效率是一样的吗？

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 1

Second method is better because join via linked server may not use proper index.

Answer 2

Second method is better because join via linked server may not use proper index.

我这边有一个存储过程，分为两步：

首先是在远端数据库中查找出数据然后insert到本地的数据库，查找的时候都是远端数据库的表进行join、

第二部是在远端数据库中的删除对应的数据，这一部分相当慢，笔第一步慢了20倍左右。

 

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 3

How do you delete rows? Delete statement against linked server?

Answer 4

How do you delete rows? Delete statement against linked server?

对，因为都是在远程操作，所以都是通过DBLINK来进行的

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 5

      DELETE [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestSWS_BAND_UNIFORMSummary       FROM [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestSWS_BAND_UNIFORMSummary AS P join [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestHeader L     on P.Lottestid=L.Lottestid and P.lotid =L.Lotid     inner join [FIS-ARCHIVE].MetroChina_ARC.dbo.w_d_archive_chips  T     on L.Lotid=T.Lotid and L.Waferid=T.Waferid and L.Chipid=T.Chipid   

这是删除的语句

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 6

Tried call remote sp for deleting? 

Answer 7

Tried call remote sp for deleting? 

还没开始测试这一部分。

我现在先在远程select这部分数据，花费时间不到5秒钟，总共120笔。

然后在远程手动执行delete的这部分，到目前为止已经花费时间10分钟，还没删除完成。----已经完成 花费时间42min

等会在测试下，在远程数据库本地进行删除试试。---已经完成，花费时间不到2秒钟

看起来应该是由于是通过DBLink删除导致速度很慢。

之后再测试在远程数据库建立存储过程，再在另外个数据库调用这个存储过程的速度

 

另外有个疑问，如果是在远程执行

SELECT P.*     FROM MetroChina_ARC.dbo.LotTestSWS_BW AS P join MetroChina_ARC.dbo.LotTestHeader L     on P.Lottestid=L.Lottestid and P.lotid =L.Lotid     inner join MetroChina_ARC.dbo.w_d_archive_chips  T     on L.Lotid=T.Lotid and L.Waferid=T.Waferid and L.Chipid=T.Chipid   

这个不到五秒钟就查询出结果，但是为什么删除的时候就会需要40多分钟时间呢？

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 8

      DELETE [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestSWS_BAND_UNIFORMSummary       FROM [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestSWS_BAND_UNIFORMSummary AS P join [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestHeader L     on P.Lottestid=L.Lottestid and P.lotid =L.Lotid     inner join [FIS-ARCHIVE].MetroChina_ARC.dbo.w_d_archive_chips  T     on L.Lotid=T.Lotid and L.Waferid=T.Waferid and L.Chipid=T.Chipid   

这是删除的语句

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

这样去删除的话 在远程服务器 执行的应该都是表扫描；

个人建议先把需要删除的键值找到，再传到本地删除

Answer 9

前几个测试已经完成。

远程select这部分数据，花费时间不到5秒钟，总共120笔

远程手动执行delete ，已经完成 花费时间42min

在远程数据库本地进行删除测试。---已经完成，花费时间不到2秒钟

看起来应该是由于DBLINK的原因了。

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 10

      DELETE [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestSWS_BAND_UNIFORMSummary       FROM [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestSWS_BAND_UNIFORMSummary AS P join [FIS-ARCHIVE].MetroChina_ARC.dbo.LotTestHeader L     on P.Lottestid=L.Lottestid and P.lotid =L.Lotid     inner join [FIS-ARCHIVE].MetroChina_ARC.dbo.w_d_archive_chips  T     on L.Lotid=T.Lotid and L.Waferid=T.Waferid and L.Chipid=T.Chipid   

这是删除的语句

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

这样去删除的话 在远程服务器 执行的应该都是表扫描；

个人建议先把需要删除的键值找到，再传到本地删除

 

但是为什么insert的时候就很快呢，只是delete的时候很慢，现在

---

If you haven't all the things you want,be grateful for the things you don't have that you didn't want.

Answer 11

inserting doesn't have to find rows from table.