Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Новый топик    Ответить
 Слишком тяжелый запрос. Как оптимизировать?  [new]
kT_________
Member

Откуда:
Сообщений: 381
Коллеги, вопрос...
Как оптимизировать запрос, в текущей версии?

Запрос высчитывает количество изменений по каждому полю, м б есть средства ДБ2 заточенные под эти цели?



with t1 AS
(
  SELECT H.CONTRACT_ID,H.H_CREATE_DT FROM DDM.H_CONTRACT H WHERE  H_CREATE_DT BETWEEN TIMESTAMP_FORMAT('2013-08-14 00:00:00',
       'YYYY-MM-DD HH24:MI:SS') AND TIMESTAMP_FORMAT('2013-08-14 23:59:59','YYYY-MM-DD HH24:MI:SS')
),
t2 AS
(
  SELECT CONTRACT_ID, H_CREATE_DT
  FROM DDM.H_CONTRACT
  GROUP BY CONTRACT_ID, H_CREATE_DT
  HAVING (H_CREATE_DT=MAX(H_CREATE_DT) AND H_CREATE_DT<TIMESTAMP_FORMAT('2013-08-14 00:00:00',
       'YYYY-MM-DD HH24:MI:SS')) 
)
,
t3 AS
(
SELECT CONTRACT_ID, H_CREATE_DT FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t2.CONTRACT_ID=t1.CONTRACT_ID)
),
t4 AS
 (
   (SELECT H.*,(0) old_flag FROM DDM.H_CONTRACT H,t1 WHERE H.CONTRACT_ID=t1.CONTRACT_ID AND
                                                         H.H_CREATE_DT=t1.H_CREATE_DT)
    UNION
   (SELECT H.*,(1) old_flag FROM DDM.H_CONTRACT H,t3 WHERE H.CONTRACT_ID=t3.CONTRACT_ID AND
                                                          H.H_CREATE_DT=t3.H_CREATE_DT)
 )
,
t5 AS
 (
   SELECT
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT CURRENCY_TP_CD)
                    ELSE COUNT(DISTINCT CURRENCY_TP_CD)-1
                    END) CURRENCY_TP_CD, 
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT CONTR_LANG_TP_CD)
                    ELSE COUNT(DISTINCT CONTR_LANG_TP_CD)-1
                    END) CONTR_LANG_TP_CD,
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT FREQ_MODE_TP_CD)
                    ELSE COUNT(DISTINCT FREQ_MODE_TP_CD)-1
                    END) FREQ_MODE_TP_CD,
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(DISTINCT BILL_TP_CD)
                    ELSE COUNT(DISTINCT BILL_TP_CD)-1
                    END) BILL_TP_CD,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT REPL_BY_CONTRACT)
                    ELSE COUNT(DISTINCT REPL_BY_CONTRACT)-1
                    END) REPL_BY_CONTRACT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT PREMIUM_AMT)
                    ELSE COUNT(DISTINCT PREMIUM_AMT)-1
                    END) PREMIUM_AMT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT NEXT_BILL_DT)
                    ELSE COUNT(DISTINCT NEXT_BILL_DT)-1
                    END) NEXT_BILL_DT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT CURR_CASH_VAL_AMT)
                    ELSE COUNT(DISTINCT CURR_CASH_VAL_AMT)-1
                    END) CURR_CASH_VAL_AMT,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LINE_OF_BUSINESS)
                    ELSE COUNT(DISTINCT LINE_OF_BUSINESS)-1
                    END) LINE_OF_BUSINESS,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT BRAND_NAME)
                    ELSE COUNT(DISTINCT BRAND_NAME)-1
                    END) BRAND_NAME,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SERVICE_ORG_NAME)
                    ELSE COUNT(DISTINCT SERVICE_ORG_NAME)-1
                    END) SERVICE_ORG_NAME,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT BUS_ORGUNIT_ID)
                    ELSE COUNT(DISTINCT BUS_ORGUNIT_ID)-1
                    END) BUS_ORGUNIT_ID,
      (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SERVICE_PROV_ID)
                    ELSE COUNT(DISTINCT SERVICE_PROV_ID)-1
                    END) SERVICE_PROV_ID,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_UPDATE_DT)
                    ELSE COUNT(DISTINCT LAST_UPDATE_DT)-1
                    END) LAST_UPDATE_DT,
        (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_UPDATE_USER)
                    ELSE COUNT(DISTINCT LAST_UPDATE_USER)-1
                    END) LAST_UPDATE_USER,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_UPDATE_TX_ID)
                    ELSE COUNT(DISTINCT LAST_UPDATE_TX_ID)-1
                    END) LAST_UPDATE_TX_ID,
        (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ISSUE_LOCATION)
                    ELSE COUNT(DISTINCT ISSUE_LOCATION)-1
                    END) ISSUE_LOCATION,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ADMIN_CONTRACT_ID)
                    ELSE COUNT(DISTINCT ADMIN_CONTRACT_ID)-1
                    END) ADMIN_CONTRACT_ID,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ADMIN_SYS_TP_CD)
                    ELSE COUNT(DISTINCT ADMIN_SYS_TP_CD)-1
                    END) ADMIN_SYS_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT PREMAMT_CUR_TP)
                    ELSE COUNT(DISTINCT PREMAMT_CUR_TP)-1
                    END) PREMAMT_CUR_TP,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT CASHVAL_CUR_TP)
                    ELSE COUNT(DISTINCT CASHVAL_CUR_TP)-1
                    END) CASHVAL_CUR_TP,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ACCESS_TOKEN_VALUE)
                    ELSE COUNT(DISTINCT ACCESS_TOKEN_VALUE)-1
                    END) ACCESS_TOKEN_VALUE,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT MANAGED_ACCOUNT_IND)
                    ELSE COUNT(DISTINCT MANAGED_ACCOUNT_IND)-1
                    END) MANAGED_ACCOUNT_IND,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_NAME)
                    ELSE COUNT(DISTINCT AGREEMENT_NAME)-1
                    END) AGREEMENT_NAME,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_NICKNAME)
                    ELSE COUNT(DISTINCT AGREEMENT_NICKNAME)-1
                    END) AGREEMENT_NICKNAME,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SIGNED_DT)
                    ELSE COUNT(DISTINCT SIGNED_DT)-1
                    END) SIGNED_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT EXECUTED_DT)
                    ELSE COUNT(DISTINCT EXECUTED_DT)-1
                    END) EXECUTED_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT END_DT)
                    ELSE COUNT(DISTINCT END_DT)-1
                    END) END_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT REPLACES_CONTRACT)
                    ELSE COUNT(DISTINCT REPLACES_CONTRACT)-1
                    END) REPLACES_CONTRACT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT ACCOUNT_LAST_TRANSACTION_DT)
                    ELSE COUNT(DISTINCT ACCOUNT_LAST_TRANSACTION_DT)-1
                    END) ACCOUNT_LAST_TRANSACTION_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT TERMINATION_DT)
                    ELSE COUNT(DISTINCT TERMINATION_DT)-1
                    END) TERMINATION_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT TERMINATION_REASON_TP_CD)
                    ELSE COUNT(DISTINCT TERMINATION_REASON_TP_CD)-1
                    END) TERMINATION_REASON_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_DESCRIPTION)
                    ELSE COUNT(DISTINCT AGREEMENT_DESCRIPTION)-1
                    END) AGREEMENT_DESCRIPTION,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_ST_TP_CD)
                    ELSE COUNT(DISTINCT AGREEMENT_ST_TP_CD)-1
                    END) AGREEMENT_ST_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT AGREEMENT_TP_CD)
                    ELSE COUNT(DISTINCT AGREEMENT_TP_CD)-1
                    END) AGREEMENT_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SERVICE_LEVEL_TP_CD)
                    ELSE COUNT(DISTINCT SERVICE_LEVEL_TP_CD)-1
                    END) SERVICE_LEVEL_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_VERIFIED_DT)
                    ELSE COUNT(DISTINCT LAST_VERIFIED_DT)-1
                    END) LAST_VERIFIED_DT,
        (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT LAST_REVIEWED_DT)
                    ELSE COUNT(DISTINCT LAST_REVIEWED_DT)-1
                    END) LAST_REVIEWED_DT,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT PRODUCT_ID)
                    ELSE COUNT(DISTINCT PRODUCT_ID)-1
                    END) PRODUCT_ID,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT CLUSTER_KEY)
                    ELSE COUNT(DISTINCT CLUSTER_KEY)-1
                    END) CLUSTER_KEY,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_SUBDIVISION_TP_CD)
                    ELSE COUNT(DISTINCT SB_SUBDIVISION_TP_CD)-1
                    END) SB_SUBDIVISION_TP_CD,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_TB)
                    ELSE COUNT(DISTINCT SB_TB)-1
                    END) SB_TB,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_OSB)
                    ELSE COUNT(DISTINCT SB_OSB)-1
                    END) SB_OSB,
       (CASE WHEN SUM(old_flag)=0
                    THEN COUNT(DISTINCT SB_VSP)
                    ELSE COUNT(DISTINCT SB_VSP)-1
                    END) SB_VSP             
   FROM t4 
   GROUP BY CONTRACT_ID
 )
 SELECT 
    SUM(CURRENCY_TP_CD) CURRENCY_TP_CD,
    SUM(CONTR_LANG_TP_CD) CONTR_LANG_TP_CD,
    SUM(FREQ_MODE_TP_CD) FREQ_MODE_TP_CD,
    SUM(BILL_TP_CD) BILL_TP_CD,
    SUM(REPL_BY_CONTRACT) REPL_BY_CONTRACT,
    SUM(PREMIUM_AMT) PREMIUM_AMT,
    SUM(NEXT_BILL_DT) NEXT_BILL_DT,
    SUM(CURR_CASH_VAL_AMT) CURR_CASH_VAL_AMT,
    SUM(LINE_OF_BUSINESS) LINE_OF_BUSINESS,
    SUM(BRAND_NAME) BRAND_NAME,
    SUM(SERVICE_ORG_NAME) SERVICE_ORG_NAME,
    SUM(BUS_ORGUNIT_ID) BUS_ORGUNIT_ID,
    SUM(SERVICE_PROV_ID) SERVICE_PROV_ID,
    SUM(LAST_UPDATE_DT) LAST_UPDATE_DT,
    SUM(LAST_UPDATE_USER) LAST_UPDATE_USER,
    SUM(LAST_UPDATE_TX_ID) LAST_UPDATE_TX_ID,
    SUM(ISSUE_LOCATION) ISSUE_LOCATION,
    SUM(ADMIN_CONTRACT_ID) ADMIN_CONTRACT_ID,
    SUM(ADMIN_SYS_TP_CD) ADMIN_SYS_TP_CD,
    SUM(PREMAMT_CUR_TP) PREMAMT_CUR_TP,
    SUM(CASHVAL_CUR_TP) CASHVAL_CUR_TP,
    SUM(ACCESS_TOKEN_VALUE) ACCESS_TOKEN_VALUE,
    SUM(MANAGED_ACCOUNT_IND) MANAGED_ACCOUNT_IND,
    SUM(AGREEMENT_NAME) AGREEMENT_NAME,
    SUM(AGREEMENT_NICKNAME) AGREEMENT_NICKNAME,
    SUM(SIGNED_DT) SIGNED_DT,
    SUM(EXECUTED_DT) EXECUTED_DT,
    SUM(END_DT) END_DT,
    SUM(REPLACES_CONTRACT) REPLACES_CONTRACT,
    SUM(ACCOUNT_LAST_TRANSACTION_DT) ACCOUNT_LAST_TRANSACTION_DT,
    SUM(TERMINATION_DT) TERMINATION_DT,
    SUM(TERMINATION_REASON_TP_CD) TERMINATION_REASON_TP_CD,
    SUM(AGREEMENT_DESCRIPTION) AGREEMENT_DESCRIPTION,
    SUM(AGREEMENT_ST_TP_CD) AGREEMENT_ST_TP_CD,
    SUM(AGREEMENT_TP_CD) AGREEMENT_TP_CD,
    SUM(SERVICE_LEVEL_TP_CD) SERVICE_LEVEL_TP_CD,
    SUM(LAST_VERIFIED_DT) LAST_VERIFIED_DT,
    SUM(LAST_REVIEWED_DT) LAST_REVIEWED_DT,
    SUM(PRODUCT_ID) PRODUCT_ID,
    SUM(CLUSTER_KEY) CLUSTER_KEY,
    SUM(SB_SUBDIVISION_TP_CD) SB_SUBDIVISION_TP_CD,
    SUM(SB_TB) SB_TB,
    SUM(SB_OSB) SB_OSB,
    SUM(SB_VSP) SB_VSP
 FROM t5;
 
16 май 14, 17:13    [16028717]     Ответить | Цитировать Сообщить модератору
 Re: Слишком тяжелый запрос. Как оптимизировать?  [new]
n&n
Guest
kT_________,

Не для оптимизации производительности,
а только для удобства чтения.
Если бы t4 была регулярной таблицей с индексом по old_flag, то
то MIN || MAX было бы предпочтительнее SUM.

   SELECT
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(A)
                    ELSE COUNT(A)-1
                    END) CURRENCY_TP_CD, 
      (CASE WHEN  SUM(old_flag)=0
                    THEN COUNT(B)
                    ELSE COUNT(B)-1
                    END) CONTR_LANG_TP_CD,
...

заменить на

  t4x (det) as (select case MAX(old_flag) = 0 then 0 else 1 end from t4),
  t5 as (select count(A)-det, count(B)-det, ... from t4, t4x group by contract_id)
16 май 14, 18:04    [16028915]     Ответить | Цитировать Сообщить модератору
 Re: Слишком тяжелый запрос. Как оптимизировать?  [new]
n&n
Guest
n&n,
точнее

t4x (cntr_id, delta) as (select contract_id, case MAX(old_flag) = 0 then 0 else 1 end ...),
t5 as (select count(A)-delta, count(B)-delta, ... from t4, t4x where t4.contract_id = t4x.cntr_id ...),

хотя если я еще правильно понимаю (в пятницу вечером),
то последние суммы эквивалентны тем же count(...) без группировки по contract_id
и суммировать надо только эти самые delta, добавляя их последним подзапросом к count()
?
16 май 14, 18:29    [16029001]     Ответить | Цитировать Сообщить модератору
 Re: Слишком тяжелый запрос. Как оптимизировать?  [new]
kT_________
Member

Откуда:
Сообщений: 381
n&n,
всё равно тяжеловато получается..

как-то можно ускорить выборку? хотя бы в каком направление копать?

индексы, особо изощренная статистика? м б что-то ещё.. м?
18 май 14, 02:25    [16032164]     Ответить | Цитировать Сообщить модератору
 Re: Слишком тяжелый запрос. Как оптимизировать?  [new]
Mark Barinstein
Member

Откуда: Москва
Сообщений: 4946
kT_________,

На таких данных:
with H_CONTRACT (CONTRACT_ID, H_CREATE_DT, CURRENCY_TP_CD) as (values
  (1, current timestamp            , '0') -- <- считать как изменение (по этому id нет предыдущей записи)?
, (1, current timestamp + 1 seconds, '1')
, (1, current timestamp + 2 seconds, '0') -- <- считать как изменение ('0' уже было)?

, (2, current timestamp - 1 seconds, '0')
, (2, current timestamp            , '0') -- <- считать как изменение (последнее предыдущее значение совпадает)?
, (2, current timestamp + 1 seconds, '1')
, (2, current timestamp + 2 seconds, '0')
)
select * from H_CONTRACT;
в предположении, что изменения надо посчитать за промежуток H_CREATE_DT >= current timestamp, сколько по каждому из 2-х CONTRACT_ID должно получиться изменений?
19 май 14, 09:46    [16034993]     Ответить | Цитировать Сообщить модератору
 Re: Слишком тяжелый запрос. Как оптимизировать?  [new]
kT_________
Member

Откуда:
Сообщений: 381
Mark Barinstein,

неограниченое кол-во раз
11 июн 14, 13:08    [16154130]     Ответить | Цитировать Сообщить модератору
Все форумы / IBM DB2, WebSphere, IMS, U2, etc Ответить