December 8, 2010 at 9:23 am
Hey Guys --
I'm an old school DBA who has always used Stored Procedures for most things, but in our current project the development team wants to rely heavily on Views instead of Stored Procedures when getting data back from the database for the application. Procedures are only being used when making inserts, updates, or deletes, but they want to use Views for everything else.
In most cases this works fine because they're just selecting a single record from one or more tables joined together so performance is fine. Problem though is with lengthy reports or for datasets that need more heavy processing Views just don't cut it. In prior projects Views weren't used at all, instead we used Stored Procedures for everything, but now that they want to use Views I'm finding some headaches trying to give them what they need.
I think the biggest hurtle is they're writing SQL as if it's an OOP application with TONS of functions and Views that call each other. There are literally cases where a View calls a Function which uses a View which calls another View and so on -- layered logic which is not only horrible to troubleshoot but also I believe performance is taking a huge hit though I'm not sure how to prove this case.
I know the pitfalls of using Views, but being that we just recently moved to SQL 2008 from SQL 2000 I don't have much experience with the new performance and statistics tables in SQL 2008. I don't have any specific examples, but are there any specific examples or documents that might help me do some performance testing where views are chained together?
Thanks for any advice --
December 8, 2010 at 9:40 am
The problem with views is that devs will want to build "one view to rule them all, one view to bind them, one view to bring them and in the darkness destroy any hope of decent database performance anyone ever had" (okay, it's less poetic than the original).
The issue is that a view calling a view calling a view will almost certainly require querying tables that aren't needed in every case where the outer view will be needed. That means heavier I/O, more CPU, more RAM, and more time working out execution plans and worse plans anyway.
The way to prove this, is take one of the more roundabout (heavily nested) views, and query some simple data out of it, and get the execution plan. Then write a query script that gets the same data, but does so in the most efficient, lean manner possible, and get that execution plan. Then show everyone the two execution plans and explain the differences between them. That'll work if the people involved either (a) understand execution plans enough to "get it", or (b) can be intimidated by complex diagrams of execution plans with scary numbers in them.
Alternately, run a million queries against the heavily nested views, and a million queries against an optimized query, and record the total time each takes, and the total I/O each takes, and tell people the difference. "The view took an average of 37 times as long to run, and took an average of 1-million times as much I/O, as the optimized query. And we all know that the most likely single-point-of-failure on a server is overloading the I/O systems and causing disk failures." That kind of thing. This one is slightly more FUD, but it might be what's needed.
As a penultimate resort, let them build their nested views and UDFs, let them test it on their five-row tables in dev, and then use a data generator to fill the tables in the test environment with a real data load, and let them see what happens there when you do load testing.
As a last resort, CYA as best you can.
- 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
December 14, 2010 at 8:33 am
For the most part I agree with GSquared. The performance issues can grow as you layer and nest things. UDFs and Views, while abstracting and reusing code, often mask issues. It's not the same as the re-user of code in other applications.
Why not use Stored procedures for the reports? Ask them that. If they are already using them for insert/update/delete, why not use them for reports as well?
December 24, 2010 at 4:18 am
Hooray, SSC is so appropriate again that I swear you're monitoring my workload!
As a Christmas present I've just been given the projects of a fellow developer who is leaving and I'm going through trying to understand what he's done. All I can say is "There's gotta be a better way.." and "WTF".
This particular project has the queries from hell - it's all nicely in stored procs but the procs query a non-indexed view which is made up by querying some tables (one primary key index) and three other non-indexed views which are made by querying other non-indexed views...... I originally identified this lot through the performance dashboard reports because the graphs were through the ceiling compared with everything else! Not only that but the procedures are generally dynamic sql all called from code using "exec procname " + params.
I'm of the "data access layer and lots of simple stored procs which can be cached" school and don't generally use dynamic sql in applications. This lot uses one generic search procedure which calls another procedure just to get the column names!
Then they wonder why it runs slowly from a remote site. :rolleyes:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply