Materialize view and the performance issue

 

In this article I am trying to illustrate some points related to materialize view and the performance issue.

Views are the good example to handle the complex query.

Thinks about the situations like this

If a view performs an aggregation of millions of rows, query performance of the view decreases dramatically and this operation is performed many times every day, the database system must access millions of rows repeatedly.

The solutions is the materialized view

A new data structure must be defined to deal with this kind of scenario. A materialized view is defined just as a regular view but the result set of the query is stored as persistent data object such as table which is frequently updated from the underlying base tables when changes occur. They are useful to aggregate data in business intelligence applications with complex queries.

How I implement it

SET STATISTICS TIME ON

It displays the number of milliseconds required to parse, compile, and execute each statement.

Now execute the Query mentioned bellow:

SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)

FROM   Production.TransactionHistory t

       INNER JOIN Production.Product p on t.ProductID=p.ProductID

GROUP BY p.ProductID;

It takes 42334 microseconds to execute.

To improve the response time, our strategy is to implement the materialize view.

To implements we must follows this steps.

1.    Create a normal view

2.    Include WITH SCHEMABINDING Options

3.    Make a clustered index on this view

So, let's start

Step-1 and 2

CREATE VIEW view_totCostQtyByProd

WITH SCHEMABINDING

AS

  SELECT p.ProductID, sum(t.ActualCost), sum(t.Quantity)

  FROM   Production.TransactionHistory t

         INNER JOIN Production.Product p ON t.ProductID=p.ProductID

  GROUP BY p.ProductID;

Step-3

CREATE UNIQUE CLUSTERED INDEX Indx_TotCostQtyByProd

ON  view_totCostQtyByProd(ProductID)

 

Now execute the view

SELECT *

FROM   view_totCostQtyByProd;

It takes just 32 milliseconds, dramatically improve the performance.

Summary

So we understand that the creating materialized views dramatically improve the performance of the executions.

Hope you like it.

Posted by: MR. JOYDEEP DAS