SQL Server Performance Forum – Threads Archive
Avoiding Views
I have been using nested views to encapsulate calculations and business rules and so that I can use the results of calculations of columns in the calculations of other columns. If I don’t do this, I end up with some calculations in my queries that are very long and difficult to work with. For example: create view A asselect
sum(case when side = "sell" then trx_total else 0.0 end) as total_sold
sum(case when side = "buy" then trx_total else 0.0 end) as total_bought
from trade create view B as
select
total_sold – total_bought as profit
sum(case when side = "buy" then shares else 0 end) as shares_bought
from A create view C as
select
profit / shares_bought as average_profit_per_share Otherwise, my calculation for average_profit_per_share would look like this: select
(
sum(case when side = "sell" then trx_total else 0.0 end) –
sum(case when side = "buy" then trx_total else 0.0 end)
) / sum(case when side = "buy" then shares else 0 end) as average_profit_per_share This is only a simplified example, of course. My actual calculations would be much more complex than this (20 or 30 lines in some cases). To make matters worse, the same calculation might be needed in several different views or stored procedures. This makes for a maintenance headache. Since I use a stored procedure to bring the data into my application, I always assumed that this nesting was being "optimized out" when the stored procedure was compiled. Isn’t this the case? Is it really a bad thing (from a performance point of view) to do this? Similarly, sometimes I have created views that have lots of extra columns that I can invoke from other views and stored procedures. For example: create view A as
select a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t, u, v, w, x, y, z,
a+b as ab, b+c as bc, c+d as cd
from trade create view B as
select a, b, c, ab, bc, cd
from A create procedure P as
select ab from B The reason I do this is to use view A as a general purpose view that encapsulates some set of rules that can then be incorporated into other views. I don’t like having duplicate code. I would rather be able to change a rule or calculation in one place. Does view A really do any harm by bringing in those extra columns? Doesn’t SQL Server optimize out all of the columns that aren’t actually needed by procedure P? Rick
Rick Harrison
KnowWare, Inc.
Using views as you have described can hurt performance, although by how much is hard to say. I recommend that you incorporate all of your logic in a stored procedure instead. You will get better performance and it is easier for you, and others, to maintain code in a SP that in one or more views. Also, if you need to, you can call a SP from another SP. —————————–
Brad M. McGehee, MVP
Webmaster
SQL-Server-Performance.Com
]]>