Kỹ thuật phân trang khi lấy dữ liệu từ DB Kỹ thuật phân trang khi lấy dữ liệu. Có những lý do ( đất hẹp người đông : giá mặt tiền, phí vận chuyển, bảo trì kho, .. ) cần đến phân trang, từ vị trí nBase cần lấy ra nRow tiếp theo ( cho một trang nPage ) với một thứ tự sắp xếp xác định trước. Để minh họa cho ms Sql, chúng ta có bảng và dữ liệu như sau : --drop table tabSamp --go create table tabSamp( nK1 VarChar(2) not null, nK2 VarChar(2) not null, Val int -- , .. Các columns khác CONSTRAINT [PK_tabSamp] PRIMARY KEY CLUSTERED( nK1, nK2) ) go --truncate table tabSamp declare @i int, @j int, @k int select @i= 0, @k= 0 while @i< 26 begin -- 'A' -> 'Z' select @i= @i + 1, @j= 0 while @j< 10 begin-- '0' -> '9' select @j= @j + 1, @k= @k + 1 insert tabSamp(nK1, nK2, Val) values( char(64 + @i), char(47 + @j), @k) end end go --select * from tabSamp Cần lấy theo order by Val với yêu cầu như trên, không dùng phép trừ hai tập hợp, sql 2000, có thể viết với ( cursor hoặc ) bảng tạm: /* Đoạn code này được sinh tự động từ một chương trình, tôi có sự hiểu chỉnh lại bằng tay */ --drop proc tabSamp_GetPageWithTemp create proc tabSamp_GetPageWithTemp @nBase int, @nRow int as begin set noCount on if @nBase < 0 or @nRow < 1 return -1 if (select count(*) FROM tabSamp) <= @nBase return -2 set noCount on declare @qry nvarchar(400) create table #Tmp2( idx int IDENTITY (1, 1) NOT NULL, nK1 VarChar(2), nK2 VarChar(3), Val int ) set @qry= 'INSERT #Tmp2(nK1, nK2, Val) SELECT TOP ' + convert(nvarchar, @nBase + @nRow) + ' nK1, nK2, Val from tabSamp order by Val' exec sp_executesql @qry -- select nK1, nK2, Val from #Tmp2 where idx > @nBase --drop table #Tmp2 return @@rowCount end go Và bạn có thể test với đoạn code : declare @nBase int, @nRow int, @nRet int select @nBase= 250, @nRow= 30 exec @nRet= tabSamp_GetPageWithTemp @nBase, @nRow -- select @nRet Results /* Tham khảo: xin giúp về câu lênh SQL, http://ddth.vn/showthread.php?t=284702 và Viết 1 proc dùng cho nhiều bảng, http://ddth.vn/showthread.php?t=284108 */ Mong các bạn sửa chữa và bổ xung những sai sót của bài viết. Cám ơn sự quan tâm của các bạn. Được sửa bởi Van8Hien62 lúc 22:03 ngày 30-06-2009.  |
#2  01-07-2009, 00:39 | |||
| |||
Ví dụ cần lấy các bản ghi từ 50 => 60 Nếu từ SQL Server 2000 trở về trước. Tôi dùng 1 trong 3 cách - Insert vào một bảng tạm, trong bảng tạm này có 1 cột RowNumber là IDENTITY. Hoặc sử dụng SELECT IDENTITY(INT,1,1) RowNumber .... INTO ... Select những bản ghi có RowNumber between 50 and 60 - Sử dụng truy vấn lồng, self-join để lấy ra kết quả - Cursor ( cách củ chuối những cũng coi là 1 cách. Không nên dùng ) Từ SQL Server 2005 trở lên, dùng luôn ROW_NUMBER() ( có từ SQL Server 2005) |
#3 01-07-2009, 13:58 | |||
| |||
 - Cursor ( cách củ chuối những cũng coi là 1 cách. Không nên dùng ) Bạn có thể nói lý do không ? Tôi thấy trong sql 2008, version 10.0.1600.22, vẫn sử dụng nó trong các stored, function của hệ thống ( system objects ). Chẳng hạn sys.sp_helpDb, sys.sp_helpText, ... Xin cho biết về củ chuối này, trong sql-2008, lại được dùng ?! Những người làm ra nó ( ms sql ) có khuyến cáo chi về củ chuối này không ? Sao không nhìn nó ở cách thông thường, tường minh, cho giải pháp cơ bản ? Còn khi hệ thống có support như 05 / 08, viết : select .. except -- Hiệu hai table select .. là tốt, dễ hơn ( so với cursor, temporary table, join, ROW_NUMBER ) chứ. Viết trong forum, tôi nghĩ nên dùng những cách cơ bản, dễ dùng cho nhiều người, chưa cần tối ưu (, phiên bản hay CT mới nhất đâu). Trong trường hợp riêng, họ đặt trong DLL, xProc win32 api (legacy) hoặc CLR integration (currently), thì bạn có thể nói được nó là củ chuối hay củ sâm ( cao ly ) không ! Được sửa bởi Van8Hien62 lúc 19:25 ngày 01-07-2009. |
#4 01-07-2009, 20:22 | |||
| |||
- Dĩ nhiên, nếu cursor không cần thiết, hoặc việc sử dụng nó là không tối ưu, thì người ta không tạo ra nó làm gì. Bạn lấy dẫn chứng là các sp trong hệ thống, hay SQL 2k8 vẫn còn nó, thì lại là chuyện khác. Ở đây tôi muốn nói việc sử dụng cursor vào Phân trang là một lựa chọn không tối ưu. Bản thân Cursor dùng cho việc xử lý tuần tự, nếu như cần lấy n dòng, thì tổng cộng bạn phải truy cập vào cái bảng đó ít nhất n lần. Và nó thích hợp nhất cho việc xử lý tuần tự chứ không phải xử lý với một tập dữ liệu. Việc sử dụng nó thế nào, dựa vào kinh nghiệm, và bản thân người dùng. Với ý kiến cá nhân tôi, dùng cách này là củ chuối - Có thể với các bạn khác, Cursor là một giải pháp tốt, vì các bạn thấy cursor giống với vòng for trong lập trình, nhưng để đạt hiệu quả cao, tôi nghĩ không nên dùng. Trừ những trừong hợp bất khả kháng. - Nếu bạn không chấp nhận ý kiến của tôi. Bạn có thể đưa lên đây script tạo bảng và dữ liệu. Rồi sử dụng Cursor để phân trang. Tôi sẽ dùng một cách khác, xem cách nào hiệu quả hơn. Ok ? |
#5 01-07-2009, 22:26 | ||||
| ||||
Về cái chuyện này thì tôi bắt buộc phải đồng ý với bạn RD rồi. Những bất lợi về dùng cursors không những đầy rẫy trên Internet (của những người đã lỡ bước chân vào hố sâu tăm tối), mà kinh nghiệm lâm sàng của tôi cũng có đầy rẫy những khổ đau khi lầm lỡ dùng cursors. Cursors cũng có một vài lợi điểm, nhưng lợi bất cập hại. Tôi cũng có thể bảo đảm rằng, bất cứ một SQL source nào dùng CURSORS, tôi có thể thay thể nó bằng một cách khác có tốc độ xử lý mau hơn. Có hai điểm chính khiến chúng ta nên tránh dùng CURSORS. 1) tốc độ xử lý tồi đến mức độ không chấp nhận được. 2) table bị khóa. Đây cũng là điều không thể chấp nhận được trên một "enterprise" database có hàng trăm người xử dụng. Trên phương diện doanh nghiệp, nếu một công ty đã bỏ ra hàng triệu đô la để đầu tư vào một CSDL, thì mỗi một phần ngàn của một giây là một thời gian quý báu, không thể có chuyện để cho một CURSOR khóa một bảng lại hàng chục phút đồng hồ. Dưới đây là ba trang web tiêu biểu. Các bạn có thể google dùng SQL CURSOR AVOID thì sẽ thấy cả một ...rừng sách báo về chuyện không nên dùng CURSOR. Cursors, How to avoid them How SQL Developers can avoid Cursors http://www.sql-server-performance.co...ursors_p1.aspx Riêng phần tôi, tôi chỉ cho các bạn một lời khuyên, đừng bao giờ dùng CURSOR nếu bạn đang viết lập trình cho các cơ quan/công ty. Nếu không, một ngày nào đó, bạn sẽ vô cùng hối hận. Tuy nhiên, cũng nên hiểu tận tường về cursor, để có một ngày nào đó, bạn sẽ phải xóa bỏ những code dùng nó để thay thế bằng một cách khác. Hơn nữa, như tôi đã nói ở trên, cursor cũng có một vào lợi điểm và có thể dùng trong trường hợp 1) cần phải tu bổ một CSDL mà thời gian không cho phép ngồi suy nghĩ để viết bằng cách khác. 2) chỉ chạy một lần với một NXD mà thôi. Chẳng hạn như trường hợp cần phải cập nhật hóa một bảng với một cột mới, và cho giá trị vào cột mới căn cứ theo giá trị của một hay nhiều cột cũ đồng hàng. Riêng phần tại sao Microsoft vẫn có những hàm dùng cursors, thì xin thưa rằng, đó là những hàm có từ xa xưa, không dễ gì có thời gian để mà thay đổi. Một công ty có một sản phẩm hàng chục triệu người dùng trên toàn thế giới, không phải muốn thay đổi là thay đổi, ngoại trừ khi bắt buộc. Những hàm như sp_help mà bạn VH đã đưa ra, tôi có thể bảo đảm rằng, ngàn năm sau, nếu SQL còn tồn tại, nó cũng sẽ còn tồn tại. Được sửa bởi dq_ninh lúc 22:54 ngày 01-07-2009. |
#6 02-07-2009, 00:55 | |||
| |||
Tôi biết cursor có những bất lợi, và tôi chỉ muốn trình bày ở mức giải thuật, dễ hình dung thôi. Nếu bạn muốn thử thì lấy ví dụ mẫu AdventureWorks, table Person.Contact có chừng gần 20 000 rows. Viết phân trang trên order by EmailAddress chẳng hạn, theo yêu cầu giả định trên là được chứ cần gì đưa lên đây script tạo bảng và dữ liệu làm chi. Chúng ta ai cũng có thể sai lầm. Nhưng cho răng " nó thích hợp nhất cho việc xử lý tuần tự chứ không phải xử lý với một tập dữ liệu " là không có cơ sở. Thao tác trên table, một tập dữ liệu ( select , SqlDataAdapter, .. ) đã che dấu việc xử lý tuần tự bên dưới ( cursor, SqlDataReader, .. ); Sql, nó có cơ sở toán học là lý thuyết tập hợp kia mà. Có những cái rất khó thay đổi, nhưng các stored thay đổi là dễ dàng, tôi đã có so sánh các stored trên trong các phiên bản khác nhau của ms sql srv. Mong các bạn sửa chữa và bổ xung những sai sót của bài viết. Xin tránh những chuyện đi xa. Cám ơn |
0 nhận xét:
Đăng nhận xét