استفاده از ROW_NUMBER() بر صفحه بندی

[mohsen_zelzela00]

با عرض سلام خدمت اساتید محترم
من کد زیر نوشتم

ALTER PROCEDURE [dbo].[SPSelectReport4] (@StringWhereParameter nvarchar(4000))
AS
BEGIN

SET NOCOUNT ON;





-- َ Begin Of Transaction
begin tran

declare @Query nvarchar(max)
set @Query='
select * from (
((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I'''+@StringWhereParameter+')
))))
Order by Mesc,Line,unit'

--

exec(@Query)


if @@error = 0
Commit Tran
Else
rollback tran
End



این کوئری تعداد رکوردهای خیلی زیادی رو بر می گردونه می خواستم که با استفاده از ROW_NUMBER() عملیات Paging رو انجام بدم ممنون میشم اساتید محترم راهنمایی کنند.

[Reza_Yarahmadi]

ابتدای کدتون بصورت زیر باشه
Select
Row_Number() Over(Order By Mesc,Line,unit) RowID,
*
From (
((SELECT Id,[Mesc]
...


انتهای کدتون (به جای Order By) هم چیزی شبیه کد زیر (تعداد سطر در هر صفحه 100 فرض شده)
Where
RowID > ' + (@PageNumber - 1) * 100 + '
AND
RowID <= ' + @PageNumber * 100

[mohsen_zelzela00]

ابتدای کدتون بصورت زیر باشه
Select
Row_Number() Over(Order By Mesc,Line,unit) RowID,
*
From (
((SELECT Id,[Mesc]
...


انتهای کدتون (به جای Order By) هم چیزی شبیه کد زیر (تعداد سطر در هر صفحه 100 فرض شده)
Where
RowID > ' + (@PageNumber - 1) * 100 + '
AND
RowID <= ' + @PageNumber * 100



زمانی که این دستور رو بنویسم میگه که RowID رو نمی شناسه . الیته درست هم میگه. چون ما در این Query فیلد RowId رو خودمون ایجاد کردیم. استاد به نظر شما راحل چیست؟؟ من دستور زیر رو نوشتم ولی خب به نظر من درست نیست چون سرعتش خیلی پایین میات



ALTER PROCEDURE [dbo].[SPSelectReport2] (@StringWhereParameter nvarchar(4000),@PageIndex int,@PageSize int)
AS
BEGIN

SET NOCOUNT ON;

-- َ Begin Of Transaction
begin tran

declare @from int=(@PageSize*(@PageIndex-1))+1
declare @to int=(@PageIndex*@PageSize)

declare @Query nvarchar(max)
set @Query=' select

distinct id,[Mesc], [Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[StCode]
,[PlanCode]
,[MIN]
,[MAX]
,[LastDateConsum]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[TypeRequest]
,[HeaderId]
from (

SELECT *, ROW_NUMBER() OVER(ORDER BY Id) ROW_NUM
FROM(
((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[StCode]
,[PlanCode]
,[MIN]
,[MAX]
,[LastDateConsum]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report2]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report2] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[StCode]
,[PlanCode]
,[MIN]
,[MAX]
,[LastDateConsum]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report2]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report2] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id, [Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[StCode]
,[PlanCode]
,[MIN]
,[MAX]
,[LastDateConsum]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report2]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report2] WHERE line=''I'''+@StringWhereParameter+')
)))) a)b where b.ROW_NUM between '+CAST(@from as varchar(10))+' and '+CAST(@to as varchar(10))

-- Order by Mesc,Line,unit
exec(@Query)
-- print @Query


if @@error = 0
Commit Tran
Else
rollback tran
End

[Reza_Yarahmadi]

بصورت زیر امتحان کنید

set @Query='With Res as(
select
RowNumber() Over(Order By Mesc,Line,unit) RowID,
*
from (
((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I'''+@StringWhereParameter+')
)))))
Select * From Res
Where
RowID > ' + (@PageNumber - 1) * 100 + '
AND
RowID <= ' + @PageNumber * 100


exec(@Query)

[mohsen_zelzela00]

بصورت زیر امتحان کنید

set @Query='With Res as(
select
RowNumber() Over(Order By Mesc,Line,unit) RowID,
*
from (
((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Description]
,[ST_CODE]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[QtyConsum1]
,[QtyConsum2]
,[QtyConsum3]
,[QtyConsum4]
,[QtyConsum5]
,[Type]
,[InvQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report4]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report4] WHERE line=''I'''+@StringWhereParameter+')
)))))
Select * From Res
Where
RowID > ' + (@PageNumber - 1) * 100 + '
AND
RowID <= ' + @PageNumber * 100


exec(@Query)

مرسی استاد فقط یه سوال تفاوت کدی که من نوشتم با این کد در چیست ؟؟ چون از لحاظ زمانی هر دو برابر هستند(یه جورایی خیلی طول می کشد)

[mohsen_zelzela00]

آیا میشه این Query رو به صورت بهینه نوشت ؟؟؟ آخه الان برای یک Table با 576822 رکورد یه چیزی حدود 7 دقیقه طول میکشه که واقعاً زیاده.

[حمیدرضاصادقیان]

سلام.
Execution Plan اونو ذخیره کرده اینجا قرار بدید.

[اوبالیت به بو]

درود بر شما

نمی خواستم تایپیک جدید بزنم ولی سوالم مربوط به همین موضوع است. من کوئری رو به این صورت نوشتم:



Declare @Page int

Set @Page = 1 -- صفحه یک

Select * FROM (Select ROW_NUMBER () OVER (Order By EmpID) RowID, * FROM Employees)
WHERE RowID >= (@Page -1)*10 AND RowID < (@Page * 10)


کدام روش بهتر است؟

در واقع اومدم گفتم جدول Employee و ستون RowID ادغام شوند و بشوند یک جدول و بعد تمام فیلدهای آن جدول (ترکیب RowID و Employees)

[Mahmoud.Afrad]

منطق کوئری اشکال داره. جای مساوی باید عوض بشه:

Select *
FROM (Select ROW_NUMBER () OVER (Order By EmpID) RowID, *
FROM Employees) as t
WHERE t.RowID > (@Page -1)*10 AND RowID <= (@Page * 10)

/* OR
;with cte as
(
Select ROW_NUMBER () OVER (Order By EmpID) RowID, *
FROM Employees
)
select *
from cte
where cte.RowID > (@Page-1)*10 and cte.RowID <= (@Page*10)
*/

[اوبالیت به بو]

جناب Mahmoud.Afrad (http://barnamenevis.org/member.php?71297-Mahmoud.Afrad) درست می فرمایید. اگر مساوی رو در اول بگذارم یک رکورد کمتر از چیزی که می خواهم می آورد.

نمونه کد زیر اصلاح شده بالاست که دوستان می توانند استفاده کنند. امیدوارم مفید واقع بشود:



Declare @Page int
Declare @PerPage int


Set @Page = 1 -- صفحه یک
Set @PerPage = 10 -- ده رکورد در هر صفحه


Select * FROM (Select ROW_NUMBER () OVER (Order By EmpID) RowID, * FROM Employees)
WHERE RowID > (@Page - 1) * @PerPage AND RowID <= (@Page * @PerPage)





متغیر @Page: شماره صفحه می باشد. مثلا صفحه شماره هفت یا یازده یا ...
متغیر @PerPage: در هر صفحه، چند رکورد فراخوانی شود.