October 21, 2011 at 3:44 pm
Let me preface this by saying I have NEVER written a view, my system has thousands of stored procedures and I'm just looking at ways to try and increase performance. The only negative that I've heard about views is that, in order to increase performance on those, you have to tweak the view index, which sounds to me like it would be trickier than tweaking the stored procedure code that goes against the original "real" tables....
I have a few batch jobs that run nightly, where there sole-purpose is to generate a table that is used by other stored procedures to select against.
Is there any rule-of-thumb that I can go by to determine if it would be better to use a view for these tables, or do I simply just need to create the view and create a second version of the stored procedures that go against that data to compare.
Just curious...
October 21, 2011 at 3:53 pm
Views are kind of like stored queries they themselves will not perform better than the same query executed outside the view. When SQL uses a view it does a re-write and integrates the view login into the query using it.
Now you mention view indexes, very few views have indexes, those that do are considered "materialized" views meaning in most ways they are treated as tables and they take up space and affect performance of writes to the base tables that they use. Also, not all views can have indexes on them due to the way they are structured.
Sprocs are stored programs but have a query plan already decided (in many cases), they also usually involve more than one query, view can have multiple queries but they must ALL return the say fields in the same order.
Short answer, views will almost certainly not increase performance. At the activity monitor in SSMS and see what the most expensive queries are and how often they are executed. Start with them. They will give you the most bang for the buck. Don't waste your time at this point on a poorly performing query that runs in a couple minutes but is only run once or twice a day. The sprocs that is run 30,000 times a data and runs for 5-6 seconds can give you a massive performance boost if you can shave 1-2 seconds off it.
CEWII
October 21, 2011 at 3:55 pm
Views are nothing more than saved select statements. They have no indexes on them (unless you explicitly index they view, but that's a whole nother story).
The following two are completely equivalent in terms of how they are executed..
SELECT * FROM (
Select col1, col2 from t1 inner join t2 on t1.id = t2.id) sub
and
create view sub as
Select col1, col2 from t1 inner join t2 on t1.id = t2.id
go
select * from sub
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 21, 2011 at 4:57 pm
I dislike views but not because of views... it's because many folks use them as if they were tables. If you join to a calculated column, especially an aggregated column, there will be a world of hurt for you. That's not the worst of it. People also tend to make views using other views for sources. Again, one of the biggest problems with that is that people tend to join on aggregated columns which (in SQL Server 2000, anyway... I've kind of outlawed such a thing where I work since then) meant that the underlying view had to be fully calculated before the wrapper view would return anything.
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply