主页 / dba / 问题 / 72191
Accepted
KidCode
Asked: 2014-07-24 13:43:14 +0800 CST

为什么我的选择语句这么慢？

  • 772
SELECT name FROM sys.databases 
WHERE 
name 
not in
('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC')
AND
CASE WHEN state_desc = 'ONLINE' 
THEN OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') 
END IS NOT NULL

我正在使用上面的内容来填充 ac# winforms 项目中的组合框。- 我过滤掉不包含该表的数据库的原因是出于安全目的。

我WHERE clause像以前一样添加了它最多需要 21 秒！？（这是在具有 4 个数据库（不包括系统数据库）的本地开发机器上，其中只有一个具有我正在寻找的表。）

现在我已经添加了 where 子句，它只需要 9 秒。

我不能让它如此缓慢地运行，因为我正在编写的程序将在可以拥有多达 20 个数据库的机器上使用。- 大约有 35 张桌子。（这些不是服务器，这些是支持猴子机器！）

从实际执行计划中，我可以看到三个Clustered Index Seek's 占 22%、24% 和 48%。- 因为这是一个“系统”查询，我实际上能做些什么吗？！

最后，让这件事变得更难的重磅炸弹：我无法控制数据库的索引，我也无法以任何方式更改它们，无论是形状还是形式。

sql-server performance

3 个回答

  1. Best Answer

    该查询非常简单，应该运行得足够快。

    我的猜测是，您有一个长时间运行的事务在其中一个数据库中执行 DDL，并且您的查询在等待锁定时被阻塞。

    不幸的是，元数据功能，例如OBJECT_ID不考虑外部事务的事务隔离级别，因此即使将隔离级别设置为允许脏读也无济于事。

    您可以尝试这种替代方法来执行相同的操作并报告吗？（如果仍然很慢，请尝试添加SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED到开头）

    DECLARE @Databases TABLE(name SYSNAME PRIMARY KEY);

DECLARE @dbname    SYSNAME,
        @tablename SYSNAME = '_LOOK_UP_TABLE'

DECLARE @C1 AS CURSOR;

SET @C1 = CURSOR FAST_FORWARD
FOR SELECT name
    FROM   sys.databases
    WHERE  state_desc = 'ONLINE'
           AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb',
                             'ReportServer', 'ReportServerTempDB', '1303VISBOC' )

OPEN @C1;

FETCH NEXT FROM @C1 INTO @dbname;

WHILE @@FETCH_STATUS = 0
  BEGIN
      DECLARE @nsql NVARCHAR(max) = 'USE ' + QUOTENAME(@dbname) + ';
                                     SELECT @dbname 
                                     FROM sys.tables WITH (READPAST) 
                                     WHERE schema_id=1 
                                           AND Name = @tablename';

      INSERT INTO @Databases
      EXEC sp_executesql
        @nsql,
        N'@dbname SYSNAME, @tablename SYSNAME',
        @dbname,
        @tablename

      FETCH NEXT FROM @C1 INTO @dbname;
  END 

SELECT name
FROM @Databases
    • 1

  2. 这行得通吗？摆脱了 where 子句中的 case 语句...

    SELECT name, OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') AS [LookUpID] 
FROM sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC')
AND state_desc = 'ONLINE' 
AND ISNULL(OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U'),-1)>=0
    • 0

  3. 只是为了好玩，为什么不试试这个 SQL，它重新安排了一些事情：

    create table #t(name sysname)

insert #t(Name)
select Name
from sys.databases 
where state_desc = 'ONLINE' 

except 

select name 
from ( values
    (cast('master' as sysname)),
    ('model'),
    ('msdb'),
    ('tempdb'),
    ('ReportServer'),
    ('ReportServerTempDB'),
    ('1303VISBOC')
)excluded(Name);

select name 
from #t
where object_id(quotename(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') is not null;

drop table #t;

    过去，我经常发现 IN 运算符是性能问题的根源，并且尽可能避免使用它（几乎总是如此）。

    • 0

相关问题