October 9, 2015 at 7:06 am
Hi our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.
I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient. Thanks for the help and all input appreciated.
October 9, 2015 at 7:09 am
thebrewersinaz (10/9/2015)
I am not really sure how a view would speed things up.
It won't.
If I have 20 users and they all call a view the view is created 20 times is that not correct?
Err, no. It's created once when someone runs the CREATE VIEW statement. If 20 users query the view, then the view is queried 20 times.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2015 at 7:19 am
Well if you're talking about running the query once to load the data into a table in your datawarehouse and then users query that table, then yes that will be faster than users rerunning a large query every time they need to see data, especially if they're trying to doing multiple queries against that same dataset for analysis.
October 9, 2015 at 7:25 am
Hi Gail thanks so much for the feedback. So when the first user calls the view the view and it physically gets created how long will that view stay available for other users and how would it refresh itself? Thanks
October 9, 2015 at 7:39 am
thebrewersinaz (10/9/2015)
Hi Gail thanks so much for the feedback. So when the first user calls the view the view and it physically gets created how long will that view stay available for other users and how would it refresh itself? Thanks
It doesn't get created when somebody selects from it. It gets created when you run the CREATE VIEW script. It will generate an execution plan for this view if one is not already cached when a select statement is executed. If the execution plan already exists in cache it will reuse it. This is the exact same behavior as any other query.
Views don't refresh themselves. They query the data when there is a request for the data. Views are persistent like a table or a stored procedure.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2015 at 7:55 am
thebrewersinaz (10/9/2015)
So when the first user calls the view the view and it physically gets created how long will that view stay available for other users and how would it refresh itself?
A view is a saved select statement. Nothing more. There's nothing to refresh.
If you have a view:
CREATE VIEW Test
AS
SELECT Col1 FROM SomeTable
GO
and you run
SELECT * FROM Test
SQL runs the query
SELECT * FROM (SELECT Col1 FROM SomeTable) Test
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2015 at 7:56 am
Sean Lange (10/9/2015)
It will generate an execution plan for this view if one is not already cached when a select statement is executed. If the execution plan already exists in cache it will reuse it.
Nope. Views don't have cached execution plans.
The query which referenced the view will have an execution plan, one which includes no mention of the view, as the view is unrolled during parsing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2015 at 8:47 am
GilaMonster (10/9/2015)
Sean Lange (10/9/2015)
It will generate an execution plan for this view if one is not already cached when a select statement is executed. If the execution plan already exists in cache it will reuse it.Nope. Views don't have cached execution plans.
The query which referenced the view will have an execution plan, one which includes no mention of the view, as the view is unrolled during parsing.
Gosh I really mangled that didn't I? I know that a view itself doesn't have an execution plan but I sure made a mess of trying to explain that. Thanks for the correction Gail.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 9, 2015 at 8:58 am
thebrewersinaz (10/9/2015)
Hi our group is debating whether we should use views or data marts. WE have some huge queries that can have many joins up to 20 in some cases. One part of our group wants to use views to pull this data and another thinks we should create a data mart off an SSIS package and run it early every morning and then have the users access the data directly from there.I am not really sure how a view would speed things up. If I have 20 users and they all call a view the view is created 20 times is that not correct? Since a view is basically just a stored sql statement (not a stored proc) I am not seeing how this is any more efficient. Thanks for the help and all input appreciated.
A view is just a single SELECT statement, just like a stored procedure is a batch of multiple T-SQL statements. The only purpose of a view is to abstract or simplify queries. The results of the view or stored procedure are not persisted or re-used anywhere in SQL Server.
Perhaps some of your co-workers are thinking about 'materialized views', which are called 'indexed views' in SQL Server. Some views can be indexed, even views containing multiple joins. However, the view must meet certain narrow requirements and it can negatively impact INSERTS and UPDATES on the base tables, because the view's index then must be updated to reflect updates in the underlying tables. Indexed views are rarely the appropriate solution in an OLTP database.
However, you may want to look into using SSRS Report Caching or Report Snapshots. From what you describe, it sounds like what's going on is multiple users need access to a rather complex report. Don't create an ad-hoc and poorly implemented data mart when the built in functionality of SSRS will give you what you need.
https://msdn.microsoft.com/en-us/library/ms155927(v=sql.105).aspx
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply