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 Day - това са чисто рутинни процедури за обработка на данни, изпълнявани на месечна база, когато данните се зареждат. Няма други слоеве освен SSIS пакет, които извикват процедурата, където се намира SQL   -  person Raymondo    schedule 27.01.2015
comment
Съгласен за четливостта Panagiotis. Както споменах, наследих код, който е написан от анализатори, а не от външни разработчици на 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
Благодаря Giorgi / Clausen, напълно съм съгласен с коментара ти Giorgi, че тестването и играта са единственият истински начин. Проблемът беше, че анализатор изпрати поредица от заявки снощи, така че сървърът се затруднява от него (това е активен сървър, така че не исках да спирам пакета от заявки) и днес е една от онези, при които мозъкът ми не не искам да работя, затова необичайно поисках да излезем в мързелив режим. Съгласих се и с второто твърдение, че наличието на няколко твърдения би било по-мъдрият начин. И двамата момчета със страхотни отговори и те дадоха храна на мозъка ми без шоу за размисъл - 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 милиона реда, но мисля, че е по-бързо от текущия ви скрипт. Съжалявам, няма да опитам част 2

person t-clausen.dk    schedule 27.01.2015