Chơng IV Giới thiệu và tối u
hóa câu lệnh SQL trong hệ tính hoá đơn và quản lý khách hàng
công ty cấp nớc Thành phố Hồ chí minh
Nội dung chính yếu của chơng này là đa ra những cách thức cụ thể tối u hoá câu lệnh SQL trong Hệ tính hoá đơn
và quản lý khách hàng công ty cấp nớc Thành phố Hồ Chí Minh. Trong phần A sẽ giới thiệu về qui mô của bài toán
nói trên để nêu lên đợc ý nghĩa của tối u hoá, do qui mô của bài toán lớn (với 500,000 khách hàng, 1 triệu đồng hồ,
3 triệu chỉ số đồng hồ, 5 triệu hoá đơn....) dẫn đến mỗi cải tiến đều rất có ý nghĩa. Trong phần B sẽ giới thiệu phơng
pháp tìm những câu lệnh cha tối u và trong phần C là các ví dụ thực nghiệm đã đợc sử dụng trong Hệ tính hoá đơn
và quản lý khách hàng để nâng cao tốc độ xử lý cho phần mềm này.
A. Giới thiệu về hệ chơng trình Tính hoá đơn và quản lý khách hàng
1. Đặt vấn đề
Công ty cấp nớc TP Hồ Chí Minh (WSC - Water Supply Company) là một doanh nghiệp quốc doanh lớn thực
hiện công việc đảm bảo cung cấp nớc sinh hoạt cho hàng trăm ngàn hộ c trú trên địa bàn thành phố. Để đảm bảo
quyền lợi nhân dân cũng nh của WSC, công ty đã kí kết hợp đồng cung cấp nớc cho khách hàng và hàng tháng
khách hàng sẽ nhận đợc hoá đơn yêu cầu thanh toán số tiền tơng ứng với khối lợng nớc đã sử dụng. Với số lợng
khách hàng nhiều nh vậy, việc quản lý doanh thu đảm bảo cho chính xác, kịp thời là yếu tố rất quan trọng ảnh h ởng
đến hoạt động kinh doanh của WSC.
WSC đã có nhiều năm ứng dụng máy tính trong sản xuất và quản lý kinh doanh. Trớc năm 1975, WSC đã sử
dụng hệ máy tính IBM để quản lý và tính hoá đơn thanh toán tiền nớc. Từ năm 1990 hệ này đợc thay bằng các ch-
ơng trình viết bằng FoxBase trên PC, sau này chuyển sang FoxPro. Hiện tại hệ thống cũ viết trên PC hoạt động có
rất nhiều hạn chế nh dữ liệu bị phân nhỏ, qui trình thao tác phức tạp, thực hiện bằng tay nhiều do không có kết nối
thống nhất. Các báo cáo phải tổng hợp từ các phần nhỏ, khi in đợc hoá đơn mà phát hiện sai thì phải sửa ở mức lập
trình do vậy các báo cáo về tình hình kinh doanh không đáp ứng kịp thời, kém chính xác.
Đứng trớc yêu cầu hiện đại hoá mạng lới cấp nớc, từ năm 1997 WSC đợc trang bị một hệ thống mạng máy tính
hiện đại, đòi hỏi một hệ thống phần mềm mới, ứng dụng công nghệ hiện đại, có khả năng kết nối diện rộng, quản lý
lợng khách hàng lớn và đáp ứng yêu cầu nghiệp vụ mới. Nhân viên cũng đã có tác phong công nghiệp và các thao
tác hệ thống đã trở thành một thói quen nghiệp vụ. WSC cần xây dựng Hệ tính hoá đơn và quản lý khách hàng
để phục vụ cho công việc kinh doanh của mình đạt hiệu quả hơn. Mục tiêu đặt ra đối với hệ thống là phải đáp ứng đ-
ợc 142 yêu cầu do các chuyên gia t vấn nớc ngoài bao trùm lên các lĩnh vực:
+ Khách hàng
+ Yêu cầu và khiếu nại của khách hàng

Phân hệ IV: Thực hiện các chức năng về báo cáo tổng hợp

2
Trang 2
Vietebooks Nguyn Hong Cng
Dới đây là các sơ đồ chức năng cho từng phân hệ
Quản trị hệ thống
Hệ thống
Khách hàng Hoá đơn Thưởng phạt
Vật tư-Thiết bị
Nhân viên Trợ giúp
Đổi người
sử dụng
Cập nhật dữ
liệu phân tán
Đăng kí
người sử dụng
Đặt tham số
hệ thống
Kết thúc
Phân quyền
truy nhập
Đối tượng
sử dụng
Mục đích sử
dụng
Ngành ngề
hoạt động
Mã số
thanh toán

Sơ đồ chức năng của phân hệ I
3
Trang 3
Vietebooks Nguyn Hong Cng
Quản lý khách hàng
Dịch vụ Vật tư Đánh giá
Danh mục
Báo cáo
Đăng kí
khách hàng
Nhập vật tư
Xuất-Hoàn trả
vật tư
Nhập thiết bị
Cập nhật
đồng hồ
Khuyến khích
khách hàng
Phạt
khách hàng
Khách hàng
phụ
Đăng kí mục
đích sử dụng
Đăng kí
ngành nghề
Ghi chú về
khách hàng
Gán đợt
hoá đơn

Đơn vị
hành chính
LoạI hình
thi công
Mã yêu cầu
và khiếu nại
Mã thiết bị
Kiểu và cỡ
đồng hồ
Trạng tháI
đọc số
BC Hợp đồng
khách hàng
BC thông tin
khách hàng
BC đồng hồ
theo địa bàn
Nước theo giá
và đồng hồ
Nước tiêu thụ
vượt mức
Liệt kê dồng
hồ theo cỡ
Chậm thanh
toán
Liệt kê đồng
hồ theo kiểu
Báo cáo đánh
giá khách hàng
Sơ đồ chức năng của phân hệ II

đồng hồ
Kiểm soát sai
lầm đọc số
Nhập chỉ số
đồng hồ nhanh
Thông tin
khách hàng
Thông tin cài
đặt đồng hồ
Thông tin đồng
hồ chi tiết
Định nghĩa dãy
số hoá đơn
Tỉ giá qui đổi
Đặt giờ tự động
tính hoá đơn
Tính hoá đơn
Tính lại
hoá đơn
In hoá đơn
Đánh số
hoá đơn
Phát hành
hoá đơn
Chuyển nợ
Phân hoá đơn
cho NV thu
Xử lý khiếu nại
Cắt dịch vụ
Phục hồi

Nước theo giá
và đồng hồ
Thanh toán
chậm
Chi tiết
khách hàng
Các báo cáo
chuẩn thu
Đánh giá
khách hàng
Sơ đồ chức năng của phân hệ III
5
Trang 5
Vietebooks Nguyn Hong Cng
Báo cáo tổng hợp
Đồng hồ Nước cung cấp Tiền nước
Phụ thuKhách hàng
Các báo cáo thông
tin khách hàng
Các báo cáo đánh
giá khách hàng
Tình hình chậm
thanh toán
Liệt kê theo cỡ và
tình trạng
Liệt kê theo kiểu
và tình trạng
Đồng hồ được cài
đặt theo cỡ
Đồng hồ được cài

Nước tiêu thụ theo
giá và đồng hồ
Nước tiêu thụ vượt
mức đăng kí
Báo cáo phân tích
nước thất thoát
Cập nhật sản
lượng nước
Sơ đồ chức năng của phân hệ IV
6
Trang 6
Vietebooks Nguyn Hong Cng
Sơ đồ quan hệ thực thể ERD (Entity Relationship Diagram):
Hệ quản lý hoá đơn nớc bao gồm 105 thực thể trong đó các thực thể quan trọng là :
+ Khách hàng (Customers) : Hệ thống lu các thông tin khách hàng nh tên, địa chỉ, số CMT... Các thông tin liên
quan đến tính tiền nớc và báo cáo thì đợc chuẩn hoá. Mỗi khách hàng có một mã số duy nhất gọi là mã danh bộ
(cust_no) dựa trên địa danh quận,phờng và đờng phố. Ngoài ra ứng với mỗi khách hàng chính còn có thể các khách
hàng phụ đợc phân biệt bởi sub_cust_id (nếu là khách hàng chính thì thuộc tính này nhận giá trị null). Với mỗi loại
khách hàng thuộc một trong các loại: T gia, Tập thể, Cơ quan, Ngời nớc ngoài. Khách hàng có thể sử dụng nớc cho
nhiều mục đích khác nhau nh dùng cho sinh hoạt, sản xuất, dịch vụ và mỗi một đích có một giá biểu riêng. Một
khách hàng thuộc một đợt tính hoá đơn/thu tiền/đọc số nhất định. Đồng hồ lắp đặt đợc lu đầy đủ các thông số kỹ
thuật và vị trí vật lý.
Hiện nay tổng số khách hàng của công ty là 500,000.
+ Đồng hồ (meter): đây là một trong các vật t của công ty cần đợc quản lý với số lợng 1,000,000 chiếc.
+ Mỗi đồng hồ đợc cài đặt cho khách hàng đều phải lu giữ các thông tin liên quan nh mã khách hàng, kinh độ,
vĩ độ, nhãn hiệu, nớc sản xuất, kiểu mặt số.
+ Hoá đơn (Bills): 5,000,000.
+ Chuyển nợ khó đòi: 1,000,000
+ Chỉ số đồng hồ (Metereading) là một trong những phần nhập liệu chính, phức tạp, đợc thực hiện hàng tháng
trớc khi tính hoá đơn tổng cộng là 3,000,000 chỉ số. Mỗi đợt cần phải nhập khoảng 20,000 chỉ số đồng hồ với các

Billing
Server
File
Server
Developer
Server
Mô hình tổng thể mạng máy tính
B. Cách thức tìm ra những lệnh cần tối u trong phần mềm
Để tìm ra đợc các lệnh SQL cha tối u trong phần mềm cần tạo ra bản sao các lệnh đã đợc thực hiện một cách
thực sự (câu lệnh SQL đợc thực hiện có thể không giống nh câu lệnh SQL viết trong chơng trình) sau đó xây dựng
lại cây phân tích. Quá trình tạo bản sao câu lệnh trong phần mềm chỉ đợc thực hiện khi các tham số trong Parameter
File có giá trị:
timed_statistics = true # Tính toán thời gian thực hiện câu lệnh
sql_trace = true # Tạo các file có tên mở rộng là trc
# Nơi chứa các file *.trc do các tiến trình của hệ thống
background_dump_dest=%RDBMS80%\trace
# Nơi chứa các file *.trc do các tiến trình của ngời dùng
user_dump_dest=%RDBMS80%\trace
Sau khi thực hiện các thay đổi trong Init File thì CSDL phải đợc kết nối lại (Shutdown sau đó Startup) để các
tham số này có tác dụng. Khi thực hiện một chơng trình thì hình ảnh của các tiến trình sinh ra sẽ đợc ghi lại ra đĩa.
Bằng trình tiện ích TKPROF.EXE có thể giải nghĩa các tiến trình này cũng nh dựng lại cây phân tích lệnh từ đó tìm
ra các lệnh cha hợp lý mà cần thiết phải tối u (có thể nối nhiều file *.trc lại cho TKPFOF thi hành một lần).
Cú pháp:
TKPROF filename1 filename2 [ SORT = [ ( ] option [ , option, option,..) ] ] [ PRINT = integer ]
[ INSERT = filename3 ] [ SYS = Yes | No ] [ TABLE = schema.table ]
[ EXPLAIN = user/password ]
Trong đó các tham số
- filename1: tên file ảnh của tiến trình ( *.trc )
8
Trang 8

------------------------------ ------------------------------
BILL_CRE_SCO_FK_I CR_SCORE_STT
BILL_EMP_FK_I EMP_ID
Theo kết quả trên thì Index cột cust_id của bảng Bills cha tồn tại vì vậy cần tạo Index cho cột CUST_ID của
bảng BILLS nh sau:
CREATE INDEX bill_cust_id ON bills(cust_id);
Sau khi tạo các Index thì phơng thức truy nhập bảng BILLS chuyển từ duyệt toàn bộ (FULL) trở thành duyệt
qua Index có giới hạn (RANGE SCAN trong cây phân tích lệnh), thời gian thực hiện của các câu lệnh tơng ứng đợc
lệt kê trong bảng so sánh:
Câu lệnh tính hoá đơn dựa trên bảng hoá đơn cơ sở (Based_Bill):
9
Trang 9
Vietebooks Nguyn Hong Cng
begin :X0 := SP_WBL_CALCULATE_BASED_BILL(:CUST_ID_, :SUB_CUST_ID_,
:BRANCH_CODE_, :PERIOD_, :YEAR_, :NAME_, :CUSTOMER_NO_, :CONTRACT_NO_,
:ACRINYM_, :BC_NAME_, :CURRENCY_ID_, :PERCENTAGE_, :ADDRESS_, :SIGN_,
:BILL_STATUS_, :OLD_STT_, :OLD_PERIOD_, :OLD_YEAR_, :BILL_NO_);
end;
Bảng so sánh thời gian thực hiện procedure trớc và sau khi tạo Index
Index Call count cpu elapsed disk query current rows
Không 2 0.37 24.85 1308 1340 4 1
Có 2 0.28 0.28 0 37 2 1
Câu lệnh tìm lợng nớc điều chỉnh, lợng nớc tiêu thụ và mã trạng thái từ bảng hoá đơn (Bill):
SELECT NVL(WATER_ADJUST,0),NVL(LNTT,0),STATUS_CODE FROM BILLS
WHERE CUST_ID = :b1 AND STT = :b2;
Bảng so sánh thời gian thực hiện câu lệnh trớc và sau khi tạo Index
Index Call count cpu elapsed disk query current rows
Không
3 0.10 24.03 1308 1310 2 2
Có 3 0.01 0.01 0 6 0 2

3 MERGE JOIN
4 SORT (JOIN)
9828 TABLE ACCESS (FULL) OF 'BILLS'
3 SORT (JOIN)
3 VIEW
3 SORT (UNIQUE)
4 TABLE ACCESS (BY ROWID) OF 'BASED_BILLS'
5 INDEX (RANGE SCAN) OF 'B_B_CUST_FK_I' (NON-UNIQUE)
Cây phân tích lệnh khi đã có Index
Rows Execution Plan
------- ---------------------------------------------------
0 UPDATE STATEMENT GOAL: CHOOSE
0 NESTED LOOPS
0 VIEW
0 SORT (UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'BASED_BILLS'
0 INDEX (RANGE SCAN) OF 'B_B_CUST_FK_I' (NON-UNIQUE)
0 TABLE ACCESS (BY ROWID) OF 'BILLS'
0 INDEX (RANGE SCAN) OF 'BILL_CUST_ID' (NON-UNIQUE)
Câu lệnh xác lập giá trị tiền thu điều chỉnh và phụ thu điều chỉnh:
UPDATE BILLS SET MONEY_ADJUST=:b1,SURCHARGE_ADJUST=:b2
WHERE CUST_ID = :b3 AND STT = :b4;
Bảng so sánh thời gian thực hiện câu lệnh trớc và sau khi tạo Index
11
Trang 11
Vietebooks Nguyn Hong Cng
Index Call count cpu elapsed disk query current rows
Không 2 0.02 28.28 1308 1312 3 1
Có
2 0.03 0.03 0 8 1 1

------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
13965 FILTER
12
Trang 12

Trích đoạn call count cpu elapsed disk query current rows

---