There are couple of ways to implement the pagination.
Lets take an example of DataGrid in ASP.NET where you need set "AllowPaging" to True to enable the DataGrid's paging features. Along with the this this property, there is another PageSize property that specifies how many records to display per page and the default value of this is 10.
In this case it will get all the records which satifies the search criteria. This is has somany performance issues. Instead of this get the No.Of records based on the Page you are looking in and get the data. This way it will improve the performance of the page in great way.
The below piece of code uses the new Analytical functions available in SQL Server 2005 to implement the pagination.
SELECT * FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY ROWNUM) AS ROW_NUM,*
FROM
(
SELECT E.EMPID,E.NAME,E.DOJ,E.SAL,D.DEPTNO,D.DNAME
ROW_NUMBER() OVER
(ORDER BY E.SAL) AS ROWNUM
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
) AS V
WHERE v.ROWNUM < @PageRowEndNumber
) AS V1
WHERE V1.ROW_NUM >= @PageRowStartNumber
[/ODE]
|
No responses found. Be the first to respond and make money from revenue sharing program.
|