CODEWITHSUNDEEP
sqlviewcalculated-columns
Matt Fellows
Matt Fellows

Reputation: 6522

If I make an SQL View with computed columns, but select a subset of those columns, what performance hit am I likely to take

If I have a view in SQL which contains various computed columns, some of which could be very expensive, but only select a few columns out at any one time will I be taking any more of a performance hit, than if I was to separate the views out to multiple views and use them individually?

For example if I have 5 columns in my table, and my view contains those same 5 columns, but also 10 simple computed columns and 10 expensive computed columns (using averages or similar) and decide to only select out one or two of the simple computed columns, would I expect that to be any more expensive than if I had separated the expensive columns into their own view?

Edit:
I'm specifically interested in answers regarding SQL Server and Postgres databases, but a more general response if appropriate will suffice.

Edit2:
I've looked at query plans in SQL Server and it appears to not bother making a plan for the computed columns when they aren't selected, so I'm hopeful that it's fine to combine all the columns into one view, but would like confirmation :D

Edit 3:
@NaveenBabu I don't have any performance problems yet - this is somewhat hypothetical. The extra columns will mostly be things like: DATEPART(mm, aDateField), DATEPART(dd, aDateField) ie. simple cheap extensions to the table. But there will be more complicated expensive columns like: (SELECT COUNT(*) FROM events WHERE events.iTicket = tickets.iCode) as NumberOfEvents

So I guess if you want a generic example the view would be:

CREATE VIEW TicketsView AS
SELECT 
   tickets.idx, tickets.a, tickets.b, tickets.c, tickets.d, 
   DATEPART(mm, a) as ticketMonth, DATEPART(dd, a) as ticketDay, 
   DATEPART(yy, a) as ticketYear, 
   (SELECT COUNT(*) FROM events WHERE events.iTicket = tickets.idx) as numEvents 
FROM tickets

Or something like that. The last column is clearly more expensive than the others so: If I SELECT tickets.idx, tickets.b, tickets.ticketMonth FROM TicketsView will it need to do the subselect / count to calculate numEvents, as I haven't selected it out from the view?

Upvotes: 3

Views: 2999

Answers (1)

MatBailie
MatBailie

Reputation: 86706

In SQL Server the basic principle is that Views are expanded in-line.

They're like code-templates that get copied and pasted into your own query. There are other over-heads as well, and you can specify a view not be be expanded in this way, but it's a good general description.

One thing that this means is that fields NOT referenced in your query don't get copied though.

If a join is needed to derive that column, the join is still necessary - It could duplicate or filter rows from another table, etc - But the scalar calculations would most likely not happen.

In your example, using a correlated sub-query for the last field is often slower than a join alternative. But in your case this has a benefit - If you don't select that field, the correlated-sub-query isn't happening. You're introducing a cost when it is selected, and a saving when it's not.

Upvotes: 2

Related Questions