Would using TOP (N) and OPTION(FAST N) interfere with GridView's built-in Optimizations?

Question

I am using a GridView which could potentially display half a million records (depending on the options the user selects in filtering the data).

I want to prevent this, for optimization/performance purposes. If it would dramatically improve performance, I am willing to limit the result set to a few thousand or so records.

Would starting my query with a TOP (N) and ending with a OPTION(FAST N) be a good thing for this situation, or would it interfere with GridView's built-in optimization (I'm assuming, and I think I read it, that GridView only loads so many records at a time, as the user scrolls through).

So should I do something like this:

SELECT TOP (1000) [column list]
FROM [table]
WHERE [bla]
ORDER BY [Bla]
OPTION(FAST 100)

...or am I better off leaving it at

SELECT [column list]
FROM [table]
WHERE [bla]
ORDER BY [Bla]

...due to a possibility that my attempts at optimization will interfere with what the GridView is already handling on its own?

Answer 1

The GridView loads all rows. As far as I know it does not optimize anything. And if you do not disable ViewState for the GridView (or the page it is on) all those rows are added to the ViewState, resulting in a HUGE page size of way too much MB's.

The paging in the GridView is nothing more than just showing the rows 100 - 200, while the rest still is loaded.

So I would do something like this (more info). Note that order by is required in this case.

SELECT [column list]
FROM [table]
WHERE [bla]
ORDER BY [Bla]
OFFSET [Offset] ROWS FETCH NEXT [Pagesize] ROWS ONLY

Now, how to implement this for a GridView. First, create a Stored Procedure. You could do it without one, but because you also would need the total number of rows you would need two requests to the database. In this example the data is required by a userID as the WHERE parameter.

CREATE PROCEDURE [dbo].[GridView_Paging]

    @offset int,
    @pagesize int,
    @userID int 

AS
BEGIN

    DECLARE @totalrows INT;

    -- you will need the total amount of rows for paging to work

    SELECT @totalrows = COUNT(itemID) FROM MyTable WHERE userid = @userID;

    --select the data and add the total rows also

    SELECT ID, name, @totalrows AS totalrows
    FROM MyTable
    WHERE userID = @userID 
    ORDER BY ID
    OFFSET @offset ROWS FETCH NEXT @pagesize ROWS ONLY;

END
GO

Then add a GridView to the aspx page and make sure ViewState is off (this saves network traffic). And add a Repeater that will function as the Pager.

<asp:GridView ID="GridView1" runat="server" EnableViewState="false"></asp:GridView>

<asp:Repeater ID="Pager_GridView" runat="server">
    <ItemTemplate>
        <asp:LinkButton ID="LinkButton1" runat="server" OnCommand="Pager_GridView_Command" 
            CommandArgument='<%# Container.ItemIndex %>'><%# Container.ItemIndex + 1 %>
        </asp:LinkButton>
    </ItemTemplate>
</asp:Repeater>

Then a function to call the stored procedure and add the data to the GridView and create the pager. I add the data to a DataTable first instead of directly to the GridView1.DataSource because I need the total number of rows.

public void LoadGridView(int page, int userID)
{
    int pagesize = 10;
    double totalrows = 0;

    int offset = page * pagesize;

    //create a connection
    using (var connection = new SqlConnection(Common.connectionString))
    using (var command = new SqlCommand("GridView_Paging @offset, @pagesize, @userID", connection))
    {
        //add the parameters
        command.Parameters.Add("@offset", SqlDbType.Int).Value = offset;
        command.Parameters.Add("@pagesize", SqlDbType.Int).Value = pagesize;
        command.Parameters.Add("@userID", SqlDbType.Int).Value = userID;

        //open the connection
        connection.Open();

        using (var reader = command.ExecuteReader())
        {
            var dt = new DataTable();
            dt.Load(reader);

            //add the data to the gridview
            GridView1.DataSource = dt;
            GridView1.DataBind();

            //get the total rows from the datatable
            if (dt.Rows.Count > 0)
                totalrows = (int)dt.Rows[0]["totalrows"];
        }
    }

    //calculate the number of pages
    var pages = Math.Ceiling(totalrows / pagesize);

    //create the pager by binding an array of the correct size to the repeater
    Pager_GridView.DataSource = new string[(int)pages];
    Pager_GridView.DataBind();
}

Then add the Repeater LinkButton command

protected void Pager_GridView_Command(object sender, CommandEventArgs e)
{
    LoadGridView(Convert.ToInt32(e.CommandArgument), 12345);
}

And finally Page_Load. Because I've disabled the ViewState, the data has to be bound on every page load and PostBack (which is also the same page load basically). If you would just put LoadGridView(0, 12345); then you would be double-hitting the database. Once in Page_Load and once you click a link in the pager. To prevent this, check if the __EVENTTARGET is from the Repeater

protected void Page_Load(object sender, EventArgs e)
{
    string eventTarget = Request.Form["__EVENTTARGET"];

    //check the eventtarget contains the repeater container to prevent double hitting
    if (string.IsNullOrEmpty(eventTarget) || !eventTarget.Contains(Pager_GridView.UniqueID))
    {
        LoadGridView(0, 12345);
    }
}