SQL Server：如何使用昂贵的非聚集索引查找改进 3 连接查询

发布于 2024-12-12 20:58:52 字数 834 浏览 0 评论 0原文

我有一个查询，它对 3 个表进行内部联接。

SELECT DISTINCT A.ID, <OTHER 'A' FIELDS> 
FROM C 
LEFT JOIN B ON C.Bin_ID = B.Bin_ID 
LEFT JOIN A ON B.App_ID = A.App_ID  
WHERE
   ((A.App_NAME LIKE '%%') AND (B.App_ID IS NOT NULL))  
ORDER BY 
    A.App_NAME ASC

所提到的表中存在的数据->

表 A：8000 行
表 B：900000 行
表 C：10,00,000 行

以下是从 SQL Server 查询计划中显示的统计信息：

这里使用了两个昂贵的索引查找，上部索引查找具有以下统计信息：

Object: B.App_ID [non clustered index, non unique] 
Seek Predicate: B.App_ID = A.App_ID[clustered index,unique]

底部索引查找有以下统计数据：

Object: C.Bin_ID [non clustered index, non unique] 
See Predicate: C.Bin_ID = B.Bin_ID[clustered index,unique]

现在这个查询平均需要 5 分钟来执行，我不知道应该做什么才能使它更快，因为查询已经使用索引查找。 {所有连接对于结果集都是必需的}。需要帮助！

原文

I have a query which makes a inner join of 3 tables.

SELECT DISTINCT A.ID, <OTHER 'A' FIELDS> 
FROM C 
LEFT JOIN B ON C.Bin_ID = B.Bin_ID 
LEFT JOIN A ON B.App_ID = A.App_ID  
WHERE
   ((A.App_NAME LIKE '%%') AND (B.App_ID IS NOT NULL))  
ORDER BY 
    A.App_NAME ASC

Data present in the tables mentioned->

Table A: 8000 rows
Table B: 900000 rows
Table C: 10,00,000 rows

Following is the stats revealed from the SQL Server query plan :

Here Two costly index seeks are used, the upper index seek has the following stats:

Object: B.App_ID [non clustered index, non unique] 
Seek Predicate: B.App_ID = A.App_ID[clustered index,unique]

the bottom index seek has the following stats:

Object: C.Bin_ID [non clustered index, non unique] 
See Predicate: C.Bin_ID = B.Bin_ID[clustered index,unique]

Now this query takes 5 minutes to execute on an average and I am not able to figure out what should be done make it faster since the query uses Index seeks already. {All joins are necessary for the result set}. Need Help !

分享到QQ

分享到微博

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

发布评论

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

梦境 2024-12-19 20:58:52

看起来您只从表 A 中获取列，并且只检查表 B 中的现有行。

我想这会给你相同的结果，而且可能更快一些：

SELECT A.ID--, <OTHER 'A' FIELDS> 
FROM A
WHERE
   A.App_NAME LIKE '%%' AND 
   A.App_ID IN (SELECT B.App_ID
                FROM B
                  INNER JOIN C
                    ON B.Bin_ID = C.Bin_ID)
ORDER BY 
    A.App_NAME ASC

It looks like you only fetch columns from table A and you only check table B for existing rows.

I guess this would give you the same result and possibly a bit faster:

SELECT A.ID--, <OTHER 'A' FIELDS> 
FROM A
WHERE
   A.App_NAME LIKE '%%' AND 
   A.App_ID IN (SELECT B.App_ID
                FROM B
                  INNER JOIN C
                    ON B.Bin_ID = C.Bin_ID)
ORDER BY 
    A.App_NAME ASC

回复收藏 0 原文

~没有更多了~