Understanding why a query is slow

by youcantryreachingme   Last Updated October 03, 2018 00:06 AM

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)


Related Questions


Updating large table worked before, now taking hours

Updated February 27, 2016 01:02 AM


Updating rows in a large table in sql server

Updated March 27, 2017 16:06 PM

Performance on updating row

Updated January 15, 2018 19:06 PM

find out postgresql query progress

Updated October 01, 2018 04:06 AM