大量数据的导入 (分页优化) 完整的实现过程实例

背景：每天需要从sqlserver数据库中导出数据，用于另一个系统的计算提供数据。

难点：而每天的数据量特别大，最多时近50万条数据。

优化：sql之所以导入慢的原因是因为向数据库中添加的时候慢，数据量大添加就慢，每一千条添加一次就快了。

• 先看一个分页的代码：

原导出sql： 数据量400000

string sql = @"select * from(select b.* from   (select * from  viewCt1HistoryTitle 
 where ( 1=1  and  cndtSCInDate between '" + t1 + "' and '" + t2 +
 "'and(cnvcContractType <> '借货订单')) ) a join 
viewCt1HistoryIncludeBorrowConvertToSalesDetail b  on a.cnvcContractID = 
b.cnvcContractID AND a.cniVersionID = b.cniVersionID AND a.cniIsBalanceForm = b.cniIsBalanceForm ) d";

改造后的sql:：

string sql = @"select * from(select *,ROW_NUMBER() OVER (ORDER BY cnvcContractID DESC)
 RN from (select b.* from   ( select * from  viewCt1HistoryTitle  where ( 1=1  and 
 cndtSCInDate between '" + t1 + "' and '" + t2 + "'and(cnvcContractType <> '借货订单')) )
 a join viewCt1HistoryIncludeBorrowConvertToSalesDetail b on a.cnvcContractID = 
b.cnvcContractID AND a.cniVersionID = b.cniVersionID AND a.cniIsBalanceForm = 
b.cniIsBalanceForm ) d )tmp where tmp.RN between'" + c + "' and '" + b + "'";

sql差异总结：

select * from(select *,ROW_NUMBER() OVER (ORDER BY cnvcContractID DESC)   cnvcContractID 为主键
 RN from (

    原sql

)tmp where tmp.RN  可以加其他条件

 

 

• 完整的代码实现：

求总数： select * from(  原sql  )    数据量=400000

string count = @"select b.* from   ( select * from  viewCt1HistoryTitle  where ( 1=1  
and  cndtSCInDate between '" + t1 + "' and '" + t2 + "' and(cnvcContractType <> '借货订
单')) ) a   join viewCt1HistoryIncludeBorrowConvertToSalesDetail b on a.cnvcContractID = 
b.cnvcContractID AND a.cniVersionID = b.cniVersionID AND a.cniIsBalanceForm = 
b.cniIsBalanceForm";
			

 

 分页代码：

int numberCount = number.Rows.Count; //总数
int num = numberCount / 1000 + numberCount % 1000 == 0 ? numberCount / 1000 : 
numberCount / 1000 + 1; //页数导入次数 

分页导入：

			for (int i = 1; i <= num; i++)
			{
				int c = (i - 1) * 1000 + 1;
				int b = i * 1000;

				string sql = @"select * from(
						select *,ROW_NUMBER() OVER (ORDER BY cnvcContractID DESC) RN from (
					select b.* from   ( select * from  viewCt1HistoryTitle  where ( 1=1  and  cndtSCInDate between '" + t1 + "' and '" + t2 + "'and(cnvcContractType <> '借货订单')) ) a join viewCt1HistoryIncludeBorrowConvertToSalesDetail b on a.cnvcContractID = b.cnvcContractID AND a.cniVersionID = b.cniVersionID AND a.cniIsBalanceForm = b.cniIsBalanceForm ) d )tmp where tmp.RN between'" + c + "' and '" + b + "'";
				DataTable dt = CDIPSqlHelper.ExecuteDataTable(sql, CommandType.Text);
				AddRunTag(dt);  //每一千条数据则导入一次。
			}

插入代码：每一千条数据则导入一次。   

private void AddRunTag(DataTable tagModel)
		{
			try
			{
				IList<SMS_ORDERDETAIL> resultList = ModelConvertHelper<SMS_ORDERDETAIL>.ConvertToModel(tagModel);
				for (int i = 0; i < resultList.Count; i++)
				{
					resultList[i].ORDERDETAILID = Guid.NewGuid().ToString("N");
					resultList[i].CREATE_DT = DateTime.Now;
					resultList[i].MAINDATE = DateTime.Now.ToString("yyyyMMdd");
				}
				DBAction<SMS_ORDERDETAIL>.AddEntityWithoutAutoDetectChangesEnabled(resultList);
			}
			catch (Exception ex)
			{
				throw;
			}

		}