It is very important that the parameters received are used to craft an SQL query that only fetches the information needed. No matter how fast jqGrid is, if the server-side SQL query is slow, the whole request will be slow. Optimizing SQL really depends on the database server used. In the common case of MS SQL Server, you need to use special query syntax to fetch only the page needed, passing the starting index and page size parameters, as well as the sort clauses and the searching where clause.

In addition to that, you also need to provide the total records count that match this query and assign the eventArgs.TotalRows property to be equal that number. Therefore you will probably need two requests to the server - one for the count and one for the actual records. This may seem like a bad idea at first, but in practice provides very fast responses even for millions rows of data.

Please, review the code below for details on how this can be achieved for MS SQL server. Pay special attention to the syntax of the SQL queries used - how to get row count, how to use the OVER keyword and autogenerated row number to only retrieve the rows you need.

<trirand:JQGrid runat="server" ID="JQGrid2" Width="680px" OnDataRequesting="JQGrid2_DataRequesting">
        <Columns>
      <trirand:JQGridColumn DataField="OrderID" Width="50" Searchable="false"  />
      <trirand:JQGridColumn DataField="OrderDate" DataFormatString="{0:d}" Width="100" Searchable="false" />
      <trirand:JQGridColumn DataField="CustomerID" Width="100" Searchable="true" 
         SearchControlID="CustomerIDDdl" SearchType="DropDown"
         SearchToolBarOperation="IsEqualTo" SearchDataType="String" />            
      <trirand:JQGridColumn DataField="Freight" Width="75" Searchable="true" 
            SearchToolBarOperation="IsGreaterOrEqualTo" 
            SearchValues="[All]:[All];10:> 10;20:> 20;50:> 50;100:> 100"  
            SearchType="DropDown" SearchDataType="NonString"/>
      <trirand:JQGridColumn DataField="ShipName" Searchable="true" 
            SearchToolBarOperation="Contains" SearchDataType="String" />
        </Columns>        
        <PagerSettings PageSize="100" PageSizeOptions="[100,200,500]" />        
        <ToolBarSettings ShowSearchToolBar="true" />
    </trirand:JQGrid>

   public void JQGrid2_DataRequesting(object sender, Trirand.Web.UI.WebControls.JQGridDataRequestEventArgs e)
   {
            string orderByColumn;
            if (!String.IsNullOrEmpty(e.SortExpression))
                orderByColumn = e.SortExpression + " " + e.SortDirection.ToString();
            else
                orderByColumn = "OrderID ASC";
            e.TotalRows = GetRowCount(orderByColumn, e.SearchExpression);

            DataTable dt = GetData(JQGrid2.PagerSettings.PageSize,
                                    (e.NewPageIndex - 1) * JQGrid2.PagerSettings.PageSize,
                                    orderByColumn,
                                    e.SearchExpression);

            JQGrid2.DataSource = dt;
            JQGrid2.DataBind();
        }                

        protected DataTable GetData(int pageSize, int startIndex, string orderByColumn, string searchExpression)
        {
            // Create a new Sql Connection and set connection string accordingly
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            sqlConnection.Open();
            
            string sql = @"
                            SELECT * FROM 
                            (
                              SELECT row_number() OVER 
                                    ({0})
                                AS 
                                 rownum, OrderID, CustomerID, OrderDate, Freight, ShipName
                              FROM 
                                    OrdersLarge
                                  {1} 
                            )
                           AS A
                           WHERE A.rownum BETWEEN {2} AND {3}";

            string orderClause = String.IsNullOrEmpty(orderByColumn) ? 
                        String.Empty : "ORDER BY " + orderByColumn;
            string whereClause = String.IsNullOrEmpty(searchExpression) ? 
                        String.Empty : "WHERE " + searchExpression;
            
            sql = String.Format(sql, orderClause, whereClause, startIndex + 1, startIndex + pageSize);

            SqlCommand cmd = new SqlCommand(sql, sqlConnection);            

            // Create a SqlDataAdapter to get the results as DataTable
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(cmd);

            // Create a new DataTable
            DataTable dtResult = new DataTable();

            // Fill the DataTable with the result of the SQL statement
            sqlDataAdapter.Fill(dtResult);

            sqlConnection.Close();

            return dtResult;
        }

        protected int GetRowCount(string orderByColumn, string searchExpression)
        {
            // Create a new Sql Connection and set connection string accordingly
            SqlConnection sqlConnection = new SqlConnection();
            sqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
            sqlConnection.Open();

            string sql = @"
                            SELECT COUNT(*) FROM 
                            (
                              SELECT row_number() OVER 
                                    ({0})
                                AS 
                                 rownum, OrderID, CustomerID, OrderDate, Freight, ShipName
                              FROM 
                                    OrdersLarge
                                  {1} 
                            ) AS A";
                           

            string orderClause = String.IsNullOrEmpty(orderByColumn) ? 
               String.Empty : " ORDER BY " + orderByColumn;
            string whereClause = String.IsNullOrEmpty(searchExpression) ?
               String.Empty : " WHERE " + searchExpression;

            sql = String.Format(sql, orderClause, whereClause);


            SqlCommand cmd = new SqlCommand(sql, sqlConnection);
            int count = (int) cmd.ExecuteScalar();

            sqlConnection.Close();

            return count;
        }
    }