0

I have a new column in SQL Server 2016, on a table of type bit. (I wanted it to be a persisted calculated column but was overruled and asked to make it a bit that is maintained by program code). Its default value is 0.

I used the following SQL excerpt to attempt to set the initial values for existing rows in a dev environment but had to terminate it after 5 minutes.

update dbo.tableName set newColumn = 

cast(
    (
        case when
            (
            isNull(colA,'') <> ''
            or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
            or colC is not null
            or isNull(colD,'') <> ''
            )
        then 1 
        else 0 
        end
    ) 
as bit);

The table has about 92,000 rows and about 3,200 should have the value 1 set. (I understand the above would also be setting the remaining 88,800 row values to 0 even though the default constraint has already set them to 0 but a SELECT using the above logic in a WHERE clause executes within 1 second).

My question is why the above should take so long, and how would I identify the root cause? (Should I obtain a query plan and work from there? What would I look for?)

I re-wrote the update as follows and it completed in 3 seconds.

with cteCommonTableExpression (cteIdColumn)
as
(
    select dbo.tableName.idColumn from dbo.tableName
        where
            case when
                (
                isNull(colA,'') <> ''
                or isNull(colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
                or colC is not null
                or isNull(colD,'') <> ''
                )
            then 1 
            else 0 
            end
        = 1
)

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in (select cteIdColumn from cteCommonTableExpression);

The following - which I expect should be logically identical to the last section of code - also completes in 3 seconds.

update dbo.tableName set newColumn = 1
where dbo.tableName.idColumn in 
(select tn2.idColumn from dbo.tableName tn2 where case when
                (
                isNull(tn2.colA,'') <> ''
                or isNull(tn2.colB,datefromparts(1901,1,1)) > datefromparts(1901,1,1)
                or tn2.colC is not null
                or isNull(tn2.colD,'') <> ''
                )
            then 1 
            else 0 
            end
        = 1)

Shortly after posting here I obtained the estimated query plan which reported 89% of the cost goes to a clustered index update on the primary key. The new column is not involved in the primary key. The question becomes: why does this update require a clustered index update (especially while the alternative updates apparently do not)?

3
  • 2
    Yes, please share the plan, if you would like help with it. It will be very difficult for anyone to help otherwise. Oct 3, 2018 at 1:32
  • The visual in SSMS has 4 nodes, from left to right: T-SQL UPDATE Cost: 0%; Clustered Index Update tableName.primaryKey Cost: 89%; Compute Scalar Cost 0%; Clustered Index Scan (Clustered) tableName.primaryKey Cost: 11%. That's it. Oct 3, 2018 at 3:42
  • 2
    @youcantryreachingme if you click the link that Erik shared, you'll find a website where you can share the plan itself with us - that will be much more useful than the text you've just shared. Oct 3, 2018 at 8:18

2 Answers 2

3

Without seeing the plan...

Updating all 92k rows

  • scans the table start to end
  • updates all rows (whether value is the same or not)
  • clustered index leaf = the actual data

= you are actually doing clustered index update

When you updating 3,200 rows means you are doing 96.5% less data changes work (3200 is 3.5% of 92000) so of course it will run faster

Also the transaction size (for rollback) in the transaction log will be a lot bigger. This needs allocated and may need a log file size increase

Basically, update only the rows you need...

2
  • I understand, but 3 seconds, divided by 3,200 records, multipled by 92,000 records = 86.25 seconds - yet I had to kill the attempt after 300 seconds on the first attempt and 360 seconds on the next attempt. Hence I think there is more going on than just the fact I'm updating every row. I do agree that all the points you raised contribute to additional work and processing time but I don't think they explain the drawn out run time. Ta. Oct 4, 2018 at 3:07
  • Run time is not linear with row count. With a larger row count it changes hwo data is accessed, how big the transaction requires to rollback, how much memory is allocated (does it spill for example?) etc
    – gbn
    Oct 4, 2018 at 13:14
1

Given:

set the initial values for existing rows in a dev environment but had to terminate it after 5 minutes.

and

The table has about 92,000 rows

It was probably blocking. It's just too few rows to take that long otherwise.

And

why does this update require a clustered index update (especially while the alternative updates apparently do not)?

The new column is located on the clustered index. So that's where it must be updated.

5
  • Thanks David - I was also wondering whether it was being blocked, for the same reason. However I did attempt the update twice, some time apart - so I guess my question becomes one of investigating blocking queries (which is off topic for here and I'll chase up separately). I don't believe the column is on the clustered index however. The clustered index is a primary key constraint containing 2 columns. The column I am updating is newly added to the table with a default 0. Oct 3, 2018 at 23:30
  • A clustered index, by definition, includes all the table's columns. Oct 3, 2018 at 23:33
  • Could you clarify a little further please? From MS "An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view" and "Clustered indexes sort and store the data rows in the table or view based on their key values." It notes "one or more columns". My index specifies the 2 columns constituting the primary key. If data rows are stored in sorted order based on these 2 columns, how does a newly added column have any impact on the index or the sort order? Oct 3, 2018 at 23:50
  • In clustered index contains the data rows. They are not in some separate data structure. Oct 4, 2018 at 0:37
  • My confusion came because I thought the query plan was re-organising the index. Checking that, it says update - so I presume this simply means the data update. Oct 4, 2018 at 2:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.