CFSDN nhấn mạnh vào giá trị tạo ra nguồn mở và chúng tôi cam kết xây dựng nền tảng chia sẻ tài nguyên để mọi nhân viên CNTT có thể tìm thấy thế giới tuyệt vời của bạn tại đây.

Bài viết blog CFSDN này Kiểm tra hiệu quả quy trình lưu trữ khối lượng dữ liệu lớn với mã kiểm tra và kết quả được tác giả sưu tầm và biên soạn. Nếu bạn quan tâm đến bài viết này, hãy nhớ thích nó.

Môi trường kiểm tra Phần cứng: CPU Core Duo T5750 Bộ nhớ: 2G Phần mềm: Windows server 2003 + sql server 2005 OK, trước tiên chúng ta tạo cơ sở dữ liệu: data_Test và tạo bảng trong cơ sở dữ liệu này: tb_TestTable.

Sao chép mã mã như sau

tạo cơ sở dữ liệu data_Test --Tạo cơ sở dữ liệu data_Test GO sử dụng data_Test GO tạo bảng tb_TestTable --Tạo bảng (id int Identity(1,1) khóa chính, userName nvarchar(20) not null, userPWD nvarchar(20) not null, userEmail nvarchar( 40) không) ĐI.

Sau đó ta chèn 2.000.000 mẩu dữ liệu vào bảng dữ liệu: .

Sao chép mã mã như sau

--Cách sử dụng set identity_insert tb_TestTable on khai báo @count int set @count=1 while @count<=2000000 begin insert into tb_TestTable(id,userName,userPWD,userEmail) values(@count,'admin','admin888','lli0077@yahoo.com.cn') set @count=@count+1 end set identity_insert tb_TestTable off 。

Lần đầu tiên tôi viết năm thủ tục lưu trữ thường được sử dụng: 1. Sử dụng select top và select not in để phân trang. Mã cụ thể như sau: .

Sao chép mã mã như sau

tạo thủ tục proc_paged_with_notin --Sử dụng chọn trên cùng và chọn không trong (@pageIndex int, --page index @pageSize int --số lượng bản ghi trên mỗi trang) khi bắt đầu đặt nocount on; nvarchar( 500) select @timediff=Getdate() set @sql='select top '+str(@pageSize)+' * from tb_TestTable Where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID' exec(@sql) --Vì không có hỗ trợ cho các tham số sau select top nên nó được viết dưới dạng Chuỗi ký tự @sql chọn dateiff(ms,@timediff,GetDate()) khi kết thúc việc đặt số lượng tốn nhiều thời gian;

2. Sử dụng select top và select max (phím cột).

Sao chép mã mã như sau

tạo thủ tục proc_paged_with_selectMax -- Định nghĩa select top và select max(số) ( @pageIndex int, -- Định nghĩa @pageSize int -- Định nghĩa hàm ) as begin set nocount on; khai báo @timediff datetime khai báo @sql nvarchar(500) select @timediff=Getdate() set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID' execute(@sql) select datesiff(ms,@timediff,GetDate()) as Bây giờ set nocount off; kết thúc 。

3. Sử dụng các biến hàng đầu và trung gian chọn lọc - có người trên Internet nói rằng phương pháp này có hiệu quả tốt nhất nên tôi đã đăng nó để cùng nhau thử nghiệm.

Sao chép mã mã như sau

create procedure proc_paged_with_Midvar -- ID đại diện>ID tối thiểu và ID trung gian ( @pageIndex int, @pageSize int ) as khai báo @count int khai báo @ID int khai báo @timediff datetime khai báo @sql nvarchar(500) begin set nocount on; select @count=0,@ID=0,@timediff=getdate() select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID) execute(@sql) select datesiff(ms,@timediff,getdate()) as Nếu không thì set nocount off; end 。

4. Sử dụng Row_number(). Phương thức này là một phương thức mới trong SQL Server 2005. Sử dụng Row_number() để thêm chỉ mục vào hàng dữ liệu.

Sao chép mã mã như sau

create procedure proc_paged_with_Rownumber -- Phiên bản SQL 2005 trung gian Row_number() ( @pageIndex int, @pageSize int ) as khai báo @timediff datetime begin set nocount on; select @timediff=getdate() select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1) select datesiff(ms,@timediff,getdate()) as Bây giờ set nocount off; end 。

5. Sử dụng bảng tạm thời và Row_number.

Sao chép mã mã như sau

tạo thủ tục proc_CTE --利用临时表及Row_number ( @pageIndex int, --页索引 @pageSize int --页记录数 ) làm số đếm được đặt trên; khai báo @ctestr nvarchar(400) khai báo @strSql nvarchar(400) khai báo @datediff datetime bắt đầu chọn @datediff=GetDate() set @ctestr='with Table_CTE as (chọn trần((Row_number() over(thứ tự theo ID ASC)) /'+str(@pageSize)+') dưới dạng page_num,* từ tb_TestTable)'; đặt @strSql=@ctestr+' chọn * Từ Table_CTE nơi page_num='+str(@pageIndex) kết thúc bắt đầu thực thi sp_executesql @strSql chọn datesiff(ms,@datediff,GetDate()) đặt nocount tắt; kết thúc 。

OK, lúc này, thủ tục lưu trữ đã được tạo. Chúng tôi tiến hành kiểm tra trên trang 2, trang 1000, trang 10000, trang 100000 và trang 199999 với 10 mẩu dữ liệu trên mỗi trang. Đơn vị tiêu tốn thời gian: ms trên mỗi trang Kiểm tra 5 lần. và lấy trung bình Cần thời gian để lưu lại trang 2, trang 1000 mất thời gian, trang 10000 mất thời gian, trang 100000 mất thời gian, cần trang 199999, hiệu quả tốn thời gian xếp hạng 1 sử dụng không trong 0ms 16ms 47ms 475ms 953ms 3 2 sử dụng chọn tối đa 5ms 16ms 35ms 325ms 623ms 1 3 Biến trung gian 966ms 970ms 960ms 945ms 933ms 5 4row_number 0ms 0ms 34ms 365ms 710ms 2 4 bảng tạm thời 780ms 796ms 798ms 780ms 805ms 4 Kết quả kiểm tra cho thấy: chọn max >row_number>không có trong>bảng tạm thời>biến trung gian Vì vậy, tôi đã chọn biến hiệu quả nhất Phương pháp tối đa được mở rộng bằng phương pháp 2 điểm. Mã được lấy từ Internet. Tôi đã sửa đổi BUG mà không thể lấy được giá trị trong quá trình sắp xếp ASC. Kết quả kiểm tra là: Phương pháp 2 điểm 156ms 156ms 180ms 470ms 156ms 1*. Từ kết quả kiểm tra, sử dụng 2 điểm Phương pháp này thực sự có thể cải thiện hiệu quả và ổn định hơn. Tôi đã thêm bài kiểm tra ở trang 159999, chỉ mất 296ms và hiệu quả khá tốt! Sau đây là mã để sử dụng select max bằng phương pháp 2 điểm, khá hoàn chỉnh. .

Sao chép mã mã như sau

--/*------Xử lý phân trang thủ tục lưu trữ Sun Wei 2005-03-28 Đã tạo -------*/ --/*------Xử lý phân trang thủ tục lưu trữ Langchen 2008-9-1 Sửa đổi ----------*/ --/*------ Thực hiện xử lý 2 điểm trên dữ liệu sao cho hiệu suất truy vấn nửa dữ liệu đầu tiên giống như truy vấn nửa sau của dữ liệu dữ liệu -------* / thay đổi THỦ TỤC proc_paged_2part_selectMax ( @tblName nvarchar(200), ----Bảng được hiển thị hoặc kết nối của nhiều bảng @fldName nvarchar(500) = '*', ----Danh sách các trường sẽ được hiển thị @pageSize int = 10, -- --Mỗi Số lượng bản ghi được hiển thị trên trang @page int = 1, ----Các bản ghi trên trang đó sẽ được hiển thị @fldSort nvarchar(200) = null, ----Sắp xếp danh sách trường hoặc điều kiện @Sort bit = 0 , ----Phương pháp sắp xếp, 0 là thứ tự tăng dần, 1 là thứ tự giảm dần (nếu là sắp xếp nhiều trường, Sắp xếp đề cập đến thứ tự sắp xếp của trường sắp xếp cuối cùng (trường sắp xếp cuối cùng không thêm dấu sắp xếp)- -Chương trình truyền các tham số như: 'SortA Asc ,SortB Desc,SortC ') @strCondition nvarchar(1000) = null, ----điều kiện truy vấn, không cần Where @ID nvarchar(150), ----primary key của bảng chính @Dist bit = 0, ----Có thêm DISTINCT của trường truy vấn hay không. Mặc định 0 không thêm/1 thêm đầu ra @pageCount int = 1, ----Tổng số trang sau kết quả truy vấn được phân trang @Counts int = 1 đầu ra ----Số bản ghi được truy vấn) NHƯ SET NOCOUNT ON Khai báo @sqlTmp nvarchar(1000) ----Lưu trữ câu lệnh SQL được tạo động Khai báo @strTmp nvarchar(1000) ---- Lưu trữ câu lệnh truy vấn Khai báo lấy tổng số kết quả truy vấn @strID nvarchar(1000) ----Lưu trữ câu lệnh truy vấn để lấy ID ở đầu hoặc cuối truy vấn Khai báo @strSortType nvarchar(10) ----Quy tắc sắp xếp dữ liệu A Khai báo @strFSortType nvarchar(10) -- --Quy tắc sắp xếp dữ liệu B Khai báo @SqlSelect nvarchar(50) ----Xây dựng SQL cho các truy vấn chứa DISTINCT Khai báo @SqlCounts nvarchar(50) ----Xây dựng SQL cho tổng số truy vấn chứa DISTINCT khai báo @timediff datetime --Chênh lệch thời gian kiểm tra tốn thời gian chọn @timediff=getdate() if @Dist = 0 bắt đầu đặt @SqlSelect = 'select ' set @SqlCounts = 'Count( *)' kết thúc khác bắt đầu đặt @SqlSelect = 'chọn khác biệt ' đặt @SqlCounts = 'Count(DISTINCT '+@ID+')' kết thúc nếu @Sort=0 bắt đầu đặt @strFSortType=' ASC ' set @strSortType=' DESC ' end else started set @strFSortType=' DESC ' set @strSortType=' ASC ' end --------Tạo câu lệnh truy vấn-------- --Ở đây @strTmp là câu lệnh để lấy số lượng kết quả truy vấn nếu @strCondition là null hoặc @strCondition='' --Không có điều kiện hiển thị nào được đặt started set @sqlTmp = @fldName + ' From ' + @tblName set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName set @strID = ' From ' + @tblName end else bắt đầu set @sqlTmp = + @fldName + 'From ' + @tblName + ' ở đâu (1>0 ) ' + @strCondition đặt @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' TỪ '+@tblName + ' trong đó (1>0) ' + @strCondition set @strID = ' From ' + @tblName + ' trong đó (1>0) ' + @strCondition end ----Lấy tổng số kết quả truy vấn---- exec sp_executesql @ strTmp,N'@Counts int out ',@Counts out khai báo @tmpCounts int if @Counts = 0 set @tmpCounts = 1 else set @tmpCounts = @Counts --Lấy tổng số trang được đặt @pageCount=(@tmpCounts+@pageSize-1)/@pageSize /** //* *//**//**Nếu trang hiện tại lớn hơn tổng số trang, lấy trang cuối cùng**/ if @page>@pageCount set @page=@pageCount --/*------Phân trang dữ liệu Đang xử lý 2 điểm-------*/ khai báo @pageIndex int --Khai báo tổng số/kích thước trang @lastcount int --Tổng số % kích thước trang được đặt @pageIndex = @ tmpCounts/ @pageSize set @lastcount = @tmpCounts%@pageSize if @lastcount > 0 set @pageIndex = @pageIndex + 1 else set @lastcount = @pagesize --//***Hiển thị phân trang nếu @strCondition là null hoặc @strCondition='' --Không có bộ điều kiện hiển thị nào bắt đầu nếu @pageIndex<2 hoặc @page<=@pageIndex / 2 + @pageIndex % 2 --Nửa đầu của quá trình xử lý dữ liệu bắt đầu nếu @page=1 được đặt @strTmp=@SqlSelect+ ' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' sắp xếp theo '+ @fldSort +' '+ @strFSortType khác sẽ bắt đầu nếu @Sort=1 bắt đầu đặt @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName +' trong đó '+@ID+' <(select min('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' từ '+@tblName +' sắp xếp theo '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' +' sắp xếp theo '+ @fldSort +' '+ @strFSortType end else bắt đầu đặt @strTmp=@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó '+@ID+' >(select max('+ @ID +') from ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20)) +' '+ @ID +' từ '+@tblName +' sắp xếp theo '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' +' sắp xếp theo '+ @fldSort +' '+ @strFSortType end end end else started set @page = @pageIndex-@page+1 --Xử lý dữ liệu nửa giây nếu @page <= 1 --Bộ hiển thị dữ liệu trang cuối cùng @ strTmp=@SqlSelect+' * từ ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TempTB'+' sắp xếp theo '+ @fldSort +' '+ @strFSortType else nếu @Sort=1 bắt đầu đặt @strTmp=@SqlSelect+' * từ ( '+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' từ '+@tblName +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TempTB'+' thứ tự theo '+ @fldSort +' '+ @strFSortType end else bắt đầu đặt @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST( @pageSize as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +'trong đó '+@ID+' <(select min('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' từ '+@tblName +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TempTB'+' thứ tự theo '+ @fldSort +' '+ @strFSortType end end end else --有查询条件 bắt đầu nếu @pageIndex<2 hoặc @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 bắt đầu nếu @page=1 bộ @strTmp=@SqlSelect+' top '+ CAST(@pageSize là VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó 1=1 ' + @strCondition + ' sắp xếp theo '+ @fldSort +' '+ @strFSortType nếu không nếu(@Sort=1) bắt đầu thiết lập @strTmp=@SqlSelect+' top '+ CAST(@pageSize là VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó '+@ID+' <( chọn min('+ @ID +') từ ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) là Varchar(20)) +' '+ @ID +' từ '+@tblName +' trong đó (1=1) ' + @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' +' '+ @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strFSortType kết thúc nếu không thì bắt đầu đặt @strTmp=@SqlSelect+' đầu '+ CAST(@pageSize là VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó '+@ID+' >(select max('+ @ID +') từ ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-1) as Varchar(20 )) +' '+ @ID +' từ '+@tblName +' trong đó (1=1) ' + @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strFSortType+') AS TBMinID)' +' ' + @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strFSortType kết thúc kết thúc khác bắt đầu đặt @page = @pageIndex-@page+1 --后半部分数据处理 if @page <= 1 --最后一页数据显示 set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó (1=1) '+ @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TempTB'+' sắp xếp theo '+ @fldSort +' '+ @strFSortType else if(@Sort =1) đặt @strTmp=@SqlSelect+' * từ ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó '+@ID+' >(select max('+ @ID +') from('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar (20)) +' '+ @ID +' từ '+@tblName +' trong đó (1=1) '+ @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' + ' '+ @strCondition+' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TempTB'+' sắp xếp theo '+ @fldSort +' '+ @strFSortType nếu không thì đặt @strTmp=@SqlSelect+' * từ('+@SqlSelect+' top '+ CAST(@pageSize là VARCHAR(4))+' '+ @fldName+' từ '+@tblName +' trong đó '+@ID+' <(select min('+ @ID +') từ ('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount là Varchar(20)) +' '+ @ID +' từ '+@tblName +' trong đó (1=1) '+ @strCondition +' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TBMaxID)' +' '+ @strCondition+' sắp xếp theo '+ @fldSort +' '+ @strSortType+') AS TempTB'+' sắp xếp theo '+ @fldSort +' '+ @strFSortType kết thúc kết thúc ------返回查询结果------ exec sp_executesql @strTmp select dateiff(ms,@timediff,getdate()) as 耗时 --print @strTmp SET NOCOUNT OFF GO 。

Ví dụ thực thi: exec proc_paged_2part_selectMax 'tb_testTable','ID,userName,userPWD,userEmail',10,100000,'ID',0,null,'ID',0 Loại thử nghiệm này chỉ được thực hiện trên một máy duy nhất và không phải thực sự được phát triển trên WEB Thử nghiệm phân trang trong dự án có một mục thử nghiệm tương đối đơn lẻ nên không toàn diện và có hệ thống. Tuy nhiên, xét về hiệu quả của nó, chúng tôi có thể kiểm soát hiệu quả thuật toán phân trang cơ sở dữ liệu.

Cuối cùng, bài viết này về kiểm tra hiệu quả của quy trình lưu trữ phân trang với khối lượng dữ liệu lớn với mã kiểm tra và kết quả sẽ kết thúc tại đây. Nếu bạn muốn biết thêm về kiểm tra hiệu quả của quy trình lưu trữ phân trang với khối lượng dữ liệu lớn với mã kiểm tra và kết quả, vui lòng tìm kiếm. Các bài viết của CFSDN hoặc tiếp tục duyệt các bài viết liên quan, mong các bạn sẽ ủng hộ blog của mình trong thời gian tới! .