删除不在查询中的位置

发布于 2024-09-05 18:26:19 字数 720 浏览 3 评论 0原文

我有一个查找表（##lookup）。我知道这是糟糕的设计，因为我正在复制数据，但它极大地加快了我的查询速度。我有一个填充此表的查询，

insert into ##lookup select distinct col1,col2,... from table1...join...etc...

我想模拟此行为：

delete from ##lookup
insert into ##lookup select distinct col1,col2,... from table1...join...etc...

这显然会正确更新表。但这是大量的插入和删除。它会扰乱我的索引并锁定表以供选择。

该表也可以通过以下方式更新：

delete from ##lookup where not in (select distinct col1,col2,... from table1...join...etc...)
insert into ##lookup (select distinct col1,col2,... from table1...join...etc...) except if it is already in the table

第二种方式可能需要更长的时间，但我可以说“没有锁”，我将能够从表中进行选择。

关于如何以第二种方式编写查询有什么想法吗？

原文

I have a lookup table (##lookup). I know it's bad design because I'm duplicating data, but it speeds up my queries tremendously. I have a query that populates this table

insert into ##lookup select distinct col1,col2,... from table1...join...etc...

I would like to simulate this behavior:

delete from ##lookup
insert into ##lookup select distinct col1,col2,... from table1...join...etc...

This would clearly update the table correctly. But this is a lot of inserting and deleting. It messes with my indexes and locks up the table for selecting from.

This table could also be updated by something like:

delete from ##lookup where not in (select distinct col1,col2,... from table1...join...etc...)
insert into ##lookup (select distinct col1,col2,... from table1...join...etc...) except if it is already in the table

The second way may take longer, but I can say "with no lock" and I will be able to select from the table.

Any ideas on how to write the query the second way?

分享到QQ

分享到微博

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

发布评论

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

旧伤慢歌 2024-09-12 18:26:19

DELETE LU
FROM ##lookup LU
LEFT OUTER JOIN Table1 T1 ON T1.my_pk = LU.my_pk
WHERE T1.my_pk IS NULL

INSERT INTO ##lookup (my_pk, col1, col2...)
SELECT T1.my_pk, T1.col1, T1.col2...
FROM Table1 T1
LEFT OUTER JOIN ##lookup LU ON LU.my_pk = T1.my_pk
WHERE LU.my_pk IS NULL

您还可以使用 WHERE NOT EXISTS 而不是上面的 LEFT JOIN 来查找不存在的行。

如果您使用的是 SQL 2008，您可能还需要查看 MERGE 语句。否则，您不会保持表同步 - 您只是保持 PK 同步。如果一个表中的一列发生更改，但另一列未更改，则不会反映在上面。

无论哪种方式，听起来您可能需要考虑优化查询。虽然复制数据似乎是解决性能问题的一个很好的方法，但正如您所看到的，它可能会带来很多麻烦（这只是其中之一）。你最好找到性能不佳的根本原因并解决它，而不是戴上这个丑陋的创可贴。

DELETE LU
FROM ##lookup LU
LEFT OUTER JOIN Table1 T1 ON T1.my_pk = LU.my_pk
WHERE T1.my_pk IS NULL

INSERT INTO ##lookup (my_pk, col1, col2...)
SELECT T1.my_pk, T1.col1, T1.col2...
FROM Table1 T1
LEFT OUTER JOIN ##lookup LU ON LU.my_pk = T1.my_pk
WHERE LU.my_pk IS NULL

You could also use WHERE NOT EXISTS instead of the LEFT JOINs above to look for non-existence of rows.

You might also want to look into the MERGE statement if you're on SQL 2008. Otherwise, you aren't keeping the tables in sync - you're only keeping the PKs in sync. If one of the column changes in one table but not the other that won't be reflected above.

Either way, it sounds like you might want to consider optimizing queries. While duplicating the data may seem like a nice fix for your performance issues, as you can see it can carry a lot of headaches with it (and this is just one). You're better off finding the underlying cause of the poor performance and fixing that rather than putting on this ugly bandaid.

回复收藏 0 原文