데이터베이스 질문과 답변
이런 형식의 코드입니다.
SELECT tbs.column1 ,tbs.column2 ,tbs.column3 ... GET_CODE_FUNCTION1(1234) as code ,GET_CODE_FUNCTION1(1234) as code1 ,GET_CODE_FUNCTION1(1234) as code2 FROM tables tbs ORDER BY column1 , column2, column3;
order by 조건 에 들어가는 컬럼이 varchar 일시 그렇다고 해서 확인 해보니
column1 = char(3)
column2 = NVARCHAR2(20)
column3 = NVARCHAR2(20)
이네요.
조인도 아니고 그냥 1개 테이블에서 order by 만 넣었는데 (70초 넘게...)이정도 시간이 걸립니다.
도와주세요.
by 마농
[2022.12.12 14:01:39]
정렬 때문이라기 보다는
사용자 함수 사용 때문에 느린거 아닐까요?
by 없음
[2022.12.12 15:08:16]
함수 빼고 쿼리 수행해도 시간은 동일한것 같습니다.
order by 빼면 쿼리는 수행시간 : 239 ms 로 빨라집니다.
제쿼리입니다.
SELECT pft.solution
, pft.bound
, pft.bound_type
, pft.order_type_mh
, pft.order_type_gac
, pft.obl_job_no, pft.job_no, pft.mbl_no, pft.hbl_no
, pft.issue_date
, pft.OBAR_DATE
, pft.profit_date
, pft.finish_date
, pft.order_date
, pft.ob_date
, pft.ar_date
, pft.origin_ob_date
, pft.origin_ar_date
, pft.finish_yn
, pft.orig_code
, pft.orig_name
, pft.dest_code
, pft.dest_name
, pft.partner_code
, pft.partner_name
, pft.customer_code
, pft.customer_name
, pft.shipper_code
, pft.shipper_name
, pft.consignee_code
, pft.consignee_name
, pft.notify_code
, pft.notify_name
, pft.cb_code
, pft.cb_name
, pft.carrier_code
, pft.carrier_name
, pft.agent_code
, pft.agent_name
, pft.account_code
, pft.ar_account_name
, pft.ap_account_name
, pft.item_code
, pft.item_name
, pft.item_desc
, pft.file_share_no
, pft.po_no
, pft.lc_no
, pft.ci_no
, pft.bl_type_sc
, pft.bl_sales_type
, pft.bl_type_lfb
, pft.bl_type_lfb_name
, pft.lcl_cbm
, pft.cntr_20
, pft.cntr_40
, pft.cntr_45
, pft.cntr_teu
, DECODE(pft.order_type_gac, 'G', 'GENERAL', 'A', 'AMEND', 'C', 'CONSOL', 'NONE') order_type_gac_name
, pft.vessel_flight
, pft.vessel_voyage
, pft.flight_vessel_name
, pft.pieces
, pft.unit_pieces
, pft.gwt
, pft.gwt_lbs
, pft.cwt
, pft.cwt_lbs
, pft.cwt_cbm
, pft.cwt_cbm_cft
, pft.ar_currency_code
, pft.ar_exrate
, pft.freight_term
, pft.incoterms
, pft.r_foreign_y_amount_cur
, pft.r_foreign_y_amount_loc
, pft.r_foreign_n_amount_loc
, pft.r_refund_amount_loc
, pft.r_amount_loc
, pft.r_amount_loc + pft.mr_amount_loc as rev_total_loc
, pft.mr_amount_loc
, pft.me_foreign_y_amount_cur
, pft.me_foreign_y_amount_loc
, pft.me_foreign_n_amount_loc
, pft.me_refund_amount_loc
, pft.me_amount_loc
, pft.c_amount_loc
, pft.c_amount_loc + pft.me_amount_loc as exp_total_loc
, pft.hdc_dr_amount_cur
, pft.hdc_dr_amount_loc
, pft.hdc_cr_amount_cur
, pft.hdc_cr_amount_loc
, pft.MDC_amount_cur AS MDC_amount_cur
, pft.MDC_amount_loc AS MDC_amount_loc
, pft.hdc_total_amount_loc AS hdc_total_amount_loc
, pft.hdc_total_amount_cur AS hdc_total_amount_cur
, NVL(pft.r_amount_loc, 0) + NVL(pft.mr_amount_loc, 0) + NVL(pft.hdc_dr_amount_loc, 0) + NVL(pft.mdc_dr_amount_loc, 0) AS TTL_AR
, NVL(pft.c_amount_loc, 0) + NVL(pft.me_amount_loc, 0) + NVL(pft.hdc_cr_amount_loc, 0) + NVL(pft.mdc_cr_amount_loc, 0) AS TTL_AP
, pft.profit_usd
, pft.profit_loc
, pft.profit_loc as profit_loc_all
, pft.profit_frt_loc
, pft.profit_otc_loc
, pft.profit_revenue_ratio
, DECODE(pft.bl_type_lfb, 'P', 1, 0) as cntr_pt
, pft.rton
, pft.selling_rate
, pft.buying_rate
, pft.salesman_user_id
, pft.salesman_user_name
, pft.area_code
, pft.od_reference_no
, pft.country_code
, pft.team_code
, GET_CODE_NAME('DEFAULT', 'TEAMCODE', pft.team_code, pft.ent_code, pft.ent_business_code) AS team_name
, pft.register_user_id
, pft.register_user_name
, pft.group_code
, pft.ent_code
, pft.ent_business_code
, pft.ent_office_code
, pft.office_print_name
, pft.customer_group
, pft.drcr_account_name
, pft.bl_cargo_type
, GET_CODE_NAME('DEFAULT', 'COUNTRY', pft.country_code, pft.ent_code, pft.ent_business_code) AS country_name
, GET_CODE_NAME('DEFAULT', 'GROUP_CODE', pft.group_code, pft.ent_code, pft.ent_business_code) AS group_name_gubun
, GET_CODE_NAME('DEFAULT', 'BL_TYPE_SC', pft.bl_type_sc, pft.ent_code, pft.ent_business_code) AS bl_type_sc_name
, GET_CODE_NAME('DEFAULT', 'AREA', pft.area_code, pft.ent_code, pft.ent_business_code) AS area_name
, pft.salesman_emp_no, pft.mbl_incoterms
FROM TB_FMS_PROFIT_BI pft
order by bound desc, mbl_no, hbl_no
;
by 마농
[2022.12.12 15:42:30]
이미지를 보면 "50개의 행이 인출됨"이라는 메시지가 보입니다.
이는 해당 툴(SQLDeveloper) 이 부분범위 처리를 지원하기 때문인데요.
모든 결과를 출력하는게 아닌 1페이지(50행)에 해당하는 결과만 출력합니다.
정렬이 없다면 테이블에서 50건만 읽고 일단 멈춤 상태가 되는 것입니다.
50건만 읽으니 빠르게 리턴이 되는 것입니다.
정렬이 있다면? 부분범위 처리가 불가하여 전체 데이터를 읽어서 정렬해야 합니다.
전열된 결과에 대해서는 50건만 반화하는 부분범위가 되긴 하지만
정렬 전 데이터를 전체 33만건을 읽어 전체를 정렬하는 부분 떄문에 오래 걸리는 것입니다.
마찬가지로 사용자 함수 사용 부분도 충분히 속도에 영향을 주는 부분입니다.
사용자 함수가 5번 사용되는데
정렬 없는 쿼리에서는 결과를 리턴하기 전까지 함수 수행 회수는 5회 * 50행 = 250번 실행 됩니다.
정렬 있는 쿼리에서는 결과를 리턴하기 전까지 함수 수행 회수는 5회 * 약33만행 = 약167만 번 실행 됩니다.
by 없음
[2022.12.12 16:05:50]
답변 감사합니다.
전체적으로 문제 있는 쿼리네요.
그럼 이쿼리를 어떻게 ? 튜닝 or 수정 ? 해야 될까요 ?
찾아보니, order by 에 넣는 컬럼들을 인텍스롤 묶어서 처리하라고 했는데 (일단 뭐라도 해야 봐야 될것 같아서 인텍스 생성 하려고 했는데... 권한이 없다고 나오고.
사용자 함수도 별거 없네요.
CREATE OR REPLACE FUNCTION G_ONE.GET_CODE_NAME (v_userLanguage IN NVARCHAR2
, v_grougCode IN NVARCHAR2
, v_detailCode IN NVARCHAR2
, v_entCode IN NVARCHAR2
, v_entBusinessCode IN NVARCHAR2)
-- 리턴되는 변수의 데이터타입을 꼭 정의해야 합니다
RETURN NVARCHAR2
IS
v_returnMessage NVARCHAR2 (4000);
BEGIN
SELECT
CASE
WHEN v_userLanguage = 'ENG' THEN NAME_ENG
WHEN v_userLanguage = 'KOR' THEN NAME_KOR
WHEN v_userLanguage = 'CHI' THEN NAME_CHI
WHEN v_userLanguage = 'JPN' THEN NAME_JPN
WHEN v_userLanguage = 'SPA' THEN NAME_SPA
ELSE NAME_DEFAULT END
INTO v_returnMessage
FROM tb_sms_code_detail
WHERE 1 = 1
AND ent_code = v_entCode
AND group_code = v_grougCode
AND code = v_detailCode
AND (ent_business_code = v_entBusinessCode
OR ent_business_code IS NULL);
-- 리턴문이 꼭 존재해야 합니다
RETURN v_returnMessage;
END;
by 마농
[2022.12.12 16:08:22]
일단 쿼리 자체의 문제는
1. 검색 조건이 없다.
2. 너무 많은 항목 조회
3. 정렬
이렇게 보입니다.
위 3가지가 정말 필요한 것인지 검토해 보세요.
1. 정말 이 모든 데이터가 필요한가?
2. 정말 이 모든 항목들이 필요한가?
3. 정렬이 필요한가?
by 없음
[2022.12.12 17:23:47]
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY pft.bound ORDER BY pft.mbl_no ,pft.hbl_no ) AS rownumber
, pft.solution
, pft.bound
, pft.bound_type
, pft.order_type_mh
, pft.order_type_gac
, pft.obl_job_no, pft.job_no, pft.mbl_no, pft.hbl_no
, pft.issue_date
, pft.OBAR_DATE
, pft.profit_date
, pft.finish_date
, pft.order_date
, pft.ob_date
, pft.ar_date
, pft.origin_ob_date
, pft.origin_ar_date
, pft.finish_yn
, pft.orig_code
, pft.orig_name
, pft.dest_code
, pft.dest_name
, pft.partner_code
, pft.partner_name
, pft.customer_code
, pft.customer_name
, pft.shipper_code
, pft.shipper_name
, pft.consignee_code
, pft.consignee_name
, pft.notify_code
, pft.notify_name
, pft.cb_code
, pft.cb_name
, pft.carrier_code
, pft.carrier_name
, pft.agent_code
, pft.agent_name
, pft.account_code
, pft.ar_account_name
, pft.ap_account_name
, pft.item_code
, pft.item_name
, pft.item_desc
, pft.file_share_no
, pft.po_no
, pft.lc_no
, pft.ci_no
, pft.bl_type_sc
, pft.bl_sales_type
, pft.bl_type_lfb
, pft.bl_type_lfb_name
, pft.lcl_cbm
, pft.cntr_20
, pft.cntr_40
, pft.cntr_45
, pft.cntr_teu
, DECODE(pft.order_type_gac, 'G', 'GENERAL', 'A', 'AMEND', 'C', 'CONSOL', 'NONE') order_type_gac_name
, pft.vessel_flight
, pft.vessel_voyage
, pft.flight_vessel_name
, pft.pieces
, pft.unit_pieces
, pft.gwt
, pft.gwt_lbs
, pft.cwt
, pft.cwt_lbs
, pft.cwt_cbm
, pft.cwt_cbm_cft
, pft.ar_currency_code
, pft.ar_exrate
, pft.freight_term
, pft.incoterms
, pft.r_foreign_y_amount_cur
, pft.r_foreign_y_amount_loc
, pft.r_foreign_n_amount_loc
, pft.r_refund_amount_loc
, pft.r_amount_loc
, pft.r_amount_loc + pft.mr_amount_loc as rev_total_loc
, pft.mr_amount_loc
, pft.me_foreign_y_amount_cur
, pft.me_foreign_y_amount_loc
, pft.me_foreign_n_amount_loc
, pft.me_refund_amount_loc
, pft.me_amount_loc
, pft.c_amount_loc
, pft.c_amount_loc + pft.me_amount_loc as exp_total_loc
, pft.hdc_dr_amount_cur
, pft.hdc_dr_amount_loc
, pft.hdc_cr_amount_cur
, pft.hdc_cr_amount_loc
, pft.MDC_amount_cur AS MDC_amount_cur
, pft.MDC_amount_loc AS MDC_amount_loc
, pft.hdc_total_amount_loc AS hdc_total_amount_loc
, pft.hdc_total_amount_cur AS hdc_total_amount_cur
, NVL(pft.r_amount_loc, 0) + NVL(pft.mr_amount_loc, 0) + NVL(pft.hdc_dr_amount_loc, 0) + NVL(pft.mdc_dr_amount_loc, 0) AS TTL_AR
, NVL(pft.c_amount_loc, 0) + NVL(pft.me_amount_loc, 0) + NVL(pft.hdc_cr_amount_loc, 0) + NVL(pft.mdc_cr_amount_loc, 0) AS TTL_AP
, pft.profit_usd
, pft.profit_loc
, pft.profit_loc as profit_loc_all
, pft.profit_frt_loc
, pft.profit_otc_loc
, pft.profit_revenue_ratio
, DECODE(pft.bl_type_lfb, 'P', 1, 0) as cntr_pt
, pft.rton
, pft.selling_rate
, pft.buying_rate
, pft.salesman_user_id
, pft.salesman_user_name
, pft.area_code
, pft.od_reference_no
, pft.country_code
, pft.team_code
, GET_CODE_NAME('DEFAULT', 'TEAMCODE', pft.team_code, pft.ent_code, pft.ent_business_code) AS team_name
, pft.register_user_id
, pft.register_user_name
, pft.group_code
, pft.ent_code
, pft.ent_business_code
, pft.ent_office_code
, pft.office_print_name
, pft.customer_group
, pft.drcr_account_name
, pft.bl_cargo_type
, GET_CODE_NAME('DEFAULT', 'COUNTRY', pft.country_code, pft.ent_code, pft.ent_business_code) AS country_name
, GET_CODE_NAME('DEFAULT', 'GROUP_CODE', pft.group_code, pft.ent_code, pft.ent_business_code) AS group_name_gubun
, GET_CODE_NAME('DEFAULT', 'BL_TYPE_SC', pft.bl_type_sc, pft.ent_code, pft.ent_business_code) AS bl_type_sc_name
, GET_CODE_NAME('DEFAULT', 'AREA', pft.area_code, pft.ent_code, pft.ent_business_code) AS area_name
, pft.salesman_emp_no, pft.mbl_incoterms
FROM TB_FMS_PROFIT_BI pft
)
WHERE rownumber BETWEEN 1 AND 400
;
페이징으로 하니 일단 2초때로 줄었습니다. 이것도 방법인가요 ?
by 없음
[2022.12.12 17:10:33]
select ROW_NUMBER() OVER(PARTITION BY pft.bound ORDER BY pft.mbl_no ,pft.hbl_no ) AS rownumber,
조회 맨 앞에 위처럼 추가했더니
일단 4초 대로 떨어졌습니다. 1초 이상 걸리니 실패한 쿼리죠?
차이점이 뭔지 모르겠네요. DBeaver 라는 GUI 툴을 사용했고. 현재 200건씩 나오는데 기존 oracle develoer 50 개 보다 많아서 그런가요 ?
by 마농
[2022.12.12 17:34:34]
네. 페이징 쿼리도 한가지 방법입니다.
페이징 쿼리시 효율적인 쿼리 작성 방법은
가공 후 페이징이 아닌 페이징 후 가공으로 가는 방법이 있습니다.
덧셈, 내장함수, 사용자함수 등은 정렬 후 페이징 이후에 처리하도록 개선
그런데 단순 페이징이 아니네요?
파티션을 나누었네요(Partition By bound). 페이징 사이즈도 크고(400)
파티션별 400건씩 가져오는 쿼리네요.
파티션(bound) 은 몇종류 인가요?
) 버튼을 클릭하여 작성 하시면 됩니다.