Giá trị mà bạn nhận được trong bài viết này:
- Hiểu được những vấn đề có thể ảnh hưởng đến hiệu năng của câu lệnh Insert
- Biết được một trong số các kỹ thuật tối ưu câu lênh Insert tôi đã áp dụng trong các dự án của Wecommit. (những dự án trọng yếu tại ngân hàng, chứng khoán, bệnh viện…). Danh sách dự án chi tiết các bạn có thể xem tại đây. https://wecommit.com.vn/du-an/. Trong bài viết này bạn sẽ được thấy những kỹ thuật tôi áp dụng để cải thiện vài chục lần thời gian thực hiện những tiến trình Insert.
- Kỹ thuật tối ưu câu lệnh Insert này là một phần nhỏ trong chuỗi Series Các kỹ thuật tối ưu mà tôi chia sẻ với nhóm học viên đặc quyền. Link series đặc quyền này tại đây: https://wecommit.com.vn/series-cac-ky-thuat-toi-uu-phan-01/.
Ghi chú: Nếu bạn muốn đọc các bài viết về tối ưu MỚI NHẤT của tôi, bạn có thể tham gia nhóm Zalo Tư Duy – Tối Ưu – Khác Biệt. Bạn có thể tham gia nhóm chỉ với phí là lời cảm ơn và giới thiệu các tri thức này đến những người đang cần nó. Click vào đây để tham gia nhóm (HOÀN TOÀN MIỄN PHÍ)
1. Cậu lệnh Insert thực hiện lâu nhưng nguyên nhân gốc có thể nằm ở chỗ khác?
Không phải bất kỳ câu lệnh Insert nào thực hiện lâu cũng do nội tại của câu lệnh, anh em có thể thực hiện kiểm tra ngay 2 vấn đề sau
- Thứ nhất: Có Trigger nào được gọi khi câu lệnh Insert của chúng ta thực thi hay không
- Thứ hai: Kiểm tra câu lệnh có đang bị LOCK không
1.1. Kiểm tra trigger trong Cơ sở dữ liệu thế nào?
Kiểm tra toàn bộ danh sách Trigger trong Cơ sở dữ liệu
select * from dba_triggers;
Kiểm tra các trigger có liên quan đến TABLE mà chúng ta đang thực hiện lệnh INSERT
select * from dba_triggers where table_name='<TABLE_NAME>’;
1.2. Kiểm tra LOCK trong cơ sở dữ liệu
Tất cả các cơ sở dữ liệu đều có cơ chế LOCK (để bảo vệ tính toàn vẹn dữ liệu) khi các transaction có thực hiên lệnh DML (Delete, Insert, Update).
Hãy xem ví dụ sau:
Thực hiện tạo table tên là WECOMMIT_TABLE phục vụ việc Demo
create table wecommit_table(id number primary key, name varchar2(100));
Chúng ta tạo 2 transaction phục vụ việc Demo này
- Tiến trình thứ nhất (bên tay trái trong ảnh) thực hiện lệnh vào thời điểm 10h. Câu lệnh này thực hiện được ngay lập tức.
SQL> insert into wecommit_table values(‘1′,’Wecommit1’);
1 row created.
- Tiến trình thứ hai (bên tay phải trong ảnh) thực hiện sau tiến trình thứ nhất 1 phút. Câu lệnh cũng vô cùng đơn giản
SQL> insert into wecommit_table values (‘1′,’Wecommit_Pending’);
- Kết quả: Tiến trình thứ hai bị TREO (do hệ thống đang LOCK vì cùng Insert một giá trị Primary Key của bảng). Nếu trường hợp này người dùng chúng ta đang đứng ở phiên làm việc thứ 2 sẽ có cảm giác “KHÔNG THỂ HIỂU NỔI, TẠI SAO INSERT CÓ 1 BẢN GHI MÀ LẠI CHẬM VÃI CHƯỞNG!!!!”
2. Kỹ thuât sử dụng HINT để tối ưu câu lệnh Insert
2.1. Phương án 1: Thực hiên INSERT dữ liệu từ kết quả của câu lệnh SELECT
Tại đây tôi thực hiện tạo 1 bảng có cấu trúc giống với bảng WECOMMIT_TABLE
CREATE TABLE HUYTQ.WECOMMIT_TABLE_NEW
(
ID NUMBER,
NAME VARCHAR2(100 BYTE)
);
SQL> insert into WECOMMIT_TABLE_NEW select * from WECOMMIT_TABLE;
1000000 rows created.
Elapsed: 00:00:01.13
SQL> commit;
Commit complete.
2.2. Phương án 2: Thực hiện INSERT nhưng thêm HINT APPEND
SQL> insert /*+ APPEND*/ into WECOMMIT_TABLE_NEW select * from WECOMMIT_TABLE;
1000000 rows created.
Elapsed: 00:00:00.60
Commit complete.
Elapsed: 00:00:00.00
2.3. Kết luận
Sử dụng HINT APPEND đã giúp câu lệnh INSERT cải thiện hơn 2 lần.
Bản chất HINT APPEND này là gì và tại sao có thể cải thiện hiệu năng sẽ được tôi phân tích riêng trên kênh trao đổi của nhóm đặc quyền.
3. Kỹ thuật tối ưu câu lệnh Insert buộc phải biết
Thực hiện 1 triệu lần, mỗi lần Insert 1 bản ghi hay thực hiện 1 lần những Insert 1 triệu bản ghi?
Hãy giảm thiểu số lần gọi câu lệnh SQL đến Database.
Tại sao phải thực hiện việc này tôi đã phân tích trong 1 bài viết tối ưu, các anh em có thể đọc lại bài viết này: https://wecommit.com.vn/database-performance-tuning-speed-up-97/
Các ví dụ dưới đây tôi sẽ áp dụng kỹ thuật sử dụng 1 bảng TEMP với câu lệnh WITH
WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp
Câu lệnh này bản chất sẽ tạo ra 1 bảng TEMP có dữ liệu như sau:
- Bảng này có 1.000.000 bản ghi
- Bảng có 2 cột: Cột ID và Cột Name
- Cột ID nhận giá trị từ 1 đến 1.000.000
- Cột Name nhận giá trị kết hợp của chuỗi Wecommit + ID
3.1 Phương án 1Thực hiện Insert 1 triệu lần, mỗi lần 1 bản ghi
Thực hiện Insert vào bảng WECOMMIT_TABLE như sau
BEGIN
FOR i IN (WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp)
LOOP
INSERT INTO WECOMMIT_TABLE
VALUES (i.id, i.name);
END LOOP;
END;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:44.56
5.2. Thực hiện 1 lần Insert nhưng Insert 1 triệu bản ghi
Thực hiện Truncate Table WECOMMIT_TABLE để đảm bảo tính khách quan trước khi Demo
SQL> truncate table WECOMMIT_TABLE;
Table truncated.
INSERT INTO WECOMMIT_TABLE
WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp;
1000000 rows created.
Elapsed: 00:00:04.76
3.3. Nếu áp dụng thêm chiêu thức HINT APPEND bên trên thì sao?
SQL> truncate table WECOMMIT_TABLE;
Table truncated.
SQL> INSERT /*+ APPEND*/
INTO WECOMMIT_TABLE
WITH
temp
AS
( SELECT LEVEL x
FROM DUAL
CONNECT BY LEVEL <= 1000000)
SELECT ROWNUM id, ‘Wecommit ‘ || ROWNUM AS name
FROM temp 2 3 4 5 6 7 8 9 10 ;
1000000 rows created.
Elapsed: 00:00:02.68
Tại đây chúng ta thấy: Cùng 1 mục đích insert 1 triệu giá trị vào bảng WECOMMIT_TABLE, nhưng ta đã tối ưu từ hơn 44 giây xuống chỉ còn 2 giây 68 (cải thiện 17 lần)
4. Các câu lệnh trong thực tế đều cần phải COMMIT, tần suất COMMIT có ảnh hưởng đến hiệu năng hay không?
Câu trả lời là có.
Chúng ta hãy cùng xem 2 vòng lặp Insert nhưng tần suất COMMIT khác nhau bên dưới đây
4.1. Phương án 1: Thực hiện COMMIT sau khi đã hoàn thành việc INSERT 1.000.000 bản ghi
SQL> set timing on
SQL> truncate table wecommit_table;
Table truncated.
Thực hiện Insert 1 triệu bản ghi vào bảng WECOMMIT_TABLES
SQL> begin
for i in 1 .. 1000000 loop
insert into WECOMMIT_TABLE values ( i, ‘WECOMMIT’||i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:43.98
4.2. Phương án 2: Thực hiện COMMIT sau mỗi lệnh INSERT
Thực hiện truncate table trước khi Insert dữ liệu mới
SQL> truncate table WECOMMIT_TABLE;
Table truncated.
SQL> begin
for i in 1 .. 1000000 loop
insert into WECOMMIT_TABLE values ( i, ‘WECOMMIT’||i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:02:39.00
5. Tối ưu câu lệnh Insert dựa trên tối ưu Subquery
Trong nhiều trường hợp chúng ta sẽ cần thực hiện câu lệnh INSERT với kết quả trả ra của 1 câu lệnh SELECT.
Ví dụ xem xét câu lệnh sau
INSERT INTO emp_bk(EMPNO,ENAME)
SELECT id, last_name FROM emp where salary < 500
Chiến lược thực thi của câu lệnh này:
Câu lênh Insert ước lượng mất 7 phút 58s để thực hiện xong, trong đó thời gian lâu nhất chủ yếu là ở câu lệnh Subquery.
Chúng ta sẽ tối ưu câu lệnh Insert này bằng cách thực hiện tối ưu câu lệnh Subquery
SELECT id, last_name FROM emp where salary < 500
Trong trường hợp này chúng ta có thể áp dụng kỹ thuật tối ưu sử dụng Index:
create index idx_wecommit_salary on emp(salary)
Chiến lược thực thi sau khi tạo Index này như sau
Kết quả câu lệnh đã giảm từ 7ph 58s xuống còn 01s.
6. Nếu bạn muốn xem Video phân tích tất cả những vấn đề trên một cách chi tiết hơn.
7. Còn có có các kỹ thuật tối ưu câu lệnh Insert khác không?
Câu trả lời là CÓ.
Trong những dự án mà tôi thực hiện, có nhiều trường hợp chúng ta cần Insert một lượng dữ liệu rất lớn (hàng trăm GB dữ liệu), trong các trường hợp đó sẽ có những kỹ thuật tối ưu khác nữa.
Toàn bộ các kỹ thuật này sẽ được tôi phân tích chi tiết trong Series các kỹ thuật tối ưu: https://wecommit.com.vn/series-cac-ky-thuat-toi-uu-phan-01/
8. Nếu bạn muốn biết câu trả lời cho câu hỏi “Tối ưu SQL bắt đầu từ đâu”?
Bạn có thể xem Video tôi chia sẻ về vấn đề này.
Trong phần chia sẻ này bạn sẽ biết được:
- Các thách thức phải đối mặt khi bạn muốn làm chủ kỹ năng tối ưu SQL
- Những sai lầm phổ biến
- Và các bí mật
Bạn có thể xem chi tiết tại đây: Click vào đây.
9. Nếu bạn muốn danh sách toàn bộ các bài viết tối ưu đến thời điểm hiện tại của tôi
Tổng hợp toàn bộ các bài viết của Trần Quốc Huy: https://wecommit.com.vn/tong-hop-link-cac-bai-viet-hay-tren-trang-wecommit-com-vn/
10. Thông tin tác giả
Tác giả: Trần Quốc Huy – Founder & CEO Wecommit.
Facebook: https://www.facebook.com/tran.q.huy.71
Youtube: https://www.youtube.com/channel/UCtsYzL7iN7rBCPnkjYp4XYw
Zalo: 0888549190
Email: huy.tranquoc@wecommit.com.vn