Post reply

View Performance with union

  • June 9, 2005 at 3:40 am

    #110894

    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 Contact

    as

    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.

  • June 9, 2005 at 7:48 am

    #564212

    I'm fairly sure that I saw that this had been fixed in SP4

  • June 9, 2005 at 3:40 pm

    #564415

    UNION ALL instead of UNION should yield a significant mprovement.

  • June 10, 2005 at 1:38 am

    #564495

    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

  • June 10, 2005 at 2:33 am

    #564506

    Apart from the fact that SQL Server doesn't actually allow the 'order by' clause in views...

  • June 10, 2005 at 2:42 am

    #564508

    Cool, that gave me a 25% improvement

  • June 10, 2005 at 2:46 am

    #564512

    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

  • June 10, 2005 at 2:59 am

    #564513

    Neat trick... unfortunately ordering this view slowed it down again

  • June 10, 2005 at 3:06 am

    #564514

    lol

  • June 10, 2005 at 3:13 am

    #564516

    Probably because all the ids are GUIDs

  • June 10, 2005 at 8:31 am

    #564627

    CREATE VIEW Contact

    AS

    SELECT id, id as Client,'Client' as Type, Name,

    FROM Client

    UNION ALL
    SELECT 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

  • June 10, 2005 at 9:24 am

    #564643

    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,

  • June 15, 2005 at 6:52 am

    #565654

    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