Neste tópico vamos falar sobre Cache de Dados e Paginação SQL, que são as duas opções de otimização disponíveis nas Consulta com saída em Grid.
Apenas uma opção de otimização pode ser utilizada na Consulta. Você deve analisar os Prós e Contras de cada uma delas, e escolher a opção que mais se adeque a sua necessidade.
Cache de Dados
Quando o Cache de Dados está habilitado, o sistema armazena o resultado da Consulta em arquivos .json no servidor.
Sempre que uma das seguintes operações ocorrer, o sistema vai utilizados os dados salvos nos arquivos .json ao invés de executar o comando SQL no banco de dados novamente:
-
Filtro em Colunas
-
Ordenação de Colunas
-
Navegação entre as páginas da Grid
-
Agrupamento de Registros
Este recurso é útil quando a execução do Comando SQL é mais lenta do que o processamento dos dados pelo sistema.
Quando o usuário solicitar uma atualização dos dados da Consulta, o Cache de Dados será ignorado.
Paginação SQL
A técnica de Paginação SQL consiste em usar cláusulas e funções para limitar e paginar os dados retornados pelo banco de dados direto na fonte, gerando uma encomia de tráfego entre os servidores de banco e aplicação, além da redução do consumo de memória e processamento pelo servidor do IIS.
No LATROMI, essa técnica é aplicada de forma transparente, sem que seja necessário especificar o limite e o número da página no comando SQL das Consulta.
Este recurso é útil quando o Comando SQL retorna uma grande quantidade de linhas.
Como Funciona?
O comando SQL original (informado pelo desenvolvedor) é “encapsulado” por um outro comando SQL, que aplica o limite, paginação e filtros “por fora”. Em outras palavras, a Query original torna-se uma Subquery.
Por exemplo, considerando que o comando SQL original seja este:
SELECT *
FROM Orders
ORDER BY OrderDate
Caso a paginação estivesse limitada em 200 linhas e o usuário estivesse navegando na página 2, o comando SQL executado no banco de dados seria este:
WITH
q_target AS
(
-- Comando SQL original - INICIO
SELECT *
FROM Orders
ORDER BY OrderDate
-- Comando SQL original - FIM
),
q_filtered AS
(
SELECT * FROM q_target
),
q_totals AS
(
SELECT
COUNT(1) "__TotalOfRows_"
FROM q_filtered
)
SELECT *
FROM q_filtered, q_totals
LIMIT 200 OFFSET 200
Perceba que várias instruções SQL foram utilizadas para transformar a Query:
-
WITH - Permite criar e apelidar uma subquery que pode ser reutilizada.
-
COUNT - Conta a quantidade de registros
-
LIMIT - Limita a quantidade de registros
-
OFFSET - Define o número de linhas que devem ser “puladas”, ou seja, as primeira N linhas que serão ignoradas no resultado.
O Comando SQL final vai apresentar variações conforme os recursos utilizados no momento da execução da Consulta. Por exemplo, o agrupamento de registros ou o filtro aplicado nas colunas pelo usuário tornarão o Comando SQL mais complexo.
Quando é utilizado?
O comando SQL sempre será transformado caso alguma dessas operações seja realizada quando a Paginação SQL estiver habilitada:
-
Atualização dos Dados
-
Filtro em Colunas
-
Ordenação de Colunas
-
Exportação para Excel / CSV
-
Navegação entre as páginas da Grid
-
Agrupamento de Registros
Compatibilidade
Como pôde ser visto até este ponto, o recurso de Paginação SQL faz a transformação do comando SQL para aplicar a limitação e paginação aos dados, o que requer uma implementação individual para cada SGBD, pois cada um possui suas próprias particularidades e recursos.
Por este motivo, a compatibilidade é restrita apenas aos SGBD’s onde a paginação foi implementada.
Até o momento, a Paginação SQL foi implementada para os seguintes SGBD’s:
-
PostgreSQL
-
Microsoft SQL Server
-
Microsoft SQL Server Express
Particularidades - SQL Server
Ao utilizar a Paginação SQL em Consultas que utilizem o banco de dados SQL Server, o erro a seguir vai ocorrer se a cláusula ORDER BY for especificada:
A cláusula ORDER BY é inválida em exibições, funções embutidas, tabelas derivadas, subconsultas e expressões de tabela comuns, a menos que TOP, OFFSET ou FOR XML também esteja especificado.
Este erro ocorre pois o “encapsulamento” da Query original da Consulta (para fazer a paginação) resulta em uma expressão onde a cláusula ORDER BY só é permitida se cláusula TOP for especificada.
Para contornar essa situação, informe TOP 100 PERCENT logo após a palavra chave SELECT. Isso fará com que o SQL Server permita a execução do comando sem limitar a quantidade de registros, pois retornará 100% das linhas.
Exemplo:
SELECT TOP 100 PERCENT *
FROM Orders
ORDER BY OrderDate