Thứ Tự Xử Lý Điều Kiện Ở Mệnh Đề WHERE

Vũ Huy Tâm

Theo bạn các điều kiện trong mệnh đề WHERE được xử lý theo thứ tự nào, từ phải sang trái hay từ trái sang phải? Thực tế là, chúng không phải luôn luôn được xử lý theo một thứ tự nhất định như các ngôn ngữ khác. Chúng được xử lý theo cách SQL Server đánh giá là đạt hiệu quả cao nhất với từng câu lệnh. Nếu bạn viết code các điều kiện của mệnh đề WHERE dựa trên giả định chúng được xử lý theo thứ tự nào đó, bạn sẽ gặp tình huống câu lệnh bị lỗi khi thứ tự xử lý bị đảo ngược (ví dụ khi lượng dữ liệu thay đổi và/hoặc có thêm index mới được tạo…).
Ta hãy thực hiện thí nghiệm sau:

CREATE TABLE test1(id1 INT PRIMARY KEY IDENTITY, id2 INT, dt VARCHAR(50))
GO
INSERT INTO test1(dt)
SELECT TOP 1000000 a.name
FROM syscolumns a CROSS JOIN syscolumns b
 
UPDATE test1 SET id2 = id1, dt = 'a'+CAST(id1 AS VARCHAR)
 
UPDATE test1 SET dt = '20121026' WHERE id2 = 1001 --sau lệnh update này chỉ một bản ghi có dt là ngày thực sự

Và sau đó thực hiện truy vấn tới bảng với hai cách viết khác nhau chút xíu, ở thứ tự các điều kiện ở mệnh đề WHERE:

SELECT * FROM test1
WHERE CAST(dt AS DATETIME) = '20121026' --CAST() là điều kiện đầu tiên
AND id2 = 1001
 
SELECT * FROM test1
WHERE id2 = 1001
AND CAST(dt AS DATETIME) = '20121026' --CAST() là điều kiện cuối cùng

Cả hai lệnh trên đều chạy được bình thường (ít nhất là trên hệ thống tôi đang dùng, bản 2008). Nay ta hãy tạo index trên trường dt:

CREATE INDEX test1_idx ON test1(dt)

Khi thực hiện lại hai câu lệnh trên, cả hai lệnh đều trả về lỗi chuyển đổi kiểu dữ liệu (conversion failed). Như vậy là tình huống đã thay đổi và không thứ tự xử lý nào còn áp dụng được nữa. Điều này được lý giải như sau:
1. Trước khi tạo index, phương án thực thi duy nhất có thể áp dụng cho hai câu lệnh trên là quét bảng, nghĩa là duyệt lần lượt từng bản ghi từ đầu tới cuối. Khi áp dụng phương án này, SQL Server đã linh hoạt chọn các điều kiện so sánh bằng số (integer) trước vì các phép so sánh này ít tốn kém, rồi sau đó mới chọn tới các phép so sánh nặng nề hơn như so sánh kiểu ngày. Do đó cả hai câu lệnh đều thực hiện trót lọt vì phép chuyển đổi và so sánh ngày luôn được thực hiện sau cùng.
2. Sau khi có index, SQL Server xác định index này thích hợp cho câu lệnh và lựa chọn phương án thực thi dùng index scan (bạn có thể bật execution plan để kiểm chứng). Khi chạy trên cây index, hệ thống sẽ kiểm tra trên từng node xem có bằng giá trị được cung cấp hay không. Vì thế điều kiện CAST(DATETIME) được kiểm tra trước và lệnh sẽ bị lỗi ngay khi gặp node đầu tiên chứa dt không phải kiểu DATETIME.
Như vậy, thứ tự xử lý mệnh đề WHERE hoàn toàn không được tôn trọng, mà SQL Server chủ động chọn cách xử lý được cho là hiệu quả nhất, tùy theo tình huống tại thời điểm câu lệnh thực hiện. Điều này xóa bỏ đồn đoán của một số bạn cho rằng thứ tự luôn tuân theo từ trái sang phải hay từ phải sang trái.
Hai câu lệnh SELECT ở trên bị coi là không an toàn vì chúng phụ thuộc vào một thứ tự xử lý nhất định. Để hóa giải, bạn nên viết lại sử dụng phát biểu CASE:

SELECT * 
FROM test1
WHERE id2 = 1001
AND CASE WHEN ISDATE(dt)=1 THEN CAST(dt AS DATETIME) END = '20121026'



Tags: , , , ,

22 Comments
Posted on 31/10/2012 | Categories: SQL Server Programming, Tip & Trick

Các bài viết tương tự

Comments
  • PhaoThu (02/11/2012 12:59 am)

    Thanks , very clear here. Nhân đây mình muốn hỏi là cây index được tạo ra từ một trường là cột ngày tháng , và một cây index được tạo ra từ trường kiểu số int thì khi tìm kiếm trên 2 cây index này tốc độ có khác nhau hay không. Hiện giờ mình đang nghĩ là quá trình tạo ra 2 cây index này có thể khác nhau nhưng tốc độ tìm kiếm trên cây index là như nhau. Như vậy có đúng không ?

    • Vũ Huy Tâm (02/11/2012 9:17 am)

      Khác biệt đến mức mình có thể cảm nhận được thì tôi nghĩ là không, vì suy cho cùng kiểu ngày cũng chỉ là con số có kích thước 8 byte trong khi kiểu int là 4 byte. Nhưng khi ở tình huống cần lựa chọn 1 trong 2 cái (giả sử mọi yếu tố khác đều cân bằng) thì SQL Server chắc sẽ chọn cây kiểu int.

  • Pham Van Loc (20/08/2013 9:54 pm)

    Bạn cho hỏi thứ tự và cách T-SQL thức thực hiện các điều kiện OR sẽ như thế nào, ví dụ có câu lệnh

    SELECT * 
    FROM t1
    WHERE (@var1= @var2 OR Field1 = @var3)

    1. Nó có thực hiện so sánh từ trái qua phải không, hoặc nếu optimize thì nguyên tắc là thế nào.
    2. Khi gặp một điều kiện True thì nó có xem xét các điều kiện OR khác nữa hay không, hay trả về kết quả luôn.

    • Red Devilic (20/08/2013 11:30 pm)

      Câu hỏi của bạn ở trên đã trả lời rồi
      “Như vậy, thứ tự xử lý mệnh đề WHERE hoàn toàn không được tôn trọng, mà SQL Server chủ động chọn cách xử lý được cho là hiệu quả nhất, tùy theo tình huống tại thời điểm câu lệnh thực hiện. Điều này xóa bỏ đồn đoán của một số bạn cho rằng thứ tự luôn tuân theo từ trái sang phải hay từ phải sang trái”

      Giải thích kĩ hơn 1 chút, là SQL Server sẽ đánh giá câu truy vấn này, dựa trên dữ liệu trong bảng truy vấn. Nhưng trường hợp của bạn thì nó sẽ ưu tiên so sánh cái @var1 với cái @var2. Nếu @var1 = @var2 thì dĩ nhiên nó không xem xét các điều kiện còn lại nữa mà trả về kết quả luôn.

      • Pham Van Loc (02/09/2013 2:59 am)

        Cảm ơn bạn.

  • nguoihanoi (21/08/2013 3:54 pm)

    Dạo này thất nghiệp nên tinh thần chém gió có phần mai một hehe

    Nhưng nhân đây múa vài đường mua vui …


    Nhưng trường hợp của bạn thì nó sẽ ưu tiên so sánh cái @var1 với cái @var2. Nếu @var1 = @var2 thì dĩ nhiên nó không xem xét các điều kiện còn lại nữa mà trả về kết quả luôn.

    Tui nghĩ câu trên có thể gây hiểu lầm. Theo tui, với liên kết OR, các điều kiện đều được xem xét (trừ trưởng hợp hằng số), tuy nhiên nếu bất cứ điều kiện nào trả về “true”, các điều kiện khác sẽ dừng.

    Theo tui, để tránh lỗi và tối ưu hóa trong những câu truy vấn phức tạp, ta nên xem xét tất cả mọi điều kiện trong where

    • tung (11/11/2013 5:53 am)

      Bác có thể cho ví dụ cụ thể được không ạ ?

  • Nguyen Dung (20/11/2013 1:13 am)

    Hi anh,

    Trong 2 câu lệnh

      SELECT * FROM tbl" và  "SELECT * FROM tbl WHERE

    .

    Câu lệnh nào có tốc độ xử lí nhanh hơn ạ ?

  • Tùng phương (24/10/2014 11:03 pm)

    anh ơi cho em hỏi. em muốn Join 2 bảng A và B với nhau nhưng kiểu dữ liệu lại không tương đồng. vì vậy em muốn sử dụng hàm CAST thì cách viết như thế nào ạ.

    • Tùng phương (24/10/2014 11:06 pm)

      Ví dụ em viết riêng 2 câu lệnh ra như thế này ạ
      SELECT CAST(ma_tinh AS bigint)
      FROM danhmucbenhvien

      SELECT ten_bv, name
      FROM danhmucbenhvien a
      JOIN lst_province b
      ON a.ma_tinh = b.code
      WHERE name LIKE ‘Khánh Hòa’

    • Vũ Huy Tâm (27/10/2014 8:54 am)
      SELECT ten_bv, name
      FROM danhmucbenhvien a
      JOIN lst_province b
      ON CAST(a.ma_tinh AS BIGINT) = b.code
      WHERE name LIKE '%Khánh Hòa%'

      Thường SQL sẽ tự chuyển đổi kiểu dữ liệu để so sánh với nhau nên bạn không cần cast trong điều kiện join. Tuy nhiên bạn nên thiết kế database sao cho các trường cần join với nhau có cùng kiểu dữ liệu, vì việc chuyển đổi kiểu trong khi join làm mất tác dụng của index và làm chậm query

  • ThanhHQ (29/10/2014 10:29 pm)

    Chào anh Tâm,

    Cho phép em đặt một câu hỏi.

    Em đang có bảng A1 ở Server1 (đối tượng nhóm 1 trả dữ liệu về)
    Bảng A2 ở Server 2 (đối tượng nhóm 2 trả dữ liệu về)
    ……
    Cấu trúc các bảng có giống nhau một số trường và khác nhau một số trường tương ứng với mỗi nhóm đối tượng.
    Em có phát triển một ứng dụng duyệt các dữ liệu gửi về cho toàn bộ hệ thống (realtime).
    Giải pháp hiện tại của em là viết một câu truy vấn lấy dữ liệu từ các bảng đó theo cấu trúc chung về hệ thống xử lý và cài đặt job (15p chạy một lần).

    Em không biết có cách nào hay hơn không vì đây chỉ là giải pháp tạm thời của em, mong anh chỉ giáo.

    Thanks anh và mọi người.

  • Vũ Huy Tâm (30/10/2014 9:57 am)

    Nếu lấy dữ liệu liên tục thì bạn nên cài đặt cơ chế chỉ lấy dữ liệu mới thay đổi từ lần chạy trước đó (dữ liệu vừa được insert/update/delete trong vòng 15′ trước đó). Nếu dùng SQL enterprise 2008 hoặc mới hơn thì bạn có thể dùng Change Data Capture (CDC).
    Không biết có đúng ý bạn hỏi?

    • ThanhHQ (07/11/2014 6:06 am)

      Cảm ơn anh Tâm đã phản hồi,

      Thực ra lúc đầu em cũng nghĩ ra được một số cách dạng như trigger dữ liệu. Nhưng làm kiểu này ảnh hưởng nhiều đến hệ thống mà em lại không lường hết được, nên đành phải làm kiểu merge dữ liệu cho an toàn.

      Cảm ơn anh nhiều lắm. Em học được không ít ở website này :) . Chúc anh sức khỏe.

  • HungFIT (21/05/2015 12:41 am)

    anh/chị cho em biết làm cách nào để thực hiện truy vấn SELECT ra ngày mà không có giờ đi kèm không. Ngày e dùng kiểu datetime, khi truy xuất nó ra em muốn hiển thị ví dụ: 15/05/2015 mà nó cứ hiển thị 15/05/2015 12:00:00 AM. Ai biết chỉ em với ạ. Cảm ơn!

    • chuabiet (15/06/2015 11:15 am)

      @HungFIT: Bạn dùng hàm Convert

      CONVERT(VARCHAR(10),DATE,103)

      Date là tên trường ngày tháng của bạn. VD dữ liệu của bạn sẽ cho kết quả: 15/05/2015

  • chuabiet (15/06/2015 11:17 am)

    Bạn cũng có thể định dạng ngay trong C# mà.

  • Hoàng Anh (28/01/2016 11:41 am)

    Chào bạn. Mình VD có 2 bảng như sau:
    GIAOVIEN(Magv,HoTen)
    DETAI(MaDT,Magv)

    Giờ mình muốn chọn hiện thị giáo viên hướng dẫn cả hai đề tài có mã DT08 và DT09 thì phải dùng WHERE như thế nào bạn? Mình đã thử MaDT=’DT08′ AND MaDT=’DT09′ không được, và theo như mình thấy thì nó sai sai thế nào ấy

  • txtcntt (21/03/2016 10:56 pm)

    Mình đang có một vấn đề chưa được rõ lắm. Mong các bạn chia sẻ giúp đỡ nhé.
    Vấn đề là thế này.
    Mình vẫn chưa hiểu rõ thứ tự thực hiện các câu lệnh trong 1 query sql.
    Ví dụ: trong câu lệnh

    SELECT A.ID, A.NAME, 
    (SELECT B.NAME FROM B WHERE B.ID = A.ID) AS B_NAME
    FROM A WHERE A.TYPE = 1 ORDER BY A.CREATE_DATE

    Trong câu lệnh ở trên, thì thứ tự thực hiện các câu lệnh sẽ thế nào. câu lệnh nào thực hiện trước, câu lệnh nào thực hiện sau?
    luôn tiên, có phần mềm nào có thể visualizer các bước thực hiện 1 query thì cho mình xin luôn nhé.
    Minh xin chân thành cảm ơn trước và rất mong nhận được phản hồi của các bạn.

  • donghoa02 (18/04/2016 11:04 pm)

    MÌnh đang bị vấn đề lọc dữ liệu băng nhau nhu thế này thì được nhưng khi mình lấy dữ liệu khác nhau thì lại không được. Nhờ các bạn giúp dùm mình với
    SELECT distinct i.HOTEN, i.SOKCB, i.NGAYSINH
    FROM MICard m,ImportExcel i
    where
    m.FullName= i.HOTEN
    and m.Birthday =i.NGAYSINH
    and m.Sex = i.GIOITINH

  • donghoa02 (18/04/2016 11:09 pm)

    select DISTINCT i.HOTEN, i.NGAYSINH, i.GIOITINH, i.SOBHXH,i.SOKCB
    from ImportExcel i
    where i.HOTEN not in (select m.FullName from MICard m)
    and i.NGAYSINH not in (select m.Birthday from MICard m )

    dùng hàm này có chỗ nào bị sai không mấy bạn

  • Thìn (12/06/2016 11:50 am)

    Em xin hỏi mọi người, database rất nhiều table môn học, trong mỗi table có thể có hoặc không có column id (không phải table môn học nào cũng có số columns giống nhau), em muốn tìm những table môn học nào có column id và id chứa giá trị cho trước, ví dụ 123456789.
    Tìm những table có column id thì em dùng SQL sau:
    SELECT columns.table_name
    FROM information_schema.columns
    WHERE table_Schema=’monhoc0516′ and column_name like ‘id’

    Nhưng không biết lọc sao để được những table có column id và id chứa giá trị 123456789
    Em xin cảm ơn mọi người ạ.

Leave a Reply

Hướng dẫn: Để nhập mã T-SQL bạn dùng thẻ <pre lang="tsql"> và </pre>.
Ví dụ: <pre lang="tsql">SELECT * FROM MyTable</pre>