배송 주문 할당 프로세스를 트리거에서 프로시저로 바꾸면서 생각보다 성능이 느리다는것을 확인했다.
그래서 프로시저의 기능을 조금 추려냈고 트리거와 연계하는 방식으로 설계를하면 시간이 단축이 될지 실험해 보았다.
delimiter //
DROP PROCEDURE if exists INSERT_processing;
CREATE PROCEDURE insert_processing(pm_order_id bigint)
BEGIN
DECLARE selected_storage_id BIGINT;
DECLARE selected_delivery_type VARCHAR(255);
DECLARE selected_worker_id BIGINT;
DECLARE selected_dv_order_id BIGINT;
DECLARE selected_customer_id BIGINT;
DECLARE selected_order_id BIGINT;
DECLARE selected_total_price DECIMAL(10,2);
DECLARE selected_product_id bigint;
DECLARE selected_product_name VARCHAR(255);
DECLARE selected_product_period INT;
DECLARE selected_product_price INT;
DECLARE selected_product_coverage_rate INT;
SELECT o.storage_id, o.delivery_type, dw.worker_id
INTO selected_storage_id, selected_delivery_type, selected_worker_id
FROM orders o
JOIN delivery_worker dw ON dw.zone_id = o.storage_id
WHERE dw.status = "대기중"
AND o.order_id = pm_order_id
AND dw.today_task < 20
AND (
(delivery_type = '일반배송' AND dw.working_time = '주간')
OR(delivery_type = '새벽배송' AND dw.working_time = '야간')
OR(delivery_type NOT IN('일반배송','새벽배송') AND dw.working_time IN ('주간','야간'))
);
if selected_delivery_type = '일반배송' OR selected_delivery_type = '새벽배송' then
INSERT INTO delivery_order(
order_id,
worker_id,
storage_id,
delivery_type
) VALUEs (
pm_order_id,
selected_worker_id,
selected_storage_id,
selected_delivery_type
);
ELSEIF selected_delivery_type = '예약배송' then
INSERT INTO reservation_time(
reserve_from,
reserve_to
) VALUES (
DATE_ADD(NOW(), INTERVAL 24 HOUR),
DATE_ADD(NOW(), INTERVAL 27 HOUR)
);
INSERT INTO delivery_order(
order_id,
worker_id,
storage_id,
delivery_type,
time_id
) VALUES (
pm_order_id,
selected_worker_id,
selected_storage_id,
selected_delivery_type,
LAST_INSERT_ID()
);
END if;
SET selected_dv_order_id = last_insert_id();
UPDATE delivery_worker SET today_task = today_task+1 WHERE worker_id = selected_worker_id;
SELECT customer_id, worker_id
INTO selected_customer_id, selected_worker_id
FROM delivery_order DO
JOIN orders o ON DO.order_id = o.order_id
WHERE DO.dv_order_id = selected_dv_order_id;
INSERT INTO chat_channel(
dv_order_id,
customer_id,
worker_id,
is_active
) VALUES(
selected_dv_order_id,
selected_customer_id,
selected_worker_id,
1
);
SELECT order_id
INTO selected_order_id
FROM delivery_order
WHERE dv_order_id = selected_dv_order_id;
SELECT total_price
INTO selected_total_price
FROM orders
WHERE order_id = selected_order_id;
SET selected_product_id =
case
when selected_total_price >= 1000000 then 4
when selected_total_price >= 200000 then 3
when selected_total_price >= 50000 then 2
when selected_total_price >= 10000 then 1
ELSE null
END;
if(selected_product_id IS not NULL) then
SELECT name, expiration_period, price, coverage_rate
INTO selected_product_name, selected_product_period, selected_product_price, selected_product_coverage_rate
FROM insurance_product
WHERE product_id = selected_product_id;
INSERT INTO delivery_insurance(
dv_order_id,
name,
purchase_date,
expiration_date,
insurance_fee,
coverage_amount,
insurance_status
) VALUES(
selected_dv_order_id,
selected_product_name,
NOW(),
DATE_ADD(NOW(), INTERVAL selected_product_period DAY),
selected_total_price * (selected_product_price/100),
selected_total_price * (selected_product_coverage_rate/100),
1
);
END if;
END //
delimiter ;
우선 수정 전 프로시저 다음과 같고 1000건의 호출시
총 33초의 시간이 걸렸다.
이후 프로시저를 통해 배송 주문과 배송 보험을 생성하고, 배송 주문 테이블에 트리거를 걸어 채팅 채널을 생성해보았다.
delimiter //
DROP TRIGGER if EXISTS insert_chat_channel_trigger;
CREATE TRIGGER insert_chat_channel_trigger
AFTER INSERT ON delivery_order
FOR EACH ROW
BEGIN
DECLARE selected_customer_id BIGINT;
DECLARE selected_worker_id BIGINT;
SELECT customer_id, do.worker_id
INTO selected_customer_id, selected_worker_id
FROM delivery_order DO
JOIN orders o ON new.order_id = o.order_id
WHERE dv_order_id = new.dv_order_id;
INSERT INTO chat_channel(
dv_order_id,
customer_id,
worker_id,
is_active
) VALUES(
NEW.dv_order_id,
selected_customer_id,
selected_worker_id,
1
);
END//
delimiter ;위와 같이 채팅 채널 생성에 관한 부분만 따로 트리거로 빼보았다.
26.235초로 약 7초를 단축 시킬 수 있었다.