Does creating a CTE in this case helps?

I have a query written very poorly in SQL Server 2008

UPDATE  PatientChartImages
SET     PatientChartImages.IsLockDown = @IsLockdown
WHERE   PatientChartImages.IsLockDown = @IsNotLockdown
    AND PatientChartId IN (
    SELECT  PatientCharts.PatientChartId
    FROM    PatientCharts
    WHERE   ( PatientCharts.ChartStatusID = @ChartCompletedStatusID
              OR PatientCharts.ChartStatusID =开发者_如何转开发 @ChartOnBaseStatusID
            )
            AND PatientCharts.IsLockDown = @IsNotLockdown
            AND PatientCharts.CompletedOn IS NOT NULL
            AND DATEDIFF(MINUTE, PatientCharts.CompletedOn, GETUTCDATE()) >= ( SELECT
                                                          tf.LockUpInterval
                                                          FROM
                                                          @tblFacCOnf tf
                                                          WHERE
                                                          tf.facilityId = PatientCharts.FacilityId
                                                          ) )

This query locks the main table and results in TimeOut. IF i create a CTE first of all the updateable records and then update the main table by joining to the CTE. Will it help ??

First thing i advice you to do is to substitute IN condition with EXISTS. Second is to move all this conditional logic into CTE. Third is to substitute sub-select with @tblFacCOnf with join.

Last advice depends on your business logic and is not so important in my opinion

So at the end you will get something as

WITH search_cte as (
  SELECT  PatientCharts.PatientChartId
  FROM    PatientCharts 
  JOIN @tblFacCOnf tf on tf.facilityId = PatientCharts.FacilityId
  WHERE   ( PatientCharts.ChartStatusID = @ChartCompletedStatusID
          OR PatientCharts.ChartStatusID = @ChartOnBaseStatusID
        )
        AND PatientCharts.IsLockDown = @IsNotLockdown
        AND PatientCharts.CompletedOn IS NOT NULL
        AND DATEDIFF(MINUTE, PatientCharts.CompletedOn, GETUTCDATE()) >= tf.LockUpInterval
) --cte end                                               
UPDATE  PatientChartImages
SET     PatientChartImages.IsLockDown = @IsLockdown
WHERE   PatientChartImages.IsLockDown = @IsNotLockdown
AND EXISTS (select 1 from PatientChartImages where PatientChartImages.PatientChartId = search_cte.PatientChartId)

One additional thing I might suggest if the other suggestions don't get you enough speed is not to use a table variable. Temp Tables are often faster for large data sets and can be indexed if need be.

The update lock is being held the time it takes to compute the CTE and the time for the update. The CTE time is probably causing the time out.

To reduce the lock time to the minimum required to update the target table. I suggest you create a temp table with two columns. Col1 is the primary key or cluster key of the target table and Col2 is the value you want in the target table. Wrap the temp table creation and fill the table with values according to your business logic within a transaction. Update the target table using a join to the temp table and the value from the temp table in a seperate transaction. After update drop the temp table.

I think you should create an SQL script (or stored procedure, if you will use it from a higher level) where you store the results of your selection into a cursor (you'll only have to find the PatientCartId's of the rows to be updated) and then you should use it in your update, so, the answer is yes.

It's easy to test this, you should put these commands into a transaction, rollback the transaction and before the rollback you should perform a selection to test your results. Good luck.