O SQL IN é ruim para o desempenho?

Eu tenho uma consulta fazendo algo como:

SELECT FieldX, FieldY FROM A WHERE FieldW IN (108, 109, 113, 138, 146, 160, 307, 314, 370, 371, 441, 454 ,457, 458, 479, 480, 485, 488, 490, 492, 519, 523, 525, 534, 539, 543, 546, 547, 550, 564, 573, 629, 642, 643, 649, 650, 651, 694, 698, 699, 761, 762, 768, 772, 773, 774, 775, 778, 784, 843, 844, 848, 851, 852, 853, 854, 855, 856, 857, 858, 859, 860, 861, 862, 863, 864, 865, 868, 869, 871, 872, 873, 891) 

Ter uma cláusula IN com tantas opções, é ruim para o desempenho da consulta? Estou passando por muitos tempos limite no meu aplicativo e acredito que isso poderia ser uma fonte desse tipo de problema. Posso otimizar a consulta sem remover os números, usando alguma boa dica de SQL?

EDITAR:

@ KM estas são chaves em uma tabela diferente. Este é um aplicativo do fórum, explicando brevemente: c # obtém todos os fóruns do database e os armazena no cache do aplicativo. Antes de o C # chamar um procedimento que obtém os threads para esses fóruns e para esse usuário, c # faz alguma lógica filtrando a coleção “all forums”, considerando permissions e alguma lógica de negócios. O tempo limite acontece no database e não no próprio aplicativo. Fazer toda essa lógica na consulta exigirá muitas junções internas e não tenho 100% de certeza de que posso fazer tudo isso dentro do procedimento.

Estou usando o SQL Server 2000

Há várias considerações ao escrever uma consulta usando o operador IN que pode afetar o desempenho.

Em primeiro lugar, as cláusulas IN geralmente são reescritas internamente pela maioria dos bancos de dados para usar o conector lógico OR. Então col IN ('a','b','c') é reescrito para: (COL = 'a') OR (COL = 'b') or (COL = 'c') . O plano de execução para ambas as consultas provavelmente será equivalente supondo que você tenha um índice na col .

Segundo, ao usar IN ou OR com um número variável de argumentos, você está fazendo com que o database precise analisar novamente a consulta e reconstruir um plano de execução toda vez que os argumentos forem alterados. Construir o plano de execução para uma consulta pode ser um passo caro. A maioria dos bancos de dados armazena em cache os planos de execução das consultas que eles executam usando o texto da consulta EXATO como chave. Se você executar uma consulta semelhante, mas com valores de argumentos diferentes no predicado, provavelmente fará com que o database gaste uma quantidade significativa de análise de tempo e criação de planos de execução. É por isso que as variables ​​de vinculação são altamente recomendadas como uma forma de garantir o desempenho ideal da consulta.

Terceiro, muitos bancos de dados têm um limite na complexidade das consultas que podem executar – um desses limites é o número de conectivos lógicos que podem ser incluídos no predicado. No seu caso, é improvável que algumas dezenas de valores alcancem o limite interno do database, mas se você espera passar centenas ou milhares de valores para uma cláusula IN – isso pode definitivamente acontecer. Nesse caso, o database simplesmente cancelará a solicitação de consulta.

Quarto, as consultas que incluem IN e OR no predicado nem sempre podem ser reescritas otimamente em um ambiente paralelo. Existem vários casos em que a otimização de servidor paralelo não é aplicada – o MSDN tem uma introdução decente para otimizar consultas para paralelismo. Geralmente, as consultas que usam o operador UNION ALL são trivialmente comparáveis ​​na maioria dos bancos de dados – e são preferíveis a conectivos lógicos (como OR e IN) quando possível.

Se você tem um bom índice no FieldW, usar essa IN está perfeitamente correto.

Acabei de testar e o SQL 2000 faz um Clustered Index Scan ao usar o IN.

Você pode tentar criar uma tabela temporária, inserir seus valores nela e usar a tabela no predicado IN .

AFAIK, o SQL Server 2000 não pode criar uma tabela de hash do conjunto de constantes, o que priva o otimizador da possibilidade de usar um HASH SEMI JOIN .

Isso ajudará apenas se você não tiver um índice no FieldW (o que você deve ter).

Você também pode tentar include suas colunas FieldY e FieldY no índice:

 CREATE INDEX ix_a_wxy ON a (FieldW, FieldX, FieldY) 

para que a consulta pudesse ser atendida apenas usando o índice.

SQL Server 2000 não possui a opção INCLUDE para CREATE INDEX e isso pode degradar um pouco o desempenho da DML mas melhorar o desempenho da consulta.

Atualizar:

A partir do seu plano de execução, vejo que você precisa de um índice composto em (SettingsID, SectionID)

SQL Server 2000 verdade, pode construir uma tabela de hash a partir de uma lista constante (e faz isso), mas o Hash Semi Join provavelmente será menos eficiente do que um Nested Loop para consultas de consulta.

E apenas uma observação: se você precisar saber a contagem de linhas que satisfazem a condição WHERE , não use COUNT(column) , use COUNT(*) .

Uma COUNT(column) não conta as linhas para as quais o valor da column é NULL .

Isso significa que, primeiro, você pode obter os resultados que você não esperava e, segundo, o otimizador precisará fazer uma Key Lookup extra / Bookmark Lookup se sua coluna não estiver coberta por um índice que atenda à condição WHERE .

Como o ThreadId parece ser uma CLUSTERED PRIMARY KEY , está tudo bem para essa mesma consulta, mas tente evitá-la em geral.

Dependendo da sua distribuição de dados, predicados adicionais na sua cláusula WHERE podem melhorar o desempenho. Por exemplo, se o conjunto de IDs for pequeno em relação ao número total na tabela e você souber que os IDs estão relativamente próximos (talvez eles sejam adições recentes e, portanto, agrupados na extremidade alta do intervalo), você poderia tentar include o predicado “AND FieldW BETWEEN 109 AND 891” (depois de determinar o min & max id no seu conjunto no código C #). Pode ser que fazer uma varredura de intervalo nessas colunas (se indexadas) funcione mais rápido do que o que está sendo usado atualmente.

Existem melhores maneiras de codificá-lo, mas duvido que seja a causa de seus tempos limite, especialmente se for apenas um SELECT. Você deve poder determinar isso olhando seus rastreamentos de consulta. Mas recodificar isso seria otimização por adivinhação, e um palpite improvável para isso.

Vamos começar com um plano de consulta para a consulta que está realmente expirando. Você sabe com certeza qual consulta é?

IN é exatamente a mesma coisa que escrever uma lista grande de ORs. E o OR muitas vezes torna as consultas não desejáveis, portanto, seus índices podem ser ignorados e o plano é executado para uma varredura completa.

Normalmente, a cláusula IN é prejudicial para o desempenho, mas o que é “ruim” depende do aplicativo, dados, tamanho do database, etc. Você precisa testar seu próprio aplicativo para ver o que é melhor.

Basicamente, o que a cláusula where faz é “FieldW = 108 OU FieldW = 109 OU FieldW = 113 …”. Às vezes, você pode obter um melhor desempenho fazendo várias seleções e combinando-as com união. Por exemplo:

 SELECT FieldX, FieldY FROM A WHERE FieldW = 108 UNION ALL SELECT FieldX, FieldY FROM A WHERE FieldW = 109 

Mas é claro que isso é impraticável quando você está comparando com muitos valores.

Outra opção pode ser inserir esses valores em uma tabela temporária e, em seguida, ingressar na tabela A para essa tabela temporária.

o tamanho da sua mesa determinará a velocidade ao usar esta declaração. Se não é uma tabela muito grande … esta declaração não está afetando seu desempenho.

Aqui está sua resposta …

http://www.4guysfromrolla.com/webtech/031004-1.shtml

Basicamente, você quer criar uma function que irá dividir uma string e preencher uma tabela temporária com o conteúdo da divisão. Então você pode se juntar a essa tabela temporária e manipular seus dados. O acima explica as coisas muito bem. Eu uso muito essa técnica.

No seu caso específico, use uma junit à tabela temporária em vez de uma cláusula in, muito mais rápido.

Eu geralmente usaria um tipo de tabela definido pelo usuário para consultas como essa.

 CREATE TYPE [dbo].[udt_int] AS TABLE ( [id] [int] NOT NULL ) 

Usando uma variável de tabela e preenchendo-a com linhas para cada um dos seus números, você pode fazer:

 SELECT FieldX, FieldY FROM A INNER JOIN @myIds B ON A.FieldW = B.id 

O desempenho só pode ser julgado no contexto do que você está tentando fazer. Nesse caso, você está solicitando a recuperação de cerca de 70 linhas (supondo que sejam valores únicos), portanto, é possível esperar algo como 70 vezes a duração da recuperação de um único valor. Pode ser menos devido ao cache, ou claro.

No entanto, o otimizador de consulta pode precisar ou optar por executar uma varredura completa da tabela para recuperar os valores. Nesse caso, a performace será um pouco diferente de recuperar um único valor por meio do mesmo plano de access.

Se você pode usar outras coisas que IN: faça (eu estava usando IN em alguns casos não é realmente o melhor caminho: eu posso facilmente replace com existir e é mais rápido)

No seu caso: não parece tão ruim.

Você pode tentar algo como:

 select a.FieldX, a.FieldY from ( select FieldW = 108 union select FieldW = 109 union select FieldW = 113 union ... select FieldW = 891 ) _a join A a on a.FieldW = _a.FieldW 

Pode ser apropriado para sua situação, por exemplo, quando você deseja gerar uma única instrução SQL dinamicamente. Na minha máquina (SQL Server 2008 Express), testando com um pequeno número (5) de valores FieldW e um grande número (100.000) de linhas em A, isso usa uma busca de índice em A com uma junit de loops nesteds entre A e _a, que é provavelmente o que você está procurando.