Tối ưu hiệu năng SQL phần 5: RANDOM

Cái series Tối ưu hiệu năng với SQL

Tối ưu hiệu năng SQL (phần 1): DATETIME

Tối ưu hiệu năng SQL (phần 2): DELETE

Tối ưu hiệu năng SQL (phần 3): Exists, IN và người thứ ba

Tối ưu hiệu năng SQL (phần 4): Trigger

cũng đã “lạc trôi” được một thời gian khá là ngắn nên hôm nay nhân dịp đầu xuân năm mới, mình quyết định mang nó trở lại đến anh em đồng bào cả nước để nâng cao thêm trình độ tin học về SQL cho con dân Đại Việt :v

khi-phach-tre

Chủ đề ngày hôm nay đã được mình cất công sưu tầm và copy ở khá nhiều nguồn tin cậy đó là việc sinh random 1 số bất kì trong Sql Server. Nghe thì có vẽ đơn giản đấy nhưng ứng dụng của việc này thì khá là lớn lao đấy nhé. Áp dụng cái này có thể giúp anh em sinh dữ liệu giả khá là hay ho đấy chứ 😕

Thôi nghe quảng cáo đủ rồi. Bạn muốn xem code hay bạn muốn xem code đẹp hãy đến blog xanh :3

Nói về bản thân SqlServer thì nội tại cũng đã có hỗ trợ 1 hàm dành riêng cho việc sinh random 1 sốtùy ý là hàm RAND(). Chức năng nhiệm vụ của thằng cu này đó là mỗi lần ae F5, nó sẽ đẻ ra 1 số dạng float từ 0-1 kiểu như  0.932251125372571, 0.88339288472542 hay  0.312594897717717.

WTF random thế thì làm đc cái quần què gì 😐 Số sinh ra xấu mù thế thì làm ăn gì 😦 Ít ra cũng phải tứ quý hay số tiến hoặc tài lộc gì chứ. =))

tra-gop-sim-tu-quy-tai-simsovip-20160919091038

Đùa vui thế thôi chứ nói thật thì đúng là nếu dùng số dạng kia thì hơi khó làm ăn rồi :3 Mục đích khi sinh random số thường là sinh 1 số nguyên dương  trong  1 khoảng cố định nào đó hơn là những con số xấu xí kia :3

Nếu vậy thì phải làm thế nào đây? Số thì lẻ kinh khủng như vậy thì tất nhiên phải áp dụng làm tròn rồi. Giới thiệu anh em hàm ROUND có tác dụng làm tròn số sau dấu phân cách nhé. Và đây là 1 ví dụ cho việc sinh random 1 số từ 1-100 này

DECLARE @Random INT;

DECLARE @Upper INT;

DECLARE @Lower INT

SET @Lower = 1

SET @Upper = 100 —- The highest random number

SELECT @Random = ROUND(((@Upper – @Lower -1) * RAND() + @Lower), 0)

 

Không dùng hàm ROUND thì còn có thể sử dụng hàm CONVERT để chuyển về kiểu INT là kiểu số dương cũng được. Dưới đây là 1 cách viế bằng hàm CONVERT để sinh random từ 20 đến 50 với cách viết ngắn gọn hơn so với ví dụ trên

SELECT 20 + CONVERT(INT, (50-20+1)*RAND())

Tèn ten thế là xong rồi nhé. Bài viết xin được tạm dừng tại  đây :v Chủ đề về random đến đây là kết thúc. Xin chào và kéo xuống dưới đê (bye)

d4c22489baf28fe5261029213dbd7ef3

Chào mừng anh em đến với phần tiếp theo của chuyên mục random :v Không nói về sinh random nữa mà là lấy random data từ 1 cái bảng nhé. Cách làm sẽ là thế nào đây?

hqdefault

1 tính năng của SQL cho việc đó là sử dụng NEWID()

SELECT TOP 10 PERCENT *

FROM Table1

ORDER BY NEWID()

NEWID dùng để sinh ra 1 GUID (Anh em nào chưa biết thì có thể đọc bài viết  cũ về thuật toán sinh ID  Bông tuyết cô độc). Đấy lệnh nó đơn giản thế thôi chứ không có gì phức tạp lắm đúng không :v. Lệnh ORDER BY là lệnh dùng để sắp xếp dữ liệu nên nói chung là tốn tài nguyên vì thế anh em đặc biệt lưu ý sử dụng TOP để hạn chế dữ liệu cho thằng em SQL làm việc đỡ vất vả.

Yeah giờ thì kết bài được rồi chứ nhỉ. Tạm biệt anh em phát nữa và hẹn gặp trong phần 6 của series này :3

131331a6d81241ebf74a92653555e93c1a64b8d47568ddc918146065f3c091fc

Đùa đấy chào mừn anh em đến phần tiếp của việc random dữ liệu =))

Việc áp dụng ORDER BY theo NEWID có thể rất là tiện lợi tuy nhiên đối với bảng dữ liệu lớn thì vấn đề bắt đầu xảy ra rồi đấy. Nó chạy chậm kinh khủng 😐 Đến đây thì anh em cần 1 giải pháp khác cho việc này không sử dụng cái NEWID() nữa mà thay vào đó sẽ dùng đủ các thứ lung tung beng đó là

SELECT * FROM Table1  WHERE (ABS(CAST(  (BINARY_CHECKSUM(*) *  RAND()) as int)) % 100) < 10

Sặc sao nhìn khó thế 😐 Thôi mình sẽ giải thích cho anh em thì đại loại ý tưởng của nó là như sau

BINARY_CHECKSUM là hàm sẽ trả lại kết quả checksum dựa trên tất cả các cột trong bảng (Anh em nào chưa biết CHECKSUM không nhỉ? Với anh em đồng dâm thì có lẽ khá quen thuộc với việc down phim và kiểm tra mã MD5 ấy nhỉ :3 Chi tiết về cái này anh em có thể tham chiếu theo link sau http://sinhvienit.net/forum/md5-checksum-la-gi.7626.html). Giá trị này dùng để kiểm tra xem có cột nào thay đổi dữ liệu hay không tuy nhiên ở cái lệnh này thì mục đích là để tạo ra 1 số random cho mỗi dòng dữ liệu. Mô tả ngắn gọn là nếu mỗi lần Excute mà dòng dữ liệu không thay đổi thì nó sẽ trả lại giá trị giống nhau dẫn đến việc query ra dữ liệu sẽ không có tính random nữa 😐

Để khắc phục vấn đề này, tại hạ mạo muội bổ sung thêm hàm RAND. 2 thằng này mà lưỡng long nhất thể với nhau sẽ đảm bảo ra được 1 kết quả duy nhất rồi :3

winner-luong-long-nhat-the-31054-1474641345-57e53dc199401

À quên rằng kết quả của 2 thằng này sẽ trả về 1 số float có thể âm nên cần sử dụng thêm hàm ABSCAST để xử lý vấn đề được vấn đề trên. Đấy đại loại nó là thế đấy. Phức tạp phết nhỉ

Nếu anh  không muốn sử dụng CHECKSUM cho tất cả các cột thì có thể chỉ đích thân 1 cột cũng được. Lưu ý là cột đó nên là uniqueindex.  Anh em có thể bổ sung thêm NEWID như 1 cột mới để chắc chắn trả lại 1 kết quả random và nếu thế thì không cần sử dụng RAND như hàm ví dụ trên nữa. Anh em tham khảo câu lệnh bên dưới

SELECT * FROM Table1  WHERE (ABS(CAST(  (BINARY_CHECKSUM  (keycol1, NEWID())) as int))  % 100) < 10

Còn giờ thì là hết thật rồi đấy. Không lừa anh em nữa :v Mình đi đây

nguyen-tan-dung-org-thanh-giong-6

Advertisements

6 thoughts on “Tối ưu hiệu năng SQL phần 5: RANDOM

  1. Pingback: Tối ưu SQL (phần 9): tính tổng các dòng | Code, code and more code

  2. Pingback: Tối ưu SQL phần 8: 1 câu chuyện đơn gian về việc cộng chuỗi | Code, code and more code

  3. Pingback: Tối ưu SQL (phần 7): Chuyện ngắn của ID | Code, code and more code

  4. Pingback: Tối ưu hiệu năng SQL (phần ): Update hay Merge? | Code, code and more code

  5. SELECT TOP 10 PERCENT * FROM Table1 ORDER BY NEWID()
    ———
    Lệnh ORDER BY là lệnh dùng để sắp xếp dữ liệu nên nói chung là tốn tài nguyên vì thế anh em đặc biệt lưu ý sử dụng TOP để hạn chế dữ liệu cho thằng em SQL làm việc đỡ vất vả.
    ———
    Theo mình thì TOP được chạy sau cùng nên việc sắp xếp bởi ORDER BY đã được thực hiện trước rồi.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s