 | |
Re: View or Not Views
microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: View or Not Views -
12-20-2006
, 07:36 AM
|
In my project we are using OLAP for some reports and just SQL with others tools for operational reporting. To create my cube, i'm using view. But for my operationl report should i use view or not. I'm wondering concerning performance of the view. When i'm using a view, SQL Server create a physical table somewhere in memory, so for a fact table i'will use a lot of storage space for nothing. What are you advice ?? |
Unless indexed, a view does not exist as a stored set of data values in
a database.
When you reference it , it just execute like other SQL Qeury. SQL
Server is not creating any physical table and handle it like other TSQL
Statement. So, you can use views in your reports.
Regards
Amish Shah
http://shahamishm.tripod.com
#2
| |||
| |||
|
|
Thank you for your answer. If I create an indexed view, i will have a stored set of data so i wil increase the disk space needs. If my fact table is 500Go, i will need 500Go for my view, Is it true? "amish" wrote: MIB wrote: In my project we are using OLAP for some reports and just SQL with others tools for operational reporting. To create my cube, i'm using view. But for my operationl report should i use view or not. I'm wondering concerning performance of the view. When i'm using a view, SQL Server create a physical table somewhere in memory, so for a fact table i'will use a lot of storage space for nothing. What are you advice ?? Unless indexed, a view does not exist as a stored set of data values in a database. When you reference it , it just execute like other SQL Qeury. SQL Server is not creating any physical table and handle it like other TSQL Statement. So, you can use views in your reports. Regards Amish Shah http://shahamishm.tripod.com |
#3
| |||
| |||
|
|
no, an indexed view take the space of the index + related content. and an indexed view is usefull when there is group by clause. Like storing sum of sales by month and store, this indexed view will takes few Kb or Mb (while the source table contains day, product, promotion etc... info) So an indexed view will take less space then the original tables. if your indexed view as the same size of you original tables its because your view contains the same level of detail as your source table, then there is no advantage of using indexed views. a view as the performance of the source tables and the indexes of these table. SQL server create a "table" in memory only when the query use order by or group by clause. SQL Server use the tempdb database to store temporary results and do the required calculations, but its a per-session "table", and you can't access or use these result directly (only the internal SQL cache will helps you). Simple queries don't required the usage of the tempdb database. Badly designed SQL statement can produce a huge tempdb usage, so take the time to create good queries. For your reporting side, I recommend to use your cube as the source of the report instead-of the database. The performance advantage will be very high. If you really want to access the database in your reports and you always access aggregated data, then create pre-aggregated tables to store the result and use these tables instead of you 500Gb table. "MIB" <MIB (AT) discussions (DOT) microsoft.com> wrote in message news:AF27FDCA-4468-4A3D-A096-CE0DFF75ABD7 (AT) microsoft (DOT) com... Thank you for your answer. If I create an indexed view, i will have a stored set of data so i wil increase the disk space needs. If my fact table is 500Go, i will need 500Go for my view, Is it true? "amish" wrote: MIB wrote: In my project we are using OLAP for some reports and just SQL with others tools for operational reporting. To create my cube, i'm using view. But for my operationl report should i use view or not. I'm wondering concerning performance of the view. When i'm using a view, SQL Server create a physical table somewhere in memory, so for a fact table i'will use a lot of storage space for nothing. What are you advice ?? Unless indexed, a view does not exist as a stored set of data values in a database. When you reference it , it just execute like other SQL Qeury. SQL Server is not creating any physical table and handle it like other TSQL Statement. So, you can use views in your reports. Regards Amish Shah http://shahamishm.tripod.com |
#4
| |||
| |||
|
|
Thank you, but in my project we want to use tables directly just for operational report, for aggregate data we plan to use MOLAP. A consultant told us , that olap isn't for operational reporting (to many details). "Jeje" wrote: no, an indexed view take the space of the index + related content. and an indexed view is usefull when there is group by clause. Like storing sum of sales by month and store, this indexed view will takes few Kb or Mb (while the source table contains day, product, promotion etc... info) So an indexed view will take less space then the original tables. if your indexed view as the same size of you original tables its because your view contains the same level of detail as your source table, then there is no advantage of using indexed views. a view as the performance of the source tables and the indexes of these table. SQL server create a "table" in memory only when the query use order by or group by clause. SQL Server use the tempdb database to store temporary results and do the required calculations, but its a per-session "table", and you can't access or use these result directly (only the internal SQL cache will helps you). Simple queries don't required the usage of the tempdb database. Badly designed SQL statement can produce a huge tempdb usage, so take the time to create good queries. For your reporting side, I recommend to use your cube as the source of the report instead-of the database. The performance advantage will be very high. If you really want to access the database in your reports and you always access aggregated data, then create pre-aggregated tables to store the result and use these tables instead of you 500Gb table. "MIB" <MIB (AT) discussions (DOT) microsoft.com> wrote in message news:AF27FDCA-4468-4A3D-A096-CE0DFF75ABD7 (AT) microsoft (DOT) com... Thank you for your answer. If I create an indexed view, i will have a stored set of data so i wil increase the disk space needs. If my fact table is 500Go, i will need 500Go for my view, Is it true? "amish" wrote: MIB wrote: In my project we are using OLAP for some reports and just SQL with others tools for operational reporting. To create my cube, i'm using view. But for my operationl report should i use view or not. I'm wondering concerning performance of the view. When i'm using a view, SQL Server create a physical table somewhere in memory, so for a fact table i'will use a lot of storage space for nothing. What are you advice ?? Unless indexed, a view does not exist as a stored set of data values in a database. When you reference it , it just execute like other SQL Qeury. SQL Server is not creating any physical table and handle it like other TSQL Statement. So, you can use views in your reports. Regards Amish Shah http://shahamishm.tripod.com |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.