Bài này mình giới thiệu thuật toán trang bằng SQL với những tùy chọn hiển thị khác nhau.

Ở bài viết Phân trang trong SQL mình đã giới thiệu cách dùng Store để phân trang, Ở bài này mình sẽ giới thiệu thuật toán phân trang tối ưu hơn.
Giả sử bạn muốn trang web của bạn phân trang theo cách hiển thị như sau
Trang đầu Trang trước 2 3 [4] 5 6 Trang sau Trang cuối (Với số trang hiển thị =5 và đang hiển thị nội dung trang thứ 4, và mỗi trang 10 bản ghi)

1. Store trả về chuỗi hiển thị phân trang.
 Trước tiên bạn cần viết 1 hàm trả về chuỗi hiển thị phân trang. Hàm này có các biến truyền vào là:
@Total=Tổng số bản ghi truy vấn
@currPage=Trang hiện hàng
@PageSize=Số trang muốn hiển thị
@rowperpage = Số bản ghi trên 1 trang

Kết quả trả về của store này là chuỗi (html) hiển thị phân trang có dạng như sau:

EXEC [spPhanTrangSQL] 56,4,5, 10 (Demo với @Total=56, @currPage=4, @Pagesize=5, @rowperpage=10)

<a href="?page=1">Trang đầu</a> 
<a href="?page=3">Trang trước</a>
<a href="?page=2">2</a> 
<a href="?page=3">3</a> 
[4] 
<a href="?page=5">5</a> 
<a href="?page=6">6</a> 
<a href="?page=5">Trang sau</a> 
<a href="?page=6">Trang cuối</a>

 Store được viết như sau:

CREATE PROCEDURE [dbo].[spPhanTrangSQL]
@Total int,
@currPage int ,
@PageSize int,
@rowperpage int  
AS
BEGIN
DECLARE  @PageNumber int SET @PageNumber=1
DECLARE @i int
SET @i=1
DECLARE @TotalPage int
IF @Total%@rowperpage>0
SET @TotalPage=(@Total/@rowperpage)+1
ELSE
SET @TotalPage=@Total/@rowperpage 
DECLARE @Start int SET @Start=0
DECLARE @SQL nvarchar(4000)
SET @SQL=''
IF @currPage<=@TotalPage
BEGIN
    -- Xử lý trường hợp @currPage=1
    IF @currPage=1
    BEGIN
        SET @SQL=@SQL+ N'Trang '
        SET @PageNumber=@PageSize
        IF @PageNumber>@TotalPage SET @PageNumber=@TotalPage
        SET @Start=1
    END
    ELSE
    BEGIN
        SET @SQL=@SQL+ N' <a href="?page=1">Trang đầu</a>'
        SET @SQL=@SQL+ ' <a href="?page='+ 
            Cast((@currPage-1) AS nvarchar(4))+N'">Trang trước</a>'
        -- Xử lý trường hợp (@TotalPage-@currPage)<@PageSize/2
        IF(@TotalPage-@currPage)<@PageSize/2
           BEGIN
              SET @Start=(@TotalPage-@PageSize)+1
              IF @Start<0 SET @Start=1 
              SET @PageNumber = @TotalPage
           END
        ELSE
        BEGIN
            IF (@currPage-(@PageSize/2))=0
            BEGIN
                SET @Start=1
                SET @PageNumber=@currPage+(@PageSize/2)+1
                IF @TotalPage<@PageNumber
                    SET @PageNumber=@TotalPage
            END
            ELSE
               BEGIN
                  SET @Start=@currPage-(@PageSize/2)
                  IF @Start<0 SET @Start=1 
                  SET @PageNumber=@currPage+(@PageSize/2)
                  IF @TotalPage<@PageNumber
                      SET @PageNumber=@TotalPage
                  ELSE
                  IF @PageNumber <@PageSize 
                      SET @PageNumber=@PageSize
               END
        END
     END    

    SET @i=@Start
    WHILE @i<=@PageNumber
    BEGIN
        IF @i=@currPage
            SET @SQL=@SQL+'
             ['+Cast(Cast(@i AS int) AS nvarchar(4))+'] '
        ELSE
            SET @SQL=@SQL+'
             <a href="?page='+Cast(@i AS nvarchar(4))+'">'
                +Cast(@i AS nvarchar(4))+'</a> '
        SET @i=@i+1 
    END
    IF @currPage<@TotalPage
    BEGIN
        SET @SQL=@SQL+ N'
         <a href="?page='+Cast((@currPage+1) 
            AS nvarchar(4))+N'">Trang sau</a>'
         SET @SQL=@SQL+ N' 
             <a href="?page='+cast(@TotalPage AS nvarchar(6))+
              N'">Trang cuối</a>'
    END
    SELECT @SQL AS PhanTrang    
    -- PRINT @SQL
END
END

2. Store phân trang

Tiếp theo chúng ta sẻ dùng thủ tục trên vào việc phân trang dữ liệu. Thủ tục này  có các biến truyền vào:
@currpage=Trang hiện hành
@recodeperpage=Số bản ghi trên mỗi trang

@Pagesize=Số trang hiển thị phân trang
Thủ tục sẽ trả về dữ liệu là 2 bảng (Dùng dataset).

--CREATE BY webmaster@hmweb.com.vn
CREATE PROCEDURE [dbo].[spTB_TableName_PhanTrang]
@currPage int,
@recodperpage int,
@Pagesize int
AS
Begin
    Begin
    WITH s AS
    (
        SELECT ROW_NUMBER() 
            OVER(ORDER BY MaTruong,TenTruong) AS RowNum, 
            MaTruong, TenTruong
        FROM dbo.TB_TableName  
    )
    Select * From s 
    Where RowNum Between 
        (@currPage - 1)*@recodperpage+1 
            AND @currPage*@recodperpage
    END
    -- Tính tổng số bản ghi
    DECLARE @Tolal int
    SELECT @Tolal=Count(*) FROM TB_TableName

    EXEC spPhanTrangSQL @Tolal, @currPage,@Pagesize, 10
End

Sử dụng Store này bạn cần bind dữ liệu và Fill vào 1 DataSet, Sau đó thực hiện hiển thị dữ liệu phân trang. Bạn đọc bài viết Kỹ thuật phân trang bằng Store trong asp.net sẽ hướng dẫn bạn cách sử dụng Store trên

Bài này mình viết hàm chuyển một chuỗi ký tự có dấu (Unicode), chuyển thành chuỗi không dấu

Ứng dụng của hàm này bạn có thể tạo thêm một trường trong table của TB_BaiViet, để khi cần tạo liên kết  dạng http://blog.thuvienit.com/post/2010/11/19/Tao-mot-ham-ma-hoa-voi-T-SQL.aspx

CREATE FUNCTION [dbo].[fuChuyenCoDauThanhKhongDau]
(
      @strInput NVARCHAR(4000)
)
RETURNS NVARCHAR(4000)
AS
BEGIN    
    IF @strInput IS NULL RETURN @strInput
    IF @strInput = '' RETURN @strInput
    DECLARE @RT NVARCHAR(4000)
    DECLARE @SIGN_CHARS NCHAR(136)
    DECLARE @UNSIGN_CHARS NCHAR (136)
    SET @SIGN_CHARS = N'ăâđêôơưàảãạáằẳẵặắầẩẫậấèẻẽẹéềểễệế
                  ìỉĩịíòỏõọóồổỗộốờởỡợớùủũụúừửữựứỳỷỹỵý
                  ĂÂĐÊÔƠƯÀẢÃẠÁẰẲẴẶẮẦẨẪẬẤÈẺẼẸÉỀỂỄỆẾÌỈĨỊÍ
                  ÒỎÕỌÓỒỔỖỘỐỜỞỠỢỚÙỦŨỤÚỪỬỮỰỨỲỶỸỴÝ'
                  +NCHAR(272)+ NCHAR(208)
    SET @UNSIGN_CHARS = N'aadeoouaaaaaaaaaaaaaaaeeeeeeeeee
                  iiiiiooooooooooooooouuuuuuuuuuyyyyy
                  AADEOOUAAAAAAAAAAAAAAAEEEEEEEEEEIIIII
                  OOOOOOOOOOOOOOOUUUUUUUUUUYYYYYDD'
    DECLARE @COUNTER int
    DECLARE @COUNTER1 int
    SET @COUNTER = 1
    WHILE (@COUNTER <=LEN(@strInput))
    BEGIN  
      SET @COUNTER1 = 1
      --Tìm trong chuỗi mẫu
       WHILE (@COUNTER1 <=LEN(@SIGN_CHARS)+1)
       BEGIN
     IF UNICODE(SUBSTRING(@SIGN_CHARS, @COUNTER1,1))
            = UNICODE(SUBSTRING(@strInput,@COUNTER ,1) )
     BEGIN          
          IF @COUNTER=1
              SET @strInput = SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1)
              + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)-1)  
          ELSE
              SET @strInput = SUBSTRING(@strInput, 1, @COUNTER-1)
              +SUBSTRING(@UNSIGN_CHARS, @COUNTER1,1)
              + SUBSTRING(@strInput, @COUNTER+1,LEN(@strInput)- @COUNTER)
              BREAK
               END
             SET @COUNTER1 = @COUNTER1 +1
       END
      --Tìm tiếp
       SET @COUNTER = @COUNTER +1
    END
    SET @strInput = replace(@strInput,' ','-')
    RETURN @strInput
END

Ví dụ:

SELECT dbo.fuChuyenCoDauThanhKhongDau (N'Tạo một hàm mã hoá với T-SQL')

 --Sẽ trả về kết quả là

Tao-mot-ham-ma-hoa-voi-T-SQL

Trong bài này chúng ta sẽ tìm hiểu ứng dụng của một loại stored procedure đặc biệt gọi là Triggers và dùng Views để thể hiện data trong một hay nhiều table như thế nào.

Triggers

Trigger là một loại stored procedure đặc biệt được execute (thực thi) một cách tự động khi có một data modification event xảy ra như Update, Insert hay Delete. Trigger được dùng để đảm bảo Data Integrity hay thực hiện các business rules nào đó.

 Khi nào ta cần sử dụng Trigger:

• Ta chỉ sử dụng trigger khi mà các biện pháp bảo đảm data intergrity khác như Constraints không thể thỏa mãn yêu cầu của ứng dụng. Nên nhớ Constraint thuộc loại Declarative Data Integrity cho nên sẽ kiểm tra data trước khi cho phép nhập vào table trong khi Trigger thuộc loại Procedural Data Integrity nên việc insert, update, delete đã xảy ra rồi mới kích hoạt trigger. Chính vì vậy mà ta cần cân nhắc trước khi quyết định dùng loại nào trong việc đảm bảo Data Integrity.
• Khi một database được denormalized (ngược lại quá trình normalization, là một quá trình thiết kế database schema sao cho database chứa data không thừa không thiếu) sẽ có một số data thừa (redundant ) được chứa trong nhiều tables. Nghĩa là sẽ có một số data được chứa cùng một lúc ở hai hay nhiều nơi khác nhau. Khi đó để đảm bảo tính chính xác thì khi data được update ở một table này thì cũng phải được update một cách tự động ở các table còn lại bằng cách dùng Trigger.

      Ví dụ: ta có table Item trong đó có field Barcode dùng để xác định một mặt hàng nào đó. Item table có vai trò như một cuốn catalog chứa những thông tin cần thiết mô tả từng mặt hàng. Ta có một table khác là Stock dùng để phản ánh món hàng có thực trong kho như được nhập về này nào được cung cấp bởi đại lý nào, số lượng bao nhiêu (tức là những thông tin về món hàng mà không thể chứa trong Item table được)...table này cũng có field Barcode để xác định món hàng trong kho. Như vậy thông tin về Barcode được chứa ở hai nơi khác nhau do đó ta cần dùng trigger để đảm bảo là Barcode ở hai nơi luôn được synchonize (đồng bộ).

• Ðôi khi ta có nhu cầu thay đổi dây chuyền (cascade) ta có thể dùng Trigger để bảo đảm chuyện đó. Nghĩa là khi có sự thay đổi nào đó ở table này thì một số table khác cũng được thay đổi theo để đảm bảo tính chính xác. Ví dụ như khi một món hàng được bán đi thì số lượng hàng trong table Item giảm đi một món đồng thời tổng số hàng trong kho (Stock table) cũng phải giảm theo một cách tự động. Như vậy ta có thể tạo một trigger trên Item table để mỗi khi một món được bán đi thì trigger sẽ được kích hoạt và giảm tổng số hàng trong Stock table.

 Ðặc điểm của Trigger:

• Một trigger có thể làm nhiều công việc (actions) khác nhau và có thể được kích hoạt bởi nhiều hơn một event. Ví dụ ta có thể viết một trigger được kích hoạt bởi bất kỳ event nào như Update, Insert hay Delete và bên trong trigger ta sẽ viết code để giải quyết cho từng trường hợp.      
• Trigger không thể được tạo ra trên temporary hay system table.      
• Trigger chỉ có thể được kích hoạt một cách tự động bởi một trong các event Insert, Update, Delete mà không thể chạy manually được.      
• Có thể áp dụng trigger cho View.      
• Khi một trigger được kích hoạt thì data mới vừa được insert hay mới vừa được thay đổi sẽ được chứa trong Inserted table còn data mới vừa được delete được chứa trong Deleted table. Ðây là 2 table tạm chỉ chứa trên memory và chỉ có giá trị bên trong trigger mà thôi (nghĩa là chỉ nhìn thấy và được query trong trigger mà thôi). Ta có thể dùng thông tin trong 2 table này để so sánh data cũ và mới hoặc kiểm tra xem data mới vừa thay đổi có hợp lệ trước khi commit hay roll back. (Xem thêm ví dụ bên dưới).      
• Có 2 loại triggers (class) : INSTEAD OF và AFTER. Loại INSTEAD OF sẽ bỏ qua (bybass) action đã kích hoạt trigger mà thay vào đó sẽ thực hiện các dòng lệnh SQL bên trong Trigger. Ví dụ ta có một Update trigger trên một table với câu INSTEAD OF thì khi table được update thay vì update SQL Server sẽ thực hiện các lệnh đã được viết sẵn bên trong trigger. Ngược lại loại AFTER (loại default tương đương với keyword FOR) sẽ thực hiện các câu lệnh bên trong trigger sau khi các action tạo nên trigger đã xảy ra rồi.

Tạo Một Trigger Như Thế Nào?

Cú pháp căn bản để tạo ra một trigger có dạng như sau:

CREATE TRIGGER trigger_name
ON table_name or view_name
FOR trigger_class and trigger_type(s)
AS Transact-SQL statements

Như vậy khi tạo ra một trigger ta phải chỉ rõ là tạo ra trigger trên table nào và được trigger khi nào (insert, update hay delete. Sau chữ AS là các câu lệnh SQL xử lý công việc.

Ta hãy nghiên cứu một ứng dụng thực tiễn sau. Giả sử ta viết một application cho phép user có thể Insert, Update và Delete những thông tin nằm trong database. User này thường là những người không thông thạo lắm về computer mà chúng tôi thường gọi đùa là "bà tám". Vào một ngày đẹp trời, "bà tám" mặt mày tái xanh đến cầu cứu ta vì đã lỡ tay "delete" những thông tin khá quan trọng và hy vọng ta có thể phục hồi dữ liệu dùm. Nếu chúng ta không phòng xa trước khi viết application thì coi như cũng vô phương cứu chữa vì data đã hoàn toàn bị delete.

Nhưng nếu bạn là một "guru" bạn sẽ gật gù "chuyện này khó lắm!" nhưng sau đó bạn chỉ tốn vài phút đồng hồ để rollback. Muốn làm được chuyện này chúng ta phải dùng một "chiêu" gọi là Audit (kiểm tra hay giám sát). Tức là ngoài các table chính ta sẽ thêm các table phụ gọi là Audit tables. Bất kỳ hoạt động nào đụng chạm vào một số table quan trọng trong database ta đều ghi nhận vào trong Audit table. Ví dụ khi user update hay delete một record trong table nào đó thì trước khi update hay delete ta sẽ âm thầm di chuyển record đó sang Audit table rồi mới update hay delete table chính. Như vậy nếu có chuyện gì xảy ra ta có thể dễ dàng rollback (trả record về chỗ cũ).

Ví dụ: 

Ta có table Orders trong PracticeDB. Ðể audit các hoạt động diễn ra trên table này ta tạo ra một audit table với tên Aud_Orders với các column giống y hệt với Orders table. Ngoài ra ta thêm vào 2 columns:

Audit_Type : với các giá trị có thể là 'I','U','D' để ghi nhận record được Insert, Update hay Delete

Date_Time_Stamp : Data Type thuộc loại DateTime dùng để ghi nhận thời điểm xảy ra sự thay đổi, có vai trò như một con dấu.

 (Nếu trong môi trường nhiều user thì ta thêm một column UserID để ghi nhận user nào thay đổi).

 Sau đó ta sẽ tạo ra 3 trigger dùng cho việc audit  như sau:

--Insert Trigger
CREATE TRIGGER [AuditInsertOrders]
ON [dbo].[Orders]
FOR Insert
AS
insert into aud_orders select *,'I',getdate() From inserted

--Update Trigger
CREATE TRIGGER [AuditUpdateOrders]
ON [dbo].[Orders]
for UPDATE
AS
insert into aud_orders select *,'U',Getdate() from deleted

--Delete Trigger
CREATE TRIGGER [AuditDeleteOrders]
ON [dbo].[Orders]
FOR DELETE
AS
insert into aud_orders select *,'D',getdate() From deleted

Trong ví dụ trên khi user insert một record thì record mới vừa được insert sẽ nằm trong inserted table như đã trình bày ở phần trên. Do đó ta sẽ select tất cả các column trong inserted table cộng thêm Audit Type "I" và dùng hàm GetDate() trong SQL Server để lấy system date time dùng cho Date_Time_Stamp column, sau đó insert vào Aud_Orders table. Tương tự với trường hợp Update và Delete, record đã được update hay delete nằm trong deleted table.

Như vậy trở lại trường hợp thí dụ ở trên nếu "bà tám" yêu cầu ta có thể vào tìm kiếm trong audit table để phục hồi lại record. Ngoài ra ta có thể dùng table này để tìm ra thủ phạm đã xoá hay sửa chữa data khi cần thiết.

Ðể tạo ra hay xem một trigger bằng Enterprise Manager bạn làm như sau: Right-Click lên table mà bạn muốn tạo trigger->All Tasks-> Manage Triggers.

Lưu ý: Ðôi Khi ta chỉ muốn trigger thực sự hoạt động khi một hay vài column nào đó được Update chứ không phải bất kỳ column nào. Khi đó ta có thể dùng hàm Update(Column_Name) để kiểm tra xem column nào đó có bị update hay không.

Ví dụ:

Tạo một trigger cho Customer table. Bên trong Trigger (sau chữ AS) ta có thể kiểm tra xem nếu column First_Name hay Last_Name bị thay đổi thì mới hành động nếu không thì không làm gì cả

IF UPDATE (first_name) OR UPDATE (Last_Name)
BEGIN
    Do some conditional processing
    when either of these columns are updated.
END

Nếu muốn kiểm tra nhiều columns ta có thể dùng hàm khác là Columns_Updated() . Xin xem thêm trong SQL Server Books Online để biết thêm chi tiết về cách sử dụng.

Views

Ðịnh nghĩa một cách đơn giản thì view trong SQL Server tương tự như Query trong Access database. View có thể được xem như một table ảo mà data của nó được select từ một stored query. Ðối với programmer thì view không khác chi so với table và có thể đặt ở vị trí của table trong các câu lệnh SQL. Ðặc điểm của View là ta có thể join data từ nhiều table và trả về một recordset đơn. Ngoài ra ta có thể "xào nấu" data (manipulate data) trước khi trả về cho user bằng cách dùng một số logic checking như (if, case...).

Ví dụ:

Create View OrderReport
As   
Select OrderID,
       (case when [Name] is null then 'New Customer'
    else [Name]
    end )As CustomerName,
    ProductName,
    DateProcessed
From Customers Right Outer Join Orders on
    Customers.CustomerID=Orders.CustomerID

 Trong ví dụ trên ta chủ yếu trả về data từ Orders table trong PracticeDB nhưng thay vì display CustomerID vốn không có ý nhiều ý nghĩa đối với user ta sẽ display tên của customer bằng cách join với Customer table. Nếu Customer Name là Null nghĩa là tên của customer đã đặt order không tồn tại trong system. Thay vì để Null ta sẽ display "New Customer" để dễ nhìn hơn cho user.

Nói chung câu lệnh SQL trong View có thể từ rất đơn giản như select toàn bộ data từ một table cho đến rất phức tạp với nhiều tính năng programming của T-SQL.

View Thường Ðược Dùng Vào Việc Gì?

View thường được sử dùng vào một số công việc sau:

• Tập trung vào một số data nhất định : ta thường dùng view để select một số data mà user quan tâm hay chịu trách nhiệm và loại bỏ những data không cần thiết.

    Ví dụ: Giả sử trong table ta có column "Deleted" với giá trị là True hay False để đánh dấu một record bị delete hay không. Việc này đôi khi được dùng cho việc Audit. Nghĩa là trong một ứng dụng nào đó khi user delete một record nào đó, thay vì ta physically delete record ta chỉ logically delete bằng cách đánh dấu record là đã được "Deleted" để đề phòng user yêu cầu roll back. Như vậy chủ yếu ta chỉ quan tâm đến data chưa delete còn data đã được đánh dấu deleted chỉ được để ý khi nào cần roll back hay audit mà thôi. Trong trường hợp này ta có thể tạo ra một view select data mà Deleted=False và làm việc chủ yếu trên view thay vì toàn bộ table.

• Ðơn giản hóa việc xử lý data: Ðôi khi ta có những query rất phức tạp và sử dụng thường xuyên ta có thể chuyển nó thành View và đối xử nó như một table, như vậy sẽ làm cho việc xử lý data dễ dàng hơn.      
• Customize data: Ta có thể dùng view để làm cho users thấy data từ những góc độ khác nhau mặc dù họ đang dùng một nguồn data giống nhau. Ví dụ: Ta có thể tạo ra views trong đó những thông tin về customer được thể hiện khác nhau tùy login ID là normal user hay manager.      
• Export và Import data: Ðôi khi ta muốn export data từ SQL Server sang các ứng dụng khác như Excel chẳng hạn ta có thể dùng view để join nhiều table và export dùng bcp.

Khi sử dụng view ta có thể select,insert, update, delete data bình thường như với một table.

Ví dụ:

Select * From OrderReport
Where DateProcessed <'2003-01-01'

Trong SQL hỗ trợ lập trình với T-SQL, bạn có thể tạo các hàm xử lý chuổi hoặc những vấn đề (đa số liên quan đến chuổi).

Và đây là ví dụ về cách mã hoá một chuổi nào đó (ví dụ mã hoá password). Thực chất của hàm này là thay đổi chuổi nhập vào = chuổi khác theo một quy luật nào đó. Và quy luật ở đây là ký tự ở vị trí lẽ sẽ tăng lên 20*number, ký tự ở vị trí chẵn sẽ giảm đi 20*number.
 
1. Hàm mã hóa ký tự

CREATE    FUNCTION fuMaHoaString
    (@strInput nvarchar(200),
     @dt       smallint=-1) 
RETURNS nvarchar(200)
AS 
BEGIN
    Declare @str        nvarchar(200)
    Declare @lenStr        tinyint
    Declare @i        tinyint
    Declare @k        smallint
    Set @lenStr = Len(@strInput)
    Set @str = ''
    Set @i = 1
    If (@dt = 0)
        Set @dt = -1
    Set @k = @dt / Abs(@dt) 
    While (@i <= @lenStr)
    Begin
        Set @str = @str + Char((Ascii(SubString(@strInput, @i, 1))+@k*20))
        Set @k = -@k
        Set @i = @i + 1
    End   
    Return @str
END

trong hàm trên chỉ cần 1 câu lệnh là quyết định, mấy câu còn lại chỉ để
làm mù mắt ai đó mà thôi

Ví dụ: 

SELECT dbo.fuMaHoaString('abc123',1)

CREATE    FUNCTION fuGiaiMaKyTuDaMaHoa
    (@strInput nvarchar(200),
     @dt       smallint=-1) 
RETURNS nvarchar(200)
AS 
BEGIN
    Declare @str        nvarchar(200)
    Declare @lenStr        tinyint
    Declare @i        tinyint
    Declare @k        smallint
    Set @lenStr = Len(@strInput)
    Set @str = ''
    Set @i = 1
    If (@dt = 0)
        Set @dt = -1
    Set @k = -@dt / Abs(@dt) 
    While (@i <= @lenStr)
    Begin
        Set @str = @str + Char((Ascii(SubString(@strInput, @i, 1))+@k*20))
        Set @k = -@k
        Set @i = @i + 1
    End   
    Return @str
END

SELECT dbo.fuGiaiMaKyTuDaMaHoa(dbo.fuMaHoaString('abc123',1),1)

Hiệu năng ứng dụng phụ thuộc vào rất nhiều yếu tố, trong đó có một yếu tố rất quan trọng đó là thời gian để máy chủ SQL xử lý câu lệnh T-SQL. Đôi khi thiết kế cơ sở dữ liệu và các yêu cầu truy vấn phức tạp làm cản trở tốc độ thực thi của các câu lệnh T-SQL. Cách viết code từng câu lệnh T-SQL cũng có thể khiến máy chủ SQL phải làm việc nhiều hơn để xử lý truy vấn. Bài viết sau đây sẽ giới thiệu cho bạn những thói quen tốt nên tập luyện khi viết code T-SQL. Qua đó bạn có thể hiểu làm thế nào để viết các câu truy vấn tối ưu, tận dụng tốt tài nguyên máy chủ SQL và cải thiện hiệu suất.

Ghi rõ tên cột trong câu lệnh SELECT

SELECT * FROM MyTable;

Bạn đã từng viết câu lệnh như trên bao nhiêu lần?

Việc sử dụng dấu sao (*) cho cơ sở dữ liệu biết rằng bạn muốn trả về tất cả các cột từ bảng (hoặc các bảng) được khai báo trong mệnh đề FROM. Đây không phải là một thói quen tốt ngay cả khi bạn muốn tất cả các cột được trả về ứng dụng. Tốt hơn bạn nên ghi rõ tên từng cột trong bảng như sau:

SELECT ID, Description, DateModified FROM MyTable;

Việc khai báo rõ ràng tên các cột trong câu lệnh SELECT mang lại rất nhiều lợi ích. Thứ nhất, máy chủ SQL sẽ chỉ trả về dữ liệu cần thiết cho ứng dụng chứ không phải là một đống dữ liệu mà trong đó có nhiều thứ ứng dụng của bạn không hề cần đến. Bằng cách chỉ yêu cầu trả về những dữ liệu cần thiết, bạn đã góp phần tối ưu hóa khối lượng công việc máy chủ SQL cần thực hiện để thu thập tất cả các cột của thông tin bạn yêu cầu. Ngoài ra, nhờ không sử dụng dấu sao (*) nên bạn đã giảm thiểu lưu lượng truyền tải qua mạng (số byte) cần thiết để gửi các dữ liệu liên quan đến câu lệnh SELECT tới ứng dụng.

Trong trường hợp bạn dùng dấu sao (*) và có một ai đó thêm cột mới vào bảng, ứng dụng của bạn sẽ bắt đầu tiếp nhận dữ liệu cho cột này mà không thay đổi mã nguồn ứng dụng. Nếu ứng dụng của bạn chờ đợi số lượng cột nhất định được trả về, nó sẽ bị lỗi ngay khi có người bổ sung cột mới vào một trong các bảng tham chiếu. Bằng việc khai báo rõ ràng tên từng cột trong câu lệnh SELECT, ứng dụng sẽ luôn nhận được số lượng cố định các cột trả về, ngay cả khi có người thêm cột mới vào các bảng tham chiếu trong câu lệnh SELECT. Nhờ vậy, bạn đã giúp ứng dụng tránh khỏi những nguy cơ tiềm tàng liên quan đến các thay đổi cơ sở dữ liệu có thể xảy đến với bất kỳ bảng nào bạn tham chiếu tới trong câu lệnh SELECT.

Ghi rõ tên cột trong câu lệnh INSERT

Cũng giống như trên, bạn nên chỉ rõ tên từng cột bạn muốn chèn dữ liệu vào trong câu lệnh INSERT. Đừng viết câu lệnh INSERT như sau:

INSERT INTO MyTable VALUES ('A','B','C');

Khi bạn viết kiểu này, máy chủ SQL đòi hỏi chỉ đúng ba cột được định nghĩa trong bảng MyTable, và giá trị “A” sẽ được chèn vào cột đầu tiên, “B” vào cột thứ hai, “C” vào cột cuối. Nếu ai đó thêm mới một cột vào bảng MyTable, ứng dụng của bạn sẽ bị lỗi:

Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
(Tên cột hoặc số lượng giá trị không khớp với bảng)

Vì thế, thay vì viết câu lệnh INSERT như trên, bạn nên viết như sau:

INSERT INTO MyTable(So1, So2, So3) VALUES ('A','B','C');

Bằng cách viết trên, khi ai đó thêm mới một cột tên là “So4” vào bảng MyTable, câu lệnh INSERT vẫn tiếp tục làm việc với điều kiện cột “So4” được tạo với giá trị mặc định DEFAULT hoặc cho phép NULL.

Thêm tiền tố cho wildcard để tăng tốc tìm kiếm

Sử dụng các ký tự thay thế (wildcard) thích hợp có thể cải thiện hiệu suất câu truy vấn. Chẳng hạn bạn muốn tìm kiếm trong bảng AdventureWorks.Person.Contact tất cả LastNames kết thúc bằng “sen”. Giả dụ rằng bạn đã xây dựng một chỉ mục trên cột LastName. Nếu bạn viết câu lệnh tìm kiếm như sau:

SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '%sen'

Câu lệnh sử dụng ký tự phần trăm (%) để thay thế cho không hoặc nhiều ký tự được theo sau bởi chuỗi “sen” trong trường LastName. Điều này khiến máy chủ SQL thực hiện thao tác quét chỉ mục nhằm tìm kiếm tất cả các tên kết thúc bằng “sen” để giải quyết câu truy vấn. Việc này rất có ý nghĩa bởi cho đến khi toàn bộ bảng được quét, máy chủ SQL không thể đảm bảo rằng đã tìm ra toàn bộ các bản ghi có LastName kết thúc bằng “sen”.

Ngoài ra, nếu bạn đang tìm kiếm các bản ghi có LastName dài đúng sáu ký tự và kết thúc bằng “sen”, bạn có thể viết câu lệnh tìm kiếm như sau:

SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '___sen’

Ở đây, câu lệnh sử dụng ký tự gạch dưới (_) để thay thế cho một ký tự đơn. Ví dụ này tương tự với ví dụ ở phần trên và sử dụng thao tác quét chỉ mục để giải quyết. Một lần nữa, máy chủ SQL biết rằng cần phải quét toàn bộ chỉ mục trước khi đảm bảo đã tìm thấy tất cả các tên dài sáu ký tự và kết thúc bằng “sen” trong bảng Person.Contact.

Máy chủ SQL có thể trả về kết quả nhanh hơn nếu nó không phải đọc toàn bộ chỉ mục bằng cách sử dụng cơ chế quét. Máy chủ SQL đủ thông minh để nhận biết khi bạn đặt thêm tiền tố trước ký tự thay thế (%, _, v.v..), nó có thể dùng một thao tác tìm kiếm chỉ mục để tiến hành giải quyết tiêu chí tìm kiếm. Sau đây là một ví dụ về câu lệnh tìm kiếm yêu cầu trả về tất cả bản ghi có LastName bắt đầu bằng ký tự “A” và kết thúc bằng “sen”:

SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE 'A%sen'

Bằng cách đặt ký tự “A” phía trước dấu phần trăm (%) trong câu lệnh tìm kiếm, bạn đã cho máy chủ SQL biết rằng nó có thể sử dụng một thao tác tìm kiếm chỉ mục để giải quyết câu truy vấn. Một khi máy chủ SQL đọc tới bản ghi cuối cùng có LastName bắt đầu bằng ký tự “A”, nó biết rằng không còn bản ghi nào có LastName bắt đầu bằng ký tự “A” nữa và sẽ dừng lại.

Không phải tất cả ký tự thay thế đều cần thêm tiền tố để máy chủ SQL dùng một thao tác tìm kiếm chỉ mục nhằm giải quyết truy vấn. Sau đây là một ví dụ trong đó câu lệnh sử dụng biểu thức thể hiện một tập hợp ký tự thay thế và vẫn cho phép máy chủ SQL giải quyết câu truy vấn bằng cách dùng một thao tác tìm kiếm chỉ mục:

SELECT Distinct LastName
FROM Person.Contact
WHERE LastName LIKE '[A-M]%sen'

Câu lệnh T-SQL trên tìm kiếm toàn bộ LastName bắt đầu bằng một ký tự bất kỳ trong khoảng từ “A” đến “M” và kết thúc bằng “sen”. Các cú pháp sử dụng ký tự thay thế khác trong đó chỉ rõ tập hợp các ký tự cũng có thể gọi thao tác quét chỉ mục để giải quyết tiêu chí tìm kiếm.

Chỉ dùng DISTINCT khi cần

Đặt từ khóa DISTINCT trong câu lệnh SELECT sẽ loại bỏ các kết quả trùng lặp trong số những kết quả trả về của câu truy vấn. Nó khiến máy chủ SQL phải thực hiện thêm thao tác SORT để sắp xếp dữ liệu nhằm nhận biết và loại bỏ các bản trùng lặp. Vì thế, nếu bạn biết trước các kết quả trả về sẽ không trùng lặp thì không nên dùng từ khóa DISTINCT trong câu lệnh T-SQL. Với việc sử dụng từ khóa DISTINCT trong câu truy vấn, bạn đã yêu cầu máy chủ SQL thực hiện thao tác sắp xếp vào loại bỏ các kết quả trùng lặp. Đây là phần công việc phụ thêm của máy chủ SQL và không có ý nghĩa gì nếu tập hợp kết quả của bạn chỉ bao gồm những bản ghi độc nhất.

Chỉ dùng UNION khi cần

Cũng giống như trường hợp từ khóa DISTINCT, toán tử UNION đòi hỏi thêm thao tác SORT để máy chủ SQL có thể loại bỏ những kết quả trùng lặp. Nếu bạn biết trước danh sách kết quả trả về không có kết quả nào giống nhau thì thao tác sắp xếp mà máy chủ SQL phải thực hiện trở nên không cần thiết. Bởi vậy khi bạn cần dùng toán tử UNION để nối hai tập hợp bản ghi với nhau, trong đó các bản ghi là độc nhất không trùng lặp, tốt hơn bạn nên dùng toán tử UNION ALL. Toán tử UNION ALL không loại bỏ các bản ghi trùng lặp bởi vậy sẽ giảm nhẹ phần công việc cho máy chủ SQL trong quá trình xử lý do không phải thực hiện thao tác sắp xếp. Giảm bớt công việc cho máy chủ SQL đồng nghĩa với việc thao tác xử lý sẽ được thực hiện nhanh hơn.

Áp dụng các thói quen tốt để code nhanh hơn

Có rất nhiều lý do để tập luyện cho mình quen với lối viết code tối ưu. Khi bạn áp dụng thành thạo những thủ thuật nhỏ nêu trên và biến nó thành thói quen mỗi khi viết câu lệnh T-SQL, bạn sẽ tránh được rủi ro có thể xảy ra khi cơ sở dữ liệu thay đổi, đồng thời cải thiện hiệu suất làm việc của máy chủ nhờ giảm thiểu lưu lượng truyền tải qua mạng. Những thủ thuật rất đơn giản này còn giúp bạn tận dụng tốt hơn tài nguyên máy chủ trong quá trình xử lý câu lệnh.

Thủ tục lưu trữ đa năng

Trước khi bước vào vấn đề làm thế nào để tối ưu hóa thủ tục lưu trữ đa năng (Jack Of All Trades Stored Procedure - SP), chúng ta cần có một chút khái niệm về loại thủ tục này. Thủ tục lưu trữ đa năng là thủ tục chấp nhận nhiều tham số khác nhau có liên quan đến thủ tục. Dựa trên các tham số được truyền vào, thủ tục lưu trữ đa năng xác định bản ghi nào sẽ được trả về. Sau đây là một ví dụ về thủ tục lưu trữ đa năng:

CREATE PROCEDURE JackOfAllTrades (@SalesOrderID int = NULL
            ,@SalesOrderDetailID int = NULL
            ,@CarrierTrackingNumber nvarchar(25) = NULL)
AS
SELECT * FROM AdventureWorks.Sales.SalesOrderDetail
WHERE
             (SalesOrderID = @SalesOrderID or @SalesOrderID IS NULL)
  AND (SalesOrderDetailID = @SalesOrderDetailID or
             @SalesOrderDetailID IS NULL)
  AND (CarrierTrackingNumber = @CarrierTrackingNumber or
             @CarrierTrackingNumber IS NULL)
GO

Ở đây SP JackOfAllTrades chấp nhận ba tham số khác nhau. Tất cả các tham số này có giá trị mặc định là NULL. Khi một giá trị được truyền vào, nó sẽ được sử dụng như một tham số trong mệnh đề WHERE để ràng buộc các bản ghi trả về. Mỗi tham số trong SP được dùng để xây dựng một mệnh đề WHERE phức tạp chứa logic sau đây trong mệnh đề WHERE đối với mỗi tham số truyền vào:

(<TableColumn> = @PARM or @PARM IS NULL)

Logic trên cho biết nếu @PARM được truyền giá trị non-null thì sẽ ràng buộc bản ghi trả về để chắc chắn rằng <TableColumn> bằng giá trị của @PARM. Phần thứ hai của điều kiện đó là “@PARM IS NULL”. Phần này có nghĩa nếu @PARM không có giá trị truyền vào (bằng NULL) thì không ràng buộc dữ liệu dựa trên tham số ấy.

Cùng xem quá trình thực thi điển hình của JackOfAllTrades SP. Giả sử ta thực thi SP với lệnh sau:

EXEC JackOfAllTrades @SalesOrderID = 43659

Khi chạy câu lệnh, sơ đồ thực thi trông như sau:

Ở đây bạn có thể thấy đối với mỗi tham số đơn được truyền vào, máy chủ quyết định sử dụng thao tác “quét chỉ mục”. Câu lệnh SELECT của SP ràng buộc cột duy nhất @SalesOrderID - một phần của khóa chỉ mục cụm. Bạn có thể nghĩ máy chủ SQL đủ thông minh để nhận ra rằng xử lý thủ tục lưu trữ đa năng bằng thao tác “tìm kiếm chỉ mục” sẽ nhanh hơn là lao vào chỉ mục cụm. Thế nhưng như ta thấy trên sơ đồ thực thi, máy chủ SQL không thông minh đến vậy. Tại sao thế?

Khi máy chủ nhìn thấy điều kiện “@PARM IS NULL”, nó như một hằng số đối với máy chủ SQL. Vì thế máy chủ coi như không có chỉ mục nào hữu ích giúp xử lý điều kiện “(<TableColumn> = @PARM1 or @PARM1 IS NULL)” bởi lẽ hằng số đang ở trong mệnh đề WHERE. Chính vì vậy mà máy chủ SQL quyết định sử dụng thao tác “quét chỉ mục” để giải quyết vấn đề. Thủ tục lưu trữ đa năng càng có nhiều tham số, hiệu suất càng giảm do tác động của số lượng thao tác quét cần thiết cho mỗi tham số truyền vào.

Tối ưu hóa thủ tục lưu trữ đa năng

Bạn không cần phải chấp nhận sử dụng thủ tục lưu trữ đa năng rất kém hiệu quả như SP đã viết trong ví dụ trên. Hãy cùng khám phá xem SP sau đây có thể làm những gì và viết lại nó để công cụ tối ưu truy vấn của máy chủ SQL có thể tạo kế hoạch thực thi tối ưu hơn.

Như đã nói ở trên, vấn đề thực sự với thủ tục lưu trữ đa năng đó là đối với mỗi tham số bạn cần có một điều kiện “OR” để kiểm tra xem tham số truyền vào có phải NULL không. Nếu ta có thể loại bỏ yêu cầu này, máy chủ SQL sẽ có khả năng lên kế hoạch sử dụng thao tác “tìm kiếm chỉ mục”. Vậy làm thế nào để loại bỏ điều kiện “@PARM IS NULL”? Câu trả lời đó là sử dụng SQL động được thông số hóa (parameterized dynamic SQL).

Đến đây có thể các bạn nghĩ tôi chuẩn bị mở đường cho SQL injection vào giải pháp của mình. Tuy nhiên, chúng ta sẽ chỉ xây dựng mã SQL động cho phép truyền tham số SP tới một SP hệ thống khác là “sp_executesql”. SP này sẽ sử dụng các tham số trong đoạn mã SQL động ta xây dựng.

SP hệ thống “sp_executesql” cho phép bạn phát triển câu lệnh T-SQL có chứa tham số, đồng thời cho phép bạn định nghĩa và truyền giá trị cho các tham số tới SQL động bằng cách truyền tham số tới SP “sp_executesql” khi chạy SP này. Câu lệnh T-SQL được thực thi theo cách này thường gọi là SQL được thông số hóa. Có nhiều lý do để sử dụng SQL được thông số hóa, nhưng trong khuôn khổ bài viết này chúng ta chỉ tập trung vào việc làm thế nào để sử dụng SQL được thông số hóa nhằm cải thiện hiệu suất của thủ tục lưu trữ đa năng. Sau đây là đoạn code tạo thủ tục lưu trữ đa năng được viết lại sử dụng SQL động được thông số hóa:

CREATE PROCEDURE JackOfAllTrades_V2 (@SalesOrderID int = NULL
                             ,@SalesOrderDetailID int = NULL
                             ,@CarrierTrackingNumber nvarchar(25) = NULL)
AS
DECLARE @CMD NVARCHAR(max)
DECLARE @WHERE NVARCHAR(max)
SET @CMD = 'SELECT * FROM AdventureWorks.Sales.SalesOrderDetail '
SET @WHERE = ''
IF @SalesOrderID IS NOT NULL
   SET @WHERE = @WHERE + 'AND SalesOrderID = @SalesOrderID '
IF @SalesOrderDetailID IS NOT NULL
   SET @WHERE = @WHERE + 'AND SalesOrderDetailID  = @SalesOrderDetailID '
IF @CarrierTrackingNumber IS NOT NULL
   SET @WHERE = @WHERE + 'AND CarrierTrackingNumber  = @CarrierTrackingNumber '
IF LEN(@WHERE) > 0
   SET @CMD = @CMD + ' WHERE ' + RIGHT(@WHERE,LEN(@WHERE) - 3)
EXEC sp_executesql @CMD
               , N'@SalesOrderID int
                  ,@SalesOrderDetailID int
                  ,@CarrierTrackingNumber nvarchar(25)'
                   ,@SalesOrderID = @SalesOrderID
                   ,@SalesOrderDetailID = @SalesOrderDetailID
                   ,@CarrierTrackingNumber = @CarrierTrackingNumber

Tiếp theo chúng ta sẽ đi sâu vào chi tiết nhằm giúp bạn hiểu rõ phần động và phần thông số hóa của đoạn mã trên. SP này bắt đầu bằng việc gán biến @CMD vào câu lệnh SELECT không có mệnh đề WHERE. Tiếp theo ta gán biến @WHERE cho một chuỗi rỗng. Tiếp đó là bốn câu IF khác nhau. Ba câu IF đầu tiên kiểm tra xem mỗi tham số truyền vào có thỏa mãn điều kiện NOT NULL hay không. Nếu một tham số NOT NULL, ta sẽ gắn điều kiện vào biến @WHERE đối với tham số ấy. Do ta đã kiểm tra và xác định tham số đó NOT NULL, ta không cần thêm điều kiện IS NULL vào mệnh đề WHERE như đoạn code SP ban đầu ở phần trên. Thay vào đó, tất cả những gì ta cần là thêm điều kiện <TableColumn> = @PARM vào biến @WHERE. Câu If cuối cùng xác định xem biến @WHERE có thỏa mãn ít nhất một điều kiện hay không, và nếu có thì nó sẽ nối biến @WHERE với biến @CMD.

Lưu ý rằng biến @WHERE là phần động của đoạn code. Nhưng tôi không đặt phần text thực của tham số vào biến @WHERE, thay vào đó chỉ đặt một tham chiếu tới các tham số trong điều kiện WHERE. Vì thế câu lệnh T-SQL động cơ bản chỉ bao gồm câu lệnh SELECT ban đầu và mệnh đề WHERE không còn cần điều kiện IS NULL để ràng buộc dữ liệu nữa.

Cuối cùng tôi sử dụng SP “sp_executesql” để thực thi câu lệnh T-SQL động thông số hóa. Để thực hiện điều này, tôi truyền năm tham số vào SP hệ thống. Tham số đầu tiên là biến T-SQL động @CMD. Tham số thứ hai khai báo tất cả các biến có thể có trong đoạn truy vấn được thông số hóa, cùng với loại dữ liệu của chúng. Với ba tham số cuối cùng, chúng chỉ được truyền vào SP hệ thống giống như chúng được truyền vào SP lưu trữ đa năng trong phần đầu. Như bạn có thể thấy, tôi hoàn toàn không làm đoạn mã SQL động của mình trở nên dễ bị tấn công bằng SQL injection hơn SP ban đầu. Lý do là vì tôi không sử dụng giá trị thực của tham số để chuyển tới biến @WHERE. Tôi chỉ truyền tham số như các biến vào SQL động qua SP hệ thống “sp_executesql”.

Bây giờ hãy chạy đoạn code tạo thủ tục lưu trữ đa năng mới viết lại bằng cách chạy câu lệnh sau:

EXEC JackOfAllTrades_V2 @SalesOrderID = 43659

Khi chạy thử nghiệm với cơ sở dữ liệu AdventureWorks trên server, tôi nhận được sơ đồ thực thi như sau:

Khi so sánh sơ đồ này với sơ đồ ở phần đầu, bạn có thể thấy nó đơn giản hơn và sử dụng thao tác “tìm kiếm chỉ mục cụm” để xử lý SP. Sở dĩ máy chủ SQL có thể sử dụng thao tác này là vì đoạn code SQL động không còn điều kiện “@PARM IS NULL” nữa. Do câu lệnh T-SQL đã được đơn giản hóa nhờ sử dụng SQL động và loại bỏ ràng buộc IS NULL, máy chủ SQL giờ đây có thể đưa ra kế hoạch thực thi tối ưu hơn cho thủ tục lưu trữ đa năng phiên bản V2.

Kết lại, thực tế thì hiệu quả chúng ta thu được ở mức nào? Nên nhớ trên đây ta chỉ mới xét những bản ghi từ bảng SalesOrderDetail có SalesOrderID bằng 43659. SP lưu trữ đa năng ban đầu sử dụng thao tác “quét chỉ mục” để xử lý truy vấn. Điều đó có nghĩa nó phải đọc lần lượt toàn bộ chỉ mục trước khi có thể hoàn thành yêu cầu truy vấn và trả về bản ghi chứa một giá trị SalesOrderID. Ngược lại, phiên bản V2 của SP lưu trữ đa năng có thể sử dụng thao tác “tìm kiếm chỉ mục” với khóa chỉ mục cụm trên bảng SalesOrderDetail để lấy trực tiếp những bản ghi nhất định có chứa SalesOrderID bằng 43659 một cách nhanh chóng. Thao tác “tìm kiếm chỉ mục” tối ưu hơn thao tác “quét chỉ mục” rất nhiều, nhưng cụ thể nhiều như thế nào?

Việc đánh giá khoản I/O tiết kiệm được nhờ dùng phiên bản SP lưu trữ đa năng V2 có thể thực hiện bằng nhiều cách. Ta sẽ chạy đoạn T-SQL sau đây:

SET STATISTICS IO ON
GO
EXEC JackOfAllTrades @SalesOrderID = 43659
GO
EXEC JackOfAllTrades_V2 @SalesOrderID = 43659
GO

Ở đây tôi sử dụng lệnh “SET STATISTICS IO ON” nên kết quả của 2 SP đang thực thi sẽ hiển thị số lượng I/O mỗi lệnh đòi hỏi để xử lý truy vấn. Dưới đây là kết quả nhận được:

(12 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 264, physical reads 0, read-ahead reads 0,
       lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(1 row(s) affected)

(12 row(s) affected)
Table 'SalesOrderDetail'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0,
       lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

Khi nhìn kết quả trên, ta có thể thấy hiệu suất của SP lưu trữ đa năng đầu tiên là 1 lần quét và 264 lần đọc logic. Ngược lại phiên bản V2 có cùng số lần quét chỉ mục nhưng chỉ cần thực hiện 3 lần đọc logic để xử lý truy vấn. Khoản I/O tiết kiệm đc là 261. Con số này có vẻ không thấm tháp gì, tuy nhiên với trường hợp bạn phải gọi đi gọi lại SP trong một vòng lặp nào đó chẳng hạn, hiệu suất sẽ được cải thiện một cách rõ rệt giữa hai phiên bản SP.

Cải thiện lượng I/O nhờ sử dụng SQL động được thông số hóa

Sau khi đọc hết phần này, bạn cần hiểu được lý do vì sao máy chủ SQL lại đưa ra bản sơ đồ thực thi kém hiệu quả. Trên đây máy chủ SQL đã coi logic “@PARM IS NULL” như một hằng số. Bởi vậy nó quyết định cần phải thực hiện thao tác “quét chỉ mục” để xử lý phiên bản thủ tục lưu trữ đa năng đầu tiên. Như chúng ta đã biết, thao tác quét (SCAN) luôn chậm hơn thao tác tìm kiếm (SEEK). Bằng cách viết lại phiên bản SP lưu trữ đa năng V2 có sử dụng T-SQL động, tôi đã loại bỏ được biểu thức hằng số trong mệnh đề WHERE của câu lệnh T-SQL. Nhờ vậy máy chủ SQL đã tìm được phương pháp đúng đắn hơn đó là sử dụng thao tác “tìm kiếm chỉ mục cụm”. Nếu trang web của bạn có sử dụng thủ tục lưu trữ đa năng, hãy thử viết lại nó bằng SQL động được thông số hóa và chờ xem hiệu suất sẽ được cải thiện thế nào.

Khám phá sơ đồ lưu bộ nhớ đệm

Bạn đã tận dụng được lợi thế từ việc lưu sơ đồ trên bộ nhớ đệm chưa? Bạn đã khai thác các sơ đồ lưu cache đến mức nào? Ứng dụng của bạn chỉ sử dụng chúng một lần hay tận dụng nhiều lần? Bạn có nhiều sơ đồ lưu cache cho cùng một truy vấn trong cache thủ tục cùng lúc không? Khoảng trống các sơ đồ lưu cache sử dụng là bao nhiêu? Trên đây là một số câu hỏi bạn cần trả lời để chắc rằng bạn đã tối ưu hóa cache thủ tục và giảm thiểu số lượng sơ đồ lưu cache mà ứng dụng tạo ra. Có một vài vấn đề nhỏ trong cách viết câu lệnh T-SQL của bạn là nguyên nhân khiến máy chủ SQL phải thực hiện thêm nhiều việc để biên dịch và lưu cache các sơ đồ thực thi cho cùng một đoạn code.

Trước khi máy chủ SQL có thể tiến hành xử lý đoạn code T-SQL, nó cần tạo một bản sơ đồ thực thi. Để tạo bản sơ đồ thực thi, trước tiên máy chủ SQL phải tiêu tốn những nguồn tài nguyên giá trị như CPU nhằm biên dịch code T-SQL. Khi sơ đồ được tạo xong, nó sẽ được lưu cache để có thể tái sử dụng khi ứng dụng gọi cùng một câu lệnh T-SQL nhiều hơn một lần. Bạn có thể cải thiện hiệu suất máy chủ SQL nếu bạn viết câu lệnh T-SQL để tăng cường tái sử dụng sơ đồ lưu cache với những đoạn T-SQL thường xuyên được thực thi.

Với sự xuất hiện của SQL Server 2005, Microsoft cung cấp các DMV (Dynamic Management Views - Cửa sổ quản lý động) cho phép bạn khám phá các sơ đồ lưu. Bằng cách sử dụng các DMV, bạn có thể tìm hiểu được nhiều điều về các sơ đồ lưu cache. Sau đây là danh sách tóm tắt những thứ bạn có thể nhận biết:

• Các đoạn text liên quan tới sơ đồ lưu cache
• Số lần sơ đồ lưu cache được thực thi
• Kích cỡ sơ đồ lưu cache

Ở đoạn sau của bài viết, tôi sẽ hướng dẫn các bạn cách sử dụng DMV để tìm hiểu thông tin sơ đồ lưu cache.

Tạo nhiều sơ đồ vì các ghi chú hoặc khoảng trắng thừa

Tôi chắc rằng các bạn đều ủng hộ ý tưởng đặt code vào các thủ tục lưu trữ (Stored Procedure - SP). Chúng ta thực hiện việc này nhằm tăng khả năng tái sử dụng code trong khuôn khổ một ứng dụng đơn lẻ hoặc trên nhiều ứng dụng. Tuy nhiên, không phải tất cả các đoạn code được thực thi bởi máy chủ SQL đều nằm trong các SP. Vài ứng dụng có thể được viết bằng các lệnh T-SQL dạng in-line (lệnh thô). Nếu bạn đang viết các đoạn mã T-SQL dạng thô, bạn cần phải cẩn thận khi ghi chú hoặc đặt khoảng trắng bởi nó có thể là nguyên nhân khiến máy chủ SQL tạo nhiều sơ đồ lưu cache cho cùng một đoạn code T-SQL.

Sau đây là một ví dụ gồm hai câu lệnh T-SQL khác nhau:

SELECT * FROM AdventureWorks.Production.Product
GO
SELECT * FROM AdventureWorks.Production.Product -- return records
GO

Như bạn thấy, tôi có hai câu lệnh T-SQL giống nhau. Cả hai đều trả về tất cả bản ghi từ bảng AdventureWorks.Production.Product. Vậy bạn nghĩ máy chủ SQL sẽ tạo ra bao nhiêu sơ đồ lưu cache khi chạy đoạn mã trên? Để trả lời câu hỏi này, tôi sẽ tìm hiểu thông tin sơ đồ lưu cache bằng cách sử dụng các DMV trong SQL Server 2005 và SQL Server 2008. Để xem các sơ đồ tạo bởi hai câu lệnh T-SQL trên, tôi sẽ chạy đoạn code sau:

DBCC FREEPROCCACHE
GO
SELECT * FROM AdventureWorks.Production.Product
GO
SELECT * FROM AdventureWorks.Production.Product -- return records
GO
SELECT stats.execution_count AS exec_count,
           p.size_in_bytes as [size],
           [sql].[text] as [plan_text]
FROM sys.dm_exec_cached_plans p
outer apply sys.dm_exec_sql_text (p.plan_handle) sql
join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle
GO

Trong đoạn code trên, trước tiên tôi giải phóng cache thủ tục bằng cách chạy lệnh DBCC FREEPROCCACHE. Lệnh này xóa bỏ toàn bộ sơ đồ thực thi trong bộ nhớ. Tuy nhiên tôi cũng xin có lời lưu ý rằng bạn không nên sử dụng lệnh này khi làm việc trong doanh nghiệp bởi nó sẽ xóa bỏ toàn bộ sơ đồ lưu cache. Điều này có thể gây ra những tác động to lớn tới công việc của bạn do những sơ đồ thường dùng đều bị biên dịch lại. Sau khi giải phóng cache thủ tục, tôi chạy tiếp hai câu lệnh SELECT khác nhau. Cuối cùng, tôi liên kết thông tin từ các DMV lại để trả về thông tin sơ đồ lưu cache của hai câu lệnh SELECT. Sau đây là kết quả nhận được khi chạy đoạn code trên:

exec_count size  plan_text
---------- ----- ---------
1          40960 SELECT * FROM AdventureWorks.Production.Product -- return records
1          40960 SELECT * FROM AdventureWorks.Production.Product

Như bạn thấy, hai câu lệnh SELECT trên tạo ra hai sơ đồ lưu cache khác nhau và mỗi sơ đồ được thực thi 1 lần (exec_count number). Sở dĩ chuyện này xảy ra là vì hai câu lệnh SELECT không hoàn toàn giống nhau. Câu lệnh SELECT thứ hai hơi khác một chút vì có thêm ghi chú. Ngoài ra, các bạn hãy để ý kích cỡ sơ đồ: 40960 byte - kích cỡ bộ nhớ quá lớn dành cho một câu lệnh T-SQL rất đơn giản. Vì thế, bạn nên cẩn thận khi thêm ghi chú vào code, tránh để máy chủ tạo ra nhiều sơ đồ thừa.

Một nguyên nhân khác dẫn đến việc tạo ra nhiều sơ đồ lưu cache cho những câu lệnh T-SQL giống nhau đó là các khoảng trắng. Sau đây là hai câu lệnh giống nhau ngoại trừ các khoảng trắng:

SELECT * FROM AdventureWorks.Production.Product
GO
SELECT * FROM         AdventureWorks.Production.Product
GO

Như bạn thấy, câu lệnh thứ hai có chứa vài khoảng trắng thừa giữa FROM và tên đối tượng. Các khoảng trắng thừa này là nguyên nhân khiến máy chủ SQL nghĩ rằng đây là hai câu lệnh khác nhau, từ đó dẫn đến việc tạo ra hai sơ đồ lưu cache khác nhau cho hai câu lệnh. Trong trường hợp này, hiển nhiên bạn dễ dàng nhận ra sự khác biệt giữa hai câu lệnh bởi lẽ các khoảng trắng nằm ở giữa câu lệnh. Thế nhưng nếu bạn vô tình thêm khoảng trắng phía trước mệnh đề SELECT hoặc phía cuối câu lệnh, bạn sẽ không thể nhận ra các khoảng trắng và câu lệnh sẽ trông giống hệt nhau. Tuy nhiên, máy chủ SQL thì có thể nhìn thấy, và thế là nó tạo ra nhiều sơ đồ lưu cache vì các khoảng trắng thừa đó.

Khi máy chủ SQL nhìn vào đoạn code, nó sẽ đem so sánh với các sơ đồ sẵn có trong cache thủ tục. Nếu xác định đoạn code giống hệt sơ đồ lưu cache sẵn có, máy chủ SQL không cần biên dịch và lưu sơ đồ vào bộ nhớ nữa. Máy chủ SQL sẽ tái sử dụng các sơ đồ có trong cache đối với những đoạn code giống nhau. Để tối ưu hóa mã nguồn, bạn cần đảm bảo việc tái sử dụng sơ đồ lưu cache bất cứ khi nào có thể.

Khi bạn đang xây dựng mã nguồn ứng dụng trong đó có sử dụng các câu lệnh T-SQL mà không dùng SP, bạn cần phải cẩn trọng để đảm bảo nhận được sơ đồ có khả năng tái sử dụng cao nhất có thể. Chúng ta thường dùng phương pháp copy - paste khi muốn sử dụng cùng một đoạn code trong các phần khác nhau của ứng dụng. Tuy nhiên như bạn thấy trong các ví dụ trên, bạn cần cẩn thận khi thực hiện thao tác này. Chỉ cần một vài khoảng trắng thừa hoặc một ghi chú nhỏ cũng khiến máy chủ SQL tạo ra nhiều sơ đồ lưu cache khác nhau.

Nâng hiệu suất lên tối đa và giảm thiểu bộ nhớ

Để tối ưu hóa mã nguồn, nếu chỉ quan tâm đến thiết kế cơ sở dữ liệu thôi thì chưa đủ, bạn còn cần để ý đến từng chi tiết nhỏ hơn chẳng hạn như các khoảng trắng và ghi chú. Nếu bạn không lưu tâm đến những chi tiết quanh các câu lệnh T-SQL giống nhau, bạn có thể khiến máy chủ SQL tạo ra nhiều sơ đồ lưu cache. Có thể việc có vài sơ đồ lưu cache thừa trong bộ nhớ không phải quá quan trọng, tuy nhiên là một lập trình viên, chúng ta cần cố gắng hết khả năng để nâng cao hiệu suất máy chủ và giảm thiểu tài nguyên sử dụng. Và một trong những cách để thực hiện mục tiêu trên, đó là tránh tạo ra nhiều sơ đồ lưu cache cho các câu lệnh T-SQL giống nhau.