Solved

When an indexed view is not an option..

Posted on 2007-12-05
9
353 Views
Last Modified: 2012-06-22
I am wondering how I should most quickly fetch the data from a group of tables for reporting / viewing.

My tables are slowly changing, meaning that I use a getDate() function to pull the right records between effective and expire dates.  An admin may be making changes occassionally to some records, but mostly the data is read-only by site visitors.  It's ok for the changes made by the admin not to go live immediately, of if urgent, for them to click a button or something to force them live.

I have a big query which brings a dozen tables together and can be a bit slow.
It includes case statements and left joins and getDate() which not allowed in Indexed Views.

I'm wondering if it makes sence to create a table that I populate from my big query, add various indexes to the table and populate it every night or every few hours or something.   Are there downsides to this?   It seems indexed views are not an option...
0
Comment
Question by:gdemaria
  • 4
  • 3
  • 2
9 Comments
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 400 total points
ID: 20416430
>> I'm wondering if it makes sence to create a table that I populate from my big query, add various indexes to the table and populate it every night or every few hours or something.   Are there downsides to this?

This is a classic start to a data warehouse or data mart.

If you can deal with data being slightly out of date then you can run an hourly/nightly/whatever job to populate your data mart.

You can take advantage of this step to denormalise the data to make things faster.


Another option is to build a cube. If the data is numerical in nature and there's lots of summaries going on, build a cube.

If the data is list-like in nature (big strings and ordered detail lists) then go the data mart route.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20417039
Hi nmcdermaid,
 Thanks for your response.

 Although numbers are involved, the numbers are not totalled or averaged in any way, so I htink data warehousing is the way to go.
 I understand how to denormalize the data with indexes for faster quering.   I am wondering, however, if there are specific SQL Server 2005 features or special data definitions that I need to look for in order to accomplish this.  Or am I basically building the tables and indexes and that's the meat of it.

  Also, does SQL Server 2005 have anything like Oracle's Materialized Views?  I've heard that Indexed Views are Microsoft's answer, but they aren't even close.  You may not be familiar with Oracle, but I'm wondering if there are other SQL Server options that I am not aware of take "snap shots" of the data.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20417302
I haven't got much experience with Oracle. The main idea of indexed views in SQL is so that when a query refers to tables, if there is pre-joined indexed view that already has the answer, it automatically goes to the indexed view, without a code change. Is that the way Oracle materialized views work?

Couldn't you use the GETDATE() outside the indexed view? or is it more complicated than that?

i.e. you create an indexed view that satisfied everything in your query (joins, summarys, but no getdate()). Then run your normal select (including GETDATE())

You'll have to experiment but its probably worth

1. Creating an indexed view as close as possible to your query
2. Run your original query and check the execution plan (CTRL-L)
3. See if the execution plan refers to your indexed view

You may get a little performance increase even if you can't code the whole lot in the view.

There are other things you can do to increase performance like set the database file to read only, mess with disk I/O, stuff like that.
0
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 100 total points
ID: 20512263
What if you created another table

WarehouseDate (WHDate datetime)

and joined to that on WHDate instead of using getdate() in your view.

Then you can have an indexed view that will change when the value of WHDate changes.

Change WHDate on some regular basis (hourly, daily etc.) using a SQL Agent job.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 39

Author Comment

by:gdemaria
ID: 20513946
kselvia, that's a very clever idea !
I would only need to change the value of the WHdate nightly as time is not a factor.

I have tried, however, to create an indexed view in another, less complex, situation and found it nearly impossible.  Indexed Views have so many restrictions it's incrediblely difficult to make any use of them at all;  no unions, no case statements, nothing inside of count(xxx), no 'distinct' or left joins (i think).  

What a nightmare !

But I wonder if your suggestion about the WHdate would help speed up regular queries or views rather than  using getDate() ?

Thanks!
0
 
LVL 12

Expert Comment

by:kselvia
ID: 20517656
Using WHdate in other queries would not be faster than using getdate().  Chances are it may even hurt performance.

As nmcdermaid said, it seems you could utilize an indexed view to generate a basis from which to make further queries which CAN have your aggregates, cases, etc.

Are you pretty confident your existing tables have the proper indexes for the kinds of reports/complex queries you are running?

Maybe there are tuning opportunities there.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20518073
Actually the seperate table thng is along the lines of a calendar table.. you have a flag for current date, and you have another two dozen flags for same day lastweek, same day last year, week to date etc.

I recall now that the oracle equivalent of indexed views is query rewrite.
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20518076
Futher to that, if you are indeed creating a data warehouse, then all of your case statements, outer joins etc. would have already been processed, and no complex queries would be required.

It sounds like many of your issues would be satisfied with a data warehouse.
0
 
LVL 39

Author Closing Comment

by:gdemaria
ID: 31412996
Thanks for the great information.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now