September 26, 2008 at 7:07 am
The application I am working with almost exclusively uses views for data access.
(The developers here think stored procs are evil, too complex etc...)
We have views that call views that call views that call views that ...
Anyway, there are a lot of views nested apon one another and they take an age to get even the simplest piece of data.
A want to change them to stored procs going forward, but could someone explain the effect that all the nested views are having? I mean how sql server deals with them.
How are they treated / optimised (or not)?
The query plans are hilarious... 😀
September 26, 2008 at 8:33 am
A view is just a query. They can be somewhat optimized, but since they usually don't include much in the WHERE clause, there's little that can be done. So, if you've got a series of queries calling queries, calling queries, all of them performing SELECT statements against the underlying tables without limits, you're right, the execution plans are going to be funny looking. That's assuming you mean lots of table scans and poor join choices are funny.
Stored procedures aren't magic. They're just a query, like the views, but one that can be more granularly optimized. If they're really opposed to stored procs then try suggesting the use of parameterized queries. This would be dynamic, client generated tsql direct against the tables, but it would be a plan that can be reused by the engine and one that you can suggest optimizations for that just aren't going to be available through all the views.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 26, 2008 at 8:41 am
Remember though, that (non-indexed) views don't get their own execution plans. In a way - they don't get optimized on their own at all.
This is because when a view is used in a statement, the NAME of the view is replaced by the TEXT of the view at the time when the outer SQL statement is being parsed. So - only the OUTER query is given an execution plan; the view itself doesn't get one.
In the same vein, materialized (or indexed) views also don't get execution plans, because they're essentially no longer treated as code but as a table. The "materialized version" of the view (i.e. essentially a hidden, indexed, temp table of the values) is used.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 26, 2008 at 12:33 pm
The most common reason nested views end up slow is because of returning unused rows and columns.
If, for example, you have a view for:
create view MyView1
as
select MyTable1.PersonID, FirstName, LastName, DateOfBirth, Gender, MaritalStatus, NumberOfChildren
from dbo.MyTable1
inner join dbo.MyTable2
on MyTable1.PersonID = MyTable2.PersonID;
And you have a view for:
create view MyView2
as
select CustomerStatus, FirstName, LastName
from dbo.Customers
inner join dbo.MyView1
on Customers.PersonID = MyView1.PersonID;
That's the same as writing:
create view MyView2
as
select CustomerStatus, FirstName, LastName
from dbo.Customers
inner join
(select MyTable1.PersonID, FirstName, LastName, DateOfBirth, Gender, MaritalStatus,
NumberOfChildren
from dbo.MyTable1
inner join dbo.MyTable2
on MyTable1.PersonID = MyTable2.PersonID) as MyView1
on Customers.PersonID = MyView1.PersonID;
In this case, even though it only needs the first and last name fields and the person ID from MyTable1, it still has to do the join to MyTable2, pull those columns from it, then discard them in the final query. It's a lot more IO, a lot more RAM, and a lot more CPU cycles.
Take it a few more nesting levels, and you'll see how much of a mess it can be.
Views can be quite nice for queries you have to write over and over and over again. I have two tables in one of my main databases, and you almost never need data from one without needing data from the other (it's a one-to-many for closely coupled data). After the 100th time I had to write a join between the two, I wrote a view that pulls the most common columns, added a few calculated columns that were showing up in dozens of Where clauses, and made querying those two tables much easier. But I only included the most common columns from each.
Views can also be badly misused, when they make unnecessary joins, pull dozens of unneeded columns, etc. They make writing the code really, really easy, at the cost of making the database really, really slow.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply