Editorials

I have found a few things that you start doing in your database when your application is not appropriately normalized. These indications are self-evident when you think about them.

  • You find you need to materialize views.
  • You find you have a huge number of indexes on tables.
  • You find you have a lot of OR operations in your queries.

All of these symptoms can often be optimized through some form of normalization. We’ll talk about these as we finish this week.

Materialized views are views that physically store the results of the view definition to disk. You have to turn on Schema Binding when you create a Materialized View so that SQL Server knows that underlying tables (or views) may not be altered. The reason is obvious. When you query a standard view, the query defined by the view is executed and the results are returned. In a materialized view the query is executed when the view is defined, and the results are materialized to disk just as if it were its own table. The only difference is that you don’t modify a materialize view directly like you would a table. You update the tables that are used in the view, and the view is then modified.

Materialized views begin to appear when you have a query with lots of joins. A magic number used to be around 16. You’re mileage may vary. Complicated queries are sometimes also converted into materialized vies. By using a materialized view, the work to create the complicated data is only executed when data is modified. However, every read from the materialized view reads directly from disk in a pre-populated set defined by the query.

This allows you to create an automatically updated different look at the data. If you have filters, your materialized view may only contain subsets of the data from the base tables. So, updates may not impact a view. The big issue is, just like indexes on a table, a materialized view must be materialized any time an underlying table is modified. This can have a big impact on the Create, Update, and Delete performance on your base tables. The advantage is that Reads can be highly optimized.

So, as a rule of thumb, materialized views probably mean you need different normalization, or you need a reporting database. When you do use them, it makes a big difference if the data against which they apply is mostly static, changing on a daily basis or less.

Tomorrow well look into over indexing.

Cheers,

Ben