Asp.net(c#) Custom Paging With Stored Procedure - Bad Performance During Pagination
<p>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 </p>

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

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

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

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

<p>HERE IS THE STORED PROCEDURE</p>

<pre><code>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 &gt;=' + 

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
</code></pre>

<p>AND HERE IS THE CODE IN ASP.NET(WITH C#)</p>

<pre><code>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&lt;&gt; object to bind to gridview as datasource
    }
...
}
</code></pre>

<p>I have to buttons 'next' and 'prvious', pressing these buttons, i am changing </p>

<p>currentPageNumber with + or - 1, calling BindData() method after it.</p>

<p>Thanks in advance</p>
