Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Background:
I have a fact table in UAT Phase. Objective to load 5 yrs of data in Prod (expected size 400 Mn records). Currently it has only 2 years of data in Test.

Table Features:

  1. No of Dimensions ~ 45
  2. Measures ~ 30
  3. Non-additive measures and other columns~ 25
  4. Current data size ~ 200 Million (2 years data)
  5. Time View: 3 different Month views: Fiscal/Calendar/Adjusted (i.e same row can fall in different months based on which view one is looking for)
  6. Only One view will be required at a time by a user. (ie. only one Month Column will be used in the query, it's stopping us to do partitioning on time view)
  7. Indexes: 1 Clustered Index on the Natural Keys (8 columns).Created 3 covering Non Clustered Indexes one on the each Month column including few Dimension SKs (FKs) and all the measures).
  8. Indexes are huge(total 190 GB) because of this.
  9. Space is not constraint (1 TB allocated)
  10. 64 GB of RAM available in server.
  11. Table compression also done.

Requirement:
Queries on this Fact table should give result within 30 seconds (General queries select sum(measure) joining few Dims group by Dim Values). Reports are directly done on top of this Fact table.

Issue:
Any query which includes columns available in the Index works fine, but if we include any other columns which are not in the include..It sucks. It takes more than 5-10 minutes. Can any one suggest some solution where it works fine for any dimension/column we select. Can Index view help in this situation?

share|improve this question

migrated from stackoverflow.com Nov 6 '12 at 6:06

This question came from our site for professional and enthusiast programmers.

2  
+1 for the question. Stackoverflow rarely gets high quality questions like this from new users. –  Neolisk Nov 6 '12 at 3:24
1  
Where is the hugh table? 200 milli on is not large, but it is hardly huge. –  TomTom Nov 6 '12 at 6:13
    
What is the hardware layout? This smells like "we put it on a large disc". –  TomTom Nov 6 '12 at 6:14
    
You didn't mention if the table is partitioned. Are you using table partitioning? msdn.microsoft.com/en-us/library/ms190787.aspx –  Jorriss Nov 12 '12 at 18:05
add comment

2 Answers 2

Will an indexed view solve your issue? How up to date does the data need to be? You could create an indexed view for a few permutations. But with that many dimemsions and measures you might run out of space quickly!

How about using SSD's?

share|improve this answer
    
Data will be updated every month. How much time will it take to update the View? –  user1801862 Nov 6 '12 at 5:28
    
If your existing query takes 5-10 minutes, then the indexed view will take 5-10 minutes. When complete, when you run the same query it will come back as though it's coming out of a table (i.e. immediately). An indexed view pre-runs a particular bit of SQL. If you submit SQL that matches it, it takes it from the indexed view, rather than running it all over again. The major advantage of an indexed view is that you don't need to change your existing queries, they will automatically use it. The disadvantage is you pretty much have to create one for a few different combinations. –  ElectricLlama Nov 6 '12 at 9:34
    
But I don't suggest that you go creating multiple indexed views to speed things up - you'll run out of time and disk space eventually. It might just be one thing to put in your arsenal. –  ElectricLlama Nov 6 '12 at 9:41
    
and please... look into columnstores as suggested! –  ElectricLlama Nov 6 '12 at 9:46
add comment

Upgrade to SQL Server 2012 and use columnstores. They thrive in these requirements. Seriously, download the evaluation edition and give it a try. Drop all indexes, drop the clustered index, simply add a non-clustered columnstore index on all columns and give it a whirl. I've seen cases just like your that reduced the execution time to 2-3 seconds, mostly because of segment elimination kicking in. Some supplemental reads:

share|improve this answer
    
Upgrading to 2012 is not possible right now. ~Thanks –  user1801862 Nov 6 '12 at 17:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.