vuht2000
15-12-2010, 08:20
UNION và UNION ALL đều dùng để hợp hai tập bản ghi cùng cấu trúc, nhưng giữa hai mệnh đề có một khác biệt khá tinh tế: UNION loại bỏ các bản ghi trùng lặp trước khi trả lại kết quả, còn UNION ALL giữ lại tất cả các bản ghi từ hai tập ban đầu.
CREATE TABLE #test1(id INT, txt VARCHAR(10) )
CREATE TABLE #test2(id INT, txt VARCHAR(10) )
GO
INSERT #test1 VALUES(1,'a1')
INSERT #test1 VALUES(2,'a2')
INSERT #test2 VALUES(1,'a1') -- trùng với một bản ghi của #test1
INSERT #test2 VALUES(3,'a3')
-- UNION
SELECT * FROM #test1
UNION
SELECT * FROM #test2
id txt
----------- ----------
1 a1
2 a2
3 a3
(3 ROW(s) affected)
-- UNION ALL
SELECT * FROM #test1
UNION ALL
SELECT * FROM #test2
id txt
----------- ----------
1 a1
2 a2
1 a1
3 a3
(4 ROW(s) affected)
Như vậy ở câu lệnh dùng UNION, chỉ một bản ghi có id=1 được giữ lại, còn ở câu lệnh dùng UNION ALL tất cả các bản ghi đều được trả về. Có thể bạn sẽ nhận thấy UNION tiện lợi hơn vì nó làm thêm công việc “dọn dẹp” cho bạn, nhưng điều này cũng dẫn đến những ảnh hưởng nhất định về hiệu năng- nhất là khi câu lệnh chạy trên các tập dữ liệu lớn. Tiếp tục với hai bảng ở ví dụ trên:
TRUNCATE TABLE #test1
TRUNCATE TABLE #test2
DECLARE @i INT, @j INT
SET @i=1
WHILE @i<1000
BEGIN
SET @j = @i + 1000
INSERT #test1 VALUES(@i,'a'+CAST(@i AS VARCHAR(10)))
INSERT #test2 VALUES(@j,'a'+CAST(@j AS VARCHAR(10)))
SET @i=@i+1
END
-- query 1
SELECT * FROM #test1
UNION
SELECT * FROM #test2
-- query 2
SELECT * FROM #test1
UNION ALL
SELECT * FROM #test2
http://www.sqlviet.com/blog/wp-content/uploads/2010/12/union-vs-union-all.jpg
Hãy quan sát các bước trong kế hoạch thực thi từ phải qua trái, ta thấy cả hai lệnh đều bắt đầu bằng việc đọc cả hai bảng, sau đó ghép nối chúng lại (thao tác “Concatenation”). Nhưng với câu lệnh dùng UNION còn có một bước tiếp theo là “Sort (Distinct Sort)”. Đây chính là bước loại bỏ các bản ghi trùng, và bạn có thể thấy thao tác này khá tốn kém (chiếm tới 79% tổng chi phí của câu lệnh). Câu lệnh dùng UNION ALL không cần tới bước này, vì thế mà chi phí của nó nhỏ hơn đáng kể so với câu lệnh dùng UNION.
Một chi tiết đáng lưu ý nữa là, mặc dù hai bảng chứa dữ liệu hoàn toàn khác nhau và không có bản ghi nào trùng (và kết quả của hai câu lệnh hoàn toàn giống nhau), thế nhưng SQL Server vẫn áp dụng thao tác “Sort (Distinct Sort)” hòng tìm ra và loại bỏ các bản ghi trùng. SQL Server không thể biết được dữ liệu trong hai bảng là khác nhau, vì thế nó vẫn phải thực hiện đủ các bước cần thiết để trả về kết quả đúng theo yêu cầu của câu lệnh.
Bài học:
- Hãy thận trọng khi dùng UNION. Chỉ dùng với tập dữ liệu nhỏ và giữa chúng có khả năng chứa các bản ghi trùng.
- Khi bạn biết chắc chắn rằng các tập dữ liệu không trùng nhau, hãy dùng UNION ALL để giảm nhẹ bớt thao tác xử lý. Chẳng hạn khi cần hợp danh sách khách hàng từ Hà Nội và Sài Gòn (lưu ở hai bảng khác nhau), hoặc cần hợp các hóa đơn bán hàng của năm 2009 và 2010 (cũng lưu ở hai bảng khác nhau). Khi đó bạn đã biết theo logic của dữ liệu các tập này không thể trùng nhau. Do đó bạn có thể dùng UNION ALL và yên tâm rằng kết quả không khác gì so với UNION, trong khi hiệu năng được tăng lên rất nhiều.
CREATE TABLE #test1(id INT, txt VARCHAR(10) )
CREATE TABLE #test2(id INT, txt VARCHAR(10) )
GO
INSERT #test1 VALUES(1,'a1')
INSERT #test1 VALUES(2,'a2')
INSERT #test2 VALUES(1,'a1') -- trùng với một bản ghi của #test1
INSERT #test2 VALUES(3,'a3')
-- UNION
SELECT * FROM #test1
UNION
SELECT * FROM #test2
id txt
----------- ----------
1 a1
2 a2
3 a3
(3 ROW(s) affected)
-- UNION ALL
SELECT * FROM #test1
UNION ALL
SELECT * FROM #test2
id txt
----------- ----------
1 a1
2 a2
1 a1
3 a3
(4 ROW(s) affected)
Như vậy ở câu lệnh dùng UNION, chỉ một bản ghi có id=1 được giữ lại, còn ở câu lệnh dùng UNION ALL tất cả các bản ghi đều được trả về. Có thể bạn sẽ nhận thấy UNION tiện lợi hơn vì nó làm thêm công việc “dọn dẹp” cho bạn, nhưng điều này cũng dẫn đến những ảnh hưởng nhất định về hiệu năng- nhất là khi câu lệnh chạy trên các tập dữ liệu lớn. Tiếp tục với hai bảng ở ví dụ trên:
TRUNCATE TABLE #test1
TRUNCATE TABLE #test2
DECLARE @i INT, @j INT
SET @i=1
WHILE @i<1000
BEGIN
SET @j = @i + 1000
INSERT #test1 VALUES(@i,'a'+CAST(@i AS VARCHAR(10)))
INSERT #test2 VALUES(@j,'a'+CAST(@j AS VARCHAR(10)))
SET @i=@i+1
END
-- query 1
SELECT * FROM #test1
UNION
SELECT * FROM #test2
-- query 2
SELECT * FROM #test1
UNION ALL
SELECT * FROM #test2
http://www.sqlviet.com/blog/wp-content/uploads/2010/12/union-vs-union-all.jpg
Hãy quan sát các bước trong kế hoạch thực thi từ phải qua trái, ta thấy cả hai lệnh đều bắt đầu bằng việc đọc cả hai bảng, sau đó ghép nối chúng lại (thao tác “Concatenation”). Nhưng với câu lệnh dùng UNION còn có một bước tiếp theo là “Sort (Distinct Sort)”. Đây chính là bước loại bỏ các bản ghi trùng, và bạn có thể thấy thao tác này khá tốn kém (chiếm tới 79% tổng chi phí của câu lệnh). Câu lệnh dùng UNION ALL không cần tới bước này, vì thế mà chi phí của nó nhỏ hơn đáng kể so với câu lệnh dùng UNION.
Một chi tiết đáng lưu ý nữa là, mặc dù hai bảng chứa dữ liệu hoàn toàn khác nhau và không có bản ghi nào trùng (và kết quả của hai câu lệnh hoàn toàn giống nhau), thế nhưng SQL Server vẫn áp dụng thao tác “Sort (Distinct Sort)” hòng tìm ra và loại bỏ các bản ghi trùng. SQL Server không thể biết được dữ liệu trong hai bảng là khác nhau, vì thế nó vẫn phải thực hiện đủ các bước cần thiết để trả về kết quả đúng theo yêu cầu của câu lệnh.
Bài học:
- Hãy thận trọng khi dùng UNION. Chỉ dùng với tập dữ liệu nhỏ và giữa chúng có khả năng chứa các bản ghi trùng.
- Khi bạn biết chắc chắn rằng các tập dữ liệu không trùng nhau, hãy dùng UNION ALL để giảm nhẹ bớt thao tác xử lý. Chẳng hạn khi cần hợp danh sách khách hàng từ Hà Nội và Sài Gòn (lưu ở hai bảng khác nhau), hoặc cần hợp các hóa đơn bán hàng của năm 2009 và 2010 (cũng lưu ở hai bảng khác nhau). Khi đó bạn đã biết theo logic của dữ liệu các tập này không thể trùng nhau. Do đó bạn có thể dùng UNION ALL và yên tâm rằng kết quả không khác gì so với UNION, trong khi hiệu năng được tăng lên rất nhiều.