Ajuste do desempenho de consulta (SQL Server Compact)

Artigo
01/14/2011

É possível melhorar o desempenho do aplicativo SQL Server Compact 3.5 (SQL Server Compact 3.5) otimizando as consultas que você usar. As seções a seguir descrevem técnicas que podem ser usadas para otimizar o desempenho da consulta.

Aperfeiçoar índices

A criação de índices úteis é uma das maneiras mais importantes para se conseguir melhor desempenho da consulta. Esses índices ajudam a localizar os dados usando um número menor de operações de E/S de disco e menos recursos do sistema.

Para criar índices úteis, é necessário entender como os dados são usados, conhecer os tipos de consulta e saber com que freqüência elas são executadas, além de saber como o processador de consultas pode usar índices para localizar os dados rapidamente.

Ao escolher os índices a serem criados, examine as consultas críticas, cujo desempenho mais afetará a experiência do usuário. Crie índices para ajudar especificamente essas consultas. Após adicionar um índice, execute novamente a consulta para ver se o desempenho melhorou. Caso não tenha melhorado, remova o índice.

Como na maioria das técnicas de otimização de desempenho, deve haver um equilíbrio. Por exemplo, com mais índices, a execução das consultas SELECT será potencialmente mais rápida. No entanto, as operações DML (INSERT, UPDATE e DELETE) ficarão significativamente mais lentas devido ao maior número de índices a serem mantidos a cada operação. Portanto, se as consultas forem principalmente instruções SELECT, um número maior de índices poderá ser útil. Se o aplicativo executar muitas operações DML, procure não aumentar o número de índices criados.

O SQL Server Compact 3.5 inclui suporte para planos de execução, o que ajuda a avaliar e otimizar consultas. O SQL Server Compact 3.5 usa o mesmo esquema de plano de execução que o SQL Server 2008, exceto pelo fato de que o SQL Server Compact 3.5 usa um subconjunto de operadores. Para obter mais informações, consulte Microsoft Showplan Schema em https://schemas.microsoft.com/sqlserver/2004/07/showplan/.

As seções a seguir fornecem informações adicionais sobre como criar índices úteis.

Criar índices altamente seletivos

A indexação em colunas usadas na cláusula WHERE de consultas críticas geralmente melhora o desempenho. No entanto, isso depende da seletividade do índice. A seletividade é a taxa de linhas qualificadas em relação ao total de linhas. Se a taxa for baixa, o índice será altamente seletivo. Ele pode eliminar a maioria das linhas e reduzir sensivelmente o tamanho do conjunto de resultados. Portanto, é um índice útil para criar. Por outro lado, um índice não-seletivo não é tão útil.

Um índice exclusivo tem a maior seletividade. Somente uma linha pode coincidir, o que a torna mais útil em consultas que devem retornar exatamente uma linha. Por exemplo, um índice em uma coluna de ID exclusiva ajudará você a localizar rapidamente uma determinada linha.

Avalie a seletividade do índice executando os procedimentos armazenados sp_show_statistics em tabelas do SQL Server Compact 3.5. Por exemplo, se estiver avaliando a seletividade de duas colunas, "Customer ID" e "Ship Via", você poderá executar os seguintes procedimentos armazenados:

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

ALFKI 0 7 0

ANATR 0 4 0

ANTON 0 13 0

AROUT 0 14 0

BERGS 0 23 0

BLAUS 0 8 0

BLONP 0 14 0

BOLID 0 7 0

BONAP 0 19 0

BOTTM 0 20 0

BSBEV 0 12 0

CACTU 0 6 0

CENTC 0 3 0

CHOPS 0 12 0

COMMI 0 5 0

CONSH 0 4 0

DRACD 0 9 0

DUMON 0 8 0

EASTC 0 13 0

ERNSH 0 33 0

(90 rows affected)

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

------------------------------------------------------------

1 0 320 0

2 0 425 0

3 0 333 0

(3 rows affected)

Os resultados mostram que a coluna "Customer ID" tem um grau de duplicação bem mais baixo. Isso significa que um índice nessa coluna será mais seletivo do que um índice na coluna "Ship Via".

Para obter mais informações sobre como usar esses procedimentos armazenados, consulte sp_show_statistics (SQL Server Compact 3.5), sp_show_statistics_steps (SQL Server Compact 3.5) e sp_show_statistics_columns (SQL Server Compact).

Criar índices de várias colunas

Os índices de várias colunas são extensões naturais de índices de coluna única. Os índices de várias colunas são úteis na avaliação de expressões de filtro correspondentes a um conjunto de prefixos de colunas de chave. Por exemplo, o índice composto CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) ajuda a avaliar as seguintes consultas:

... WHERE "Last Name" = 'Doe'
... WHERE "Last Name" = 'Doe' AND "First Name" = 'John'
... WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

No entanto, ele não é útil nesta consulta:

... WHERE "First Name" = 'John'

Ao criar um índice de várias colunas, coloque as colunas mais seletivas mais à esquerda na chave. Isso torna o índice mais seletivo quando coincidir com várias expressões.

Evitar a indexação de pequenas tabelas

Tabela pequena é aquela cujo conteúdo cabe em uma ou em poucas páginas de dados. Evite indexar essas tabelas, pois em geral é mais eficiente verificá-las. Isso economiza o custo de carregar e processar páginas de índice. Deixando de criar um índice em tabelas muito pequenas, você elimina a possibilidade de o otimizador selecionar um índice.

O SQL Server Compact 3.5 armazena dados em páginas de 4 Kb. A contagem de páginas pode ser aproximada com o uso da fórmula a seguir, embora a contagem real possa ser ligeiramente maior devido à sobrecarga do mecanismo de armazenamento.

<número de páginas> = -----------------------------------------------------------------

4096

Por exemplo, suponha que uma tabela tenha o seguinte esquema:

Nome da coluna	Tipo (tamanho)
ID do pedido	INTEGER (4 bytes)
ID do produto	INTEGER (4 bytes)
Preço unitário	MONEY (8 bytes)
Quantidade	SMALLINT (2 bytes)
Desconto	REAL (4 bytes)

A tabela tem 2.820 linhas. De acordo com a fórmula, são necessárias cerca de 16 páginas para armazenar seus dados:

<Nº de páginas > = ((4 + 4 + 8 + 2 + 4) * 2820) / 4096 = 15,15 páginas

Escolher o que indexar

É recomendável sempre criar índices em chaves primárias. Geralmente é útil também criar índices em chaves estrangeiras. Isso ocorre porque as chaves primárias e as estrangeiras freqüentemente são usadas para unir tabelas. Os índices dessas chaves permitem que o otimizador considere algoritmos de junção de índice mais eficientes. Se a sua consulta unir tabelas usando outras colunas, geralmente é útil criar índices nessas colunas pelo mesmo motivo.

Quando são criadas restrições de chave primária e de chave estrangeira, o SQL Server Compact 3.5 cria automaticamente índices para elas, aproveitando-as ao otimizar consultas. Lembre-se de manter o tamanho pequeno das chaves primárias e estrangeiras. As junções são executadas mais rapidamente dessa maneira.

Usar índices com cláusulas de filtro

Os índices podem ser usados para agilizar a avaliação de certos tipos de cláusulas de filtro. Embora todas as cláusulas de filtro reduzam o conjunto final de resultados de uma consulta, algumas também podem ajudar a reduzir a quantidade de dados a serem examinados.

Um SARG (argumento de pesquisa) limita a pesquisa porque especifica uma correspondência exata, um intervalo de valores ou uma conjunção de dois ou mais itens unidos por AND. Ele tem uma destas formas:

Operador Column <constante ou variável>

<constante ou variável> operador Column

Alguns dos operadores SARG são =, >, <, >=, <=, IN, BETWEEN e, às vezes, LIKE (em casos de correspondência de prefixo, como LIKE 'John%'). O SARG pode conter várias condições unidas com um AND. Os SARGs podem ser consultas que correspondam a um valor específico, como:

"Customer ID" = 'ANTON'
'Doe' = "Last Name"

Os SARGs também podem ser consultas que correspondam a um intervalo de valores, como:

"Order Date" > '1/1/2002'
"Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'
"Customer ID" IN ('ANTON', 'AROUT')

Uma expressão que não usa operadores SARG não melhora o desempenho, pois o processador de consultas do SQL Server Compact 3.5 precisa avaliar cada linha para determinar se ela corresponde à cláusula de filtro. Portanto, um índice não é útil em expressões que não usam operadores SARG. Entre os operadores não-SARG estão: NOT, <>, NOT EXISTS, NOT IN, NOT LIKE e funções intrínsecas.

Usar o otimizador de consulta

Ao determinar os métodos de acesso de tabelas base, o otimizador do SQL Server Compact 3.5 determina se existe um índice para uma cláusula SARG. Se existir um índice, o otimizador o avaliará calculando quantas linhas são retornadas. Ele então estimará o custo da localização de linhas qualificadas com o uso do índice. O acesso indexado será escolhido se o seu custo for menor que o do exame da tabela. O índice será potencialmente útil se a sua primeira coluna ou o conjunto de colunas de prefixo forem usados no SARG, e este estabelecer uma associação inferior, uma associação superior, ou ambas, para limitar a pesquisa.

Entender o tempo de resposta vs. tempo total

Tempo de resposta é o tempo necessário para que uma consulta retorne o primeiro registro. Tempo total é o tempo necessário para que a consulta retorne todos os registros. Em um aplicativo interativo, o tempo de resposta é importante, pois é o tempo que leva para o usuário receber confirmação visual de que uma consulta está sendo processada. Para um aplicativo em lotes, o tempo total reflete a produtividade geral. É necessário determinar quais são os critérios de desempenho para o aplicativo e as consultas, e usar essas informações no design.

Por exemplo, suponha que a consulta retorne 100 registros e seja usada para popular uma lista com os cinco primeiros registros. Nesse caso, o tempo necessário para retornar todos os 100 registros não é relevante. Em vez disso, você deseja que a consulta retorne os cinco primeiros registros rapidamente, para poder popular a lista.

Muitas operações de consulta podem ser executadas sem a necessidade de armazenar os resultados intermediários. Elas são conhecidas como operações em pipeline. Exemplos de operações em pipeline são projeções, seleções e junções. As consultas implementadas com essas operações podem retornar resultados imediatamente. Outras operações, como SORT e GROUP-BY, requerem que toda a sua entrada seja usada antes de retornar resultados para suas operações pai. Elas são conhecidas como operações que requerem materialização. As consultas implementadas com essas operações geralmente sofrem um atraso inicial devido à materialização. Após esse atraso inicial, em geral elas retornam registros com bastante rapidez.

As consultas com requisitos de tempo de resposta devem evitar a materialização. Por exemplo, com o uso de um índice para implementar a cláusula ORDER-BY o tempo de resposta é melhor do que quando a classificação é usada. A seção a seguir descreve esse processo com mais detalhes.

Indexar as colunas ORDER-BY / GROUP-BY / DISTINCT para obter um tempo de resposta melhor

As operações ORDER-BY, GROUP-BY e DISTINCT são tipos de classificação. O processador de consultas do SQL Server Compact 3.5 implementa a classificação de duas maneiras. Se os registros já estiverem classificados por um índice, o processador precisará usar somente o índice. Caso contrário, o processador terá que usar uma tabela de trabalho temporária para classificar os registros primeiro. Essa classificação preliminar pode gerar atrasos iniciais significativos em dispositivos cujas CPUs tenham menos energia e memória limitada, e deverá ser evitada se o tempo de resposta for importante.

No contexto de índices de várias colunas, para que a cláusula ORDER-BY ou GROUP-BY considere um determinado índice, a coluna ORDER-BY ou GROUP-BY deve corresponder ao conjunto de colunas de índice do prefixo com a ordem exata. Por exemplo, o índice CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) pode ajudar a otimizar as seguintes consultas:

... ORDER BY / GROUP BY "Last Name" ...
... ORDER BY / GROUP BY "Last Name", "First Name" ...

Ele não ajudará a otimizar:

... ORDER BY / GROUP BY "First Name" ...
... ORDER BY / GROUP BY "First Name", "Last Name" ...

Para que uma operação DISTINCT considere um índice de várias colunas, a lista de projeções deverá coincidir com todas as colunas do índice, embora não seja necessário que estejam na ordem exata. O índice anterior pode ajudar a otimizar as seguintes consultas:

... DISTINCT "Last Name", "First Name" ...
... DISTINCT "First Name", "Last Name" ...

Ele não ajudará a otimizar:

... DISTINCT "First Name" ...
... DISTINCT "Last Name" ...

Dica

Se a consulta sempre retornar linhas exclusivas por conta própria, evite especificar a palavra-chave DISTINCT, pois ela só adiciona sobrecarga

Reescrever subconsultas para usar JOIN

Às vezes, você pode reescrever uma subconsulta para usar JOIN e obter melhor desempenho. A vantagem de criar uma subconsulta JOIN é poder avaliar as tabelas em uma ordem diferente daquela definida pela consulta. A vantagem de usar uma subconsulta é que geralmente não é necessário examinar todas as linhas da subconsulta para avaliar a expressão. Por exemplo, uma subconsulta EXISTS pode retornar TRUE ao ver a primeira linha qualificada.

Dica

O processador de consultas do SQL Server Compact 3.5 sempre reescreve a subconsulta IN para usar JOIN. Não é necessário tentar essa abordagem com as consultas que contenham a cláusula de subconsulta IN.

Por exemplo, para determinar todos os pedidos que tenham pelo menos um item com um desconto de 25 por cento ou mais, você pode usar a seguinte subconsulta EXISTS:

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

É possível reescrever essa subconsulta usando JOIN:

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

Limitar o uso de OUTER JOINs

OUTER JOINs são tratadas de forma diferente de INNER JOINs, pois o otimizador não tenta reorganizar a ordem de junção de tabelas OUTER JOIN, como faz com as tabelas INNER JOIN. A tabela externa (a tabela esquerda em LEFT OUTER JOIN e a tabela direita em RIGHT OUTER JOIN) é acessada primeiro, seguida pela tabela interna. Essa ordem de junção fixa pode levar à execução de planos de qualidade inferior.

Para obter mais informações sobre uma consulta com INNER JOIN, consulte a Base de Dados de Conhecimento Microsoft.

Usar consultas com parâmetros

Se o aplicativo executar uma série de consultas cuja diferença está somente em algumas restrições, você poderá melhorar o desempenho usando uma consulta com parâmetros. Por exemplo, para retornar pedidos de clientes diferentes, execute esta consulta:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

As consultas com parâmetros produzem um desempenho melhor, pois compilam a consulta somente uma vez e executam o plano compilado várias vezes. Programaticamente, é necessário manter o objeto de comando que contém o plano de consulta em cache. A destruição do objeto de comando anterior e a criação de um novo objeto elimina o plano em cache. Isso requer que a consulta seja compilada novamente. Se for necessário executar várias consultas com parâmetros de modo intercalado, crie vários objetos de comando, cada um deles com o plano de execução em cache para uma consulta com parâmetros. Dessa forma, você efetivamente evita ter que compilar novamente todas as consultas.

Consultar somente quando necessário

O processador de consultas do SQL Server Compact 3.5 é uma ferramenta avançada para consulta de dados armazenados no banco de dados relacional. No entanto, qualquer processador de consultas tem um custo intrínseco associado. Ele deve compilar, otimizar e gerar um plano de execução antes de iniciar a execução do plano propriamente dita. Em geral isso ocorre em consultas simples que são concluídas rapidamente. Portanto, há ocasiões em que, se você mesmo implementar a consulta, poderá melhorar sensivelmente o desempenho. Se cada segundo for importante no seu componente crítico, é recomendável que você considere a alternativa de implementar você mesmo as consultas simples. Quanto às consultas grandes e complexas, é melhor deixar o trabalho para o processador de consultas.

Por exemplo, suponha que você queira procurar a ID do cliente de uma série de pedidos organizados pelas respectivas IDs. Há duas maneiras de fazer isso. Em primeiro lugar, você pode seguir estas etapas para cada pesquisa:

Abra a tabela base Orders
Localize a linha usando a "Order ID " específica
Recupere a "Customer ID "

Ou você pode emitir a seguinte consulta para cada pesquisa:

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

A solução baseada em consulta é mais simples, porém mais lenta do que a solução manual, pois o processador de consultas do SQL Server Compact 3.5 converte a instrução SQL declarativa nas mesmas três operações que você poderia implementar manualmente. Essas três etapas são executadas em seqüência. A escolha do método a ser usado dependerá do grau de importância da simplicidade ou do desempenho para o seu aplicativo.

Compartilhar via