如何提高Select查询从数据库中获取大量数据的性能 [英] How to increase the Performance of the Select query fetching Huge data from database

问题描述

我希望在网站上获取大量数据（大约19000条记录）并在datalist控件上显示。

我的数据列表具有分页功能，并且我第一次在datalist上只显示6条记录。

那么他的用户可以去第2页和第3页......

获取所有记录到数据表获得更多时间。

请帮我详细说明我应该在sql server中使用什么。

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.

 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;
       
    }

推荐答案

以下是使用存储过程的实现





CREATE PROCEDURE GetEmployeeDetails

（

@PageNumber int，

@PageSize int < br $>
）

AS

BEGIN

DECLARE @StartRowNum int

DECLARE @EndRowNum int



SET @PageSize = ISNULL（@ PageSize，20） - 默认情况下，我们每页显示20条记录

SET @StartRowNum = @PageSize *（@ PageNumber - 1）+ 1

SET @EndRowNum = @PageNumber * @PageSize



SELECT * FROM

（SELECT的EmpID，ROW_NUMBER（）OVER（ORDER BY的EmpID）作为ROWNUM FROM雇员）如不是Temptable

其中ROWNUM> = @StartRowNum AND ROWNUM< = @EndRowNum

结束



EXEC GetEmployeeDetails 2,20

这将显示每页20条记录

Here is the implementation using Stored Procedure


CREATE PROCEDURE GetEmployeeDetails
(
@PageNumber int,
@PageSize int
)
AS
BEGIN
DECLARE @StartRowNum int
DECLARE @EndRowNum int

SET @PageSize = ISNULL(@PageSize, 20) --As by default we display 20 records per page
SET @StartRowNum = @PageSize * (@PageNumber - 1) + 1
SET @EndRowNum = @PageNumber * @PageSize

SELECT * FROM
(SELECT EmpID, ROW_NUMBER() OVER(ORDER BY EmpID) as RowNum FROM Employee) as TempTable
WHERE RowNum >= @StartRowNum AND RowNum <= @EndRowNum
END

EXEC GetEmployeeDetails 2, 20
This will display 20 record per page

而不是Sql查询，使用存储过程并使用ROW_NUMBER（）函数在存储过程中实现分页。

Instead of Sql query, use a stored procedure and implement paging within the stored procedure using ROW_NUMBER() function.

我想，你需要优化查询。因为您确定每页只显示6条记录，那么您可以在sql查询中自行完成吗？

您可以使用以下查询来执行此操作

Hi,

I think, you need to optimize the query.Because you are so sure that you are going to show only 6 records per page then you can do it in sql query itself right?
You can use the below query to do it

SELECT field_name FROM (SELECT ROW_NUMBER() OVER (ORDER BY field_name)as rowNo,* FROM dbo.table_name)as TotalRecords
 WHERE TotalRecords.rowNo BETWEEN @startingPageNo AND @endingPageNo

这里，对于参数@startingPageNo AND @endingPageNo，你需要传递C＃的值可能更容易。



希望这对你有所帮助。



问候，

RK

Here, for parameter @startingPageNo AND @endingPageNo you need to pass the value from C# which might be easier to do.

Hope this helps you a bit.

Regards,
RK

这篇关于如何提高Select查询从数据库中获取大量数据的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！