把数据复制到datatable中

Question

把数据从数据库复制到datatable中，速度非常慢

---

please verify my account

Answer 1

請問是否有代碼嗎?

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 2

你或许可以使用SQL的BulkCopy：https://msdn.microsoft.com/zh-cn/library/ex21zs8x.aspx

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 3

代码

---

please verify my account

代码

Answer 4

你好，

在SqlBulkCopy.WriteToServer 方法 (DataTable) 文章中已经有很详细的代码示例了，我建议你参考一下。

另外，你可以详细描述下为什么你的导出数据到datatable这么慢吗？

是数据源的数据很多，需要做很多查询还是其他原因？

可以贴一下具体的代码吗？

这样可以更加方便地让我们找到问题。

还有，我建议你可以使用SQL Profiler工具来检查你的sql连接和查询语句的执行时间。

如何使用你可以参照如下链接：

http://www.cnblogs.com/kissdodog/p/3398523.html

Answer 5

代码

---

please verify my account

Answer 6

用不用把项目贴出来啊？

---

please verify my account

Answer 7

lctk:

希望你以后可以具体描述问题。最好先上代码。否则问题不明不白的，浪费大家时间哦。

看到你代码里用了模型映射，请问为何还需要映射到DataTable呢？你具体是哪里出了问题？你希望的预期结果是什么？

请截取关键部分代码，你那个是完整代码连接吧？

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 8

问题就是1/337，我想测试数据，它有300多条，每一条都是6,7秒，

我看代码里面有

  KeyNum_Total_Rent = EmployeeItemTotalNum(dr_Month_QuantitativeStatisticsForBusinessTripResource_Rent, "KeyNum", KeyNum_Rent);
                        LookingNum_Total_Rent = EmployeeItemTotalNum(dr_Month_QuantitativeStatisticsForBusinessTripResource_Rent, "LookingNum", LookingNum_Rent);
                        TalksNum_Total_Rent = EmployeeItemTotalNum(dr_Month_QuantitativeStatisticsForBusinessTripResource_Rent, "TalksNum", TalksNum_Rent);
                        SecondViewNum_Total_Rent = EmployeeItemTotalNum(dr_Month_QuantitativeStatisticsForBusinessTripResource_Rent, "SecondViewNum", SecondViewNum_Rent);
                        ResuViewNum_Total_Rent = EmployeeItemTotalNum(dr_Month_QuantitativeStatisticsForBusinessTripResource_Rent, "ResuViewNum", ResuViewNum_Rent);
                    }
                   
                    BindQuantitativeStatisticsInfoChartInfo(CompanyID, CompanyName, DepartmentID, DepartmentName, EmployeeID, EmployeeName
                                        , AuxiliaryID, StationID, StationName, 172, 3, NewHousingResourcesNum_Total_Rent
                                        , MothLoseHousingResourcesNum_Total_Rent, NewClientInfoNum_Total_Rent, MothLoseClientInfoNum_Total_Rent, Total_RentFollowNum_Total_Rent, SurveyNum_Total_Rent
                                        , PictrueNum_Total_Rent, KeyNum_Total_Rent, EntrustNum_Total_Rent, FollowNum_Total_Rent, LookAroundNum_Total_Rent, LookingNum_Total_Rent, TalksNum_Total_Rent
                                        , SincereMoney_Total_Rent, SecondViewNum_Total_Rent,ResuViewNum_Total_Rent, StatisticsYear, StatisticsMonth, StatisticsDay, RegionName
                                        , RegionNO, RegionStartTime, RegionEndTime, ExecDateTime, 1, SendoutCount_Total_Rent, TheWholeCount_Total_Rent, RefreshCount_Total_Rent, 0);
                    #endregion
                    //mess_M = DateTime.Now + "出租量化统计汇总(月) 用时：" + stopwatch_M.Elapsed.TotalSeconds;
                    //Write(DateTime.Now, mess_M);
                    //stopwatch_M.Restart();
                    #endregion

                    #endregion

                    string outThisMessage = "[" + _MangerAuxiliaryStationRound + "/" + _MangerAuxiliaryStationCount + "] 部门:" + Departmentname + "--岗位:" + StationName + "--人员:" + Employeename + "...总耗时:" + st.Elapsed.TotalSeconds + "秒";

我看这个方法BindQuantitativeStatisticsInfoChartInfo是往datatable里面复制数据了，具体的代码我也看不太明白，上面还有一大堆，我猜是这个方法耗时啊

---

please verify my account

Answer 9

你好，

这个只是调用方法的代码，具体方法我们并不能看见啊。

并且上面的链接里面的文件，我下载后安全验证通不过，我建议你贴一下BindQuantitativeStatisticsInfoChartInfo这个方法的代码。

Answer 10

 private void BindQuantitativeStatisticsInfoChartInfo(string CompanyID, string CompanyName, string DepartmentID, string DepartmentName, string EmployeeID, string EmployeeName
                                                                , string AuxiliaryID, string StationID, string StationName, int TransactionType, int DateType, int NewHousingResourcesNum
                                                                , int MothLoseHousingResourcesNum, int NewClientInfoNum, int MothLoseClientInfoNum, int TotalFollowNum, int SurveyNum
                                                                , int PictrueNum, int KeyNum, int EntrustNum, int FollowNum, int LookAroundNum, int LookingNum, int TalksNum
                                                                , int SincereMoney, int SecondViewNum, int ResuViewNum, int StatisticsYear, int StatisticsMonth, int StatisticsDay, string RegionName
                                                                , int RegionNO, DateTime RegionStartTime, DateTime RegionEndTime, DateTime UpdateTime, int Category, int SendoutCount, int TheWholeCount
                                                                , int RefreshCount, decimal TotalScore)
        {
            DataRow dr = dt_QuantitativeStatisticsInfoChart.NewRow();
            dr["ChartID"] = Guid.NewGuid();
            if (CompanyID == "")
            {
                dr["CompanyID"] = DBNull.Value;
            }
            else
            {
                dr["CompanyID"] = CompanyID;
            }

            if (CompanyName == "")
            {
                dr["CompanyName"] = DBNull.Value;
            }
            else
            {
                dr["CompanyName"] = CompanyName;
            }
            if (DepartmentID == "")
            {
                dr["DepartmentID"] = DBNull.Value;
            }
            else
            {
                dr["DepartmentID"] = DepartmentID;
            }
            if (DepartmentName == "")
            {
                dr["DepartmentName"] = DBNull.Value;
            }
            else
            {
                dr["DepartmentName"] = DepartmentName;
            }
            if (EmployeeID == "")
            {
                dr["EmployeeID"] = DBNull.Value;
            }
            else
            {
                dr["EmployeeID"] = EmployeeID;
            }
            if (EmployeeName == "")
            {
                dr["EmployeeName"] = DBNull.Value;
            }
            else
            {
                dr["EmployeeName"] = EmployeeName;
            }
            if (AuxiliaryID == "")
            {
                dr["AuxiliaryID"] = DBNull.Value;
            }
            else
            {
                dr["AuxiliaryID"] = AuxiliaryID;
            }
            if (StationID == "")
            {
                dr["StationID"] = DBNull.Value;
            }
            else
            {
                dr["StationID"] = StationID;
            }
            if (StationName == "")
            {
                dr["StationName"] = DBNull.Value;
            }
            else
            {
                dr["StationName"] = StationName;
            }
            dr["TransactionType"] = TransactionType;
            dr["DateType"] = DateType;
            dr["NewHousingResourcesNum"] = NewHousingResourcesNum;
            dr["MothLoseHousingResourcesNum"] = MothLoseHousingResourcesNum;
            dr["NewClientInfoNum"] = NewClientInfoNum;
            dr["MothLoseClientInfoNum"] = MothLoseClientInfoNum;
            dr["TotalFollowNum"] = TotalFollowNum;
            dr["SurveyNum"] = SurveyNum;
            dr["PictrueNum"] = PictrueNum;
            dr["KeyNum"] = KeyNum;
            dr["EntrustNum"] = EntrustNum;
            dr["FollowNum"] = FollowNum;
            dr["LookAroundNum"] = LookAroundNum;
            dr["LookingNum"] = LookingNum;
            dr["TalksNum"] = TalksNum;
            dr["SincereMoney"] = 0;
            dr["SecondViewNum"] = SecondViewNum;
            dr["ResuViewNum"] = ResuViewNum;       
            dr["StatisticsYear"] = StatisticsYear;
            dr["StatisticsMonth"] = StatisticsMonth;
            dr["StatisticsDay"] = StatisticsDay;
            dr["RegionName"] = RegionName;
            dr["RegionNO"] = RegionNO;
            dr["RegionStartTime"] = RegionStartTime;
            dr["RegionEndTime"] = RegionEndTime;
            dr["UpdateTime"] = UpdateTime;
            dr["Category"] = Category;
            dr["SendoutCount"] = SendoutCount;
            dr["TheWholeCount"] = TheWholeCount;
            dr["RefreshCount"] = RefreshCount;
            dr["TotalScore"] = TotalScore;
            dr["IsUse"] = false;

            dt_QuantitativeStatisticsInfoChart.Rows.Add(dr);
        }

---

please verify my account

Answer 11

已经贴出来了，还需要贴别的吗？

---

please verify my account

Answer 12

您好，

可以使用 BeginLoadData, EndLoadData 及新增 datarow的方式，試看看。

可參考: DataTable新增資料之效率問題

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 13

hi,

链接打不开那

---

please verify my account

Answer 14

在最外面先用 dt_QuantitativeStatisticsInfoChart.BeginLoadData() 及 dt_QuantitativeStatisticsInfoChart.EndLoadData() 類似下面這樣..

 dt_QuantitativeStatisticsInfoChart.BeginLoadData();

// loop add new row

dt_QuantitativeStatisticsInfoChart.EndLoadData();

https://msdn.microsoft.com/zh-cn/library/system.data.datatable.beginloaddata(v=vs.110).aspx

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 15

hi，

还是特别慢那，怎么回事啊？

---

please verify my account

Answer 16

hi，

还是特别慢那，怎么回事啊？

---

please verify my account

如果前台卡死，建议多线程，或者使用Parallel的并发线程。

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 17

您好，

您可以將設定欄位值的方式

dr["ChartID"] = Guid.NewGuid();

改以 index 的方式， 如果 ChartID 是第一個欄位的話

dr[0] = Guid.NewGuid();

詳細可參考: It's too slow to use datatable.rows.add()

datarow改用 object[] 的方式，

例如以下的方式，

object[] itemArray;
table.BeginLoadData();
for (int rowIndex = 1; rowIndex <= RowCount; rowIndex++)
{
 itemArray = new object[table.Columns.Count];
 itemArray[0] = col-0value;
 itemArray[1] = col-1value;
 // ....
 table.Rows.Add(itemArray);
}
table.EndLoadData();

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 18

hi,

好像不是复制数据到datatable慢，

代码如下：

怎么判断哪里比较慢那？

---

please verify my account

Answer 19

您好，

可以下載 redgate 的 ANTS Performance Profiler tool 來分析一下，

https://www.red-gate.com/products/dotnet-development/ants-performance-profiler/

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 20

楼主：问题真解决了？

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 21

看这个状况不是插入datatable的方法费时，是linq查询费时间啊

---

please verify my account

Answer 22

---

please verify my account

Answer 23

---

please verify my account

Answer 24

看看啊，耗时的操作

---

please verify my account

Answer 25

如果没有解决，就请不要标记答案。请取消所有标记操作。：）

此外，是否可以尝试使用Parallel.Invoke方法进行并行处理呢？

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 26

具体来说上面的代码怎么改那？

如果没有解决，就请不要标记答案。

找到耗时的地方了，，，，

---

please verify my account

Answer 27

那我过一段时间在标记答案吧，，，，

---

please verify my account

Answer 28

那我过一段时间在标记答案吧，，，，

---

please verify my account

客气了，我们都是义务的，没有任何利益驱动，所以不必到了一定时间标记答案。分数对我们而言毫无意义——除非你真心觉得问题解决了。

哪儿耗时？可以具体指出来吗？

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 29

看楼上的图啊，

---

please verify my account

Answer 30

楼上的3张图

---

please verify my account

Answer 31

你可以考虑把这些关联表弄成一个View查询，然后把View拉倒LINQ里边当成表查询操作。

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report
Free Tech Books Search and Download

Answer 32

請問是SQL Server嗎?

可以用 SQL profiler 錄一下 linq 組出的 SQL ，

然後用 SSMS 執行一下，看看它的執行計畫是什麼?

不一定 SQL 它會建議你加上index就解掉你的問題了哦!

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 33

 List<DataRow> dr_Looking_Sale = (from b in EnumerableLookingDataTabe
                                                     join c in EnumerableClientInfoDataTable
                                                     on b.Field<Guid>("ClientID") equals c.Field<Guid>("ClientID")
                                                     join a in DepartmentParentDataTable.AsEnumerable()
                                                     on b.Field<Guid>("DepartmentID") equals a.Field<Guid>("DepartmentID")
                                                     where a.Field<Guid>("DepartmentParentID").Equals(NewDepartmentID) && b.Field<int>("TransactionType").Equals(171) && (b.Field<int?>("SeeHouseResult").Equals(1) || b.Field<int?>("SeeHouseResult").Equals(2))
                                                     select b).ToList();

traceMessage = "返回当天所有带看";
                StringBuilder Looking_Sql = new StringBuilder();
                Looking_Sql.AppendFormat("select distinct TransactionType,AuxiliaryID,HFSH.ClientID,IsSeeClient,CSH.SeeHouseTime, CSH.SeeHouseID,DepartmentID=CSH.DepartmentID,CSH.SeeHouseResult,CSH.IsLiangHua from HC_ClientSeeHouse as CSH join HC_HouseForSeeHouse as HFSH on CSH.SeeHouseID=HFSH.SeeHouseID where CSH.RecordTime>='{0}' and CSH.RecordTime<='{1}' and CSH.IsDelete=0 and HFSH.Isback=1  and CSH.BackTime<='{2}' AND CSH.IsLiangHua=1 and (CSH.SeeHouseResult<>3 or CSH.SeeHouseResult is null)", ExecTimeStart, ExecTimeEnd, NextExecTime);
                DataTable LookingDataTabe = SqlHelper.DbHelperSQL.RunSqlDataTable(Looking_Sql.ToString());
                Console.WriteLine("已经提取数据:" + traceMessage + "...{0}条", LookingDataTabe.Rows.Count);

var EnumerableLookingDataTabe = LookingDataTabe.AsEnumerable();

这个链接查询，连接的表在执行链接查询前，数据已经插一遍了，存到了datatable中.AsEnumerable();

var dr_Looking_Rent = (from b in EnumerableLookingDataTabe
                                           join c in EnumerableClientInfoDataTable
                                           on b.Field<Guid>("ClientID") equals c.Field<Guid>("ClientID")
                                           join a in EnumerableDepartmentParentDataTable
                                           on b.Field<Guid>("DepartmentID") equals a.Field<Guid>("DepartmentID")
                                           where a.Field<Guid>("DepartmentParentID").Equals(NewDepartmentID) && b.Field<int>("TransactionType").Equals(172)
                                           select b);
                    if (dr_Looking_Rent != null)
                    {
                        dr_Looking_Rent = dr_Looking_Rent.AsEnumerable().Distinct(new DataTableRowCompare());
                        LookingNum_Rent = dr_Looking_Rent.Count();
                    }
                    Write(DateTime.Now, "管理人员带看量:" + dr_Looking_Rent.Count());

这个好像是这个.Count()耗时间那，

这是怎么回事，我跟踪的，应该是对的吧？

---

please verify my account

Answer 34

我把耗时的语句注释之后速度的确是快了

---

please verify my account

Answer 35

您好，

您那3個datatable資料不少吧!

因為 .Count 才真的下去執行，所以時間會花在那裡.

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/

Answer 36

用什么办法能解决这个问题那，

---

please verify my account

Answer 37

用什么办法能解决这个问题那，

---

please verify my account

考虑直接用SQL方式获得Count个数，然后直接使用SqlCommand的ExecuteScalar方法。

---

ASP.NET Forum
StackOverFlow
FreeRice Donate
Issues to report

Answer 38

應該說，它一定要處理一筆資料就要 Count 一次嗎?

可以全都處理完後，再一次算出所有的再去 Update 嗎?

或是使用 Parallel.For Loop 試看看。

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/