Can this query be improved? Is there a way to eliminate the duplicate function call?
-- a Special Group has many Items which have many Bookings create function f_BookingsForSpecialGroup(@specialGroupId varchar(99)) returns table as return select * from v_Booking where ITEM_CODE in (select ITEM_CODE from v_Item i where i.SPECIAL_GROUP_ID = @specialGroupId); go create view v_SpecialGroup as select (NON_BAD_BOOKINGS - PAID_BOOKINGS) as PENDING_BOOKINGS, * from ( select (select count(*) from f_BookingsForSpecialGroup(g.SPECIAL_GROUP_ID) where IS_BAD=0) as NON_BAD_BOOKINGS, (select count(*) from f_BookingsForSpecialGroup(g.SPECIAL_GROUP_ID) where IS_PAID=1) as PAID_BOOKINGS, * from SPECIAL_GROUP g ) g go
The view v_SpecialGroup is never queried directly by the application; it is used to build other views which select individual columns as needed. (You can think of v_SpecialGroup as a "base view" which exists solely to augment the SPECIAL_GROUP table. I have profiled this strategy and it seems that if you don't select the more expensive columns you don't pay for them, but I could be wrong of course...)