Início / dba / Perguntas / 72191
Accepted
KidCode
Asked: 2014-07-24 13:43:14 +0800 CST

Por que minha instrução select é tão lenta?

  • 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

Estou usando o acima para preencher uma caixa de combinação no projeto ac# winforms. -A razão pela qual estou filtrando bancos de dados que não incluem a tabela é para fins de segurança.

Eu adicionei o WHERE clausecomo antes de fazer que estava demorando até 21 segundos!? (Isso está em uma máquina de desenvolvimento local com 4 bancos de dados (não incluindo os bancos de dados do sistema), apenas um dos quais possui a tabela que eu estava procurando.)

Agora que adicionei a cláusula where, leva apenas nove segundos.

Não posso deixá-lo rodando tão devagar, pois o programa que estou escrevendo será usado em máquinas que podem ter até 20 bancos de dados nelas. -Todos com cerca de 35 mesas. (Estes não são servidores, são Support Monkey Machines!)

Pelo Plano de Execução Real vejo que são três Clustered Index Seek's ocupando 22%, 24% e 48%. -Como esta é uma consulta de 'sistema', há algo que eu possa realmente fazer sobre isso?!

E, finalmente, a bomba para tornar isso um pouco mais difícil: não tenho controle sobre os índices dos bancos de dados e não posso alterá-los de forma alguma.

sql-server performance

3 respostas

  1. Best Answer

    A consulta é bastante simples e deve ser executada com rapidez suficiente.

    Meu palpite é que você tem uma transação de execução longa realizando DDL em um dos bancos de dados e sua consulta está sendo bloqueada aguardando um bloqueio.

    Infelizmente, as funções de metadados, como OBJECT_IDnão levam em conta o nível de isolamento da transação externa, portanto, mesmo definir o nível de isolamento para permitir leituras sujas não ajudaria.

    Você pode tentar este método alternativo de fazer a mesma operação e relatar? (Se ainda estiver lento, tente adicionar SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTEDao início)

    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. Isso funcionaria? Livrou-se da instrução case na cláusula where ...

    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. Apenas por diversão, por que não tentar este SQL, que reorganiza um pouco as coisas:

    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;

    Muitas vezes descobri que o operador IN é uma fonte de problemas de desempenho no passado e o evito sempre que posso (o que é quase sempre).

    • 0

relate perguntas