Para otimizar a performance geral, as seguintes ferramentas e keywords podem ser usados.

Fieldlist

Uma das maneiras de otimizar a comunicação com o servidor de dados é especificando quais campos devem ser retornados. Suponha que você tenha uma tabela com 40 campos, se você precisa apenas da informação de 4 campos você pode reduzir drasticamente a quantidade de dados que irão transitar na operação server -> client, apenas especificando os campos que realmente precisa.

Ex.:

1
2
3
4

while select amountMST from ledgerTrans
{
  amountMST += ledgerTrans.amountMST
}

Aggregation

Se você precisa da soma dos registros você pode pedir ao servidor de dados que faça a soma e lhe retorne apenas o resultado ao invés de ler todas as linhas e então fazer a soma você mesmo. Se você precisa somar em algum caso especifico um ou mais campos, você deve combinar a agregação com a clausula group by.

Ex.:

1
2

select sum(amountMST) from ledgerTrans;
sumAmountMST = ledgerTrans.amountMST;

Join

Imagine que você tem duas tabelas:
tableA = Cabeçalho de ordens de venda;
tableB = Linhas das ordens de venda.
Ambas são relacionadas, ou seja, para cada registro na tableA você pode ter um ou vários na tableB.

Agora imagine que você quer vasculhar a tableA e pra cada registro encontrado você quer vasculhar a tableB e recuperar os valores encontrados em ambas. Para realizar tal tarefa, você tem duas opções. A primeira consiste em fazer um loop na tableA e pra cada registro encontrado fazer outro loop na tableB fazendo assim o relacinamento entre as duas. A segunda tafera e é a que sempre devemos usar nestes casos é a que juntamos os registros na propria base de dados e trazemos todas as linhas já relacionadas usando o JOIN e diminuindo as várias instruções SQL do processo um para apenas uma instrução no processo dois.

Ex.:

1
2
3
4
5
6

while select ledgerTable
  join ledgerTrans
    where ledgerTrans.accountNum == ledgerTable.accountNum
{
  amountMST += ledgerTrans.amountMST;
}

ForceLiterals

Diz ao kernel para usar os valores literais quando for montar a instrução ao banco de dados.

Ex.:

SELECT forceLiterals * FROM custTable WHERE zipcode == '12220';

No SQL Server, será traduzido para:

SELECT * FROM CustTable WHERE zipcode == '12220';

A vantagem de usar o ForceLiterals é que o servidor agora sabe todas as informações para calcular o melhor plano de acesso para a instrução. A desvantagem é que o acesso ao plano não poderá ser reutilizado por outros valores e a otimização usará mais CPU do servidor de dados. Instruções com alta freqüência de utilização NÃO devem usar literais.

ForcePlaceholders

Diz ao kernel para não usar os valores literais usados nas condições where no momento em que o servidor de dados faz as otimizações. (inverso ao ForceLiterals)
A vantagem de se usar o ForcePlaceholders é que o kernel pode reutilizar o plano de acesso para as outras instruções similares com outros valores nas condições. Para uma fácil compreensão, podemos dizer que quando usamos o ForcePlaceholders o kernel irá compilar a instrução como uma Stored Procedure e esta estará ativa até o final da conexão, tornando o processo mais rápido.

Ex.:

SELECT forceLiterals * FROM custTable WHERE zipcode == '12220';

No SQL Server será traduzido para:

SELECT A.VALUE, A.MODIFIEDTIME, A.CREATEDTIME, A.RECID 
FROM HINTTABLE A(NOLOCK) 
WHERE (ZIPCODE=" @P1") OPTION(FAST 47)

Usar o ForcePlaceholders irá ajudar o servidor de dados a salvar tempo em recompilar o plano de execução, ou seja, reutilizar o plano de execução criado anteriormente quando for executar novamente a instrução. Mas fique atento, o uso excessivo do ForcePlaceholders pode causar uma queda de performance. Se uma instrução é executada apenas uma vez (ou poucas vezes), o keyword forceLiterals é melhor porque requer apenas uma viagem de ida-e-volta ao servidor de dados, enquanto o forcePlaceHolders vai precisar de uma ‘viagem’ para preparar a instrução e outra para executa-la.

FirstFast

Instrui ao servidor de dados para priorizar o processo de trazer apenas as primeiras linhas do resultado. Também significa que o servidor de dados pode escolher um índice apropriado para a clausula order by. O firstFast é automaticamente usado por todos os formulários, mas raramente é usado diretamente no código (X++).

Firstonly

Quando o DAX carrega os registros do servidor de dados, ele transfere uma pacote para cada registro. O FirstOnly é chamado de read-ahead e é usado para economizar chamadas ao servidor. Se o programador sabe que apenas um registro será retornado ele pode desabilitar o cache read-ahead usando esse qualificador.

Index ou Index Hint Keywords

Este keyword pode ser usado tanto na forma “INDEX indexName” ou “INDEX HINT indexName”. Se o keyword HINT não é usado, o DAX gera um order by correspondente aos componentes indexados. Como um index hint “errado” pode ter um grande problema de performance, index hints devem ser aplicados apenas em instruções onde não usamos condições dinâmicas (where) ou ordenadores (order by) e onde o resultado do hint possa ser constatado. O DAX remove automaticamente os index hints referentes a index desabilitados.

ForceSelectOrder

Este keyword força o servidor de dados a acessar as tabelas na ordem em que elas são escritas. Se duas tabelas são ligadas, a primeira tabela na instrução será sempre a primeira a ser acessada. Este keyword é usado freqüentemente com o ForceNestedLoop.

Uma situação onde pode ser interessante é onde queremos forçar uma ordenação em um select quando você está usando um index hint em um join. Suponha que você tenha o seguinte:

static voide DemoForceNestedLoops(Args _args)
{
  InventTrans inventTrans;
  InventDim   inventDim;
  ;
 
  while select inventTrans
    index hint ItemIdx
    where inventTrans.ItemId == 'X'
    join inventDim
      where inventDim.InventDimId == inventTrans.IventDimId &&
            inventDim.InventBatchID == 'Y'
  {
  }
}

Você diz a database para usar o index ItemIdx na tabela InventTrans. Isto está OK se a database decidir procurar primeiro na primeira tabela. Mas se a database, por questões de ‘estatísticas’, decidir começar com a tabela InventDim e então encontrar registros na InventTrans para cada ocorrência da inventDimId, o uso do index ItemIdx possivelmente não será o esperado. Ou seja, se você escolher usar índices em um join, você deve considerar especificar o keyword ForceSelectOrder.

ForceNestedLoop

Este keyword força o servidor de dados a usar um algorítimo de loop alinhado para processar a instrução SQL recebida contendo o join. Isto significa que um registro da primeira tabela é carregado antes de tentar carregar qualquer registro da segunda tabela. Normalmente outros joins como hash-join, merge-joins e outros devem ser considerados. Este keyword é frequentemente combinado com o keyword ForceSelectOrder.

Vejamos o exemplo acima com as tabelas InventTrans e InventDim. Você pode chutar que a base de dados decide procurar todos os registros da IventTrans usando o index ItemIdx e todos os registros da InventDim usando o BatchId. As duas coleções de registros irão subsequentemente ser colocadas juntas. Se você quiser que a database encontre primeiro a inventTrans e então a inventDim para cada inventTrans você deve especificar o ForceNestedLoops.

NoLock

Este keyword instrui o kernel a ler dados que ainda não foram efetivados (commited), por exemplo, ignorando locks mesmo quando estão em uma transação. Uma consulta irá ler dados não efetivos ainda se o método Buffer.SelectLocked(FALSE) for chamado antes de se fazer a consulta. Este keyword é automaticamente aplicado quando acessamos tabelas com o cache lookup esta setado com Found ou maior. Seja cuidado ao usar este keyword no sentido de respeitar a consistência dos dados.

Fonte: http://dynamics-axinfoen.blogspot.com/2007/04/following-tools-and-keywords-2.html