自连接表上长时间运行的查询

发布于 2024-12-07 09:19:11 字数 492 浏览 1 评论 0原文

我尝试通过将实际行的值与同一表中的所有其他行进行比较来提高更新表的每一行上的列的查询的性能。查询如下：

update  F set   
    PartOfPairRC = 1
from    RangeChange F
where     Reject=0
and exists( 
            select 1 from RangeChange S
            where   F.StoreID = S.StoreID
            and F.ItemNo = S.ItemNo
            and       F.Reject = S.Reject
            and F.ChangeDateEnd = S.ChangeDate - 1)

随着表中行数的增加，查询的性能会迅速下降。我的表中有 5000 万行。

有更好的方法吗？ SSIS 能够更好地处理这样的操作吗？

非常感谢任何帮助，谢谢罗伯特

原文

I try to improve the performance of a query which updates a coloumn on each row of a table, by comparing the actual row's values with all other rows in the same table. Here is the query:

update  F set   
    PartOfPairRC = 1
from    RangeChange F
where     Reject=0
and exists( 
            select 1 from RangeChange S
            where   F.StoreID = S.StoreID
            and F.ItemNo = S.ItemNo
            and       F.Reject = S.Reject
            and F.ChangeDateEnd = S.ChangeDate - 1)

The query's performance degrades rapidly as the number of rows in the table increases. I have 50 millon rows in the table.

Is there a better way to do this? Would SSIS be able to handle such an operation better?

Any help much appreciated, thanks Robert

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

櫻之舞 2024-12-14 09:19:11

您可以尝试在该表上创建索引：

create index idx_test on RangeChange(StoreID, ItemNo, Reject, ChangeDateEnd) where reject = 0

--当您不使用 SQL Enterprise 时，摆脱索引中的 where 条件并将拒绝列作为索引中的包含列
--确保表上已有聚集索引（如果没有，您可以将上面的索引创建为聚集索引）

--我会将查询编写为联接：

update F set
   F.PartOfPairRC = 1
from  RangeChange F
   join RangeChange S
      on F.StoreID = S.StoreID
         and F.ItemNo = S.ItemNo
         and F.Reject = S.Reject
         and F.ChangeDateEnd = S.ChangeDate - 1
where F.Reject=0 and S.Reject = 0

You can try to create a index on that table:

create index idx_test on RangeChange(StoreID, ItemNo, Reject, ChangeDateEnd) where reject = 0

--when you are not using the SQL enterprise get rid of the where condition in the index and put the reject column as included column in the index
--make sure you have a clustered index already on the table (when not you can create the index above as clustered)

-- I would write the query as a join:

update F set
   F.PartOfPairRC = 1
from  RangeChange F
   join RangeChange S
      on F.StoreID = S.StoreID
         and F.ItemNo = S.ItemNo
         and F.Reject = S.Reject
         and F.ChangeDateEnd = S.ChangeDate - 1
where F.Reject=0 and S.Reject = 0

回复收藏 0 原文

~没有更多了~