Asp.net(c#) Custom Paging With Stored Procedure - Bad Performance During Pagination

I have a asp:GridView with datasource like List I added custom paging, using a procedure, when i use the procedure in MS SERVER Management

Studio its performance is fast, as soon as i try it in asp.net, performance is awful during

pagination. The 1st step (when the gridview fills) is very fast, but when i am starting to paginate,

the performance kills, i am waiting 5-25 seconds for passing to the next page.

Dear all, what must i do to solve this problem, will you help me ?

HERE IS THE STORED PROCEDURE

CREATE PROCEDURE [sp_QS]
@startRowIndex INT,
@maximumRows INT,
@afterWhere NVARCHAR(MAX),
@sortBy NVARCHAR(MAX),
@totalRows INT OUT
AS

SET NOCOUNT ON;

DECLARE @P NVARCHAR(MAX), @Q1 NVARCHAR(MAX), @Q2 NVARCHAR(MAX)
DECLARE @first_id INT

SET @startRowIndex = (@startRowIndex - 1) * @maximumRows

SET @Q1 = 'query part 1'

SET @Q2 = 'query part 2'

IF @startRowIndex = 0
BEGIN
SET @startRowIndex = 1
END

SET ROWCOUNT @startRowIndex
SET @P = 'SET NOCOUNT ON;     DECLARE @out INT     SELECT @out = id FROM table1 ' + @Q2 + ' 

WHERE ' + @afterWhere + '     SELECT @out'

IF OBJECT_ID('tempdb..#t1','u') IS NOT NULL
BEGIN
DROP TABLE #t1
END
CREATE TABLE #t1 (col INT)
INSERT #t1 EXEC(@P)
SELECT @first_id = col FROM #t1
DROP TABLE #t1

--SELECT @first_id AS FFFF --PRINT @first_id

SET ROWCOUNT @maximumRows
SET @P = 'SET NOCOUNT ON;' + 'SELECT ' + @Q1 + ' FROM table ' + @Q2 + ' WHERE (id >=' + 

CAST(@first_id AS NVARCHAR(60)) + ') AND (' + @afterWhere + ') ' + @sortBy
EXEC(@P)

SET ROWCOUNT 0

-- GET THE TOTAL ROWS
IF @sta开发者_高级运维rtRowIndex = 1
BEGIN
SET @P = 'SET NOCOUNT ON;' + 'SELECT COUNT(id) FROM table1 ' + @Q2 + ' WHERE ' + 

@afterWhere
IF OBJECT_ID('tempdb..#t2','u') IS NOT NULL
BEGIN
DROP TABLE #t2
END
CREATE TABLE #t2 (col INT)
INSERT #t2 EXEC (@P)
SELECT @totalRows = col FROM #t2
DROP TABLE #t2
SELECT @totalRows AS QueryResultRowCount
END

GO

AND HERE IS THE CODE IN ASP.NET(WITH C#)

private void BindData()
{
    string connectionString = "Server=localhost;" + 
           "Database=Northwind;Trusted_Connection=true";
    SqlConnection myConnection = new SqlConnection(connectionString);
    SqlCommand myCommand = new SqlCommand("usp_GetProducts", 
                                           myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;

    myCommand.Parameters.AddWithValue("@startRowIndex", 
                                      currentPageNumber);
    myCommand.Parameters.AddWithValue("@maximumRows", PAGE_SIZE);
    myCommand.Parameters.Add("@totalRows", SqlDbType.Int, 4);
    myCommand.Parameters["@totalRows"].Direction = 
                       ParameterDirection.Output;

    SqlDataReader sqlReader = myCommand.ExecuteReader();

    while(sqlReader.Read())
    {
        // filling List<> object to bind to gridview as datasource
    }
...
}

I have to buttons 'next' and 'prvious', pressing these buttons, i am changing

currentPageNumber with + or - 1, calling BindData() method after it.

Thanks in advance

That's an SQL against the AdventureWorks database from SQL Server 2005 samples:

    DECLARE 
    @FirstRow int,
    @LastRow int,
    @Sorting varchar(50);

Declare @SelectClause nvarchar(max),
        @Params nvarchar(MAX);

SELECT @FirstRow = 1, @LastRow = 10;

SELECT @SelectClause = 'WITH CTE AS (
SELECT 
    ROW_NUMBER() OVER ( ORDER BY ' + COALESCE(@Sorting, 'SalesOrderID ASC') + ' ) AS RowNumber,
    COUNT(*) OVER() AS TotalRows,
    SalesOrderID,
    OrderDate,
    DueDate,
    CASE OnlineOrderFlag WHEN 1 THEN ''Yes'' ELSE ''No'' END as OnlineOrderFlagString
FROM 
    Sales.SalesOrderHeader
WHERE
    SubTotal > 100)

SELECT * FROM CTE WHERE RowNumber >= @FirstRow AND RowNumber < @LastRow',
@Params = '@FirstRow int, @LastRow int';

exec sp_executesql 
    @statement = @SelectClause,
    @params = @Params,
    @FirstRow = @FirstRow,
    @LastRow = @LastRow;

After you have execute query you can fetch total rows value from first row if exists. Note, that if you must provide ability to sort by computed columns like the OnlineOrderFlagString, the query will become bit more complex:

DECLARE 
    @FirstRow int,
    @LastRow int,
    @Sorting varchar(50);

Declare @SelectClause nvarchar(max),
        @Params nvarchar(MAX);

SELECT @FirstRow = 1, @LastRow = 10, @Sorting = 'OnlineOrderFlagString ASC'

SELECT @SelectClause = 'WITH CTE_1 AS (
SELECT 
    SalesOrderID,
    OrderDate,
    DueDate,
    CASE OnlineOrderFlag WHEN 1 THEN ''Yes'' ELSE ''No'' END as OnlineOrderFlagString
FROM 
    Sales.SalesOrderHeader
WHERE
    SubTotal > 100),
CTE_2 AS (
SELECT
    ROW_NUMBER() OVER ( ORDER BY ' + COALESCE(@Sorting, 'SalesOrderID ASC') + ' ) AS RowNumber,
    COUNT(*) OVER() AS TotalRows,
    SalesOrderID,
    OrderDate,
    DueDate,
    OnlineOrderFlagString
FROM
    CTE_1
)

SELECT * FROM CTE_2 WHERE RowNumber >= @FirstRow AND RowNumber < @LastRow',
@Params = '@FirstRow int, @LastRow int';

exec sp_executesql 
    @statement = @SelectClause,
    @params = @Params,
    @FirstRow = @FirstRow,
    @LastRow = @LastRow;