TSQL Улучшение запросов

Унаследовал два запроса, выполнение которых требует времени. Около 50 миллионов строк в таблице и никаких индексов. Последнее будет исправлено и, несомненно, улучшит производительность, но какие-нибудь быстрые предложения по повышению их эффективности?

Update OP_working
Set Is_fertility = 
(case
when PRIMARY_DIAGNOSIS_CODE_CLND in (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_1_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE
update OP_working
 set derived_commissioner = 
 (case
when derived_commissioner_type = 'Private/Chargeable OSV' then 'VPP00'
 when derived_commissioner_type =  'CCG_of_practise' and GP_PRACTICE_CODE not in(select GP_PRACTICE_CODE from dbo.CH_ref_practise_codes )then 'CCG_of_practise - unknown'
 when derived_commissioner_type =  'CCG_of_practise' then (select [PARENT_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type =  'AT_of_Provider' and provider_code not in (select provider_code from CH_ref_provider_codes) then 'AT_of_Provider - Unknown'
 when derived_commissioner_type =  'AT_of_Provider' then (select Commissioning_AT from dbo.CH_ref_provider_codes where OP_working.provider_code = CH_ref_provider_codes.ORG_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type =  'Commissioning_AT_of_prison' and GP_PRACTICE_CODE not in (select GP_PRACTICE_CODE from CH_ref_prison_practises) then 'Commissioning_AT_of_prison - Unknown'
 when derived_commissioner_type = 'Commissioning_AT_of_prison' then (select [HUB_CODE] from dbo.CH_ref_prison_practises where OP_working.GP_PRACTICE_CODE = CH_ref_prison_practises.GP_PRACTICE_CODE)--+' - '+derived_commissioner 
 when derived_commissioner_type =  'Dental - AT_of_CCG' and GP_PRACTICE_CODE not in  (select GP_PRACTICE_CODE from dbo.CH_ref_practise_codes) then 'AT_of_CCG - Unknown'
 when derived_commissioner_type =  'Dental - AT_of_CCG' then (select [AREA_TEAM_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type =  'AT_of_CCG' and GP_PRACTICE_CODE not in  (select GP_PRACTICE_CODE from dbo.CH_ref_practise_codes) then 'AT_of_CCG - Unknown'
 when derived_commissioner_type =  'AT_of_CCG' then (select [AREA_TEAM_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type = 'Wales' then (select [PARENT_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner


 else derived_commissioner_type
 end) 

CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_3_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_4_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_5_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_6_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_7_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_8_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_9_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_10_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_11_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
or SECONDARY_DIAGNOSIS_CODE_12_CLND IN (select ICD10 from dbo.CH_ref_fertility_ICD10)
 then '1'

when PRIMARY_PROCEDURE_CODE in (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_1 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_2 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_3 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_4 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_5 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_6 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_7 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_8 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_9 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_10 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
OR SECONDARY_PROCEDURE_CODE_11 IN (select OPCS from dbo.CH_ref_fertility_OPCS) 
then '1'
else '0'
end
)

а также

update OP_working
 set derived_commissioner = 
 (case
when derived_commissioner_type = 'Private/Chargeable OSV' then 'VPP00'
 when derived_commissioner_type =  'CCG_of_practise' and GP_PRACTICE_CODE not in(select GP_PRACTICE_CODE from dbo.CH_ref_practise_codes )then 'CCG_of_practise - unknown'
 when derived_commissioner_type =  'CCG_of_practise' then (select [PARENT_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type =  'AT_of_Provider' and provider_code not in (select provider_code from CH_ref_provider_codes) then 'AT_of_Provider - Unknown'
 when derived_commissioner_type =  'AT_of_Provider' then (select Commissioning_AT from dbo.CH_ref_provider_codes where OP_working.provider_code = CH_ref_provider_codes.ORG_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type =  'Commissioning_AT_of_prison' and GP_PRACTICE_CODE not in (select GP_PRACTICE_CODE from CH_ref_prison_practises) then 'Commissioning_AT_of_prison - Unknown'
 when derived_commissioner_type = 'Commissioning_AT_of_prison' then (select [HUB_CODE] from dbo.CH_ref_prison_practises where OP_working.GP_PRACTICE_CODE = CH_ref_prison_practises.GP_PRACTICE_CODE)--+' - '+derived_commissioner 
 when derived_commissioner_type =  'Dental - AT_of_CCG' and GP_PRACTICE_CODE not in  (select GP_PRACTICE_CODE from dbo.CH_ref_practise_codes) then 'AT_of_CCG - Unknown'
 when derived_commissioner_type =  'Dental - AT_of_CCG' then (select [AREA_TEAM_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type =  'AT_of_CCG' and GP_PRACTICE_CODE not in  (select GP_PRACTICE_CODE from dbo.CH_ref_practise_codes) then 'AT_of_CCG - Unknown'
 when derived_commissioner_type =  'AT_of_CCG' then (select [AREA_TEAM_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner
 when derived_commissioner_type = 'Wales' then (select [PARENT_CODE] from dbo.CH_ref_practise_codes where OP_working.GP_PRACTICE_CODE = CH_ref_practise_codes.GP_PRACTICE_CODE)--+' - '+derived_commissioner


 else derived_commissioner_type
 end)

Я подумывал о присоединении, а не о «выборе», но сначала хотел услышать несколько мнений.

Версия SQL Server также 2012 года.

Спасибо


sql sql-server tsql
person Raymondo    schedule 27.01.2015    source источник
comment
Высказывания не очень... читабельны. Первый оператор станет намного чище с соединением. Что касается второго, если у вас так много разных значений, вы должны поместить их в справочную таблицу и соединить ее с OP_Working, чтобы установить правильные значения.   -  person Panagiotis Kanavos    schedule 27.01.2015
comment
Похоже, вы выполняете много бизнес-логики в своем SQL, если возможно, я бы перенес ее на уровень вашей бизнес-логики и упростил SQL.   -  person R Day    schedule 27.01.2015
comment
День R — это чисто процедуры обработки данных, выполняемые ежемесячно при загрузке данных. Нет никаких слоев, кроме пакета SSIS, которые вызывают процедуру, в которой находится SQL.   -  person Raymondo    schedule 27.01.2015
comment
Сошлись на удобочитаемости Панайотиса. Как уже упоминалось, я унаследовал код, который был написан аналитиками, а не внештатными разработчиками sql. Таким образом, такие вещи, как стандарты кодирования и т. д., им неизвестны.   -  person Raymondo    schedule 27.01.2015

Ответы (2)


arrow_upward
0
arrow_downward

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

DECLARE @ICD10 TABLE ( ID INT )
DECLARE @OPCS TABLE ( ID INT )

INSERT  INTO @ICD10
        SELECT  ICD10
        FROM    dbo.CH_ref_fertility_ICD10

INSERT  INTO @OPCS
        SELECT  OPCS
        FROM    dbo.CH_ref_fertility_OPCS

UPDATE  OP_working
SET     Is_fertility = 0

UPDATE  w
SET     Is_fertility = 1
FROM    OP_working w
        JOIN @ICD10 t ON t.ID = PRIMARY_DIAGNOSIS_CODE_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_1_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_2_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_3_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_4_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_5_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_6_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_7_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_8_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_9_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_10_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_11_CLND
                         OR t.ID = SECONDARY_DIAGNOSIS_CODE_12_CLND

UPDATE  w
SET     Is_fertility = 1
FROM    OP_working w
        JOIN @OPCS t ON t.ID = PRIMARY_PROCEDURE_CODE
                        OR t.ID = SECONDARY_PROCEDURE_CODE_1
                        OR t.ID = SECONDARY_PROCEDURE_CODE_2
                        OR t.ID = SECONDARY_PROCEDURE_CODE_3
                        OR t.ID = SECONDARY_PROCEDURE_CODE_4
                        OR t.ID = SECONDARY_PROCEDURE_CODE_5
                        OR t.ID = SECONDARY_PROCEDURE_CODE_6
                        OR t.ID = SECONDARY_PROCEDURE_CODE_7
                        OR t.ID = SECONDARY_PROCEDURE_CODE_8
                        OR t.ID = SECONDARY_PROCEDURE_CODE_9
                        OR t.ID = SECONDARY_PROCEDURE_CODE_10
                        OR t.ID = SECONDARY_PROCEDURE_CODE_11

Что касается второго утверждения, его сложно оптимизировать. Попробуйте разделить случаи на разные операторы обновления.

person Giorgi Nakeuri    schedule 27.01.2015
comment
Спасибо, Джорджи / Клаузен, я полностью согласен с вашим комментарием, Джорджи, в том, что тестирование и игра - единственный реальный способ. Проблема заключалась в том, что аналитик запустил серию запросов прошлой ночью, поэтому сервер был забит им (это рабочий сервер, поэтому я не хотел останавливать пакет запросов), и сегодня один из тех, где мой мозг не Работать не хочется, поэтому я непривычно попросил в ленивом режиме. Согласен и со вторым утверждением, что иметь несколько утверждений было бы мудрее. Оба отличных ответа, ребята, и они дали моему неявному мозгу пищу для размышлений. - person Raymondo; 27.01.2015

arrow_upward
0
arrow_downward

Попробуйте это вместо этого:

UPDATE t1
SET Is_fertility = coalesce(t2.x, t3.x, 0)
FROM OP_working t1
OUTER APPLY
(SELECT top 1 1 x 
FROM dbo.CH_ref_fertility_ICD10 
WHERE ICD10 IN 
  (t1.PRIMARY_DIAGNOSIS_CODE_CLND, t1.SECONDARY_DIAGNOSIS_CODE_1_CLND,
   t1.SECONDARY_DIAGNOSIS_CODE_2_CLND, t1.SECONDARY_DIAGNOSIS_CODE_3_CLND, 
   t1.SECONDARY_DIAGNOSIS_CODE_4_CLND, t1.SECONDARY_DIAGNOSIS_CODE_5_CLND,
   t1.SECONDARY_DIAGNOSIS_CODE_6_CLND, t1.SECONDARY_DIAGNOSIS_CODE_7_CLND,
   t1.SECONDARY_DIAGNOSIS_CODE_8_CLND, t1.SECONDARY_DIAGNOSIS_CODE_9_CLND,
   t1.SECONDARY_DIAGNOSIS_CODE_10_CLND, t1.SECONDARY_DIAGNOSIS_CODE_11_CLND,
   t1.SECONDARY_DIAGNOSIS_CODE_12_CLND)
ORDER BY (SELECT 1)
) t2
OUTER APPLY
(SELECT top 1 1 x 
FROM dbo.CH_ref_fertility_OPCS 
WHERE OPCS IN 
  (t1.PRIMARY_PROCEDURE_CODE, t1.SECONDARY_PROCEDURE_CODE_1,
   t1.SECONDARY_PROCEDURE_CODE_2, t1.SECONDARY_PROCEDURE_CODE_3,
   t1.SECONDARY_PROCEDURE_CODE_4, t1.SECONDARY_PROCEDURE_CODE_5,
   t1.SECONDARY_PROCEDURE_CODE_6, t1.SECONDARY_PROCEDURE_CODE_7,
   t1.SECONDARY_PROCEDURE_CODE_8, t1.SECONDARY_PROCEDURE_CODE_9,
   t1.SECONDARY_PROCEDURE_CODE_10, t1.SECONDARY_PROCEDURE_CODE_11)
ORDER BY (SELECT 1)
) t3

Не уверен, что это будет работать с 50-миллионными строками, но я думаю, что это быстрее, чем ваш текущий скрипт. Извините, не буду пробовать вторую часть

person t-clausen.dk    schedule 27.01.2015