3

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?

Jon Egerton
  • 40,401
  • 11
  • 97
  • 129
Matt Fellows
  • 6,512
  • 4
  • 35
  • 57
  • you could post your query, so that we can suggest best way to solve your performance issue. There had been situations where the poor performance logic could be written better way by an Experienced Developer – Naveen Babu Dec 16 '11 at 11:29
  • It really depends on your view. There are many cases where the optimizer will have no choice but to include even the columns which you are not selecting. – ivan Dec 16 '11 at 11:34
  • @NaveenBabu Edited my question with responses... – Matt Fellows Dec 16 '11 at 11:51
  • If you post code (also SQL), XML or data samples, **PLEASE** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Dec 16 '11 at 11:56
  • @MattFellows you could also change `COUNT(*)` to `COUNT(1)`. other than that i believe your `events.iTicket` is a foreign key field. so you don't have to worry about your query at all – Naveen Babu Dec 16 '11 at 18:22
  • @naveenbabu You are missing the point. I'm not trying to optimise my view. This is an example. A hypothetical question attempting to illustrate my question about the way views work. I'm querying the concept, not the actual specific view. Thanks though. Also as far as I'm aware count(*) is perfectly valid and gets optimised. – Matt Fellows Dec 16 '11 at 23:33
  • @MattFellows i never said it is invalid, the count(*) actually fetches all the columns in the table and hence the byte data that is fetches increases depending on the table being fetched. But the count(1) will resolve this issue and lets us reduce the total bytes fetched in a query hit. Thus giving a small maybe a milli-second of performance. Btw, for the question there was nothing more to add to the answer, and only input i could give was this count(1). – Naveen Babu Dec 17 '11 at 03:27
  • I think you should double check that. Count(*) does not fetch all row data as far as I'm aware. It's interpreted as count(rows). It's a special case for the count function. http://stackoverflow.com/questions/1221559/count-vs-count1 – Matt Fellows Dec 17 '11 at 07:36

1 Answers1

2

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.

MatBailie
  • 83,401
  • 18
  • 103
  • 137