Datagrid slow for paging.

I have a million records. I need to fix the below
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;


namespace SIDWebClient
{
    public partial class GridCustomPaging : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.DataGrid MyDataGrid;
        protected System.Web.UI.WebControls.Label CurrentPage;
        protected System.Web.UI.WebControls.Label TotalPages;
        protected System.Web.UI.WebControls.LinkButton NextPage;
        protected System.Web.UI.WebControls.LinkButton PreviousPage;
        protected System.Web.UI.WebControls.LinkButton FirstPage;
        protected System.Web.UI.WebControls.LinkButton LastPage;

        protected Int32 _currentPageNumber = 1;

        public void Page_Load(Object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                BindData();
            }
        }

        public void BindData()
        {
            SqlConnection myConnection = new SqlConnection("Data Source=SM3-93-BILL2;Initial Catalog=KP-FE-KW-PROD-SID-PROV1;User ID=sa;Password=devserver;");

            SqlCommand myCommand = new SqlCommand("Get_SIMS_By_Page", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

            myCommand.Parameters.Add(new SqlParameter("@CurrentPage", SqlDbType.Int)).Value = _currentPageNumber;
            myCommand.Parameters.Add(new SqlParameter("@PageSize", SqlDbType.Int)).Value = MyDataGrid.PageSize;
            myCommand.Parameters.Add(new SqlParameter("@TotalRecords", SqlDbType.Int)).Direction = ParameterDirection.Output;

            try
            {
                myConnection.Open();
                MyDataGrid.DataSource = myCommand.ExecuteReader();
                MyDataGrid.DataBind();
            }
            finally
            {
                myConnection.Close();
            }

            CurrentPage.Text = _currentPageNumber.ToString();

            Double _totalPages = 1;
            if (!Page.IsPostBack)
            {
                Int32 _totalRecords = (Int32)myCommand.Parameters["@TotalRecords"].Value;
                _totalPages = _totalRecords / MyDataGrid.PageSize;
                TotalPages.Text = (System.Math.Ceiling(_totalPages)).ToString();
            }
            else
            {
                _totalPages = Double.Parse(TotalPages.Text);
            }

            if (_currentPageNumber == 1)
            {
                PreviousPage.Enabled = false;

                if (_totalPages > 1)
                {
                    NextPage.Enabled = true;
                }
                else
                {
                    NextPage.Enabled = false;
                }
            }
            else
            {
                PreviousPage.Enabled = true;

                if (_currentPageNumber == _totalPages)
                {
                    NextPage.Enabled = false;
                }
                else
                {
                    NextPage.Enabled = true;
                }
            }
        }

        protected void NavigationLink_Click(Object sender, CommandEventArgs e)
        {
            switch (e.CommandName)
            {
                case "First":
                    _currentPageNumber = 1;
                    break;
                case "Last":
                    _currentPageNumber = Int32.Parse(TotalPages.Text);
                    break;
                case "Next":
                    _currentPageNumber = Int32.Parse(CurrentPage.Text) + 1;
                    break;
                case "Prev":
                    _currentPageNumber = Int32.Parse(CurrentPage.Text) - 1;
                    break;
            }

            BindData();
        }

    }
}
<%@ Page Title="" Language="C#" MasterPageFile="~/App_MasterPages/SIDWebClientMasterPage.Master" AutoEventWireup="true" CodeBehind="GridCustomPaging.aspx.cs" Inherits="SIDWebClient.GridCustomPaging" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="pageTitle" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="cphLeft" runat="server">
</asp:Content>
<asp:Content ID="Content4" ContentPlaceHolderID="cphRight" runat="server">
 <asp:DataGrid runat="server" id="MyDataGrid"
  Width="740" CellPadding="4" CellSpacing="0"
  Gridlines="Horizontal" HorizontalAlign="Center"
 
  HeaderStyle-CssClass="tableHeader"
  ItemStyle-CssClass="tableItem"
  AlternatingItemStyle-BackColor="#FFFFCC"
 
  AllowPaging="True"
  AllowCustomPaging="True"
  PageSize="10"
  PagerStyle-Visible="False"
 />
 <center>
 <p class="pageLinks">
  <b>Page
  <asp:Label id="CurrentPage" CssClass="pageLinks" runat="server" />
  of
  <asp:Label id="TotalPages" CssClass="pageLinks" runat="server" />
 </p>
 <asp:LinkButton runat="server" CssClass="pageLinks"
  id="FirstPage" Text="[First Page]"
  OnCommand="NavigationLink_Click" CommandName="First" />
 <asp:LinkButton runat="server" CssClass="pageLinks"
  id="PreviousPage" Text="[Previous Page]"
  OnCommand="NavigationLink_Click" CommandName="Prev" />
 <asp:LinkButton runat="server" CssClass="pageLinks"
  id="NextPage" Text="[Next Page]"
  OnCommand="NavigationLink_Click" CommandName="Next" />
 <asp:LinkButton runat="server" CssClass="pageLinks"
  id="LastPage" Text="[Last Page]"
  OnCommand="NavigationLink_Click" CommandName="Last" />
 </center>
</asp:Content>
 
======================================================================
CREATE PROCEDURE [Get_SIMS_By_Page]
 @CurrentPage int,
 @PageSize int,
 @TotalRecords int output
AS
 --Create a temp table to hold the current page of data
 --Add and ID column to count the records
 CREATE TABLE #TempTable
 (
    ID int IDENTITY PRIMARY KEY,
    SIMNumber nvarchar(50), 
    MSISDN nvarchar(50), 
    IMSI nvarchar(50), 
    SIMStatus nvarchar(50)
 )
 --Fill the temp table with the Customers data
 INSERT INTO #TempTable 
 (
    SIMNumber, 
    MSISDN, 
    IMSI, 
    SIMStatus
  )
 SELECT 
     SIMNumber, 
    MSISDN, 
    IMSI, 
    SIMStatus
 FROM 
    tbl_sims
 --Create variable to identify the first and last record that should be selected
 DECLARE @FirstRec int, @LastRec int
 SELECT @FirstRec = (@CurrentPage - 1) * @PageSize
 SELECT @LastRec = (@CurrentPage * @PageSize + 1)
 --Select one page of data based on the record numbers above
 SELECT 
      SIMNumber, 
    MSISDN, 
    IMSI, 
    SIMStatus
 FROM 
    #TempTable
 WHERE 
    ID > @FirstRec 
 AND 
    ID < @LastRec
 --Return the total number of records available as an output parameter
 SELECT @TotalRecords = COUNT(*) FROM tbl_tickets

Open in new window

mathieu_cuprykAsked:
Who is Participating?
 
nmarunCommented:
I'm not sure what the actual issue is. If you're saying you need to display a million records, this WILL be slow. Please let us know what the actual issue is or where the error is occurring.
0
 
mathieu_cuprykAuthor Commented:
no error. just slow.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.