Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kimi86
Yak Posting Veteran
79 Posts |
 Posted - 2012-07-16 : 10:09:45
|
| I am creating a massive view with around 250 columns. Let me call it consolidated_ViewThis view is created by joining 11 views on a single key which is an int. I need to be sending data in this view to few business users on daily basisA select * from the consolidated_View takes around 6 hrs to populate data which is tooo much. To reduce the time I created a job that physicalizes the 11 views into tables. Now the consolidated_View takes 4 hours which is also a lot. I would love to physicalize the consolidated_View but that would take too much space which I don’t have.Any suggestions what I can do to just be able to get the data on daily basis without taking so long |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-16 : 10:13:38
|
| how many rows does your select from 11 tables retrieve? did you have a look at execution plan for query?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2012-07-16 : 10:18:14
|
| The view is massive. It generates around 4546764 rows daily. I tried to look at the execution plan but could not find much clue. I am not very efficient in reading execution plans. But As i said i am doing a joining on a single key which is an integer |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-16 : 10:27:55
|
| do you've indexes present on joining columns? also do you really need all these records. can you apply filters to bring down record count?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2012-07-16 : 10:54:59
|
| not really. Actually I have physicallized ony 6 of the 11 views instead of phisicallizing them all. These were the ones which took longest while running individually. If i have to create index on all the joining tables and views i will have to create indexed views.. is that right?? |
|
|
|
Kimi86
Yak Posting Veteran
79 Posts |
Posted - 2012-07-16 : 10:57:57
|
| also if i have to create indexed views its better that i phisicalize all the remaining views as well since indexed views also take up space. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-16 : 11:32:20
|
quote:
Originally posted by Kimi86
also if i have to create indexed views its better that i phisicalize all the remaining views as well since indexed views also take up space.
i was asking on physicalised tables------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Performance related issue