title: How to increase the Performance of the Select query fetching Huge data from database
body: <p>i want fetch a huge data on site (about 19000 record) and show that on datalist control.
  my data list have a paging feature and on the first time i show only 6 record on datalist.
  then he user can go to page 2 and 3 and ...
  fetch all record to data table get more time.
  Please help me in details what should i use in sql server.</p>
  
  <pre><code>private void FetchDataToDataList()
  {
      DataTable dt = new DataTable();
      if (Cache["DataTable-cach"] == null)
      {
          String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
          SqlConnection con = new SqlConnection(strConnString);
          SqlCommand cmd = new SqlCommand("SELECT dbo.table_name.field_name, FROM dbo.table_name ", con);
          con.Open();
          dt = new DataTable("T");
          string startime = System.DateTime.Now.ToLongTimeString();
          dt.Load(cmd.ExecuteReader());
          string endtime = System.DateTime.Now.ToLongTimeString();
          Cache.Insert("DataTable-cach", dt, null, DateTime.Now.AddMinutes(5), System.Web.Caching.Cache.NoSlidingExpiration);
          con.Close();
      }
      else 
      {
          dt = ((DataTable)Cache["DataTable-cach"]);
      }
  
      // pagedDS is data list control
      PagedDataSource pagedDS = new PagedDataSource();
      pagedDS.DataSource = dt.DefaultView;
      pagedDS.AllowPaging = true;
      pagedDS.PageSize = 6;
      pagedDS.CurrentPageIndex = CurrentPage;
      dlPaging.DataSource = pagedDS;
      dlPaging.DataBind();
      lblCurrentPage.Text =  pagedDS.PageCount.ToString() +" صفحه    " +  (CurrentPage + 1).ToString()+ " از " ;
      // Disable Prev or Next buttons if necessary
      cmdPrev.Enabled = !pagedDS.IsFirstPage;
      cmdNext.Enabled = !pagedDS.IsLastPage;
  }
  </code></pre>