Condicionalmente OR vs CASE vs IF

No sql-server:

Uma opção é sql dinâmico, outra é option (recompile).

usando option (recompile):

select * from MyTable where MyParam = 0 or MyColumn = MyParam option (recompile);

exemplo de sql dinâmico:

declare @sql nvarchar(max);

if nullif(@MyParam,0) is not null
  begin;
  set @sql = 'select * from MyTable where MyColumn=@MyParam;'
  exec sp_exeuctesql @sql, '@MyParam int', @MyParam;
  end;
else 
  begin;
  set @sql = 'select * from MyTable;'
  exec sp_exeuctesql @sql;
  end;

Referência:

• Sniffing, Incorporação e Opções de Recompilação de Parâmetros - Paul White
• Condições de pesquisa dinâmica - Erland Sommarskog
• Consultas gerais - Gail Shaw
• Um exemplo atualizado de "pia de cozinha" - Aaron Bertand

Acabei de executar um teste em meu ambiente no AdventureWorks e descobri que a abordagem CASE funciona melhor. O uso da abordagem OR fez com que o mecanismo usasse o índice clusterizado, onde a instrução CASE permitia que ele usasse o índice não clusterizado.

para que sua consulta possa ficar assim.

SELECT *
FROM MyTable
WHERE CASE WHEN @MyParam = 0 THEN 1
           WHEN MyColumn = @MyParam THEN 1
           ELSE 0
           END = 1

No entanto, se, como você disse, houver muitos parâmetros, é improvável que você possa usar um índice em qualquer caso. Então você pode deixá-lo sozinho. Meu teste foi para uma consulta simples.

SELECT * FROM MyTable WHERE MyParam = 0 OR MyColumn = MyParam

Mas nosso DBA está dizendo que o OR fará com que seja lento e o db sofrerá.

Existem várias falhas com isso:

• O mundo da programação, e especialmente o mundo DB(A) está repleto de ditados como este que se perpetuam ad nauseam. "Nunca use NOT EXISTS, nunca use NOT IN, evite isso, evite aquilo, você deve fazer XYZ a todo custo, evitar ORa todo custo" e assim por diante. O problema com essas coisas é que elas podem ter sido verdadeiras em algum momento, para uma versão específica de um RDBMS específico e, muitas vezes, para um subconjunto específico de configurações de parâmetros para essa versão específica do RDBMS.
• Isso é em espírito como otimizar antes de saber que há algo para otimizar. A única coisa que é completamente óbvia aqui é que agora você está usando mais tempo nesta declaração (pensando sobre isso, perguntando a SE etc.). Então o custo já subiu. Se a declaração em si é "ruim" nem mesmo foi mostrada, e se realmente é "ruim", alguém nem provou que é ruim o suficiente para justificar uma ação.

Então, o que você deve fazer nesses casos: criar um caso de teste e fazer um benchmark . Nada mais pode ajudá-lo.

Para o seu caso específico:

• O DB poderia , em teoria, otimizar a declaração e reconhecer o problema; poderia então ser quase tão rápido quanto se você tivesse feito de forma diferente.
• A sobrecarga pode ser insignificante e totalmente inútil para se preocupar. Pode levar 1 ms a mais para uma solicitação que leva 1000 ms de ponta a ponta, com um bom pedaço de código digno de otimização que leva os outros 999 ms. Perder até mesmo um minuto do seu tempo para se preocupar com essas coisas pode ser demais.
• Por outro lado, esse problema pode ter um tremendo impacto no seu desempenho.

Faça uma referência; fazer testes; avaliar estatísticas de execução de banco de dados e assim por diante. Então você saberá com certeza.