Tối ưu hóa tải trong tác vụ “Cân bằng kho” bằng cách sử dụng phân vùng trong SQL Server. Hướng dẫn tham khảo MySQL Pl sql phần dư của phép chia

Cú pháp SQL 2003 được hỗ trợ trên tất cả các nền tảng.

SÀN (biểu thức)

Nếu bạn truyền một số dương cho một hàm, hành động của hàm đó là xóa mọi thứ sau dấu thập phân.

CHỌN TẦNG(100.1) TỪ kép;

Tuy nhiên, hãy nhớ rằng đối với số âm, làm tròn xuống có nghĩa là tăng giá trị tuyệt đối.

CHỌN TẦNG(-100.1) TỪ kép;

Để có được tác dụng ngược lại của hàm FLOOR, hãy sử dụng hàm CEIL.

LN

Hàm LN trả về logarit tự nhiên của một số, tức là lũy thừa của hằng số toán học e (xấp xỉ 2,718281) phải được nâng lên để thu được số đã cho.

Cú pháp SQL 2003

LN (biểu thức)

DB2, Oracle, PostgreSQL

Nền tảng DB2, Oracle và PostgreSQL hỗ trợ hàm LN theo cú pháp SQL 2003 và PostgreSQL cũng hỗ trợ hàm LOG như một từ đồng nghĩa với LN.

Máy chủ MySQL và SQL

MySQL và SQL Server có chức năng tính logarit tự nhiên riêng - LOG.

LOG (biểu thức)

Ví dụ Oracle sau đây tính logarit tự nhiên của một số gần đúng với một hằng số toán học.

CHỌN LN(2.718281) TỪ kép;

Để thực hiện thao tác ngược lại, hãy sử dụng chức năng EXP.

MOD

Hàm MOD trả về số dư khi chia số bị chia cho số chia. Tất cả các nền tảng đều hỗ trợ cú pháp câu lệnh MOD tiêu chuẩn SQL 2003.

Cú pháp SQL 2003

MOD (cổ tức, số chia)

Hàm MOD tiêu chuẩn được thiết kế để lấy số dư khi chia số bị chia cho số chia. Nếu số chia bằng 0 thì cổ tức được trả về.

Phần sau đây cho thấy cách bạn có thể sử dụng hàm MOD trong câu lệnh SELECT.

CHỌN MOD(12, 5) TỪ SỐ 2;

CHỨC VỤ

Hàm POSITION trả về một số nguyên cho biết vị trí bắt đầu của chuỗi trong chuỗi tìm kiếm.

Cú pháp SQL 2003

VỊ TRÍ(dòng 1 TRONG dòng2)

Hàm POSITION tiêu chuẩn được thiết kế để lấy vị trí xuất hiện đầu tiên của một chuỗi (chuỗi!) nhất định trong chuỗi tìm kiếm (chuỗi!). Hàm trả về 0 nếu chuỗi! không xuất hiện trong chuỗi!, và NULL - nếu bất kỳ đối số nào là NULL.

DB2

DB2 có chức năng tương đương, POSSTR.

MySQL

Nền tảng MySQL hỗ trợ chức năng POSITION theo tiêu chuẩn SQL 2003.

QUYỀN LỰC

Hàm POWER được sử dụng để nâng một số lên lũy thừa xác định.

Cú pháp SQL 2003

POWER (cơ sở, chỉ báo)

Kết quả của việc thực hiện chức năng này là cơ số được nâng lên lũy thừa được xác định bởi chỉ báo. Nếu cơ số âm thì số mũ phải là số nguyên.

DB2, Oracle, PostgreSQL và SQL Server

Tất cả các nhà cung cấp này đều hỗ trợ cú pháp SQL 2003.

Lời tiên tri

Oracle có hàm INSTR tương đương.

PostgreSQL

Nền tảng PostgreSQL hỗ trợ chức năng POSITION theo tiêu chuẩn SQL 2003.

MySQL

Nền tảng MySQL hỗ trợ chức năng này chứ không phải từ khóa POW.

P0W (cơ sở, chỉ báo)

Việc nâng một số dương lên lũy thừa là điều khá hiển nhiên.

CHỌN ĐIỆN (10.3) TỪ kép;

Bất kỳ số nào lũy thừa 0 đều bằng 1.

CHỌN ĐIỆN (0,0) TỪ kép;

Số mũ âm di chuyển dấu thập phân sang trái.

CHỌN ĐIỆN (10, -3) TỪ kép;

LOẠI

Hàm SQRT trả về căn bậc hai của một số.

Cú pháp SQL 2003

Tất cả các nền tảng đều hỗ trợ cú pháp SQL 2003.

SẮP XẾP (biểu thức)

CHỌN SQRT(100) TỪ kép;

XÔ CHIỀU RỘNG

Hàm WIDTH BUCKET gán giá trị cho các cột của biểu đồ có chiều rộng bằng nhau.

Cú pháp SQL 2003

Trong cú pháp sau đây, biểu thức biểu thị một giá trị được gán cho cột biểu đồ. Thông thường, biểu thức dựa trên một hoặc nhiều cột trong bảng được truy vấn trả về.

BỘ RỘNG (biểu thức, tối thiểu, tối đa, biểu đồ_bars)

Tham số cột biểu đồ hiển thị số lượng cột biểu đồ được tạo, từ tối thiểu đến tối đa. Giá trị của tham số tối thiểu được bao gồm trong phạm vi, nhưng không bao gồm giá trị của tham số tối đa. Giá trị của biểu thức được gán cho một trong các cột biểu đồ, sau đó hàm trả về số của cột biểu đồ tương ứng. Nếu biểu thức không nằm trong phạm vi cột được chỉ định, hàm sẽ trả về 0 hoặc max + 1, tùy thuộc vào việc biểu thức đó nhỏ hơn min hay lớn hơn hoặc bằng max.

Ví dụ sau phân phối các giá trị số nguyên từ 1 đến 10 giữa hai cột biểu đồ.

Ví dụ tiếp theo thú vị hơn. 11 giá trị từ 1 đến 10 được phân bổ trên ba cột của biểu đồ để minh họa sự khác biệt giữa giá trị tối thiểu, được bao gồm trong phạm vi và giá trị tối đa, không được bao gồm trong phạm vi.

CHỌN x, WIDTH_BUCKET(x, 1.10.3) TỪ trục;

Đặc biệt chú ý đến các kết quả cX =, X= 9,9 và X- 10. Giá trị đầu vào của tham số, tức là trong ví dụ này - 1, rơi vào cột đầu tiên, biểu thị giới hạn dưới của phạm vi, vì cột Số 1 được định nghĩa là x >= phút. Tuy nhiên, giá trị đầu vào tối đa không được bao gồm trong cột tối đa. Trong ví dụ này, số 10 rơi vào cột tràn, được đánh số max + 1. Giá trị 9,9 rơi vào cột số 3 và điều này minh họa quy tắc giới hạn trên của phạm vi được xác định là x< max.

Bài viết này cung cấp giải pháp tối ưu hóa cho bài toán tính toán số dư kho bằng Transact SQL. Đã áp dụng: phân vùng các bảng và các khung nhìn cụ thể hóa.

Xây dựng vấn đề

Tính toán số dư theo ngày giờ đã chọn (chính xác theo giờ) cho tất cả/bất kỳ kho nào của từng sản phẩm. Để phân tích, cần tạo một đối tượng (hàm, bảng, dạng xem) với sự trợ giúp của đối tượng đó, đối với phạm vi ngày đã chọn, hiển thị dữ liệu của bảng gốc và cột tính toán bổ sung cho phạm vi ngày đã chọn cho tất cả các kho và các sản phẩm.

Những tính toán này dự kiến ​​sẽ chạy theo lịch trình với các phạm vi ngày khác nhau và sẽ chạy trong khoảng thời gian có thể chấp nhận được. Những thứ kia. nếu cần hiển thị bảng có số dư trong giờ hoặc ngày cuối cùng thì thời gian thực hiện phải nhanh nhất có thể, cũng như nếu cần hiển thị cùng một dữ liệu trong 3 năm qua cho lần tải tiếp theo vào cơ sở dữ liệu phân tích.

Chi tiết kỹ thuật. Bản thân bảng:

Tạo bảng dbo.Turnover (id int khóa chính nhận dạng, dt datetime không null, ProductID int không null, StorehouseID int không null, Kiểm tra thao tác Smallint không null (Thao tác trong (-1,1)), -- +1 đến kho , -1 chi phí từ kho Số lượng (20,2) không rỗng, Chi phí tiền không rỗng)

Dt - Ngày, giờ nhập/xóa kho.
ID sản phẩm - Sản phẩm
StorehouseID - nhà kho
Hoạt động - 2 giá trị vào hoặc ra
Số lượng - số lượng sản phẩm trong kho. Nó có thể là thật nếu sản phẩm không ở dạng miếng, nhưng, ví dụ, tính bằng kilôgam.
Giá thành - giá thành của lô sản phẩm.

Nghiên cứu vấn đề

Nếu object_id("dbo.Turnover","U") không phải là null, hãy thả bảng dbo.Turnover; đi theo thời gian như (chọn 1 id liên kết tất cả chọn id+1 từ những thời điểm mà id< 10*365*24*60 -- 10 лет * 365 дней * 24 часа * 60 минут = столько минут в 10 лет) , storehouse as (select 1 id union all select id+1 from storehouse where id < 100 -- количество складов) select identity(int,1,1) id, dateadd(minute, t.id, convert(datetime,"20060101",120)) dt, 1+abs(convert(int,convert(binary(4),newid()))%1000) ProductID, -- 1000 - количество разных продуктов s.id StorehouseID, case when abs(convert(int,convert(binary(4),newid()))%3) in (0,1) then 1 else -1 end Operation, -- какой то приход и расход, из случайных сделаем из 3х вариантов 2 приход 1 расход 1+abs(convert(int,convert(binary(4),newid()))%100) Quantity into dbo.Turnover from times t cross join storehouse s option(maxrecursion 0); go --- 15 min alter table dbo.Turnover alter column id int not null go alter table dbo.Turnover add constraint pk_turnover primary key (id) with(data_compression=page) go -- 6 min
Trên PC của tôi có ổ SSD, tập lệnh này chạy trong khoảng 22 phút và kích thước bảng chiếm khoảng 8GB trên ổ cứng. Bạn có thể giảm số năm và số lượng kho để giảm thời gian tạo và điền bảng. Nhưng tôi khuyên bạn nên để lại một lượng thích hợp để đánh giá các gói truy vấn, ít nhất là 1-2 gigabyte.

Hãy nhóm dữ liệu lên đến một giờ

Tiếp theo, chúng ta cần nhóm số lượng theo sản phẩm trong kho trong khoảng thời gian đang nghiên cứu; khi xây dựng bài toán, đây là một giờ (tối đa một phút, tối đa 15 phút, một ngày đều có thể. Nhưng rõ ràng là có thể). , tối đa mili giây, hầu như không ai cần báo cáo). Để so sánh trong phiên (cửa sổ) nơi chúng tôi thực hiện các truy vấn của mình, chúng tôi sẽ thực thi lệnh - đặt thời gian thống kê trên;. Tiếp theo, chúng tôi tự thực hiện các truy vấn và xem xét các kế hoạch truy vấn:

Chọn top(1000) Convert(datetime,convert(varchar(13),dt,120)+":00",120) làm dt, -- làm tròn đến giờ gần nhất ProductID, StorehouseID, sum(Operation*Quantity) làm Số lượng từ dbo .Nhóm doanh thu theo Convert(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID

Chi phí yêu cầu - 12406
(hàng được xử lý: 1000)
Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 2096594 ms, thời gian đã trôi qua = 321797 ms.

Chọn top(1000) Convert(datetime,convert(varchar(13),dt,120)+":00",120) làm dt, -- làm tròn đến giờ gần nhất ProductID, StorehouseID, sum(Operation*Quantity) làm Số lượng , tổng (tổng(Operation*Quantity)) trên (phân vùng theo StorehouseID, thứ tự ProductID theo Convert(datetime,convert(varchar(13),dt,120)+":00",120)) dưới dạng Số dư từ nhóm dbo.Turnover bằng cách chuyển đổi (datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID

Chi phí yêu cầu - 19329
(hàng được xử lý: 1000)
Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 2413155 ms, thời gian đã trôi qua = 344631 ms.

Mọi thứ ở đây khá đơn giản. Bản thân truy vấn, không có tổng số đang chạy, có thể được tối ưu hóa bằng chế độ xem cụ thể hóa (chế độ xem chỉ mục). Để xây dựng chế độ xem cụ thể hóa, những gì được tính tổng không được có giá trị NULL, chúng tôi tính tổng (Thao tác*Số lượng) hoặc đặt mỗi trường KHÔNG NULL hoặc thêm isnull/kết hợp vào biểu thức. Tôi đề nghị tạo một chế độ xem cụ thể hóa.

Tạo chế độ xem dbo.TurnoverHour với liên kết lược đồ dưới dạng select Convert(datetime,convert(varchar(13),dt,120)+":00",120) as dt, -- làm tròn đến giờ gần nhất ProductID, StorehouseID, sum(isnull( Hoạt động* Số lượng, 0)) dưới dạng Số lượng, count_big(*) qty từ dbo. Nhóm doanh thu theo chuyển đổi (datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID đi
Và xây dựng một chỉ mục nhóm trên đó. Trong chỉ mục, chúng ta chỉ ra thứ tự của các trường theo cách tương tự như khi nhóm (đối với việc nhóm, thứ tự không quá quan trọng, điều quan trọng là tất cả các trường nhóm đều có trong chỉ mục) và tổng tích lũy (thứ tự là quan trọng ở đây - đầu tiên là những gì được phân vùng theo, sau đó là những gì theo thứ tự):

Tạo chỉ mục được nhóm duy nhất uix_TurnoverHour trên dbo.TurnoverHour (StorehouseID, ProductID, dt) với (data_compression=page) - 19 phút

Bây giờ, sau khi xây dựng chỉ mục được nhóm, chúng ta có thể thực hiện lại các truy vấn bằng cách thay đổi tổng hợp như trong dạng xem:

Chọn top(1000) Convert(datetime,convert(varchar(13),dt,120)+":00",120) làm dt, -- làm tròn đến giờ gần nhất ProductID, StorehouseID, sum(isnull(Operation*Quantity, 0) ) dưới dạng Số lượng từ dbo. Nhóm doanh thu theo chuyển đổi(datetime,convert(varchar(13),dt,120)+":00",120), ProductID, StorehouseID chọn top(1000) chuyển đổi(datetime,convert(varchar (13 ),dt,120)+":00",120) dưới dạng dt, -- làm tròn đến giờ gần nhất ProductID, StorehouseID, sum(isnull(Operation*Quantity,0)) dưới dạng Số lượng, sum(sum(isnull( Operation*Quantity, 0))) over (phân vùng theo StorehouseID, ProductID sắp xếp theo chuyển đổi (datetime,convert(varchar(13),dt,120)+":00",120)) dưới dạng Số dư từ dbo. Nhóm doanh thu theo chuyển đổi (datetime,chuyển đổi (varchar(13),dt,120)+":00",120), ProductID, StorehouseID

Kế hoạch truy vấn đã trở thành:

Chi phí 0,008

Chi phí 0,01

Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 31 ms, thời gian đã trôi qua = 116 ms.
(hàng được xử lý: 1000)
Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 0 ms, thời gian đã trôi qua = 151 ms.

Đây có thể là sự kết thúc quá trình tối ưu hóa của chúng tôi, nếu không phải vì thực tế là chúng tôi thường không cần toàn bộ bảng (chế độ xem) mà là một phần của bảng cho phạm vi đã chọn.

Số dư tạm thời

Do đó, chúng ta cần thực hiện nhanh truy vấn sau:

Đặt định dạng ngày ymd; khai báo @start datetime = "2015-01-02", @finish datetime = "2015-01-03" chọn * từ (chọn dt, StorehouseID, ProductId, Số lượng, tổng (Số lượng) qua (phân vùng theo StorehouseID, sắp xếp ProductID theo dt) dưới dạng Số dư từ dbo.TurnoverHour với (noexpand) trong đó dt<= @finish) as tmp where dt >= @bắt đầu

Đầu ra của dữ liệu xem và cân bằng cụ thể hóa cho từng sản phẩm trong kho kể từ ngày được làm tròn đến giờ gần nhất. Để tính số dư, bạn cần tổng hợp tất cả số lượng tính đến ngày cuối cùng được chỉ định (@finish) ngay từ đầu (từ số dư bằng 0), sau đó cắt bỏ dữ liệu sau tham số bắt đầu trong tập kết quả tổng hợp.

Rõ ràng ở đây số dư được tính toán trung gian sẽ hữu ích. Ví dụ: vào ngày 1 hàng tháng hoặc vào Chủ nhật hàng tuần. Có số dư như vậy, nhiệm vụ tóm lại là bạn sẽ cần tổng hợp các số dư đã tính toán trước đó và tính số dư không phải ngay từ đầu mà từ ngày tính toán cuối cùng. Để thử nghiệm và so sánh, hãy xây dựng một chỉ mục không được nhóm bổ sung theo ngày:

Tạo chỉ mục ix_dt trên dbo.TurnoverHour (dt) bao gồm (Số lượng) với(data_compression=page); --7 phút Và yêu cầu của chúng ta sẽ như sau: set dateformat ymd; khai báo @start datetime = "2015-01-02", @finish datetime = "2015-01-03" khai báo @start_month datetime = Convert(datetime,convert(varchar(9),@start,120)+"1", 120) chọn * từ (chọn dt, StorehouseID, ProductId, Số lượng, tổng (Số lượng) trên (phân vùng theo StorehouseID, thứ tự ProductID theo dt) làm Số dư từ dbo.TurnoverHour với (noexpand) trong đó dt nằm giữa @start_month và @finish) làm tmp ở đâu dt >
Nói chung, truy vấn này, ngay cả khi có chỉ mục theo ngày bao gồm hoàn toàn tất cả các trường bị ảnh hưởng trong truy vấn, sẽ chọn chỉ mục và quét theo cụm của chúng tôi. Và không tìm kiếm theo ngày rồi sắp xếp. Tôi khuyên bạn nên chạy 2 truy vấn sau và so sánh những gì chúng tôi nhận được, sau đó hãy phân tích truy vấn nào tốt hơn:

Đặt định dạng ngày ymd; khai báo @start datetime = "2015-01-02", @finish datetime = "2015-01-03" khai báo @start_month datetime = Convert(datetime,convert(varchar(9),@start,120)+"1", 120) chọn * từ (chọn dt, StorehouseID, ProductId, Số lượng, tổng (Số lượng) trên (phân vùng theo StorehouseID, thứ tự ProductID theo dt) làm Số dư từ dbo.TurnoverHour với (noexpand) trong đó dt nằm giữa @start_month và @finish) làm tmp trong đó dt >= @bắt đầu đặt hàng theo StorehouseID, ProductID, dt chọn * từ (chọn dt, StorehouseID, ProductId, Số lượng, tổng (Số lượng) trên (phân vùng theo StorehouseID, thứ tự ProductID theo dt) dưới dạng Số dư từ dbo.TurnoverHour với( noexpand,index=ix_dt) trong đó dt giữa @start_month và @finish) là tmp trong đó dt >= @start đặt hàng theo StorehouseID, ProductID, dt

Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 33860 ms, thời gian đã trôi qua = 24247 ms.

(hàng được xử lý: 145608)

(hàng được xử lý: 1)

Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 6374 ms, thời gian đã trôi qua = 1718 ms.
Thời gian CPU = 0 ms, thời gian đã trôi qua = 0 ms.

Chi phí của truy vấn đầu tiên với chỉ mục được nhóm được chọn tự động = 2752, nhưng chi phí với chỉ mục theo ngày truy vấn = 3119.

Dù vậy, ở đây chúng tôi yêu cầu hai nhiệm vụ từ chỉ mục: sắp xếp và lựa chọn phạm vi. Chúng tôi không thể giải quyết vấn đề này chỉ với một chỉ mục có sẵn. Trong ví dụ này, phạm vi dữ liệu chỉ trong 1 ngày, nhưng nếu có khoảng thời gian dài hơn nhưng không phải toàn bộ khoảng thời gian, chẳng hạn như trong 2 tháng, thì việc tìm kiếm theo chỉ mục chắc chắn sẽ không hiệu quả do chi phí sắp xếp.

Ở đây, từ những giải pháp tối ưu có thể nhìn thấy được, tôi thấy:

1. Tạo trường được tính toán Năm-Tháng và tạo chỉ mục (Năm-Tháng, các trường khác của chỉ mục được nhóm). Trong trường hợp dt nằm giữa @start_month và kết thúc, hãy thay thế nó bằng Year-Month=@month, sau đó áp dụng bộ lọc cho các ngày được yêu cầu.
2. Chỉ mục được lọc - bản thân chỉ mục này giống như chỉ mục cụm, nhưng được lọc theo ngày, cho tháng mong muốn. Và tạo tổng cộng nhiều chỉ mục như vậy mà chúng tôi có trong nhiều tháng. Ý tưởng này gần giống như một giải pháp, nhưng ở đây nếu phạm vi điều kiện bao gồm 2 chỉ mục được lọc thì sẽ cần có kết nối và việc sắp xếp trong tương lai vẫn là điều không thể tránh khỏi.
3. Chúng tôi phân vùng chỉ mục được nhóm để mỗi phần chỉ chứa dữ liệu trong một tháng.

Chúng tôi phân vùng chỉ mục nhóm của chế độ xem. Trước hết, hãy xóa tất cả các chỉ mục khỏi chế độ xem:

Thả chỉ mục ix_dt trên dbo.TurnoverHour; thả chỉ mục uix_TurnoverHour trên dbo.TurnoverHour;
Và hãy tạo một hàm và sơ đồ phân vùng:

Đặt định dạng ngày ymd; tạo hàm phân vùng pf_TurnoverHour(datetime) làm phạm vi phù hợp cho các giá trị ("2006-01-01", "2006-02-01", "2006-03-01", "2006-04-01", "2006- 05- 01", "2006-06-01", "2006-07-01", "2006-08-01", "2006-09-01", "2006-10-01", "2006-11- 01", "2006-12-01", "2007-01-01", "2007-02-01", "2007-03-01", "2007-04-01", "2007-05-01" , " 2007-06-01", "2007-07-01", "2007-08-01", "2007-09-01", "2007-10-01", "2007-11-01", " 2007- 12-01", "2008-01-01", "2008-02-01", "2008-03-01", "2008-04-01", "2008-05-01", "2008- 06- 01", "2008-07-01", "2008-08-01", "2008-09-01", "2008-10-01", "2008-11-01", "2008-12- 01", "2009-01-01", "2009-02-01", "2009-03-01", "2009-04-01", "2009-05-01", "2009-06-01" , " 2009-07-01", "2009-08-01", "2009-09-01", "2009-10-01", "2009-11-01", "2009-12-01", " 2010- 01-01", "2010-02-01", "2010-03-01", "2010-04-01", "2010-05-01", "2010-06-01", "2010- 07- 01", "2010-08-01", "2010-09-01", "2010-10-01", "2010-11-01", "2010-12-01", "2011-01- 01", "2011-02-01", "2011-03-01", "2011-04-01", "2011-05-01", "2011-06-01", "2011-07-01" , " 2011-08-01", "2011-09-01", "2011-10-01", "2011-11-01", "2011-12-01", "2012-01-01", " 2012-02-01", "2012-03-01", "2012-04-01", "2012-05-01", "2012-06-01", "2012-07-01", "2012- 08- 01", "2012-09-01", "2012-10-01", "2012-11-01", "2012-12-01", "2013-01-01", "2013-02- 01", "2013-03-01", "2013-04-01", "2013-05-01", "2013-06-01", "2013-07-01", "2013-08-01" , " 2013-09-01", "2013-10-01", "2013-11-01", "2013-12-01", "2014-01-01", "2014-02-01", " 2014-03-01", "2014-04-01", "2014-05-01", "2014-06-01", "2014-07-01", "2014-08-01", "2014- 09- 01", "2014-10-01", "2014-11-01", "2014-12-01", "2015-01-01", "2015-02-01", "2015-03- 01", "2015-04-01", "2015-05-01", "2015-06-01", "2015-07-01", "2015-08-01", "2015-09-01" , " 2015-10-01", "2015-11-01", "2015-12-01", "2016-01-01", "2016-02-01", "2016-03-01", " 2016-04-01", "2016-05-01", "2016-06-01", "2016-07-01", "2016-08-01", "2016-09-01", "2016- 10- 01", "2016-11-01", "2016-12-01", "2017-01-01", "2017-02-01", "2017-03-01", "2017-04- 01", "2017-05-01", "2017-06-01", "2017-07-01", "2017-08-01", "2017-09-01", "2017-10-01" , " 2017-11-01", "2017-12-01", "2018-01-01", "2018-02-01", "2018-03-01", "2018-04-01", " 2018- 05-01", "2018-06-01", "2018-07-01", "2018-08-01", "2018-09-01", "2018-10-01", "2018- 11- 01", "2018-12-01", "2019-01-01", "2019-02-01", "2019-03-01", "2019-04-01", "2019-05- 01", "2019-06-01", "2019-07-01", "2019-08-01", "2019-09-01", "2019-10-01", "2019-11-01" , "2019-12-01"); đi tạo sơ đồ phân vùng ps_TurnoverHour dưới dạng phân vùng pf_TurnoverHour tất cả thành (); à, chỉ mục được nhóm mà chúng ta đã biết chỉ có trong sơ đồ phân vùng đã tạo: tạo chỉ mục được nhóm duy nhất uix_TurnoverHour trên dbo. Doanh thuHour (StorehouseID, ProductID, dt) với (data_compression=page) trên ps_TurnoverHour(dt); --- 19 phút Và bây giờ hãy xem chúng ta có gì. Bản thân yêu cầu: set dateformat ymd; khai báo @start datetime = "2015-01-02", @finish datetime = "2015-01-03" khai báo @start_month datetime = Convert(datetime,convert(varchar(9),@start,120)+"1", 120) chọn * từ (chọn dt, StorehouseID, ProductId, Số lượng, tổng (Số lượng) trên (phân vùng theo StorehouseID, thứ tự ProductID theo dt) làm Số dư từ dbo.TurnoverHour với (noexpand) trong đó dt nằm giữa @start_month và @finish) làm tmp trong đó dt >= @bắt đầu đặt hàng theo StorehouseID, ProductID, dt option(recompile);

Thời gian hoạt động của máy chủ SQL:
Thời gian CPU = 7860 ms, thời gian đã trôi qua = 1725 ms.
Thời gian biên dịch và phân tích cú pháp SQL Server:
Thời gian CPU = 0 ms, thời gian đã trôi qua = 0 ms.
Chi phí gói truy vấn = 9,4

Đây là một nhiệm vụ phổ biến khác. Nguyên tắc cơ bản là tích lũy các giá trị của một thuộc tính (phần tử tổng hợp) dựa trên thứ tự của một hoặc nhiều thuộc tính khác (phần tử đặt hàng), có thể với các phần hàng được xác định dựa trên một thuộc tính hoặc thuộc tính khác (phần tử phân vùng) . Có rất nhiều ví dụ trong cuộc sống về việc tính tổng tích lũy, chẳng hạn như tính số dư tài khoản ngân hàng, theo dõi tình trạng sẵn có của hàng hóa trong kho hay số liệu bán hàng hiện tại, v.v.

Trước SQL Server 2012, các giải pháp dựa trên tập hợp được sử dụng để tính toán tổng số lần chạy cực kỳ tốn nhiều tài nguyên. Vì vậy, mọi người có xu hướng chuyển sang các giải pháp lặp lại, tuy chậm nhưng vẫn nhanh hơn các giải pháp dựa trên tập hợp trong một số trường hợp. Với sự hỗ trợ mở rộng cho các hàm cửa sổ trong SQL Server 2012, tổng số lần chạy có thể được tính bằng cách sử dụng mã dựa trên tập hợp đơn giản, hoạt động tốt hơn nhiều so với các giải pháp dựa trên T-SQL cũ hơn—cả dựa trên tập hợp và lặp lại. Tôi có thể đưa ra giải pháp mới và chuyển sang phần tiếp theo; nhưng để giúp bạn thực sự hiểu phạm vi của sự thay đổi, tôi sẽ mô tả những cách cũ và so sánh hiệu suất của chúng với cách tiếp cận mới. Đương nhiên, bạn chỉ được phép đọc phần đầu tiên mô tả cách tiếp cận mới và bỏ qua phần còn lại của bài viết.

Tôi sẽ sử dụng số dư tài khoản để trình bày các giải pháp khác nhau. Đây là mã tạo và điền vào bảng Giao dịch một lượng nhỏ dữ liệu thử nghiệm:

BẬT SỐ SỐ; SỬ DỤNG TSQL2012; NẾU OBJECT_ID("dbo.Transactions", "U") KHÔNG PHẢI LÀ BẢNG DROP NULL dbo.Transactions; TẠO BẢNG dbo.Transactions (actid INT NOT NULL, -- phân vùng cột tranid INT NOT NULL, -- đặt hàng cột val MONEY NOT NULL, -- đo CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid)); GO - tập dữ liệu thử nghiệm nhỏ INSERT INTO dbo.Transactions(actid, tranid, val) GIÁ TRỊ (1, 1, 4,00), (1, 2, -2,00), (1, 3, 5,00), (1, 4, 2,00), (1, 5, 1,00), (1, 6, 3,00), (1, 7, -4,00), (1, 8, -1,00), (1, 9, -2,00), (1, 10 , -3,00), (2, 1, 2,00), (2, 2, 1,00), (2, 3, 5,00), (2, 4, 1,00), (2, 5, -5,00), (2, 6 , 4,00), (2, 7, 2,00), (2, 8, -4,00), (2, 9, -5,00), (2, 10, 4,00), (3, 1, -3,00), (3, 2, 3,00), (3, 3, -2,00), (3, 4, 1,00), (3, 5, 4,00), (3, 6, -1,00), (3, 7, 5,00), (3, 8, 3,00), (3, 9, 5,00), (3, 10, -3,00);

Mỗi hàng của bảng thể hiện một giao dịch ngân hàng trên một tài khoản. Tiền gửi được đánh dấu là giao dịch có giá trị dương trong cột val và tiền rút được đánh dấu là giá trị giao dịch âm. Nhiệm vụ của chúng tôi là tính số dư tài khoản tại từng thời điểm bằng cách tích lũy số tiền giao dịch trong hàng val, sắp xếp theo cột tranid và việc này phải được thực hiện riêng cho từng tài khoản. Kết quả mong muốn sẽ trông như thế này:

Để kiểm tra cả hai giải pháp, cần nhiều dữ liệu hơn. Điều này có thể được thực hiện với một truy vấn như thế này:

KHAI THÁC @num_partitions NHƯ INT = 10, @rows_per_partition NHƯ INT = 10000; BẢNG TRUNCATE dbo.Giao dịch; CHÈN VÀO dbo.Giao dịch VỚI (TABLOCK) (actid, tranid, val) CHỌN NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM( NEWID())%5)) TỪ dbo.GetNums(1, @num_partitions) NHƯ NP CROSS THAM GIA dbo.GetNums(1, @rows_per_partition) NHƯ RPP;

Bạn có thể đặt thông tin đầu vào của mình để thay đổi số phần (tài khoản) và hàng (giao dịch) trong một phần.

Giải pháp dựa trên tập hợp sử dụng các hàm cửa sổ

Tôi sẽ bắt đầu với giải pháp dựa trên tập hợp sử dụng hàm tổng hợp cửa sổ SUM. Định nghĩa về cửa sổ ở đây khá rõ ràng: bạn cần phân chia cửa sổ theo Actid, sắp xếp theo tranid và sử dụng bộ lọc để chọn các dòng trong khung từ dưới cùng (UNBOUNDED PRECEDING) đến dòng hiện tại. Đây là yêu cầu tương ứng:

CHỌN Actid, tranid, val, SUM(val) OVER(PHẦN THAM GIA THEO Actid ĐẶT HÀNG THEO tranid HÀNG GIỮA HÀNG TRƯỚC KHÔNG GIỚI HẠN VÀ HÀNG HIỆN TẠI) NHƯ số dư TỪ dbo.Transactions;

Mã này không chỉ đơn giản và dễ hiểu mà còn nhanh. Kế hoạch cho truy vấn này được hiển thị trong hình:

Bảng có chỉ mục được nhóm đáp ứng các yêu cầu POC và có thể sử dụng được bởi các chức năng của cửa sổ. Cụ thể, danh sách khóa chỉ mục dựa trên phần tử phân vùng (actid), theo sau là phần tử thứ tự (tranid) và chỉ mục cũng bao gồm tất cả các cột khác trong truy vấn (val) để cung cấp phạm vi bao phủ. Kế hoạch bao gồm quét theo thứ tự, sau đó là tính toán số dòng cho nhu cầu nội bộ và sau đó là tổng hợp cửa sổ. Vì có chỉ mục POC nên trình tối ưu hóa không cần thêm toán tử sắp xếp vào kế hoạch. Đây là một kế hoạch rất hiệu quả. Ngoài ra, nó có quy mô tuyến tính. Sau này, khi tôi hiển thị kết quả so sánh hiệu suất, bạn sẽ thấy phương pháp này hiệu quả hơn bao nhiêu so với các giải pháp cũ.

Trước SQL Server 2012, truy vấn con hoặc kết nối đã được sử dụng. Khi sử dụng truy vấn con, tổng số đang chạy được tính bằng cách lọc tất cả các hàng có cùng giá trị Actid với hàng bên ngoài và giá trị tranid nhỏ hơn hoặc bằng giá trị ở hàng bên ngoài. Tổng hợp sau đó được áp dụng cho các hàng được lọc. Đây là yêu cầu tương ứng:

Một cách tiếp cận tương tự có thể được thực hiện bằng cách sử dụng các kết nối. Vị từ tương tự được sử dụng như trong mệnh đề WHERE của truy vấn con trong mệnh đề ON của phép nối. Trong trường hợp này, đối với giao dịch thứ N của cùng một tài khoản A trong phiên bản được chỉ định T1, bạn sẽ tìm thấy N kết quả trùng khớp trong phiên bản T2, với số giao dịch chạy từ 1 đến N. Kết quả của các kết quả khớp này là các hàng trong T1 là được lặp lại, vì vậy bạn cần nhóm các hàng trên tất cả các phần tử từ T1 để lấy thông tin về giao dịch hiện tại và áp dụng tính tổng hợp cho thuộc tính val từ T2 để tính tổng hoạt động. Yêu cầu đã hoàn thành trông giống như thế này:

CHỌN T1.actid, T1.tranid, T1.val, SUM(T2.val) NHƯ số dư TỪ dbo.Giao dịch NHƯ T1 THAM GIA dbo.Giao dịch NHƯ T2 TRÊN T2.actid = T1.actid VÀ T2.tranid<= T1.tranid GROUP BY T1.actid, T1.tranid, T1.val;

Hình dưới đây cho thấy các kế hoạch cho cả hai giải pháp:

Lưu ý rằng trong cả hai trường hợp, việc quét toàn bộ chỉ mục được nhóm được thực hiện trên phiên bản T1. Sau đó, đối với mỗi hàng trong kế hoạch, có một thao tác tìm kiếm trong chỉ mục đầu phần tài khoản hiện tại ở trang cuối của chỉ mục, thao tác này sẽ đọc tất cả các giao dịch trong đó T2.tranid nhỏ hơn hoặc bằng T1. tranid. Điểm diễn ra việc tổng hợp hàng hơi khác nhau trong các kế hoạch, nhưng số lượng hàng được đọc là như nhau.

Để hiểu có bao nhiêu hàng đang được xem, bạn cần xem xét số lượng phần tử dữ liệu. Gọi p là số phần (tài khoản) và r là số hàng trong phần (giao dịch). Khi đó số hàng trong bảng xấp xỉ bằng p*r, nếu chúng ta giả định rằng các giao dịch được phân bổ đều trên các tài khoản. Vì vậy, quá trình quét ở trên bao gồm các hàng p*r. Nhưng điều chúng tôi quan tâm nhất là những gì xảy ra trong vòng lặp Nested Loops.

Trong mỗi phần, kế hoạch cung cấp khả năng đọc 1 + 2 + ... + r hàng, tổng cộng là (r + r*2) / 2. Tổng số hàng được xử lý trong kế hoạch là p*r + p* (r + r2) / 2. Điều này có nghĩa là số lượng thao tác trong kế hoạch tăng bình phương khi kích thước phần tăng lên, nghĩa là nếu bạn tăng kích thước phần lên f lần thì khối lượng công việc sẽ tăng khoảng f 2 lần. Thật tệ. Ví dụ: 100 dòng tương ứng với 10 nghìn dòng và một nghìn dòng tương ứng với một triệu, v.v. Nói một cách đơn giản, điều này dẫn đến sự chậm lại đáng kể trong việc thực hiện truy vấn với kích thước phần khá lớn, vì hàm bậc hai tăng rất nhanh. Các giải pháp như vậy hoạt động tốt với vài chục dòng trên mỗi phần, nhưng không nhiều hơn.

Giải pháp con trỏ

Các giải pháp dựa trên con trỏ được triển khai trực tiếp. Một con trỏ được khai báo dựa trên truy vấn sắp xếp dữ liệu theo Actid và tranid. Sau đó, việc lặp lại các bản ghi con trỏ được thực hiện. Khi phát hiện một tài khoản mới, biến chứa tổng hợp sẽ được đặt lại. Tại mỗi lần lặp, số lượng giao dịch mới được thêm vào biến, sau đó hàng được lưu trong một biến bảng có thông tin về giao dịch hiện tại cộng với giá trị hiện tại của tổng số đang chạy. Sau khi lặp lại, kết quả từ biến bảng sẽ được trả về. Đây là mã cho giải pháp hoàn chỉnh:

KHAI THÁC @Result NHƯ BẢNG (actid INT, tranid INT, val MONEY, số dư MONEY); KHAI THÁC @actid NHƯ INT, @prvactid NHƯ INT, @tranid NHƯ INT, @val NHƯ TIỀN, @balance NHƯ TIỀN; KHAI THÁC C CURSOR FAST_FORWARD CHO CHỌN Actid, tranid, val TỪ dbo.Giao dịch ĐẶT HÀNG THEO Actid, tranid; MỞ C FETCH TIẾP THEO TỪ C VÀO @actid, @tranid, @val; CHỌN @prvactid = @actid, @balance = 0; KHI @@fetch_status = 0 BẮT ĐẦU NẾU @actid<>@prvactid CHỌN @prvactid = @actid, @balance = 0; THIẾT LẬP @balance = @balance + @val; XÁC NHẬN VÀO GIÁ TRỊ @Result(@actid, @tranid, @val, @balance); LẤY TIẾP THEO TỪ C VÀO @actid, @tranid, @val; KẾT THÚC ĐÓNG C; XÓA CẤP C; CHỌN * TỪ @Result;

Kế hoạch truy vấn sử dụng con trỏ được hiển thị trong hình:

Kế hoạch này chia tỷ lệ tuyến tính vì dữ liệu từ chỉ mục chỉ được quét một lần theo một thứ tự cụ thể. Ngoài ra, mỗi thao tác để truy xuất một hàng từ một con trỏ có chi phí cho mỗi hàng gần như bằng nhau. Nếu chúng ta coi tải được tạo bằng cách xử lý một dòng con trỏ bằng g thì chi phí của giải pháp này có thể được ước tính là p*r + p*r*g (như bạn nhớ, p là số phần và r là số hàng trong phần này). Vì vậy, nếu bạn tăng số lượng hàng trên mỗi phần lên f lần thì tải trên hệ thống sẽ là p*r*f + p*r*f*g, tức là nó sẽ tăng tuyến tính. Chi phí xử lý trên mỗi hàng cao, nhưng do tính chất tuyến tính của việc chia tỷ lệ, từ một kích thước phân vùng nhất định, giải pháp này sẽ thể hiện khả năng mở rộng tốt hơn so với các giải pháp dựa trên truy vấn lồng nhau và kết hợp do tỷ lệ bậc hai của các giải pháp này. Các phép đo hiệu suất mà tôi đã thực hiện cho thấy rằng số lượng giải pháp con trỏ nhanh hơn là vài trăm hàng trên mỗi phân vùng.

Bất chấp những lợi ích về hiệu suất được cung cấp bởi các giải pháp dựa trên con trỏ, chúng ta thường nên tránh chúng vì chúng không có tính chất quan hệ.

Giải pháp dựa trên CLR

Một giải pháp khả thi dựa trên CLR (Thời gian chạy ngôn ngữ chung) về cơ bản là một dạng giải pháp sử dụng con trỏ. Sự khác biệt là thay vì sử dụng con trỏ T-SQL vốn lãng phí nhiều tài nguyên để lấy hàng tiếp theo và lặp lại, bạn sử dụng các phép lặp .NET SQLDataReader và .NET, nhanh hơn nhiều. Một trong những tính năng của CLR giúp tùy chọn này nhanh hơn là hàng kết quả không cần thiết trong bảng tạm thời - kết quả được gửi trực tiếp đến quá trình gọi. Logic của giải pháp dựa trên CLR tương tự như logic của giải pháp con trỏ và T-SQL. Đây là mã C# xác định thủ tục giải quyết được lưu trữ:

Sử dụng hệ thống; sử dụng System.Data; sử dụng System.Data.SqlClient; sử dụng System.Data.SqlTypes; sử dụng Microsoft.SqlServer.Server; lớp công khai StoredProcedures ( public static void AccountBalances() ( sử dụng (SqlConnection conn = new SqlConnection("context connect=true;")) ( SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = @" " + "CHỌN Actid, tranid, val " + "TỪ dbo.Transactions " + "ĐẶT HÀNG THEO Actid, tranid;"; SqlMetaData cột = SqlMetaData mới; cột = new SqlMetaData("actid" , SqlDbType.Int); cột = mới SqlMetaData("tranid", SqlDbType.Int); cột = new SqlMetaData("val", SqlDbType.Money); cột = new SqlMetaData("balance", SqlDbType.Money); Pipe.SendResultsStart(recorder); .ExecuteReader(); SqlInt32 prvactid = 0; while (reader.Read()) ; SqlMoney val = reader.GetSqlMoney(2); if (actid == prvactid) ( Balance += val; ) else ( Balance = val; ) prvactid = actid; record.SetSqlInt32(0, reader.GetSqlInt32(0)); record.SetSqlInt32(1, reader.GetSqlInt32(1)); record.SetSqlMoney(2, val); record.SetSqlMoney(3, số dư); SqlContext.Pipe.SendResultsRow(bản ghi); ) SqlContext.Pipe.SendResultsEnd(); ) ) )

Để có thể thực thi quy trình được lưu trữ này trong SQL Server, trước tiên bạn cần xây dựng một tập hợp có tên AccountBalances dựa trên mã này và triển khai nó vào cơ sở dữ liệu TSQL2012. Nếu bạn không quen với việc triển khai các tập hợp trong SQL Server, bạn có thể muốn đọc phần Thủ tục lưu trữ và CLR trong bài viết Thủ tục lưu trữ.

Nếu bạn đặt tên cho tập hợp AccountBalances và đường dẫn đến tệp tập hợp là "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll", bạn có thể tải tập hợp vào cơ sở dữ liệu và đăng ký quy trình được lưu trữ bằng mã sau:

TẠO TẠO TÀI KHOẢN TỪ "C:\Projects\AccountBalances\bin\Debug\AccountBalances.dll"; TẠO THỦ TỤC dbo.AccountBalances NHƯ TÊN BÊN NGOÀI AccountBalances.StoredProcedures.AccountBalances;

Sau khi triển khai hợp ngữ và đăng ký thủ tục, bạn có thể thực thi nó bằng mã sau:

EXEC dbo.AccountBalances;

Như tôi đã nói, SQLDataReader chỉ là một dạng con trỏ khác, nhưng phiên bản này có chi phí đọc hàng ít hơn đáng kể so với việc sử dụng con trỏ truyền thống trong T-SQL. Các bước lặp trong .NET cũng nhanh hơn nhiều so với T-SQL. Do đó, các giải pháp dựa trên CLR cũng có quy mô tuyến tính. Thử nghiệm đã chỉ ra rằng hiệu suất của giải pháp này trở nên cao hơn hiệu suất của các giải pháp sử dụng truy vấn con và kết nối khi số lượng hàng trong một phần vượt quá 15.

Khi hoàn tất, bạn cần chạy mã dọn dẹp sau:

THỦ TỤC THẢ dbo.AccountBalances; THẢ HỘI Số dư tài khoản;

Lặp lại lồng nhau

Cho đến thời điểm này, tôi đã chỉ ra các giải pháp lặp đi lặp lại và dựa trên tập hợp. Giải pháp tiếp theo dựa trên các bước lặp lồng nhau, là sự kết hợp giữa các phương pháp lặp và dựa trên tập hợp. Ý tưởng trước tiên là sao chép các hàng từ bảng nguồn (trong trường hợp của chúng tôi là tài khoản ngân hàng) vào một bảng tạm thời cùng với một thuộc tính mới gọi là rownum, được tính bằng hàm ROW_NUMBER. Số dòng được phân chia theo Actid và sắp xếp theo tranid nên giao dịch đầu tiên trong mỗi tài khoản ngân hàng được gán số 1, giao dịch thứ hai được gán số 2, v.v. Sau đó, một chỉ mục nhóm được tạo trên bảng tạm thời với danh sách các khóa (rownum, actid). Sau đó, biểu thức CTE đệ quy hoặc vòng lặp được tạo đặc biệt sẽ được sử dụng để xử lý một hàng cho mỗi lần lặp trên tất cả các tài khoản. Tổng số đang chạy sau đó được tính bằng cách cộng giá trị được liên kết với hàng hiện tại với giá trị được liên kết với hàng trước đó. Đây là cách triển khai logic này bằng cách sử dụng CTE đệ quy:

CHỌN Actid, tranid, val, ROW_NUMBER() OVER(PARTITION BY Actid ORDER BY tranid) AS rownum VÀO #Transactions FROM dbo.Transactions; TẠO CHỈ SỐ CỤM ĐỘC ĐÁO idx_rownum_actid TRÊN #Transactions(rownum, actid); VỚI C AS (CHỌN 1 AS rownum, actid, tranid, val, val AS sumqty FROM #Transactions WHERE rownum = 1 UNION ALL SELECT PRV.rownum + 1, PRV.actid, CUR.tranid, CUR.val, PRV.sumqty + CUR.val TỪ C NHƯ PRV THAM GIA #Giao dịch NHƯ CUR TRÊN CUR.rownum = PRV.rownum + 1 VÀ CUR.actid = PRV.actid) CHỌN Actid, tranid, val, sumqty TỪ C OPTION (MAXRECURSION 0); BẢNG THẢ #Giao dịch;

Và đây là cách triển khai sử dụng vòng lặp rõ ràng:

CHỌN ROW_NUMBER() QUÁ (PHẦN THAM GIA THEO Actid ĐẶT HÀNG THEO tranid) NHƯ rownum, actid, tranid, val, CAST(val AS BIGINT) NHƯ sumqty VÀO #Transactions FROM dbo.Transactions; TẠO CHỈ SỐ CỤM ĐỘC ĐÁO idx_rownum_actid TRÊN #Transactions(rownum, actid); KHAI THÁC @rownum NHƯ INT; ĐẶT @rownum = 1; KHI 1 = 1 BẮT ĐẦU THIẾT LẬP @rownum = @rownum + 1; CẬP NHẬT CUR SET sumqty = PRV.sumqty + CUR.val TỪ #Giao dịch NHƯ CUR THAM GIA #Giao dịch NHƯ PRV TRÊN CUR.rownum = @rownum VÀ PRV.rownum = @rownum - 1 VÀ CUR.actid = PRV.actid; NẾU @@rowcount = 0 BREAK; KẾT THÚC CHỌN Actid, tranid, val, sumqty TỪ #Transactions; BẢNG THẢ #Giao dịch;

Giải pháp này cung cấp hiệu suất tốt khi có số lượng lớn phân vùng với số lượng hàng nhỏ trên mỗi phân vùng. Sau đó, số lần lặp lại nhỏ và phần lớn công việc được thực hiện bởi phần dựa trên tập hợp của giải pháp, kết nối các hàng được liên kết với một số hàng với các hàng được liên kết với số hàng trước đó.

Cập nhật nhiều dòng với các biến

Các phương pháp tính tổng tích lũy được hiển thị cho đến thời điểm này được đảm bảo mang lại kết quả chính xác. Kỹ thuật được mô tả trong phần này đang gây tranh cãi vì nó dựa trên hành vi của hệ thống được quan sát chứ không phải được ghi lại và nó cũng mâu thuẫn với các nguyên tắc tương đối. Sức hấp dẫn cao của nó là do tốc độ làm việc cao.

Phương thức này sử dụng câu lệnh UPDATE với các biến. Câu lệnh UPDATE có thể gán biểu thức cho các biến dựa trên giá trị của một cột và cũng có thể gán các giá trị trong cột cho một biểu thức có một biến. Giải pháp bắt đầu bằng cách tạo một bảng tạm thời có tên là Giao dịch với các thuộc tính Actid, tranid, val và Balance và một chỉ mục được nhóm với danh sách các khóa (actid, tranid). Sau đó, bảng tạm thời chứa tất cả các hàng từ cơ sở dữ liệu Giao dịch nguồn và giá trị 0,00 được nhập vào cột số dư của tất cả các hàng. Sau đó, câu lệnh CẬP NHẬT được gọi với các biến liên quan đến bảng tạm thời để tính tổng số đang chạy và chèn giá trị được tính vào cột số dư.

Các biến @prevaccount và @prevbalance được sử dụng và giá trị trong cột số dư được tính bằng biểu thức sau:

THIẾT LẬP @prevbalance = số dư = TRƯỜNG HỢP KHI Actid = @prevaccount THEN @prevbalance + val ELSE val END

Biểu thức CASE kiểm tra xem ID tài khoản hiện tại và trước đó có giống nhau hay không và nếu có, sẽ trả về tổng của các giá trị trước đó và hiện tại trong cột số dư. Nếu ID tài khoản khác nhau, số tiền giao dịch hiện tại sẽ được trả về. Tiếp theo, kết quả của biểu thức CASE được chèn vào cột số dư và gán cho biến @prevbalance. Trong một biểu thức riêng biệt, biến ©prevaccount được gán ID của tài khoản hiện tại.

Sau câu lệnh CẬP NHẬT, giải pháp sẽ hiển thị các hàng từ bảng tạm thời và xóa hàng cuối cùng. Đây là mã cho giải pháp hoàn chỉnh:

TẠO BẢNG #Giao dịch (actid INT, tranid INT, val MONEY, số dư MONEY); TẠO CHỈ SỐ CỤM idx_actid_tranid TRÊN #Transactions(actid, tranid); CHÈN VÀO #Giao dịch VỚI (TABLOCK) (actid, tranid, val, Balance) CHỌN Actid, tranid, val, 0,00 TỪ dbo.Giao dịch ĐẶT HÀNG THEO Actid, tranid; KHAI THÁC @prevaccount NHƯ INT, @prevbalance NHƯ TIỀN; CẬP NHẬT #Giao dịch SET @prevbalance = số dư = TRƯỜNG HỢP KHI Actid = @prevaccount THEN @prevbalance + val ELSE val END, @prevaccount = actid TỪ #Transactions WITH(INDEX(1), TABLOCKX) OPTION (MAXDOP 1); CHỌN * TỪ #Giao dịch; BẢNG THẢ #Giao dịch;

Phác thảo của giải pháp này được thể hiện trong hình dưới đây. Phần đầu tiên được thể hiện bằng câu lệnh INSERT, phần thứ hai bằng UPDATE và phần thứ ba bằng câu lệnh SELECT:

Giải pháp này giả định rằng tối ưu hóa thực thi CẬP NHẬT sẽ luôn thực hiện quét theo thứ tự chỉ mục được nhóm và giải pháp này cung cấp một số gợi ý để ngăn chặn các trường hợp có thể ngăn chặn điều này, chẳng hạn như đồng thời. Vấn đề là không có đảm bảo chính thức nào rằng trình tối ưu hóa sẽ luôn tìm kiếm theo thứ tự của chỉ mục được nhóm. Bạn không thể dựa vào tính toán vật lý để đảm bảo mã đúng về mặt logic trừ khi có các phần tử logic trong mã mà theo định nghĩa có thể đảm bảo hành vi đó. Không có tính năng logic nào trong mã này có thể đảm bảo hành vi này. Đương nhiên, việc lựa chọn có sử dụng phương pháp này hay không hoàn toàn nằm ở lương tâm của bạn. Tôi nghĩ việc sử dụng nó là vô trách nhiệm, ngay cả khi bạn đã kiểm tra nó hàng nghìn lần và "mọi thứ dường như vẫn hoạt động như bình thường".

May mắn thay, SQL Server 2012 khiến lựa chọn này hầu như không cần thiết. Khi bạn có một giải pháp cực kỳ hiệu quả bằng cách sử dụng các hàm tổng hợp theo cửa sổ, bạn không cần phải suy nghĩ về các giải pháp khác.

đo lường hiệu suất

Tôi đã đo và so sánh hiệu suất của các kỹ thuật khác nhau. Kết quả được thể hiện trong các hình dưới đây:

Tôi chia kết quả thành hai biểu đồ vì phương thức truy vấn con/nối chậm hơn nhiều so với các biểu đồ khác nên tôi phải sử dụng thang đo khác cho nó. Trong mọi trường hợp, hãy lưu ý rằng hầu hết các giải pháp đều thể hiện mối quan hệ tuyến tính giữa khối lượng công việc và kích thước phân vùng và chỉ giải pháp truy vấn con hoặc giải pháp nối mới thể hiện mối quan hệ bậc hai. Cũng rõ ràng để xem giải pháp mới dựa trên chức năng tổng hợp cửa sổ hiệu quả hơn đến mức nào. Giải pháp CẬP NHẬT với các biến cũng rất nhanh, nhưng vì những lý do đã được mô tả, tôi khuyên bạn không nên sử dụng nó. Giải pháp CLR cũng khá nhanh, nhưng bạn phải viết tất cả mã .NET đó và triển khai tập hợp vào cơ sở dữ liệu. Cho dù bạn nhìn nó như thế nào, giải pháp dựa trên bộ sản phẩm sử dụng các thiết bị cửa sổ vẫn là giải pháp thích hợp nhất.

Tất cả các hàm toán học đều trả về NULL nếu xảy ra lỗi.

Điểm trừ đơn nhất. Thay đổi dấu của đối số: mysql> SELECT - 2; -> -2 Xin lưu ý rằng nếu toán tử này được sử dụng với dữ liệu thuộc loại BIGINT thì giá trị trả về cũng sẽ thuộc loại BIGINT ! Điều này có nghĩa là bạn nên tránh sử dụng toán tử trên các số nguyên có thể có độ lớn -2^63 ! ABS(X) Trả về giá trị tuyệt đối của X: mysql> SELECT ABS(2); -> 2 mysql> CHỌN ABS(-32); -> 32 Hàm này có thể được sử dụng một cách tự tin cho các giá trị BIGINT. SIGN(X) Trả về dấu của đối số là -1, 0 hoặc 1, tùy thuộc vào X là âm, 0 hay dương: mysql> SELECT SIGN(-32); -> -1 mysql> CHỌN ĐĂNG KÝ(0); -> 0 mysql> CHỌN ĐĂNG KÝ(234); -> 1 giá trị MOD(N,M) % Modulo (tương tự toán tử % trong C). Trả về số dư khi N chia cho M: mysql> SELECT MOD(234, 10); -> 4 mysql> CHỌN 253% 7; -> 1 mysql> CHỌN MOD(29,9); -> 2 Hàm này có thể được sử dụng một cách tự tin cho các giá trị BIGINT. FLOOR(X) Trả về số nguyên lớn nhất không vượt quá X: mysql> SELECT FLOOR(1.23); -> 1 mysql> CHỌN TẦNG(-1.23); -> -2 Xin lưu ý rằng giá trị trả về được chuyển thành BIGINT ! CEILING(X) Trả về số nguyên nhỏ nhất không nhỏ hơn X: mysql> SELECT CEILING(1.23); -> 2 mysql> CHỌN TRẦN(-1.23); -> -1 Xin lưu ý rằng giá trị trả về được chuyển thành BIGINT ! ROUND(X) Trả về đối số X được làm tròn đến số nguyên gần nhất: mysql> SELECT ROUND(-1.23); -> -1 mysql> VÒNG CHỌN (-1,58); -> -2 mysql> VÒNG CHỌN (1,58); -> 2 Lưu ý rằng hành vi của hàm ROUND() khi giá trị đối số là trung điểm giữa hai số nguyên phụ thuộc vào cách triển khai cụ thể của thư viện C. Làm tròn có thể được thực hiện: đến số chẵn gần nhất, luôn cao hơn gần nhất, luôn ở mức thấp nhất gần nhất, luôn hướng về 0. Để đảm bảo rằng việc làm tròn luôn chỉ xảy ra theo một hướng, thay vào đó, bạn phải sử dụng các hàm được xác định rõ ràng như TRUNCATE() hoặc FLOOR(). ROUND(X,D) Trả về X được làm tròn đến D chữ số thập phân. Nếu D là 0, kết quả sẽ được trình bày không có chữ số thập phân hoặc phân số: mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> VÒNG CHỌN (1.298, 0); -> 1 EXP(X) Trả về giá trị của e (cơ số logarit tự nhiên) lũy thừa của X: mysql> SELECT EXP(2); -> 7.389056 mysql> CHỌN EXP(-2); -> 0,135335 LOG(X) Trả về logarit tự nhiên của số X: mysql> SELECT LOG(2); -> 0. 693147 mysql> NHẬT KÝ CHỌN(-2); -> NULL Để lấy logarit của một số X cho logarit cơ số B tùy ý, hãy sử dụng công thức LOG(X)/LOG(B) . LOG10(X) Trả về logarit thập phân của X: mysql> SELECT LOG10(2); -> 0,301030 mysql> CHỌN LOG10(100); -> 2,000000 mysql> CHỌN LOG10(-100); -> NULL POW(X,Y) POWER(X,Y) Trả về giá trị của đối số X lũy thừa của Y: mysql> SELECT POW(2,2); -> 4.000000 mysql> CHỌN POW(2,-2); -> 0,250000 SQRT(X) Trả về căn bậc hai không âm của X: mysql> SELECT SQRT(4); -> 2,000000 mysql> CHỌN SQRT(20); -> 4.472136 PI() Trả về giá trị của số pi. Theo mặc định, 5 chữ số thập phân được biểu thị, nhưng MySQL sử dụng độ chính xác gấp đôi đầy đủ để biểu thị số pi cho các phép tính nội bộ. mysql> CHỌN PI(); -> 3.141593 mysql> CHỌN PI()+0,000000000000000000; -> 3.141592653589793116 COS(X) Trả về cosin của số X, trong đó X được tính bằng radian: mysql> SELECT COS(PI()); -> -1.000000 SIN(X) Trả về sin của số X, trong đó X được tính bằng radian: mysql> SELECT SIN(PI()); -> 0,000000 TAN(X) Trả về tang của số X, trong đó X được tính bằng radian: mysql> SELECT TAN(PI()+1); -> 1.557408 ACOS(X) Trả về cung cosin của số X, tức là một đại lượng có cosin bằng X. Nếu X không nằm trong phạm vi -1 đến 1 , trả về NULL: mysql> SELECT ACOS(1); -> 0,000000 mysql> CHỌN ACOS(1,0001); -> NULL mysql> CHỌN ACOS(0); -> 1.570796 ASIN(X) Trả về arcsin của số X, tức là một đại lượng có sin bằng X. Nếu X không nằm trong phạm vi -1 đến 1, trả về NULL: mysql> SELECT ASIN(0.2); -> 0,201358 mysql> CHỌN ASIN("foo"); -> 0,000000 ATAN(X) Trả về arctang của số X, tức là một giá trị có tiếp tuyến bằng X: mysql> SELECT ATAN(2); -> 1.107149 mysql> CHỌN ATAN(-2); -> -1.107149 ATAN(Y,X) ATAN2(Y,X) Trả về arctang của hai biến X và Y . Cách tính cũng giống như arctang của Y/X, ngoại trừ dấu của cả hai đối số đều được dùng để xác định góc phần tư của kết quả: mysql> SELECT ATAN(-2,2); -> -0.785398 mysql> CHỌN ATAN2(PI(),0); -> 1.570796 COT(X) Trả về cotang của số X: mysql> SELECT COT(12); -> -1.57267341 mysql> CHỌN COT(0); -> NULL RAND() RAND(N) Trả về giá trị ngẫu nhiên dấu phẩy động trong phạm vi từ 0 đến 1,0. Nếu một đối số nguyên N được chỉ định thì nó được sử dụng làm giá trị ban đầu của giá trị này: mysql> SELECT RAND(); -> 0. 9233482386203 mysql> CHỌN RAND(20); -> 0,15888261251047 mysql> CHỌN RAND (20); -> 0,15888261251047 mysql> CHỌN RAND(); -> 0,63553050033332 mysql> CHỌN RAND(); -> 0,70100469486881 Trong biểu thức ORDER BY, bạn không nên sử dụng cột có giá trị RAND() vì việc sử dụng toán tử ORDER BY sẽ dẫn đến nhiều phép tính trên cột đó. Tuy nhiên, trong MySQL 3.23, bạn có thể thực hiện câu lệnh sau: SELECT * FROM table_name ORDER BY RAND() : Điều này rất hữu ích để lấy một phiên bản ngẫu nhiên từ một tập hợp SELECT * FROM table1,table2 WHERE a=b AND c