Блог

У меня есть запрос, написанный очень плохо в 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
                                                          ) )
  

Этот запрос блокирует основную таблицу и приводит к таймауту. ЕСЛИ я создам CTE сначала все обновляемые записи, а затем обновлю основную таблицу, присоединившись к CTE. Поможет ли это??

Первое, что я советую вам сделать, это заменить IN condition на EXISTS . Второе — перенести всю эту условную логику в CTE. Третье — заменить подвыбор на @tblFacCOnf на join.

Последний совет зависит от вашей бизнес-логики и, на мой взгляд, не так важен

Итак, в конце вы получите что-то вроде

 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)
  

Еще одна вещь, которую я мог бы предложить, если другие предложения не обеспечат вам достаточной скорости, — это не использовать табличную переменную. Временные таблицы часто выполняются быстрее для больших наборов данных и при необходимости могут быть проиндексированы.

Блокировка обновления удерживается в течение времени, необходимого для вычисления CTE и времени обновления. Время CTE, вероятно, вызывает тайм-аут.

Сократить время блокировки до минимума, необходимого для обновления целевой таблицы. Я предлагаю вам создать временную таблицу с двумя столбцами. Col1 — это первичный ключ или ключ кластера целевой таблицы, а Col2 — это значение, которое вы хотите внести в целевую таблицу. Завершите создание временной таблицы и заполните таблицу значениями в соответствии с вашей бизнес-логикой в рамках транзакции. Обновите целевую таблицу, используя соединение с временной таблицей и значение из временной таблицы в отдельной транзакции. После обновления удалите временную таблицу.

Я думаю, вам следует создать SQL-скрипт (или хранимую процедуру, если вы будете использовать его с более высокого уровня), где вы сохраняете результаты вашего выбора в курсоре (вам нужно будет только найти PatientCartId строк, которые будут обновлены), а затем вы должны использовать его в своем обновлении, так что ответ — да.

Это легко проверить, вы должны поместить эти команды в транзакцию, выполнить откат транзакции и перед откатом выполнить выбор для проверки ваших результатов. Удачи.