Asp.net(c#) Cutom 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 @startRowIndex = 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

---

My 2 cents with this: If you can execute the query fast in SQL Server Manager, then the application code is inefficient/at fault. You may need to post more code of the actual web page/lifecycle for us to get a better idea of what's going on.