慢INFORMATION_SCHEMA查询

Question

我们有一个.NET Windows应用程序，它在登录时运行以下查询，以获取有关数据库的一些信息：

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc 
ON pk_tc.TABLE_NAME = t.TABLE_NAME 
AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu 
ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc 
ON fk_tc.TABLE_NAME = t.TABLE_NAME 
AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu 
ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME 

这通常在几秒钟内运行，但在一台运行server 2000的服务器上，运行时间超过4分钟。我运行它时启用了执行计划，结果是巨大的，但是这个部分吸引了我的注意(它不允许我发布图像)：

http://img35.imageshack.us/i/plank.png/

然后，我更新了执行计划中提到的所有表的统计数据：

update statistics sysobjects
update statistics syscolumns
update statistics systypes
update statistics master..spt_values
update statistics sysreferences

但这没什么用。索引调优向导也没有帮助，因为它不允许我选择系统表。此服务器上没有其他任何运行，因此没有其他任何东西可以减慢它的运行速度。我还能做些什么来诊断或修复服务器上的问题呢？

Answer 1

如果情况允许，左联接因生成大量记录集而臭名昭著。我偶尔会遇到这样的问题，我没有一个快速而简单的答案。我发现，处理查询，例如，一次构建一个连接，是解决问题的最佳方法。

您是否尝试过从查询中的表中执行select计数(*)，以查看其中一个表中是否有一个出乎意料的高off行？

JR

更多的想法:目前还不清楚问题是在于服务器，还是查询是病态的，是否会杀死任何服务器。我想您可以将数据库复制到另一台服务器，然后再尝试查询。

如何将查询修改为如下所示：

SELECT t.TABLE_NAME, ISNULL(pk_ccu.COLUMN_NAME,'') PK, ISNULL(fk_ccu.COLUMN_NAME,'') FK 
FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN 
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE pk_ccu
  ON pk_ccu.CONSTRAINT_NAME = pk_tc.CONSTRAINT_NAME 
)
 ON pk_tc.TABLE_NAME = t.TABLE_NAME AND pk_tc.CONSTRAINT_TYPE = 'PRIMARY KEY' 
LEFT JOIN
( INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk_tc INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fk_ccu
  ON fk_ccu.CONSTRAINT_NAME = fk_tc.CONSTRAINT_NAME
)
 ON fk_tc.TABLE_NAME = t.TABLE_NAME AND fk_tc.CONSTRAINT_TYPE = 'FOREIGN KEY' 

我认为内部连接应该是安全的，因为tc和ccu表应该有匹配的记录。以这种方式执行查询是否会提高执行时间？

Answer 2

只是一个想法，SQL 2000中的优化器引擎过去很难为超过4个内部联接的语句找到最优化的查询计划，我认为用左联接更难。

正如您在所提供的quereplan中所看到的，它执行了许多对许多查询的操作。

SQL 2000的最佳实践是，如果需要来自4个以上表的数据，则拆分代码。

/Håkan Winther

Answer 3

这可能和你遇到的问题有关

http://bugs.mysql.com/bug.php?id=19588