December 13, 2008 at 5:14 am
Apologies if this has been discussed elsewhere - I have looked 🙂
We have a view that unions 15 tables, some of which contain millions of records. The view is used for one pupose it in our application - displaying a basic summary of all the records that have been entered against a particular client.
The view is VERY slow.
We have removed any nested selections from the view and now store the equivalent data in the tables themeseleves. This has improved the performance somewhat, but it's still unacceptable.
1. To what extent do case statements affect the speed of a view?
2. Some of the columns defined in the view are not actually needed from every table because theyre only used in certain instances to detemine how a record is displayed in the application.
So in the cases where data is not required for a particular column would it speed up the view if I replace it with '' or null? (see example below)
select Client_Ref as Client, Entered_By, entered_date, subject, status, 'NOTE' as Data_Type
from NOTES
union
select Client_Ref, NULL, entered_date, NULL, NULL, 'APPOINTMENT'
from APPOINTMENTS
My hunch is that SQL fetches the entire record first and then picks out what has been requested. If that is the case, then I guess substituting individual fields with null won't make much difference?
3. I have been told that using unions in a view prevents you from indexing it. Is there an alternative?
December 13, 2008 at 7:46 am
You cannot create indexed view if your view definition contains UNION.
You can use UNION ALL instead of UNION as UNION does a select distinct on the cumulative resultset.
In UNION ALL, You'll have duplicates coming in though, if multiple base tables contain identical rows....
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply