View Performance with union
-
I have an old system that includes a 'Client' table and a 'Nurse' table. Now they want to add CRM functionality and need to be able to deal with 'Contacts', where a contact could be either a client or a nurse.
So I created a simple view 'Contact' which unions key information from the 2 tables.
create view Contactas
select id, id as Client, null as Nurse, 'Client' as Type, Name,
from Client
union
select id, null as Client, id as Nurse, Type, [Name],
from Nurse
Unfortuantely this view runs like a dog.
I am wondering if it has to make a temporary table because of the union?
The following query takes 30 seconds with 2000 rows in the document table, but under 1 second when the union is removed from the view:
select X.id,
(
select R.Name
from Contact as R
where ((R.Client is null and X.Client is null) or R.Client = X.Client)
and ((R.Nurse is null and X.Nurse is null) or R.Nurse = X.Nurse)
)
from Document as X
Is there any way to speed up the performance of this view?
Thanks.
-
I'm fairly sure that I saw that this had been fixed in SP4
-
UNION ALL instead of UNION should yield a significant mprovement.
-
It could be improved by an "order by" within your view, which promotes your selection criteria when you invoke your select view...
Regards,Coach James
-
Apart from the fact that SQL Server doesn't actually allow the 'order by' clause in views...
-
Cool, that gave me a 25% improvement
-
Don't forget, you can use the select top 100 percent trick in a subquery to allow you to do the orderby in a view.
create view test
as
select * from (select top 100 percent * from table order by column)mm
-
Neat trick... unfortunately ordering this view slowed it down again
-
lol
-
Probably because all the ids are GUIDs
- CREATE VIEW Contact
AS
SELECT id, id as Client,'Client' as Type, Name,FROM Client
UNION ALLSELECT id, id as Nurse, Type, [Name],FROM Nurse
took out the NULL's wich don't make sense for you.I don't know why you keep "id as Client" and "id as Nurse" are they different from ID?
Kindest Regards,Vasc
-
Arr yes... there is a reason for that.
Tables that reference a contact e.g. document have a link to client and a link to nurse, making the primary key on Contact the Client,Nurse pair, even though one will always be null.
The reason for that was to allow referential integrity to be applied using a foreign key constraint between the base tables (i.e. document.nurse - nurse.id and document.client - client.id).
However having had this performance problem I am wondering if using the id,type pair as the primary key and then enforcing referential integrity using a trigger might not have been a better optioin.
Cheers,
-
If the records are unique, u might try using UNION ALL instead of UNION.
regds/ramanuj
🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply