Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2015

    Unanswered: which operation has less work load

    which operation has less work load ?

    i have a IIS asp website which calls a sproc in SQL 2008R2.
    table A has over 2 million records
    table B has about 33 records
    we are updating the text from one column to another column effectively but either column can have nulls

    it runs something along the lines of=
    update table B
    set comment = comment
    from table b
    inner join table a on a.rowid=b.rowid

    looking at above, if tableA.comment= null and tableB.comment = null . which is a perfectly normal scenario. its going to un necessarily do null= null which is pointless?

    the customer has reported performance issues with it so (i have added indexes which worked for half a year , since updated stats and rebuild indexes to no avail ) and profile trace tells me over 2million reads and taking about 2 minutes to do it hence the time out so i was wondering to add
    additional where clauses to filter out un necessary rows i dont need to update with a null to a null ...

    update tableB
    set comment = comment
    from tableB
    inner join table a on a.rowid=b.rowid
    where tableB.comment is null
    and tableA.comment is not null

    so my question is the original blanket approach easier workload because it updates regardless , or the extra where clauses in the second approach just outweighs the work required to identify un necessary rows it doesnt need to update?

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Filtering out the NULL values will help by percentage points, but explicitly filtering out rows where a.commment and b.comment are equal will help by many orders of magnitude.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts