优化 SQL 连接语句

发布于 2024-11-01 03:57:36 字数 359 浏览 2 评论 0原文

我正在加入两张桌子。第一个包含工作订单及其相关的零件号。第二个包含所有部件号的 BOM。它们都是大桌子。单独地，我可以在几秒钟内查询这两个表，甚至更短。当我执行连接时，需要几分钟的时间。这条语句末尾的where有可能是在join之后执行的吗？如果首先执行连接，我可能会发现这需要很长时间。但如果第一个表首先减少了 where，我认为这应该会很快。有没有办法编写更优化的查询？

SELECT  Table2.ItemNum As ItemNum  
FROM Table1  
INNER Join Table2  
ON Table1.PartNum = Table2.PartNum
WHERE Table1.WorkOrder = 10100314

原文

I am joining two tables. The first contains work orders and their associated part numbers. The second contains the BOM for all of the part numbers. They are both large tables. Individually, I can query the two tables in seconds if not less. When I perform the join, it takes minutes. Is it possible that the where at the end of this statement is being performed after the join? If the join is performed first, I could see this taking a long time. But if the first table is reduced first by the where, I would think this should go fast. Is there someway to write a more optimized query?

SELECT  Table2.ItemNum As ItemNum  
FROM Table1  
INNER Join Table2  
ON Table1.PartNum = Table2.PartNum
WHERE Table1.WorkOrder = 10100314

分享到QQ

分享到微博

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

发布评论

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

孤凫 2024-11-08 03:57:36

这会做得更好：

SELECT  Table2.ItemNum As ItemNum  
FROM Table2  
INNER JOIN
(
    SELECT * 
    FROM Table1
    WHERE Table1.WorkOrder = 10100314
)AS Table1
ON Table1.PartNum = Table2.PartNum

PartNum 字段上的索引也是必需的......

That will do a better job:

SELECT  Table2.ItemNum As ItemNum  
FROM Table2  
INNER JOIN
(
    SELECT * 
    FROM Table1
    WHERE Table1.WorkOrder = 10100314
)AS Table1
ON Table1.PartNum = Table2.PartNum