SQL Server Performance

Best Way to Combining Two Records

Discussion in 'T-SQL Performance Tuning for Developers' started by rcatiggay, Oct 7, 2004.

  1. rcatiggay New Member

    I have a table that contains records from an outside source, there are times that we would like to "override" bits of information from the original record. We won't to still maintain the original record but reflect the "user overridden" data.

    Table A (Original Data)
    colB denotes the associated original record, if its 0 then its the original record.

    colA colB colC colD colE .....
    1 0 N XX 123
    2 0 N XX 456
    3 0 N XX 789
    4 0 N XX 012

    Table not with user overridden data
    colA colB colC colD colE .....
    1 0 N XX 123
    2 0 N XX 456
    3 0 N XX 789
    4 0 N XX 012
    5 1 null JJ 012
    6 3 null YY null


    So the query that I came up with is the following solution to retrieve all the
    records to include the appropriate "user-overridden" data.

    SELECT A.[ID], A.Parent, ISNULL(B.Field1,A.Field1), ISNULL(B.Field2,A.Field2),
    ISNULL(B.Field3,A.Field3)
    FROM TableA As A LEFT OUTER JOIN TableA As B ON (A.ID = B.Parent)
    WHERE A.Parent = 0

    Two questions:
    1) Is my solution effecient sql wise? This table will have between 1 - 1.5 million records of which half of them could be overridden so atmost there could be 2.25 million records. There is about 20 fields in the table that the user can override. Under this scenario will performing the ISNULL on that many fields for this many records the best way? I'm also unsure about having to join the same table to itself in this manner.

    2) Is it a better approach to have 2 tables one for the original data and another
    for that cotaings only the overridden data?

    Thanks in advance for your suggestions...








Share This Page