A short introduction into ‘Indexed Views’ a really handy performance-improvement tool available in SQL Server.

I’ve generally tried to stay clear of using traditional (non-indexed) SQL Views as they severely hinder performance when building applications that query a large set of data.

Traditional SQL Views and the Problems they Cause

Here is what happens when you create a View on a large database: Typically you’ll want to see data from several tables aggregated into just the results you are looking for, and while it is true that this happens, the view is a virtual query that takes up no space so every query you make to the View will be passed on to the underlying tables. Worst of all, if you try to use View in one of your stored procedures, the view needs to be fully resolved to all underlying records even if you use a WHERE clause outside it to limit a subset of data, however the same does not happen if you get rid of the View and use the same SELECT query with a WHERE clause!

You can imagine that if you are trying to build a ‘dashboard’ on a web application that gives you some totals and gets hit every 2-3 seconds, that means that millions of rows will be traversed over and over again. This can be somehow mitigated with cached output on stored procedures but its still murder on the database.

Improving Performance with ‘Indexed Views’

Now here comes the exciting bit:

• What if you could automatically store just the records you need to create your dashboard?

That is exactly what happens when you create an index in one of your views. The data becomes materialized to disk and the results you are after are available (ie. ‘cached’) without having to query the underlying tables every time you are after some data.

The Downside of Using Indexed Views

Be aware that here are a couple of drawbacks in using this type of construct.

1. First, your underlying tables become ‘schema-bound’, this means that you can no longer get rid of them or change their structure (add an extra column for example) without dropping the view first.
2. Second, any insert or update into the underlying tables will be slowed down because they cause a refresh of the indexed view. This means transactions involving INSERT, DELETE or UPDATE into these tables will ideally have to be batched (ie, try to avoid inserting/updating one row at a time, insert/update many rows at a time instead)

However, in my opinion, the drawbacks may be well worth it, as most applications involve many database reads and few database writes.

More about Indexed Views

• Improving Performance with SQL Server 2008 Indexed Views

Support for Indexed Views in other database systems.

Oracle 8i and upwards have Materialized Views which are a very similar feature, MySQL however is one of those database systems that do not support Materialized (or Indexed) Views.

If you want to have similar functionality in MySQL and you use Stored Procedures for inputting data into your database, you can enhance the Stored Procedures that update/insert data by running an extra calculation at the end of the procedure that updates a summary table which acts as your view. This is essentially doing the same thing as an Indexed View but keeping it updated manually.

Hope the explanation was useful.