Using Replication and Indexed Views to create a Reporting Server: Part 1

Reports that require heavy aggregation and run often can become taxing on our database, and perform poorly. Who wants that? Requiring our system to aggregate the data each time it is requested costs us time, and as any DBA knows, less response time is better. Luckily, SQL Server provides ways to aggregate data ahead of time, and one of the ways of doing this is through indexed views. Coupled with SQL Server’s Transactional Replication technology, we can create a reporting server with pre-aggregated data that will improve the performance of reports by orders of magnitude. In this series we’ll look at how this works, starting with today’s overview.

Well, that sounds great, right? So, how do we do it? We’ll get to that in a later post, first let’s get a basic idea of what each of these two technologies we’re combining do.

What is SQL Server Replication?

SQL Server’s replication technology, in a nutshell, lets us actively move data from one database to another. Those databases do not have to be on the same server. SQL Server will keep the data synchronized and consistent. This allows us to make our data available on one or more secondary servers in near real-time.  This can be useful for numerous reasons, not the least of which are off-loading workloads and reporting.

Replication can require a bit of work to set up but, in my experience, is very stable once configured.

So, we can move our data to a second server. That’s handy and all, but aside from moving the workload, how are we really gaining speed? That is accomplished through Indexed Views.

What is an Indexed View?

To understand an Indexed View, first we need to understand what a View is. A View is basically a way to store a query as an object within SQL Server. So, let’s imagine we have a database that stores data for our e-commerce page, and we need to track inventory. If we have a simple report that finds the name, ID and number available of widgets we currently have in stock:

SELECT i.widgetID, i.numInStock,
FROM inventory i
JOIN widgets w on i.widgetID = w.widgetID
WHERE i.numInStock > 0

We can create a View:

CREATE VIEW currentWidgets AS
SELECT i.widgetID, i.numInStock,
FROM inventory i
JOIN widgets w on i.widgetID = w.widgetID
WHERE i.numInStock > 0

And then:

SELECT widgetID, numInStock, name
FROM currentWidgets

This will give us the same result as our original report query. There are a lot of uses for views, but what we primarily need to understand here is that we can use the text of a report’s query (or sometimes something slightly different, in order to meet some requirements) as the definition of a view to create a view that will give us the same results as that report.

Simply creating a view for a report does not give us any performance gain. Getting the data from a view still runs the underlying query every time we need to get the data, which means any aggregation or joining the reports needs to do is still done at run time. This is where the index part of indexed view comes into play.

When we create an index on a view, rather than the database simply storing the query we used in the definition of the view and running it when we query the view, it actually stores the results of that query as its own object. You can think of this like a special table that is tailored to hold only the results of that specific query. This means any aggregation or joining is done ahead of time, as the data changes, leaving the results immediately available to your when you run your reports.

Note: You will see Indexed Views referred to as Materialized Views in some Database Management Systems.

In my next post, we’ll take a look at how we can combine these two technologies to create a report server. After that, we’ll getting into some details of how it’s done.

Tagged with: , , ,
Posted in reporting, SQLserver

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: