Em có viết 1 số Proc trong SQL có tham số để trả về 1 bảng danh sách.
Vậy làm thế nào để em có thể lấy được nó về ạ. Em chỉ thấy table khi connect đến Slq server thôi ạ.
Và nếu có thì điều này có bảo mật không ạ. File excel này còn được gửi đi cho những người khác nữa ạ.
Em cảm ơn!
Em có viết 1 số Proc trong SQL có tham số để trả về 1 bảng danh sách.
Vậy làm thế nào để em có thể lấy được nó về ạ. Em chỉ thấy table khi connect đến Slq server thôi ạ.
Và nếu có thì điều này có bảo mật không ạ. File excel này còn được gửi đi cho những người khác nữa ạ.
Em cảm ơn!
Đây là code mình đang sử dụng cho file của mình, bạn có thể xử lý tiếp rs như câu truy vấn thông thường.
Mã:
Command.ActiveConnection = cn
Command.CommandTimeout = 60
Command.CommandType = adCmdText
Command.CommandText = "sl_ListHocSinh '""& Biến tương ứng với Biến @ma trong SP &""'"
Set rs = Command.Execute
Nếu bạn có quyền trong database thì có thể tạo một 1 user chỉ có quyền thực thi các câu lệnh SP thôi, sau đó bỏ user đó vào câu truy vấn kết nối đến database, đây là cách bảo mật dữ liệu hiện tại của mình, người dùng có thể biết code VBA, nhưng dữ liệu trên database thì sẽ không xem được hết.
Đây là code mình đang sử dụng cho file của mình, bạn có thể xử lý tiếp rs như câu truy vấn thông thường.
Mã:
Command.ActiveConnection = cn
Command.CommandTimeout = 60
Command.CommandType = adCmdText
Command.CommandText = "sl_ListHocSinh '""& Biến tương ứng với Biến @ma trong SP &""'"
Set rs = Command.Execute
Nếu bạn có quyền trong database thì có thể tạo một 1 user chỉ có quyền thực thi các câu lệnh SP thôi, sau đó bỏ user đó vào câu truy vấn kết nối đến database, đây là cách bảo mật dữ liệu hiện tại của mình, người dùng có thể biết code VBA, nhưng dữ liệu trên database thì sẽ không xem được hết.
CommandType là adCmdStoredProc
CommandText là tên của SP (stored procedure)
Sau đó phải refresh Parameters, và set từng Parameter.
Đối với các kết nối vung văng (cho phép ADO kết nối như vậy có thể coi là vung văng, không đáng tin cậy), mục đích chính của SP là giới hạn quyền truy cập và chống SQL Injection.
SQL Injection là một kỹ thuật dùng tham số sửa đổi câu lệnh CommandText để phá hoại CSDL. SP chỉ nhận tham số qua parameters cho nên người phá hoại không thể sửa đổi câu lệnh SQL bên trong SP.
Đây là code mình đang sử dụng cho file của mình, bạn có thể xử lý tiếp rs như câu truy vấn thông thường.
Mã:
Command.ActiveConnection = cn
Command.CommandTimeout = 60
Command.CommandType = adCmdText
Command.CommandText = "sl_ListHocSinh '""& Biến tương ứng với Biến @ma trong SP &""'"
Set rs = Command.Execute
Nếu bạn có quyền trong database thì có thể tạo một 1 user chỉ có quyền thực thi các câu lệnh SP thôi, sau đó bỏ user đó vào câu truy vấn kết nối đến database, đây là cách bảo mật dữ liệu hiện tại của mình, người dùng có thể biết code VBA, nhưng dữ liệu trên database thì sẽ không xem được hết.
Cảm ơn bạn nhé, để mình thử xem ạ.
Hiện tại server đó là mình quản lý bạn à. Vì ứng dụng viết trên Access dùng nội bộ và cả qua Internet, Vì mình cứ phải lấy dữ liệu qua access sau đó tải ra file excel sau đó thực hiện copy các data thô vào 1 file excel mà đã xây dựng sẵn để refrest là dữ liệu sẽ có như đã xây dựng,
nên mình muốn là khi nào mở file excel ra thì refresh tại excel luôn không bị mất nhiều thao tác
CommandType là adCmdStoredProc
CommandText là tên của SP (stored procedure)
Sau đó phải refresh Parameters, và set từng Parameter.
Đối với các kết nối vung văng (cho phép ADO kết nối như vậy có thể coi là vung văng, không đáng tin cậy), mục đích chính của SP là giới hạn quyền truy cập và chống SQL Injection.
SQL Injection là một kỹ thuật dùng tham số sửa đổi câu lệnh CommandText để phá hoại CSDL. SP chỉ nhận tham số qua parameters cho nên người phá hoại không thể sửa đổi câu lệnh SQL bên trong SP.
Nếu tôi trả lời thẳng câu này là tôi nói dóc.
Muốn biết "ổn" (an ninh) hay không thì phải biết thiết kế của Server. Chỉ có người Admin của Server mới trả lời được.
Nếu CSDL chứa trong file Excel là căn hộ nhỏ thì SQL Server Database là một cơ quan làm việc.
Cho phép một user kết nối là bạn đã giao một chùm chìa khoá cơ quan. Số chìa khoá tuỳ theo số quyền của user, nhưng chùm nào cũng phải có cái chìa mở cổng chính.
Vì vậy cơ quan luon phải cảnh giác 2 điều sau:
1. user lạm dụng quyền
2. kẻ xấu ăn cắp chùm chìa khoá từ user (hoặc là bạn bè, bám theo) để vào cơ quan. Điểm này quan trọng hơn điểm trên, vì kẻ xấu này rất nhiều khả năng là giỏi hơn bạn.
Những nơi tôi làm việc, SQL Server được bảo vệ như thành trì. Một user phải có văn bản trưởng phòng yêu cầu Admin cho phép kết nối và văn bản nêu rõ phạm vi truy cập. Nếu user không phải là developer thì Adimm buộc phải qua một buổi huấn luyện về sử dụng. Admin thường xuyên phân tích cái log để kiểm soát những hoạt động của users.
Nếu tôi trả lời thẳng câu này là tôi nói dóc.
Muốn biết "ổn" (an ninh) hay không thì phải biết thiết kế của Server. Chỉ có người Admin của Server mới trả lời được.
Nếu CSDL chứa trong file Excel là căn hộ nhỏ thì SQL Server Database là một cơ quan làm việc.
Cho phép một user kết nối là bạn đã giao một chùm chìa khoá cơ quan. Số chìa khoá tuỳ theo số quyền của user, nhưng chùm nào cũng phải có cái chìa mở cổng chính.
Vì vậy cơ quan luon phải cảnh giác 2 điều sau:
1. user lạm dụng quyền
2. kẻ xấu ăn cắp chùm chìa khoá từ user (hoặc là bạn bè, bám theo) để vào cơ quan. Điểm này quan trọng hơn điểm trên, vì kẻ xấu này rất nhiều khả năng là giỏi hơn bạn.
Những nơi tôi làm việc, SQL Server được bảo vệ như thành trì. Một user phải có văn bản trưởng phòng yêu cầu Admin cho phép kết nối và văn bản nêu rõ phạm vi truy cập. Nếu user không phải là developer thì Adimm buộc phải qua một buổi huấn luyện về sử dụng. Admin thường xuyên phân tích cái log để kiểm soát những hoạt động của users.
Vâng. Em cảm ơn anh.
Hiện tại SQL SV em đang dùng là em cài đặt luôn trên 1 PC mà em làm việc. Về vấn đề bảo mật thì đúng là em không hiểu gì nhiều và thực chất cũng không có nhiều kiến thức để tìm hiểu và làm cho nó an toàn. Ban đầu chỉ là xây dựng 1 ứng dụng nhỏ tiện mình quản lý, càng sau càng nâng cấp để người khác dùng được và dùng qua được cả internet, để đến được từ bước xấy dựng code và cách kết nối đến SQL SV cũng là 1 trạng đường dài để tìm hiểu. Về vấn đề an toàn thì e chỉ có thể backup data hàng ngày để đề phòng vì e nghĩ dữ liệu này chỉ quan trọng với e và 1 số đồng nghiệp vì nếu mất thì không có gì để sử dụng tiếp theo. Còn với người khác thì chắc không có ý nghĩa
Các Form nhập liệu đầu vào em cũng sử dụng parameter và xử lý các ký tự đặc biết để tránh người dùng vô tình hoặc gì thôi ạ.
Vậy anh có thể cho em hỏi. Nếu khi sử dụng qua internet và các file excel hoặc front end được kết nối đến server mà chỉ toàn người trong cty sử dụng (đa phần k biết gì về data) thì những người mà k có ứng dụng e xây dựng kết nối đến SQL server thì có thể phá hoại được CSDL của em không ạ?
Vâng. Em cảm ơn anh.
Hiện tại SQL SV em đang dùng là em cài đặt luôn trên 1 PC mà em làm việc. Về vấn đề bảo mật thì đúng là em không hiểu gì nhiều và thực chất cũng không có nhiều kiến thức để tìm hiểu và làm cho nó an toàn. ...
Nếu nó chỉ trên một PC thì vấn đề bảo mật và an toàn không quan trọng.
Chỉ quan trọng ở chỗ cần backup thường xuyên. Nếu backup qua mạng được thì càng tốt.
Nếu bị attacked thì bạn có thể recover và reconfigure dễ dàng.
Vậy anh có thể cho em hỏi. Nếu khi sử dụng qua internet và các file excel hoặc front end được kết nối đến server mà chỉ toàn người trong cty sử dụng (đa phần k biết gì về data) thì những người mà k có ứng dụng e xây dựng kết nối đến SQL server thì có thể phá hoại được CSDL của em không ạ?
File font end là Excel hay Access điều phải có cái hàm để kết nối tới SQL Server mà hàm này tất nhiên phải chứa thông tin username và pass đăng nhập SQL Server. Khi có 2 thông tin này thì người ta có thể dùng lệnh "Select.." để liệt kê các Table có trong SSV và lấy dữ liệu về xem.
Để khoá không cho xem code thì Access chỉ cần chuyển sang .accde là không xem được, còn Excel thì bạn phải phải kiếm tool, hàm để khoá xem VBA code, mã hoá phần thông tin đăng nhập này,
CommandType là adCmdStoredProc
CommandText là tên của SP (stored procedure)
Sau đó phải refresh Parameters, và set từng Parameter.
Đối với các kết nối vung văng (cho phép ADO kết nối như vậy có thể coi là vung văng, không đáng tin cậy), mục đích chính của SP là giới hạn quyền truy cập và chống SQL Injection.
Em đã thử kiểu khai báo như trên và set Parameter thì cho thời gian trả về kết quả lâu hơn so với việc truy vấn theo text (cùng 1 SP), nên em chọn các tham số kia. Ở VBA em sẽ tạo các hàm để kiểm duyệt các biến của user nhập vào sao cho đúng với kiểu biến nạp vào SP, nếu user nhập vào biến không hợp lệ thì sẽ không thực thi câu lệnh , như thế sẽ nhanh hơn và hiệu quả hơn.
Em đã thử kiểu khai báo như trên và set Parameter thì cho thời gian trả về kết quả lâu hơn so với việc truy vấn theo text (cùng 1 SP), nên em chọn các tham số kia. Ở VBA em sẽ tạo các hàm để kiểm duyệt các biến của user nhập vào sao cho đúng với kiểu biến nạp vào SP, nếu user nhập vào biến không hợp lệ thì sẽ không thực thi câu lệnh , như thế sẽ nhanh hơn và hiệu quả hơn.
"lâu" đối với tôi không phải là một yếu tố. "lâu" ở một hệ thống này vẫn có thể "nhanh" ở hệ thống khác.
Sử dụng adCmdStoredProc là cách chính thống để gọi SP
Dùng text là cách đi vòng. Vì nó là full text cho nên không đủ an toàn.
Tuy nhiên, như toi đã nói ở bài #10, nếu nó chỉ là hệ thống cá nhân thì chả cần an toàn. Và trừ phi cái SP rất lớn, xài dynamic SQL luôn cho khoẻ.
"lâu" đối với tôi không phải là một yếu tố. "lâu" ở một hệ thống này vẫn có thể "nhanh" ở hệ thống khác.
Sử dụng adCmdStoredProc là cách chính thống để gọi SP
Dùng text là cách đi vòng. Vì nó là full text cho nên không đủ an toàn.
Lâu đối với thầy không phải là một yếu tố, nhưng với em là một yếu tố, đôi khi đối với user cũng là một yếu tố để quyết định có sử dụng sản phẩm đó không.
Em đồng ý với thầy sử dụng adCmdStoredProc là cách chính thống để gọi SP, nhưng đôi khi con đường chính lại về nhà chậm hơn so với mấy con đường vòng, thầy có thể cho em biết được việc sử dụng full text không đủ an toàn ở điểm nào không, người dùng (không có ý định phá hoại) có thể làm được những gì? người dùng (có ý định phá hoại) có thể làm được những gì?
Em thiết kế database cho công ty khoảng 20 người sử dụng, nên việc bảo mật em cũng quan tâm. Hệ thống cá nhân thì chả cần an toàn nhưng em nghĩ nên tìm hiểu và sử dụng SP thay vì dynamic SQL, vì nếu câu lệnh đó được sử dụng nhiều nơi với nhiều điều kiện khác nhau trong file excel thì mỗi lần muốn chỉnh sửa lại tốn nhiều thời gian hơn so với sửa trên SP (Trước đây em đã viết kiểu như vậy và việc chỉnh sửa khá mất thời gian nên em mới tìm hiểu đến SP), chưa kể đến những ưu điểm của SP so với truy vấn thông thường.
... nếu câu lệnh đó được sử dụng nhiều nơi với nhiều điều kiện khác nhau trong file excel thì mỗi lần muốn chỉnh sửa lại tốn nhiều thời gian hơn so với sửa trên SP ...
SP chỉ dùng cho các truy vấn đã được thành hình. Một khi đã được dùng vào code rồi thì rất ít khi người ta chỉnh sửa nó. Các hệ thống lớn đều có cache lệnh sử lý của SP.
Tuy theo lý thuyết, chỉnh SP là tất cả mọi code gọi nó đều xài được nhưng trên thực tế, bạn chỉ chỉnh sửa được cách làm việc bên trong thôi, cái giao diện (interface: tức là parameters và result set) thì bạn phải giữ. Nếu nó thay đổi tínhn chất hoặc cấu trúc kết quả hoặc giao diện thì mọi code gọi nó cũng phải chỉnh sửa như thường (thêm bớt parameters, thêm bớt số cột trả về...)
Trên thực tế, khi chỉnh sửa một SP, người ta đặt luôn một cái tên mới và hẹn ngày decommission cái cũ.
Ví dụ: Bạn có một cái SP nhiều người dùng. Mỗi lần bạn sửa nó bạn phải báo cho tất cả những người dùng nó. Code của tôi gọi SP của bạn. Bạn sửa SP, quên báo cho tôi biết thì sao? Code tôi có khả năng lấy kết quả sai (đúng với bạn, nhưng sai với tôi) mà tôi không hề hay.
Em ví dụ như vầy, trong file Excel có nhiều chỗ truy vấn tới bảng danh sách học sinh (tùy vào mục đích của người dùng khác nhau) với các câu lệnh như sau:
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh -- Không có biến để nạp
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh WHERE GioiTinh=N'"& @BienNvarChar &"'
SELECT MaHocSinh,TenHocSinh,GioiTinh FROM DanhSachHocSinh WHERE Tuoi>='"& @BienInt &"'
SELECT MaHocSinh,TenHocSinh,GioiTinh,Lop FROM DanhSachHocSinh WHERE HienDien='"& @BienBit &"'
Vậy em sẽ viết câu SP như sau:
CREATE PROCEDURE DanhSachHocSinh _SELECT @option TINYINT, @BienNvarChar NVARCHAR(5) =NULL, @BienInt INT=NULL, @BienBit BIT=NULL
IF @option ='1'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh
ELSE IF @option ='2'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh WHERE GioiTinh=@BienNvarChar
ELSE IF @option ='3'
SELECT MaHocSinh,TenHocSinh,GioiTinh FROM DanhSachHocSinh WHERE Tuoi>=@BienInt
ELSE IF @option ='4'
SELECT MaHocSinh,TenHocSinh,GioiTinh,Lop FROM DanhSachHocSinh WHERE HienDien=@BienBit
Và tùy vào người dùng khác nhau mà 4 câu truy vấn ban đầu em sẽ thay đổi như sau:
DanhSachHocSinh _SELECT '1'
DanhSachHocSinh _SELECT '2',N'"& @BienNvarChar &"'
DanhSachHocSinh _SELECT '3',NULL,'"& @BienInt &"'
DanhSachHocSinh _SELECT '4',NULL,NULL,'"& @BienBit &"'
Vậy một cái SP nhiều người dùng. Mỗi khi người dùng nào đó báo lỗi hoặc muốn thay đổi kết quả trả về,em chỉ cần sửa nội dung trong @option tương ứng, nếu kết quả trả về thay đổi giao diện của người dùng đó( mà đó cũng do yêu cầu từ người dùng đó),em chỉ cần chỉnh sửa code và giao diện của người đó, em không cần phải báo cho người dùng khác biết. Và với câu truy vấn cho bảng DanhSachHocSinh đó, có phát sinh nhu cầu thêm từ người dùng, nếu truy vấn đã có thì em gọi lại, nếu kết quả trả về mới thì em chỉ cần tạo thêm @option khác. Đây là cách em đang thiết kế cho database và file của mình.
Nếu nó thay đổi tính chất hoặc cấu trúc kết quả hoặc giao diện thì mọi code gọi nó cũng phải chỉnh sửa như thường (thêm bớt parameters, thêm bớt số cột trả về...)
Em bắt chước các sub trong vba với các biến có thể để optional và áp dụng cho câu SP với biến để giá trị NULL như trên của mình, như thế sẽ linh động cho việc sử dụng SP.
Vậy em sẽ viết câu SP như sau:
CREATE PROCEDURE DanhSachHocSinh _SELECT @option TINYINT, @BienNvarChar NVARCHAR(5) =NULL, @BienInt INT=NULL, @BienBit BIT=NULL
IF @option ='1'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh
ELSE IF @option ='2'
SELECT MaHocSinh,TenHocSinh FROM DanhSachHocSinh WHERE GioiTinh=@BienNvarChar
ELSE IF @option ='3'
SELECT MaHocSinh,TenHocSinh,GioiTinh FROM DanhSachHocSinh WHERE Tuoi>=@BienInt
ELSE IF @option ='4'
SELECT MaHocSinh,TenHocSinh,GioiTinh,Lop FROM DanhSachHocSinh WHERE HienDien=@BienBit
CREATE PROCEDURE TimKiemDSHocSinh
@GioiTinh CHAR(5)
@Tuoi INT
@HienDien BIT
WITH RECOMPILE
AS
SELECT A.*
FROM DanhSachHocSinh A
WHERE (@GioiTinh IS NULL OR A.GioiTinh = @GioiTinh)
AND (@Tuoi IS NULL OR A.Tuoi >= @Tuoi)
AND (@HienDien IS NULL OR A.HienDien = @HienDien)
Sau này muốn thêm điều kiện cũng thêm 1 dòng đơn giản hơn.
Còn về viêc dùng CommandType là adCmdStoreProc hay adCmdText nhanh chậm cũng là do cách thiết kế Store proc. Nếu Stored proc dùng SQl động sẽ nhanh hơn dùng SQL tĩnh.
Cái ví dụ trên là tôi dùng SQL tĩnh nên có thể khi chạy lần đầu sẽ nhanh, mấy lần sau sẽ châm, qua máy khác cũng vậy. Đó là do kiểu Stored Proc này tạo một cái Execution plan khi lần đầu chạy và lưu vào sp cache, những lần chạy sau nó lấy cái execution plan này ra chạy nhưng có thể không phù hợp với file dữ liệu lần sau nên tốc độ sẽ chậm. Để khắc phục tôi thêm "WITH RECOMPILE" để mỗi lần chạy là mỗi lần tạo Execution plan mới, tốc độ chăc chắn nhanh hơn nhưng hệ thống lại mất thêm thời gian tạo execution plan.
CREATE PROCEDURE TimKiemDSHocSinh
@GioiTinh CHAR(5)
@Tuoi INT
@HienDien BIT
WITH RECOMPILE
AS
SELECT A.*
FROM DanhSachHocSinh A
WHERE (@GioiTinh IS NULL OR A.GioiTinh = @GioiTinh)
AND (@Tuoi IS NULL OR A.Tuoi >= @Tuoi)
AND (@HienDien IS NULL OR A.HienDien = @HienDien)
Sau này muốn thêm điều kiện cũng thêm 1 dòng đơn giản hơn.
Còn về viêc dùng CommandType là adCmdStoreProc hay adCmdText nhanh chậm cũng là do cách thiết kế Store proc. Nếu Stored proc dùng SQl động sẽ nhanh hơn dùng SQL tĩnh.
Cái ví dụ trên là tôi dùng SQL tĩnh nên có thể khi chạy lần đầu sẽ nhanh, mấy lần sau sẽ châm, qua máy khác cũng vậy. Đó là do kiểu Stored Proc này tạo một cái Execution plan khi lần đầu chạy và lưu vào sp cache, những lần chạy sau nó lấy cái execution plan này ra chạy nhưng có thể không phù hợp với file dữ liệu lần sau nên tốc độ sẽ chậm. Để khắc phục tôi thêm "WITH RECOMPILE" để mỗi lần chạy là mỗi lần tạo Execution plan mới, tốc độ chăc chắn nhanh hơn nhưng hệ thống lại mất thêm thời gian tạo execution plan.
Mỗi khi thực thi câu lệnh mà thấy tộc độ lâu mình cũng thử nhiều phương pháp khác đề tìm nguyên nhân, và cũng sử dụng WITH RECOMPILE để chạy, có câu cải thiện tốc dộ hơn, nhưng mình so sánh ở đây là giữa 2 cách viết câu truy vấn thì việc set parameter cho nó lại khiến tốc độ chậm hơn (bạn có thể test thử xem, mình nhớ là nó chậm ở code VBA, lúc debug các dòng set parameter mình nhớ nó chậm,hình như chứ không phải là chậm lúc thực thi ), và mình cảm thấy việc set parameter cho nó cũng chỉ để kiểm tra tính đúng đắn của dữ liệu nạp vào, mình có thể tạo các hàm kiểm duyệt biến từ VBA để làm việc đó nên sử dụng adCmdText. Câu truy vấn của bạn mình có đọc đâu đó rồi, để mình tìm hiểu xem, nhưng câu truy vấn của bạn có bị giới hạn đối với nhu cầu người dùng về các field khác nhau không? Như ví dụ của mình chỉ trả về các field mà người dùng cần chứ không Select * , và mình cũng hạn chế select * vì nó làm chậm .
...
Còn về viêc dùng CommandType là adCmdStoreProc hay adCmdText nhanh chậm cũng là do cách thiết kế Store proc. Nếu Stored proc dùng SQl động sẽ nhanh hơn dùng SQL tĩnh.
Cái ví dụ trên là tôi dùng SQL tĩnh nên có thể khi chạy lần đầu sẽ nhanh, mấy lần sau sẽ châm, qua máy khác cũng vậy. Đó là do kiểu Stored Proc này tạo một cái Execution plan khi lần đầu chạy và lưu vào sp cache, những lần chạy sau nó lấy cái execution plan này ra chạy nhưng có thể không phù hợp với file dữ liệu lần sau nên tốc độ sẽ chậm. Để khắc phục tôi thêm "WITH RECOMPILE" để mỗi lần chạy là mỗi lần tạo Execution plan mới, tốc độ chăc chắn nhanh hơn nhưng hệ thống lại mất thêm thời gian tạo execution plan.
Ở bài #13, ngừoi ta không nói rõ cái tốc độ là tốc độ tính toán hay tốc độ trả về kết quả cho nên tôi khong muốn nói thêm.
Tôi chỉ nhấn mạnh ở đây là dùng lệnh gọi SP tức là xử lý thẳng câu lệnh EXEC, tức là tự bỏ qua tính chất an toàn và độc lập của SP.
Cách gọi chính thống xác định rõ ràng đây là mọt procedure call, không phải là xử lý một lệnh. Và giữ nguyên tính chất an toàn của SP.
Nếu tôi viết SP thì tôi chỉ cho user quyền gọi SP. Tôi không cấp quyền chạy lệnh SQL.
Và ở bài #10 tôi có nói rằng nếu chỉ một máy nhỏ quèn thì chẳng cần phải biết chính quy hay đường vòng gì cả.
...
Em bắt chước các sub trong vba với các biến có thể để optional và áp dụng cho câu SP với biến để giá trị NULL như trên của mình, như thế sẽ linh động cho việc sử dụng SP.
Càng linh động thì càng phải giải thích cho người dùng nhiều.
Thói quen của nhiều người trên diễn đàn này là làm một cái module đủ thứ công việc, rồi dùng parameters để lái nó vào chỗ code cần thiết.
Cái đó không hoàn toàn sai, nhưng họ sai ở chỗ là một cái hàm hầm bà lằng như vây thì phải chú thích cách sử dụng parameters. Ở diễn đàn này, thói quen chú thích code hầu như không có.
Mặt khác, ngày xưa, máy tính yếu nên người ta dồn nhiều công việc vào một chỗ. Bây giờ là thời buổi lập trình hướng đói tượng. Người ta bó gọn công việc của procedure, hàm nào làm đúng công việc của nó. Nếu cần một cái "làm mọi thứ" thì viết hêm một cái "hầm bà lằng" tuỳ theo tình huống mà nó gọi những cái bó gọn kia. Đương nhiên nhiên gọi hàm là overhead, nhưng tôi sẵn sàng hy sinh tốc độ để bảo đảm việc quản lý code.
Càng linh động thì càng phải giải thích cho người dùng nhiều.
Thói quen của nhiều người trên diễn đàn này là làm một cái module đủ thứ công việc, rồi dùng parameters để lái nó vào chỗ code cần thiết.
Cái đó không hoàn toàn sai, nhưng họ sai ở chỗ là một cái hàm hầm bà lằng như vây thì phải chú thích cách sử dụng parameters. Ở diễn đàn này, thói quen chú thích code hầu như không có.
Mặt khác, ngày xưa, máy tính yếu nên người ta dồn nhiều công việc vào một chỗ. Bây giờ là thời buổi lập trình hướng đói tượng. Người ta bó gọn công việc của procedure, hàm nào làm đúng công việc của nó. Nếu cần một cái "làm mọi thứ" thì viết hêm một cái "hầm bà lằng" tuỳ theo tình huống mà nó gọi những cái bó gọn kia. Đương nhiên nhiên gọi hàm là overhead, nhưng tôi sẵn sàng hy sinh tốc độ để bảo đảm việc quản lý code.
Đứng ở góc độ người dùng thì họ không cần biết mình code linh động, code có chú thích, code đẹp, code có quản lý tốt hay không, cái họ quan tâm là kết quả có đúng không, thao tác có tiện không, thời gian chờ có lâu không, giao diện nhìn có bắt mắt không, nên em nghĩ sẽ không tốn thời gian giải thích cho người dùng nhiều. Những vấn đề này chỉ có những người viết code quan tâm với nhau thôi.
Em đồng ý với thầy một đoạn code nên chú thích thì sẽ tốt hơn một đoạn code không có chú thích, nó sẽ hữu ích cho mình cũng như người khác đọc để hiểu. Đối với những đoạn code phức tạp, khi mình viết cho một người nào đó đọc (có thể sau này mình cần đọc lại) thì viết chú thích sẽ tiết kiệm thời gian đọc hiểu hơn. Em cũng đồng ý hàm nào làm đúng công việc của nó là điều cần thiết khi viết code, nhưng không phải không có lý do mà người ta tạo ra optional hay được phép khai báo biến có giá trị NULL làm gì, nó vẫn có ích trong trường hợp nào đó, chỉ khi lạm dụng nó quá nhiều theo như thầy nói dẫn đến việc code khó hiểu hay khó bảo trì thì mới phản tác dụng.
Nói về hướng đói tượng cũng như việc quản lý code thì em không thấy liên quan gì tới máy mạnh hay yếu, em thấy quan trọng ở cách tổ chức và quản lý code, mỗi cá nhân có cách tổ chức và quản lý code khác nhau, miễn sao họ cảm thấy thuận tiện cho công việc, dễ hiểu, dễ tái sử dụng, không gây mất thời gian bảo trì hay mở rộng tính năng sau này là được, với thầy thì cách thầy đang làm thầy thấy hiệu quả nhưng em thì không ( có thể em chưa thấy giải pháp của thầy dựa trên code thực tế nên em vẫn sẽ bảo vệ quan điểm của mình), như em tạo sp cho bảng DanhSachHocSinh để dành các câu truy vấn liên quan tới bảng đó thì em nghĩ sẻ quản lý tốt hơn so với tạo từng câu SP riêng lẽ chứ, giả sử nếu thêm câu lệnh INSERT,UPDATE,DELETE thì em sẽ tạo thêm các @option nữa cho các câu lệnh đó, hoặc tái sử dụng các biến đang có trong những câu lệnh khác trong cùng 1 SP, sau này cần chỉnh sửa nội dung bảng nào thì em chỉ cần vào SP của bảng đó là được.
Em chỉ biết khái niệm override và overload trong lập trình hướng đối tượng, và em nghĩ thầy đang nhầm overload thành overhead.
CREATE PROCEDURE TimKiemDSHocSinh
@GioiTinh CHAR(5)
@Tuoi INT
@HienDien BIT
WITH RECOMPILE
AS
SELECT A.*
FROM DanhSachHocSinh A
WHERE (@GioiTinh IS NULL OR A.GioiTinh = @GioiTinh)
AND (@Tuoi IS NULL OR A.Tuoi >= @Tuoi)
AND (@HienDien IS NULL OR A.HienDien = @HienDien)
Sau này muốn thêm điều kiện cũng thêm 1 dòng đơn giản hơn.
Còn về viêc dùng CommandType là adCmdStoreProc hay adCmdText nhanh chậm cũng là do cách thiết kế Store proc. Nếu Stored proc dùng SQl động sẽ nhanh hơn dùng SQL tĩnh.
Cái ví dụ trên là tôi dùng SQL tĩnh nên có thể khi chạy lần đầu sẽ nhanh, mấy lần sau sẽ châm, qua máy khác cũng vậy. Đó là do kiểu Stored Proc này tạo một cái Execution plan khi lần đầu chạy và lưu vào sp cache, những lần chạy sau nó lấy cái execution plan này ra chạy nhưng có thể không phù hợp với file dữ liệu lần sau nên tốc độ sẽ chậm. Để khắc phục tôi thêm "WITH RECOMPILE" để mỗi lần chạy là mỗi lần tạo Execution plan mới, tốc độ chăc chắn nhanh hơn nhưng hệ thống lại mất thêm thời gian tạo execution plan.
Không thể khẳng định sql động sẽ nhanh hơn dùng sql tĩnh bạn.
Không phải lúc nào procedure cũng tạo plan tốt và lưu vao cache nó phụ thuộc vào parameter sniffing nên bạn nói nó chậm hơn là do plan chưa tốt thôi, procedure dùng sql tĩnh sẽ nhanh hơn động nếu có plan tốt vì nó không phải mất công tạo plan nũa mà sử dụng lại. Đương nhiên là execution plan sẽ thay đổi chứ nó không cố định nên chúng ta vẫn phải theo dõi thường xuyên nếu proc đó quan trọng.
Execution plan se thay đổi khi có sự thay đổi về cấu trúc bảng, db, và statitics của các bảng..
Không thể khẳng định sql động sẽ nhanh hơn dùng sql tĩnh bạn.
Không phải lúc nào procedure cũng tạo plan tốt và lưu vao cache nó phụ thuộc vào parameter sniffing nên bạn nói nó chậm hơn là do plan chưa tốt thôi, procedure dùng sql tĩnh sẽ nhanh hơn động nếu có plan tốt vì nó không phải mất công tạo plan nũa mà sử dụng lại. Đương nhiên là execution plan sẽ thay đổi chứ nó không cố định nên chúng ta vẫn phải theo dõi thường xuyên nếu proc đó quan trọng.
Execution plan se thay đổi khi có sự thay đổi về cấu trúc bảng, db, và statitics của các bảng..
Nay mình mới biết tới khái niệm sql động, sql tĩnh và parameter sniffing, thường thì mình thấy câu truy vấn đó chậm thì thử thêm hay bỏ WITH RECOMPILE. Anh quanluu1989 cho mình hỏi với SP mình tạo như trên sử dụng điệu kiện if else để tạo các truy vấn trả về các bảng kết quả khác nhau thì có ảnh hưởng tới parameter sniffing không?
Đứng ở góc độ người dùng thì họ không cần biết mình code linh động, code có chú thích, code đẹp, code có quản lý tốt hay không, cái họ quan tâm là kết quả có đúng không, thao tác có tiện không, thời gian chờ có lâu không, giao diện nhìn có bắt mắt không, nên em nghĩ sẽ không tốn thời gian giải thích cho người dùng nhiều. Những vấn đề này chỉ có những người viết code quan tâm với nhau thôi.
...
...
Em đồng ý với thầy một đoạn code nên chú thích thì sẽ tốt hơn một đoạn code không có chú thích, nó sẽ hữu ích cho mình cũng như người khác đọc để hiểu. Đối với những đoạn code phức tạp, khi mình viết cho một người nào đó đọc (có thể sau này mình cần đọc lại) thì viết chú thích sẽ tiết kiệm thời gian đọc hiểu hơn. Em cũng đồng ý hàm nào làm đúng công việc của nó là điều cần thiết khi viết code, nhưng không phải không có lý do mà người ta tạo ra optional hay được phép khai báo biến có giá trị NULL làm gì, nó vẫn có ích trong trường hợp nào đó, chỉ khi lạm dụng nó quá nhiều theo như thầy nói dẫn đến việc code khó hiểu hay khó bảo trì thì mới phản tác dụng.
...
Cứ theo dõi code VBA trên diễn đàn này sẽ thấy.
Có một vài hàm được viết ra với cả đống options. Nhìn vào rừng bỏ bố. Sỡ dĩ một số thành viên khác vẫn dùng và khuyến khích (recommend) dùng là vì họ quen với lối code này, và đã quen thuộc với hàm này.
Nếu bạn để ý sẽ thấy các thành viên được khuyến khích sẽ chẳng hiểu gì cả, cứ nhắm mắt mà theo thôi.
Mạt khác, có những hàm viết rất hoành tráng đầy đủ tùm lum, nhưng đưa đến ngừoi dùng thì họ tá hoả tam tinh. Chính thực là do ngừoi viết chỉ cốt hoành tráng mà không giải thích cách sử dụng.
...
Nói về hướng đói tượng cũng như việc quản lý code thì em không thấy liên quan gì tới máy mạnh hay yếu, em thấy quan trọng ở cách tổ chức và quản lý code, mỗi cá nhân có cách tổ chức và quản lý code khác nhau, miễn sao họ cảm thấy thuận tiện cho công việc, dễ hiểu, dễ tái sử dụng, không gây mất thời gian bảo trì hay mở rộng tính năng sau này là được, với thầy thì cách thầy đang làm thầy thấy hiệu quả nhưng em thì không ( có thể em chưa thấy giải pháp của thầy dựa trên code thực tế nên em vẫn sẽ bảo vệ quan điểm của mình), như em tạo sp cho bảng DanhSachHocSinh để dành các câu truy vấn liên quan tới bảng đó thì em nghĩ sẻ quản lý tốt hơn so với tạo từng câu SP riêng lẽ chứ, giả sử nếu thêm câu lệnh INSERT,UPDATE,DELETE thì em sẽ tạo thêm các @option nữa cho các câu lệnh đó, hoặc tái sử dụng các biến đang có trong những câu lệnh khác trong cùng 1 SP, sau này cần chỉnh sửa nội dung bảng nào thì em chỉ cần vào SP của bảng đó là được.
...
Trên quan điểm của một project manager, câu bôi đỏ trên sẽ được khuyên đỏ.
Tổ chức quản lý code thuộc về project chứ không phải cá nhân. Bởi vì bạn quen dựng project cá nhân cho nên có thể tự mình quản lý.
Trên thực tế, chỉ những developers kỳ cựu mới được leader cho quyền tự phân lấy interfaces của module mình viết ra. Các developers non thì chính thức chỉ là người dịch thuật toán thành code.
Những project lớn, code có peer review. Viết mà không đủ chú thích cho dễ hiểu tụi nó khuyên đỏ tan nát hết.
"Tái sử dụng" là một trong những tínhn chất của LTHĐT. Chính vì cái này mà tôi mới nói chuyện rằng việc nào procedure nấy, nhiều việc thì viết thêm một cái procedure gọi mấy cái kia. Căn là tránh một cái ôm đồm một đống công việc. Và viết một cái hàm "uyển chuyển" với nhiều options là đường lối xưa (xem định nghĩa overhead bên dưới). Lý do:
- theo quan niệm ai là việc nấy của LTHĐT thì nếu công việc A và B không chia sẻ nhau đa số code thì A nên độc lập với B. Nếu số code chia sẻ xứng đáng gọi là một công việc thì thêm phần tử C để cho A và B cùng gọi C. Code như vậy mới dễ "tái sử dụng". Vì chúng rõ rệt và riêng biệt. Lúc debug có thể bubble từ dưới lên trên. Lúc cần thay đổi thì chỉ cần nhắm đúng cái procedure chứa phần cần thay đổi.
Phần tô tím ở trên cũng sẽ được khuyên đỏ.
Trên nguyên tắc lập trình, những cái thay đổi dữ liệu, nếu có thể được thì nên được đặt riêng với những cái chỉ truy xuất dữ liệu.
Đối với SQL Server procedure, điều này khá quan trọng vì tôi có thể cho users class A quyền Insert nhưng không Update, user class B chỉ truy vấn. Chia ra nhiều procedures giúp tôi kiểm soát điều này dễ dàng. (có nhiều cách đi vonngf để không chia cũng được, nhưng cách kiểm soát rắc rối hơn)
Đối với lập trình thì đó là nhiệm vụ của ByVal và ByRef.
Microsoft có nói rõ là ByVal sẽ tạo overhead, bởi vì chương trình phải copy một phiên bản local của tham, nhét tham vào stack và khi thoát thì lấy trở ra. Vậy thì ByVal được cái lợi gì? Cái lợi rõ rệt nhất của nó là người sử dụng hàm chỉ cần nhìn vào câu khai báo là có thể an tâm rằng biến nạp vào tham này sẽ không bị thay đổi. Nói cách khác, hàm khai báo ByVal HỨA rằng nó sẽ không thay đổi giá trị của biến nạpn vào tham.
Nếu bạn có dùng C++ thì cũng biết C++ đã khổ tâm với Const biết bao nhiêu để bảo vệ cái tính chất "tôi hứa rằng tôi sẽ không biến đổi trị của biến này"
Overhead của hướng dối tượng là kết nối trễ, là bảng ảo (virtual table).
Overhead của hàm là gọi hàm thì phải lập stack, và kết nối.
Overhead của các tham ByVal là phải copy lại thành một biến mới. Nếu biến là một object thì có thể liên hệ nhiều điều khác.
... các overhead khác...
Khi tôi nói overhead của kỹ thuật chia ra nhiều hàm nhỏ là tôi muốn nói cái điểm thứ hai ở trên.
Trên quan điểm của một project manager, câu bôi đỏ trên sẽ được khuyên đỏ.
Tổ chức quản lý code thuộc về project chứ không phải cá nhân. Bởi vì bạn quen dựng project cá nhân cho nên có thể tự mình quản lý.
Trên thực tế, chỉ những developers kỳ cựu mới được leader cho quyền tự phân lấy interfaces của module mình viết ra. Các developers non thì chính thức chỉ là người dịch thuật toán thành code.
Những project lớn, code có peer review. Viết mà không đủ chú thích cho dễ hiểu tụi nó khuyên đỏ tan nát hết.
"Tái sử dụng" là một trong những tínhn chất của LTHĐT. Chính vì cái này mà tôi mới nói chuyện rằng việc nào procedure nấy, nhiều việc thì viết thêm một cái procedure gọi mấy cái kia. Căn là tránh một cái ôm đồm một đống công việc. Và viết một cái hàm "uyển chuyển" với nhiều options là đường lối xưa (xem định nghĩa overhead bên dưới). Lý do:
- theo quan niệm ai là việc nấy của LTHĐT thì nếu công việc A và B không chia sẻ nhau đa số code thì A nên độc lập với B. Nếu số code chia sẻ xứng đáng gọi là một công việc thì thêm phần tử C để cho A và B cùng gọi C. Code như vậy mới dễ "tái sử dụng". Vì chúng rõ rệt và riêng biệt. Lúc debug có thể bubble từ dưới lên trên. Lúc cần thay đổi thì chỉ cần nhắm đúng cái procedure chứa phần cần thay đổi.
Phần tô tím ở trên cũng sẽ được khuyên đỏ.
Trên nguyên tắc lập trình, những cái thay đổi dữ liệu, nếu có thể được thì nên được đặt riêng với những cái chỉ truy xuất dữ liệu.
Đối với SQL Server procedure, điều này khá quan trọng vì tôi có thể cho users class A quyền Insert nhưng không Update, user class B chỉ truy vấn. Chia ra nhiều procedures giúp tôi kiểm soát điều này dễ dàng. (có nhiều cách đi vonngf để không chia cũng được, nhưng cách kiểm soát rắc rối hơn)
Đối với lập trình thì đó là nhiệm vụ của ByVal và ByRef.
Microsoft có nói rõ là ByVal sẽ tạo overhead, bởi vì chương trình phải copy một phiên bản local của tham, nhét tham vào stack và khi thoát thì lấy trở ra. Vậy thì ByVal được cái lợi gì? Cái lợi rõ rệt nhất của nó là người sử dụng hàm chỉ cần nhìn vào câu khai báo là có thể an tâm rằng biến nạp vào tham này sẽ không bị thay đổi. Nói cách khác, hàm khai báo ByVal HỨA rằng nó sẽ không thay đổi giá trị của biến nạpn vào tham.
Nếu bạn có dùng C++ thì cũng biết C++ đã khổ tâm với Const biết bao nhiêu để bảo vệ cái tính chất "tôi hứa rằng tôi sẽ không biến đổi trị của biến này"
Đa phần người biết VBA dùng để phục vụ cho mục đích cá nhân hoặc một nhóm nhỏ, nên em nói góc độ cá nhân cũng không hẳn sai. Cách thiết kế database hiện tại của em mọi thứ vẫn đang ổn (phân quyền,bảo trì,mở rộng tính năng, thời gian trả về kết quả), có thể nó nhỏ, chỉ phục vụ cho khoảng 20 người nên sẽ không thấy được sự khác biệt giữa cách của em và lời khuyên của thầy nên em vẫn giữ quan điểm của mình về cách viết SP như vậy (trong tương lai, khi tính năng được mở rộng ra hơn có thể sẽ phát sinh những tình huống khác gây khó khăn, ảnh hưởng tới việc quản lý hay bảo mật thì em sẽ thay đổi quan điểm của mình và tìm giải pháp khắc phục, em sẽ nhớ tới lời khuyên của thầy), em đang quan tâm tới khái niệm parameter sniffing như bạn quanluu1989 có nói ở trên không biết có ảnh hưởng tới kết quả trả về các bảng khác nhau hay không theo cách viết SP như vậy của em. Về các optional trong VBA hay khai báo biến giá trị NULL trong SP như em đã nói (và đang áp dụng) thì em cảm thấy nó giống phương thức overload trong C# hơn (em không biết C++), VBA không cho tạo 2 tên Sub trùng nhau, nên em có thể đi đường vòng bằng cách thêm các optional này.
Overhead của hướng dối tượng là kết nối trễ, là bảng ảo (virtual table).
Overhead của hàm là gọi hàm thì phải lập stack, và kết nối.
Overhead của các tham ByVal là phải copy lại thành một biến mới. Nếu biến là một object thì có thể liên hệ nhiều điều khác.
... các overhead khác...
Khi tôi nói overhead của kỹ thuật chia ra nhiều hàm nhỏ là tôi muốn nói cái điểm thứ hai ở trên.
Mình nhớ (không chắc chắn) nếu bạn khai báo như vậy thì tùy vào SP đó có parameter hay không, nếu có thì bạn phải set, dưới đây là đoạn code mà mình đã dùng để test thử trước đây:
Nay mình mới biết tới khái niệm sql động, sql tĩnh và parameter sniffing, thường thì mình thấy câu truy vấn đó chậm thì thử thêm hay bỏ WITH RECOMPILE. Anh quanluu1989 cho mình hỏi với SP mình tạo như trên sử dụng điệu kiện if else để tạo các truy vấn trả về các bảng kết quả khác nhau thì có ảnh hưởng tới parameter sniffing không?
parameter sniffing nó quan tâm đến giá trị của param bạn truyền vào thôi chứ nó không liên quan đến if else cả. Bạn tìm hiểu thêm trên mạng nhé. parameter sniffing khó tránh khỏi và người ta khuyên không nên tránh nó mà mình có thể tìm phương án tối ưu với từng trường hợp, nó tùy thuộc vào tập dữ liệu của bạn. with recomplie là 1 cách giải quyết nếu bạn thấy nó tốt hơn, nhưng bạn phải xét đến trường hợp tần suất sử dụng thủ tục của bạn nữa nhé.
Trước khi bạn kia xác định là tốc độ đo bên SQL Server hay bên ADODB thì khó có thể đoán được gì cả.
Khi ADODB và Server 'handshake' qua cái connection thì ta chỉ mới tạo đường dẫn thôi.
Khi Command Execute thì ADO mới qua cái connection ấy, thảy một đống điều kiện cho SS và chúng cũng sẽ đồng ý với nhau về protocols (nghi thức trao đổi). Kế đó SS mới làm việc.
Protocol cho adCmdStoredProc khác với protocol adCmdText.
Vì vậy có cả đống lý do để tốc độ khác nhau. Ví dụ (chỉ ví dụ) bên procedure cache kết quả cho đủ rồi mới bắt đầu truyền trong khi bên text thì lấy đuọc package nào truyền pachage nấy thì rất có thể tốc độ khác nhau tuỳ theo độ rộng và độ ưu tiên của đường truyền.
Nếu bắt buộc phải đoán mò thì tôi đoán tốc độ chạy SP như nhau. Phần khác nhau nằm ở cái protocol mà ADO thoả thuận với SS. Hoặc ở chỗ máy bạn ấy nó trả về kết quả một loạt nếu gọi thẳng SP và trả về từng đợt nếu gọi qua lệnh Exec. Nếu đúng cái này thì admin phải tune nó.
Em nghĩ thầy nói đúng ở vấn đề này, trước em có test (không nói đến nội dung câu truy vấn có tối ưu hay không, cũng như giữa 2 cách thì cách adCmdStoredProc có tốt hơn không) thì thời gian trả về kết quả giữa 2 cách chạy như nhau (Set rs = Cmd.Execute), chỉ khác nhau ở vấn đề set parameter trên code VBA, em không nhớ chính xác, hình như là ở đoạn này:
Đoạn code đó có khoảng 15 dòng khai báo parameter (một số câu lệnh UPDATE lại thông tin, phải khai báo nhiều parameter trong việc SET lại nhiều trường kèm theo các điều kiện có khi cần tới 15 parameter tùy vào số trường cần update). Em thấy thời gian trả về kết quả chậm và debug thử thì nhớ nó chậm ở mấy dòng code đó (nếu chậm ở Set rs = Cmd.Execute thì em đã xem lại nội dung câu SP của mình rồi), nên quyết định không dùng cách này nữa, mà chuyển hẳn sang adCmdText kết hợp các hàm kiểm duyệt biến để chắc chắn câu lệnh trước khi thực thi luôn đúng. Em nghĩ có thể việc refresh lại các biến cũng như khai báo tên biến phải giống với tên biến khai báo trong SP làm mình tốn thêm một khoản thời gian, giống như thầy nói "Phần khác nhau nằm ở cái protocol mà ADO thoả thuận với SS ", đại loại như 1 cái là đoạn text tôi chắc chắn đúng anh cứ chạy đi (cách an toàn của em) và 1 cái là anh hãy refresh và kiểm tra lại tên SP và các biến giúp tôi chắc chắn đúng trước khi chạy (cách an toàn của thầy). Giả sử ở dòng Cmd.Parameters("@option").Value nếu em khai báo biến string ở VBA, nhưng trên SP là biến INT thì sẽ nó sẽ xảy ra lỗi, vậy mỗi lần chạy 1 dòng khai báo thì nó phải tốn thời gian kiểm tra giữa 2 nơi rồi . Em cũng chưa chắc là kiểm tra các kiểu biến khác nhau thì có mất thời gian khác nhau không, cũng như số lượng parameter, đối với các câu truy vấn ít parameter thì thời gian không khác nhau mấy.
Sẵn nói về số lượng parameter thì em thấy cách sử dụng của em vẫn tiện hơn (chưa bàn đến vấn đề an toàn hơn như thầy nói) trong việc tái sử dụng, ví dụ như đoạn code dưới đây:-
JavaScript:
Sub StoredProcedure(ChuoiSP As String)
MoKetNoi
Cmd.ActiveConnection = cn
Cmd.CommandTimeout = 60
Cmd.CommandType = adCmdText
Cmd.CommandText = ChuoiSP
Set rs = Cmd.Execute
End Sub
Tất cả các SP trong database em đều có thể dùng chung bằng cách nap đoạn text vào ChuoiSP và thực thi, trong khi nếu sử dụng adCmdStoredProc thì mình phải set parameter dài dòng, phải set đúng tên biến tạo ra trong SP đó dẫn tới mỗi SP phải tạo ra một Sub riêng biệt dành cho nó. Nếu thầy tách mỗi SP làm đúng một chức năng riêng biệt nữa thì số lượng Sub lại càng nhiều hơn, em chưa áp dụng theo cách của thầy nhưng em nghĩ việc quản lý sẽ mất thời gian hơn.
...
Sẵn nói về số lượng parameter thì em thấy cách sử dụng của em vẫn tiện hơn (chưa bàn đến vấn đề an toàn hơn như thầy nói) trong việc tái sử dụng, ...
Quan niệm về "tái sử dụng" của bạn khác với tôi.
Từ lúc sử dụng HĐT đến giờ. Tái sử dụng đố với tôi là một function hoặc class đã dùng được tốt rồi thì để yên đó mà dùng. Muốn thêm thắt gì thì viết thêm cái wrapper (nếu function thì viết thêm function khác gọi nó, nếu class thì inherit nó ra class rộng hơn). Cái functioon/class cũ luôn luôn để yên đó.
Sub StoredProcedure(ChuoiSP As String)
MoKetNoi
Cmd.ActiveConnection = cn
Cmd.CommandTimeout = 60
Cmd.CommandType = adCmdText
Cmd.CommandText = ChuoiSP
Set rs = Cmd.Execute
End Sub
Tất cả các SP trong database em đều có thể dùng chung bằng cách nap đoạn text vào ChuoiSP và thực thi, trong khi nếu sử dụng adCmdStoredProc thì mình phải set parameter dài dòng, phải set đúng tên biến tạo ra trong SP đó dẫn tới mỗi SP phải tạo ra một Sub riêng biệt dành cho nó. Nếu thầy tách mỗi SP làm đúng một chức năng riêng biệt nữa thì số lượng Sub lại càng nhiều hơn, em chưa áp dụng theo cách của thầy nhưng em nghĩ việc quản lý sẽ mất thời gian hơn.
Tôi đã nói rồi. Gọi SP bằng command là tiêu chuẩn. Gọi bằng text là cách đi vòng. Vấn đề bottleneck/chạy chậm (nếu có) ở ưu tiên xử lý của SS hoặc cách truyèn kết quả là do tính chất kết nói giữa ADO và SS. Thường thì cái bottleneck này có thể được giải quyết bởi một admin giỏi. (tôi chỉ biết đến vậy thôi, admin của Server là ngành ngoài kiến thức của tôi)
Nếu đi vòng mà hiệu quả hơn thì chỉ cần cân nhắc giữa cái lợi hiệu quả và an toàn. Cái nào hơn thì dùng. Chấm hết.
Bạn quen dùng text thì thấy câu lệnh text dễ dùng. Tôi quen nạp params tuần tự thì thấy nạp từng params nó rõ hơn.
Về việc quản lý số hàm thì cũng như người trại chủ đặt một khu trại chung và phân chuồng bên trong thì thấy dễ quản lý. Nhưng trại chủ khác lại thích chia trại mình ra từng khu riêng quản lý dễ hơn. Cái hại là tốn nhiều chỗ, và đi lại nhiều hơn. Cái lợi là nếu có bệnh thì không đến nổi chết cả đàn.
Chú: theo như lời bạn nói thì tôi đoán có bạn cũng dùng C#. Winform, Webform, hay MVC không thành vấn đề; code C# là chỉ viết mấy cái method cho class framework có sẵn hoặc class mới.
C# là loại ngôn ngữ "strong type". Tính chất "strong type" này giúp nó sử dụng hàm chồng (function overloading) dễ dàng.
Theo lý thuyết, loại ngôn ngữ có hàm chồng như C++, Java, C#... trình dịch không dịch thẳng tên hàm ra địa chỉ mà dùng một kỹ thuật name mangling để dịch cái prototype (thực ra là signature) của hàm. Prototype của hàm gồm tên hàm, kiểu trả về, và parameters list. Signature chỉ gồm tên hàm và params list. Name mangling không lý đến kiểu trả về, lý do hiển nhiên.
Trái lại, JavaScript là ngôn ngữ "super type". Vì tính chất này cho nên nó khó viết hàm chồng. Để viết hàm chồng, ngừoi ta dùng một hàm lớn và gọi các hàm con (tìm hiểu về kỹ thuật dùng closure để viết hàm chồng, jQuerry cũng có cách viết hàm chồng của nó). Đó là kỹ thuật tôi đề nghị với bạn ở các bài trước. Tuy nhiên, bạn có vẻ thích viết kiểu một hàm làm đủ thứ.
Kết luận: VBA không phải là ngôn ngữ HĐT. Ép nó theo kiểu hàm chồng là gượng ép nặng tay. Viết một hàm làm đủ thứ không phải là chính sách của tái sử dụng (reusable code). Nguyên tắc chung của tái sử dụng là hàm chỉ làm một công việc, n công việc thì n hàm, và một hàm đã tốt rồi thì để yên nó.
Tôi không nói nhiều về SP của SS vì Microsoft bỏ rất nhiều đầu tư vào SS để cạnh tranh với Oracle. Cho nên nó cũng có tính chất như Excel. Tức là performance của nó không dựa trên lô gic mà dựa trên nhu cầu thị trường.
Sẵn nói về số lượng parameter thì em thấy cách sử dụng của em vẫn tiện hơn (chưa bàn đến vấn đề an toàn hơn như thầy nói) trong việc tái sử dụng, ví dụ như đoạn code dưới đây:-
JavaScript:
Sub StoredProcedure(ChuoiSP As String)
MoKetNoi
Cmd.ActiveConnection = cn
Cmd.CommandTimeout = 60
Cmd.CommandType = adCmdText
Cmd.CommandText = ChuoiSP
Set rs = Cmd.Execute
End Sub
Tất cả các SP trong database em đều có thể dùng chung bằng cách nap đoạn text vào ChuoiSP và thực thi, trong khi nếu sử dụng adCmdStoredProc thì mình phải set parameter dài dòng, phải set đúng tên biến tạo ra trong SP đó dẫn tới mỗi SP phải tạo ra một Sub riêng biệt dành cho nó. Nếu thầy tách mỗi SP làm đúng một chức năng riêng biệt nữa thì số lượng Sub lại càng nhiều hơn, em chưa áp dụng theo cách của thầy nhưng em nghĩ việc quản lý sẽ mất thời gian hơn.
Tôi không biết bạn thiết kế Sub như thế nào chứ như cái hàm dưới đây của tôi là chạy chung cho bất kỳ Stored proc. nào và đưa tham số vô cũng đơn giản. (Thực ra đây là cái hàm trong Class, chuyển qua standard module cũng tương tự)
- Hàm ExecuteSPWithADOCommand(): tôi dùng hàm này chạy chung cho 7 cái stored proc liệt kê trong Listbox trên.
Mã:
Public Function ExecuteSPWithADOCommand(StoredProcName As String, _
ParamArray InputParameters()) As ADODB.Recordset
Dim objCmd As ADODB.Command
Dim rsCmd As ADODB.Recordset
Dim intParam As Integer
Dim value As Variant
'Dim recordsAffected As Long
On Error GoTo ESPError
If ConnectDB() Then
Set objCmd = New ADODB.Command
With objCmd
.ActiveConnection = mobjConn
.CommandText = StoredProcName
.CommandType = adCmdStoredProc
'Lay tham so
For intParam = LBound(InputParameters) To UBound(InputParameters)
value = InputParameters(intParam)
.Parameters.Append getType(value)
Next
Set ExecuteSPWithADOCommand = .Execute
End With
End If
Exit Function
ESPResume:
On Error Resume Next
Set ExecuteSPWithADOCommand = Nothing
Set objCmd = Nothing
CloseDB
Exit Function
ESPError:
ShowErrorMessages Err, mstrModuleName, "ExecuteSPWithADOCommand"
Resume ESPResume
End Function
Private Function getType(ByVal thamso As Variant) As ADODB.Parameter
Dim result As New ADODB.Parameter
Dim direction As ADODB.ParameterDirectionEnum
direction = adParamInput
result.direction = direction
Select Case TypeName(thamso)
Case "String"
result.Type = adVarChar
result.Size = Len(CStr(thamso))
result.value = CStr(thamso)
Case "Integer"
result.Type = adInteger
result.value = CLng(thamso)
Case "Double"
result.Type = adDouble
result.value = CDbl(thamso)
Case "Datetime"
result.Type = adDate
result.value = CDate(thamso)
End Select
Set getType = result
End Function
- Gọi hàm khi thực thi: 7 cái stored proc riêng biêt.
Mã:
...
Select Case Me.lstProcs.value
Case 1, 3
Set rs = gobjDB_sql.ExecuteSPWithADOCommand("" & lstProcs.Column(1) & "", "" & Me.cboCustID & "")
Case 2
Set rs = gobjDB_sql.ExecuteSPWithADOCommand("" & lstProcs.Column(1) & "", "" & Me.cboOrderID & "")
Case 4, 5
Set rs = gobjDB_sql.ExecuteSPWithADOCommand("" & lstProcs.Column(1) & "", Me.txtStart, Me.txtEnd)
Case 6
Set rs = gobjDB_sql.ExecuteSPWithADOCommand("" & lstProcs.Column(1) & "", Me.cboCatID, Year(Me.txtStart))
Case 7
Set rs = gobjDB_sql.ExecuteSPWithADOCommand("" & lstProcs.Column(1) & "")
End Select
If Nz(rs.RecordCount, 0) = 0 Then
MsgBoxUni "Không tìm th" & ChrW(7845) & "y d" & ChrW(7919) & " li" & ChrW(7879) & "u..."
Exit Sub
End If
PopulateListBoxADORs Me.lstResults, rs
...
Tôi không biết bạn thiết kế Sub như thế nào chứ như cái hàm dưới đây của tôi là chạy chung cho bất kỳ Stored proc. nào và đưa tham số vô cũng đơn giản. (Thực ra đây là cái hàm trong Class, chuyển qua standard module cũng tương tự)
Hai function của bạn hay quá, mình không biết sử dụng .Parameters.Append getType(value) và Cmd.Parameters("@option").Value có khác nhau không, nhưng cách của bạn thì không cần phải khai báo biến trên SP, rãnh mình sẽ dùng thử. Cách của mình cũng tương tự như bạn, Function ExecuteSPWithADOCommand của bạn là Sub StoredProcedure của mình, Function getType của bạn thì như mình đã nói ở trên là mình tạo ra các hàm kiểm duyệt biến trước khí chuyển sang ChuoiSP và nạp vào Sub của mình.
Hai function của bạn hay quá, mình không biết sử dụng .Parameters.Append getType(value) và Cmd.Parameters("@option").Value có khác nhau không, nhưng cách của bạn thì không cần phải khai báo biến trên SP, rãnh mình sẽ dùng thử. Cách của mình cũng tương tự như bạn, Function ExecuteSPWithADOCommand của bạn là Sub StoredProcedure của mình, Function getType của bạn thì như mình đã nói ở trên là mình tạo ra các hàm kiểm duyệt biến trước khí chuyển sang ChuoiSP và nạp vào Sub của mình.
Mình vẫn chưa hiểu ý bạn. Bạn giải thích rõ hơn 2 kiểu nạp biến .Parameters.Append getType(value) và Cmd.Parameters("@option").Value khác nhau như thế nào được không?
Mình đã nghiên cứu rồi, cách truyền tham số của mình buộc phải khai báo biến trên SP ( hoặc là mình không biết 1 cách khai báo đơn giản hơn như bạn nói), nên mình đã tranh luận với thầy VetMini nếu viết kiểu đó thì dài dòng, nhưng cách của bạn ongke0711 thì lại cho mình biết thêm 1 cách mới không cần phải khai báo tên biến trên SP và tái sử dụng được. Nếu bạn biết cách đơn giản thì có thể chia sẻ cho mình với mợi người cùng biết.
Mình đã nghiên cứu rồi, cách truyền tham số của mình buộc phải khai báo biến trên SP ( hoặc là mình không biết 1 cách khai báo đơn giản hơn như bạn nói), nên mình đã tranh luận với thầy VetMini nếu viết kiểu đó thì dài dòng, nhưng cách của bạn ongke0711 thì lại cho mình biết thêm 1 cách mới không cần phải khai báo tên biến trên SP và tái sử dụng được. Nếu bạn biết cách đơn giản thì có thể chia sẻ cho mình với mợi người cùng biết.
Nếu đi vòng mà hiệu quả hơn thì chỉ cần cân nhắc giữa cái lợi hiệu quả và an toàn. Cái nào hơn thì dùng. Chấm hết.
Bạn quen dùng text thì thấy câu lệnh text dễ dùng. Tôi quen nạp params tuần tự thì thấy nạp từng params nó rõ hơn.
Trái lại, JavaScript là ngôn ngữ "super type". Vì tính chất này cho nên nó khó viết hàm chồng. Để viết hàm chồng, ngừoi ta dùng một hàm lớn và gọi các hàm con (tìm hiểu về kỹ thuật dùng closure để viết hàm chồng, jQuerry cũng có cách viết hàm chồng của nó). Đó là kỹ thuật tôi đề nghị với bạn ở các bài trước. Tuy nhiên, bạn có vẻ thích viết kiểu một hàm làm đủ thứ.
Em đồng ý với thầy việc cân nhắc giữa cái lợi hiệu quả và an toàn, cái nào hơn thì dùng.
Đúng là em thích viết kiểu một hàm làm đủ thứ, nhưng em cảm thấy ko có lạm dụng optional như thầy đang nói, vì khi viết code em luôn cố gắng chia code nhỏ nhất ra có thể để dễ quản lý, dễ debug. Em chưa biết JavaScript, em sẽ tìm hiểu về các kỹ thuật closure như thầy nói để thử vận dụng vào VBA để xem coi nó ưu điểm hơn so với kiểu nạp chồng hiện tại không. Em cảm ơn thầy.
Hai function của bạn hay quá, mình không biết sử dụng .Parameters.Append getType(value) và Cmd.Parameters("@option").Value có khác nhau không, nhưng cách của bạn thì không cần phải khai báo biến trên SP, rãnh mình sẽ dùng thử. Cách của mình cũng tương tự như bạn, Function ExecuteSPWithADOCommand của bạn là Sub StoredProcedure của mình, Function getType của bạn thì như mình đã nói ở trên là mình tạo ra các hàm kiểm duyệt biến trước khí chuyển sang ChuoiSP và nạp vào Sub của mình.
Tôi nghĩ bạn đọc code sai ở đoạn này rồi hoặc do tôi chưa hiểu cách dùng từ của bạn.
- Cái hàm getType() là tôi tạo tham số (parameter) để đưa vào tập hợp các tham số (Parameters). Có thể tôi đặt tên chưa sát tính năng của nó, nó không có kiểm duyệt biến gì cả. Vì tôi đã biết các tham số thiết kế trong SP là kiểu gì rồi nên tôi khai báo tường mình luôn các tham số này (size, type, value) và Append vào Parameter Collection. Sau đó chỉ viêc thi hành (Execute).
- Tôi dùng objCmd.Parameters.Append getType(value) nó khác so với Cmd.Parameters.Item("@option").Value. Tôi tạo tập hợp tham số (Parameter collection) và tạo tham số và nạp giá trị của nó vô luôn. Còn câu lệnh của bạn là đã có tập hợp tham số rồi, giờ dùng phương thức .Item("tên tham số") để gán giá trị cho nó. Tôi nghĩ có thể đâu đây là nguyên nhân khiến bạn cảm thấy chậm khi bạn nói khai báo các parameter.
Đối tượng Command của ADO có một phương thức là "Refresh". Khi bạn tương tác với Stored Proc, nếu bạn không chủ động tạo parameter collection như cách tôi đang làm (createParameter, append) thì dùng Command.Refresh. Khi "Refresh" ADO sẽ tự động liên lạc provider SQL SV lấy thông tin tất cả các tham số của Stored Proc mà bạn đang gọi tự tạo collection cho bạn, khi đó bjan chỉ cần dùng câu lệnh của bạn để gán trị cho tham số. Chính cái việc "Refresh" này làm chậm tiến trình xử lý Stored proc đáng kể.
Nói về cái SP của bạn, bạn nghĩ SP nôm na như một cuốn sách và các câu lệnh "Select" trong nó là 1 trang của cuốn sách. Vậy khi bạn gọi SP giống như mở cuốn sách, số trang ít thì cuốn sách nó nhẹ và tìm trang theo mục lục cũng nhanh hơn một cuốn Bách khoa toàn thư. Bạn cũng biết là khi load stored proc thì SQL SV cũng phải cấp phát bộ nhớ cho nó mà, nó nặng nề thì chiếm nhiều bộ nhớ cache thôi.
Tôi nghĩ bạn đọc code sai ở đoạn này rồi hoặc do tôi chưa hiểu cách dùng từ của bạn.
- Cái hàm getType() là tôi tạo tham số (parameter) để đưa vào tập hợp các tham số (Parameters). Có thể tôi đặt tên chưa sát tính năng của nó, nó không có kiểm duyệt biến gì cả.
Đoạn Select Case TypeName(thamso) của bạn sử dụng các hàm CStr,CLng,CDbl,CDate trong result.value, ý mình là ở đoạn này, bạn đưa các giá trị nó về tương ứng với các KIỂU biến trên SP, cũng giống như nạp text của mình, mình sử dụng các function để đưa biến về ĐỊNH DẠNG biến trên SQL server và kiểm duyệt một số thao tác nhập liệu từ người dùng, ví dụ như:
Function BienNvarchar(ByVal Str As String) As String
BienNvarchar = WorksheetFunction.Trim(Replace(Str, "'", "''"))
End Function
Hàm này phòng các trường hợp nhập liệu từ người dùng, nếu bạn đưa cho nhiều người sử dụng, mình nghĩ chắc bạn cũng gặp trường hợp có một số người dùng thường ấn spacebar ở từ cuối, thỉnh thoảng cũng có phía trước nữa. Cái nữa là một số trường hợp khi nhập biến Nvarchar, có một số trường hợp sử dụng dấu ' như "Cat's tongue cookies", nó sẽ chuyển về thành "Cat''s tongue cookies" để SP hiểu.
Function BienDate(ByVal Ngay As Date)
BienDate = Format(Ngay, "yyyy-mm-dd")
End Function
Hàm này đưa Ngày về đúng định dạng trên SQL server, vì định dạng ngày tháng trong VBA khác với định dạng ngày tháng trong SS
Function BienChar(ByVal Str As String) As String
BienChar = RemoveSpecial(ConvertToUnSign(Str))
End Function
Hàm này mình lượm lặt 2 hàm RemoveSpecial,ConvertToUnSign trên GPE, RemoveSpecial sử dụng Regex để xóa bỏ các ký tự ngoài ký tự và chữ và số, ConvertToUnSign sẽ đưa các ký tự có dấu về không dấu, mình đã gặp trường hợp người dùng vô tình tạo ra chuỗi ký tự có dấu, kiểu gõ telex sẽ tạo ra những ký tự có dấu mà người dùng vô tình không để ý.
Ngoài việc sử dụng các hàm này để kiểm duyệt trong code, thì mình cũng dùng Data Validation trên Excel để kiểm duyệt thao tác nhập liệu của người dùng về đúng các định dạng yêu cầu, cũng như hạn chế xài các textbox mà ưu tiên dùng dùng các control để chọn lựa trong Userform để giảm thiểu thao tác nhập liệu của người dùng. Cho đến hiện tại thì gần như mình không còn gặp trường hợp phát sinh lỗi (vô tình) từ việc thao tác nhập liệu của người dùng nữa, nếu sau này gặp phát sinh thì mình chỉ cần bổ sung điều kiện kiểm duyệt trong các hàm này thôi.
Đối tượng Command của ADO có một phương thức là "Refresh". Khi bạn tương tác với Stored Proc, nếu bạn không chủ động tạo parameter collection như cách tôi đang làm (createParameter, append) thì dùng Command.Refresh. Khi "Refresh" ADO sẽ tự động liên lạc provider SQL SV lấy thông tin tất cả các tham số của Stored Proc mà bạn đang gọi tự tạo collection cho bạn, khi đó bjan chỉ cần dùng câu lệnh của bạn để gán trị cho tham số. Chính cái việc "Refresh" này làm chậm tiến trình xử lý Stored proc đáng kể.
Mình nghĩ bạn nói đúng, lúc tranh luận với thầy VetMini mình cũng có nói ở dòng .refresh này, việc refresh rồi set nhiều parameter có thể làm nó chậm hơn.
Nói về cái SP của bạn, bạn nghĩ SP nôm na như một cuốn sách và các câu lệnh "Select" trong nó là 1 trang của cuốn sách. Vậy khi bạn gọi SP giống như mở cuốn sách, số trang ít thì cuốn sách nó nhẹ và tìm trang theo mục lục cũng nhanh hơn một cuốn Bách khoa toàn thư. Bạn cũng biết là khi load stored proc thì SQL SV cũng phải cấp phát bộ nhớ cho nó mà, nó nặng nề thì chiếm nhiều bộ nhớ cache thôi.
Mình chưa đi sâu vào quản trị database nên không rành về cache lắm, nhưng mình nhớ là mình đọc đâu đó trên mạng có nói, khi mình chạy 1 câu SP, nó sẽ lưu vào bộ nhớ cache, sau một thời gian nếu không sử dung SP đó nữa bộ nhớ cache sẽ xóa nó đi, vậy ở cách viết SP của mình, nó luôn được sử dụng thì mình nghĩ khuyết điểm của nó là chiếm bộ nhớ cache cao, nhưng ưu điểm của nó là luôn được sử dụng nên sẽ ít có sự lãng phí, nếu chia SP ra nhiều câu truy vấn riêng lẽ, có những câu bạn không truy vấn thường xuyên ( như các truy vấn để xem báo cáo thì tần suất xem có khi 1 tháng 1 lần) thì vô tình lãng phí bộ nhớ cache 1 thời gian cho tới khi nó tự xóa đi. Nhưng đó là suy nghĩ của mình, có thể mình sai.
