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

    Unanswered: which operation has less work load

    which operation has less work load ?

    scenario=
    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
    Location
    In front of the computer
    Posts
    15,579
    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.

    -PatP
    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
  •