Materialized Query Table in SQL Server 2005

views:

181

answers:

Materialized Query Table in SQL Server 2005

In DB2 there is a support for Materialized Query Table (MQT). Basicly you write a query and create a MQT. But the difference from View is that the query is pre-executed and resulting data is stored in MQT and there are some options when to refresh/syncronize the MQT with base tables.

I want same functionality in SQL Server. Is there a way to achieve same result?

I've tables with millions of rows, and I want to show summary (like total # of members, total expense and etc) in dashboard. So I don't want to count every time user gets to dashboard, instead I want to store them in table and I want that table to be refresh each night.

Any kind of hints, answers,suggestions and ideas are welcome. Thanks.

+1 A:

Yes, look at this article http://msdn.microsoft.com/en-us/library/cc917715.aspx about "Indexed view"

and look also at (WITH SCHEMABINDING) option

Dewfy 2010-04-25 10:54:43

I've investigated this before posting here. It seams that Indexed View doesn't pre-execute query and store it's result and does not give refresh options. To achieve what I want I think I need to build index on all columns of the view. But even this does not give me refreshing options, and data is refershen on demand. Am I right? Or did misunderstand it?

Azho KG 2010-04-25 11:02:54

Hmm, when you design view you should keep in mind which queries are executed with this one. Including all possible columns can cause performance degradation for modification operations. Returning to your question " I want that table to be refresh each night." - msdn explicitly handles it: "SQL Server maintains views automatically; therefore, any changes to a base table on which a view is defined may initiate one or more changes in the view indexes. Thus, additional maintenance overhead is incurred.". So may be better solution is stand alone table for night-flushing or OLAP cube?

Dewfy 2010-04-25 11:15:36

Does OLAP Cube allow refresh after some interval, but not each time data is changed? It seams that learning curve for Data Cube would be high (even though I know thoery of Data Mining and OLAP). If SQL server does not support MQT, workaround would be stand-alone tables,and triggers waking up each night and updating the table.

Azho KG 2010-04-25 11:35:09

yes, think about OLAP scheme as standalone database with own synchronization settings

Dewfy 2010-04-25 12:23:31

Thanks for your help. I decided to go with SQL Agent job, since learning curve for OLAP is high.

Azho KG 2010-04-26 08:15:38

+1 A:

It seams that Indexed View doesn't pre-execute query and store it's result and does not give refresh options.

But absolutely it does!!

An "indexed view" is a materialization of a view in SQL Server - the resulting data is assembled and stored on disk. So the query is pre-executed, in that sense.

And no, you don't have to index on every field -- just the fact you're adding a clustered index to the view (based on a suitable column) actually stores the resulting data to disk. In SQL Server, the clustered index is the data, really.

Check out this article in SQL Server 2000 Books Online: Creating an Indexed View

Microsoft clearly writes:

When a unique clustered index is created on a view, the view is executed and the result set is stored in the database in the same way a table with a clustered index is stored.

marc_s 2010-04-25 15:14:47

"DB2 provides so-called "materialized query tables" (MQTs) for the same purpose. Microsoft SQL Server introduced in its 2000 version indexed views which only store a separate index from the table, but not the entire data" - wikipedia thinks in the same way as me. If you case is true, the when the data is syncronized between base tables and view?

Azho KG 2010-04-25 16:04:56

@Azho KG: Wikipedia is wrong in this instance. The **clustered index** **IS** the data! When you add a clustered index to a view, you are **materializing** all the data onto disk. Trust me. As for updates: they are automatic - you don't need to do anything. Indexed views are always kept up to date.

marc_s 2010-04-25 17:54:01

"Indexed views are always kept up to date." - That's the problem. I've huge data and I don't want index to be kep up to date, I want to refresh data at let's say midnight. But for curiousity I tried to make clustered index on the view and I didn't get any increase in efficiency. As in normal case (without indexed view) it's taking aroung 10 minutes. Any ideas why?

Azho KG 2010-04-26 08:14:22

@Azho KG: no idea - way too many things that could be wrong. Do you have SQL Server Enterprise edition?? What does your table look like, what query do you use? Can't say without a lot more information..... just to set things straight: I have seen **dramatic** performance increases (like going from 30 min. to under a second) when properly applying a clustered index to a view

marc_s 2010-04-26 09:45:40

What Do You think abaut Replication, They said that almost every business scenario considering replication for data reporting

adopilot 2010-04-25 18:18:44

It seems it's not good idea to use replication for my case. I'm not replicating anything, just want to get summary which is estimated to be 0.01% of the whole data. But thanks for your answers

Azho KG 2010-04-26 08:27:55

+1 A:

Materialized views are based on a source table (or tables), and will update their data immediately when the source table(s) are updated. This is a powerful feature, but based on discussion on the prior posts it does not sound like what you want or need.

A simple way to do what you want is to:

Create a separate table to contain the aggregated (summarized) data
Write a process (preferrably a stored procedure) to calculate and store that data
Determine how and when to launch this procedure

Does the summarized data need to be prepared at (or "as of") a specific time, such as 12:01am? If so, create a SQL Agent job and configure it to launch the procedure at 12:01am. Can the summarized data only be prepared after a prior routine or two has prepared or finalized the preceding day's data? If so, add a call to the summarizing routine at the end of that process.

(How would this be configured this in DB2? How do you determine or configure when an MQT is refreshed?)

Philip Kelley 2010-04-25 18:27:20

1)Hi, Philip. Thanks for your answer. I was doing same thing as you suggested here. I want to do "materialization" when there is minimum number of users (it's around 3:00 AM). But I didn't know that it's called SQL Agent (I was just assuming that there should be such basic functionality that executes SQL in predefined intervals). About DB22) To be honest I forgot details, but something like DEFERED REFRESH and supply some options when to refresh (???) (I understood that 8 months is enough to forget details).

Azho KG 2010-04-26 08:23:47

ansaurus

tags:

views:

answers:

Materialized Query Table in SQL Server 2005

related questions