Data

13 jun, 2017

Consultando dados sumarizados – Parte 01: usando visões

Publicidade

Recuperar informações sobre o negócio é, na minha opinião, a principal função de um SGBD. O armazenamento obviamente é importante, mas eu entendo que ele seja um meio para chegar a um fim: informar.

Inevitavelmente toda aplicação faz consultas e/ou gera relatórios para apresentar dados sumarizados, por mais simples que sejam. Mas é preciso estar atento às características e a frequência de execução dessas consultas para adotar uma estratégia adequada para geração da informação.

Casos mais comuns

Quando não há nenhum requisito especial de volumetria, por exemplo, os desenvolvedores costumam criar consultas para agregação dos dados.

A abordagem aqui é criar a declaração SQL adequada e testar a performance. Vejamos um exemplo simples: um relatório de status de pedidos de compra por produto e fornecedor. Veja a seguir a declaração e o resultado final.

USE WideWorldImporters
GO

SELECT 
	  C.SupplierCategoryName AS Category
	, S.SupplierName AS Supplier
	, I.StockItemName AS Item
	, SUM( L.OrderedOuters ) AS Ordered
	, SUM( L.ReceivedOuters) AS Received
	, SUM( L.OrderedOuters ) - SUM(L.ReceivedOuters) AS ToBeDelivered
FROM [Purchasing].[Suppliers] S
	INNER JOIN [Purchasing].[SupplierCategories] C ON S.SupplierCategoryID = C.SupplierCategoryID
	INNER JOIN [Purchasing].[PurchaseOrders] O ON S.SupplierID = O.SupplierID
	INNER JOIN [Purchasing].[PurchaseOrderLines] L ON O.PurchaseOrderID = L.PurchaseOrderID 
	INNER JOIN [Warehouse].[StockItems] I ON L.StockItemID = I.StockItemID
GROUP BY 
	  C.SupplierCategoryName
	, S.SupplierName
	, I.StockItemName
GO

Output:

Category Supplier Item Ordered Received ToBeDelivered
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) 3XL                    24                    24                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) 3XS                       6                       6                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) 4XL        1.575.453        1.571.736               3.717
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) 5XL                    14                    14                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) 6XL                    14                    14                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) 7XL                       5                       5                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) L                       9                       9                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) M                    14                    14                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) S                       6                       6                     –
Clothing Supplier Fabrikam, Inc. The Gu red shirt XML tag t-shirt (Black) XL           772.432           770.605               1.827

Duas dicas importantes:

  1. Analise o plano de execução da consulta. Ele nos mostra quais são as operações mais pesadas dessa consulta (as que tem custo maior) e isso nos orienta na criação de índices adequados à melhoria da performance dessa consulta.
  2. Não confie cegamente em ferramentas do tipo ORM. Elas são muito eficientes nas operações CRUD, mas frequentemente geram péssimas consultas de sumarização. É uma simples questão de bom senso.

Escrever a declaração SQL direto na aplicação costuma ser bastante eficiente, mas podem surgir problemas quando usamos esta mesma consulta em outras partes da aplicação. Observe que este relatório “simples” requer uma declaração de quase 600 caracteres.

Obviamente existe um risco considerável de erro ao reescrevermos a consulta várias vezes, o que pode levar, por exemplo, a inconsistência de informações. Isso sem falar na dificuldade de manutenção, caso a consulta precise ser alterada.

Visões

Uma medida interessante é criar uma visão. Assim ficamos livres de conhecer os detalhes internos da declaração SQL usada. Basta invocarmos a visão e teremos sempre os mesmos critérios para geração do relatório.

USE WideWorldImporters
GO
--criando a visao
CREATE VIEW vwRelatorioCompras
AS
SELECT 
	  C.SupplierCategoryName AS Category
	, S.SupplierName AS Supplier
	, I.StockItemName AS Item
	, SUM( L.OrderedOuters ) AS Ordered
	, SUM( L.ReceivedOuters) AS Received
	, SUM( L.OrderedOuters ) - SUM(L.ReceivedOuters) AS ToBeDelivered
FROM [Purchasing].[Suppliers] S
	INNER JOIN [Purchasing].[SupplierCategories] C ON S.SupplierCategoryID = C.SupplierCategoryID
	INNER JOIN [Purchasing].[PurchaseOrders] O ON S.SupplierID = O.SupplierID
	INNER JOIN [Purchasing].[PurchaseOrderLines] L ON O.PurchaseOrderID = L.PurchaseOrderID 
	INNER JOIN [Warehouse].[StockItems] I ON L.StockItemID = I.StockItemID
GROUP BY 
	  C.SupplierCategoryName
	, S.SupplierName
	, I.StockItemName
GO
--chamando a visao
SELECT * FROM vwRelatorioCompras
GO

Eu considero uma boa prática criar visões para as consultas mais importantes e/ou frequentes. Isso facilita muito a manutenção destes objetos.

Apesar de interessante, o uso de visões não implica em nenhuma melhoria de performance. Afinal de contas as visões são, para todos os efeitos, consultas predefinidas, que serão executadas à cada chamada desse objeto.

No próximo artigo falaremos dos casos de sumarização onde a performance é um fator crítico.

Até a próxima!