首页 > 代码库 > 关于大数据查询与导出

关于大数据查询与导出

2024-07-17 09:27:40   227人阅读

上周末,帮朋友处理了一个关于大数据的查询与导出问题,整理一下,在此记录一下用以备忘,同时也为有类似需要的朋友提供一个参考.

背景:

  1. 数据库服务使用: SqlServer2008 ;
  2. 查询的流水表总数据量约在 800W 条左右 ;
  3. 需要展示的字段需要从流水表+基础资料表中
  4. 导出需要 加载指定模板 ;
  5. 要求查询响应时间<=2s,导出<=10s; (当然每次仅处理符合条件的数据) .
  6. 该系统运行了大概2年时间,系统刚上线的时候,各项性能指标还ok,目前该功能点查询和导出时直接卡死.
  7. 该项目为 常规 winform 类型,普通三层架构.

改造步骤:

  1. 数据库,
    1. 该功能主要查询的表为日常业务流水,增长比较大,且查询多已产生的时间段作为查询条件,首先考虑使用创建时间段(每半年做一个分区)做分区表处理.
    2. 为提高数据文件读写性能,将该业务流水表存储为独立的数据文件,
    3. 创建查询条件 ”创建时间”,“付款公司Id”(int 类型)  和 “付款方式” 字段的索引.
  2. 程序方面,
    1. 首先引入后台线程, 将耗时的查询从主线程[UI线程]中移除,转为后台线程处理,
    2. 采用分页查询数据,每次固定加载1000条数据,待滚动条滚动至当前结果集中最后一页的时候,自动加载下一页数据,
    3. 导出需要处理 查询和填充文件 两个操作.而这两个操作都比较耗时.引入队列+生成/消费模式处理.
    4. 去掉关联查询,查询的数据,采用仅查询业务流水表,内存中读取缓存的基础数据组合为前段UI需要的数据,
    5. 简化程序,同时也为降低sql语句复杂度,引入ORM,这里引入微软自家的EntityFramework(版本6.0) .
  3. 下面略微介绍一下程序中的处理代码片段.
    1. 在原界面上增加一个友好提示,
      image
    2. 查询功能按钮代码片段
      1. 查询按钮点击.
        /// <summary>        /// 响应查询按钮事件        /// </summary>        /// <param name="sender"></param>        /// <param name="e"></param>        private void btnQuery_Click(object sender, EventArgs e)        {            if (txtCompany.Tag == null)            {                MessageBox.Show(this, "请选择指定的结算公司", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);                return;            }            _pageIndex = 0;            lblTotalCount.Text = "当前共[0]条数据";            gridData.Rows.Clear();            LoadQueryData();        }        /// <summary>        /// 开始查询数据        /// </summary>        private void LoadQueryData()        {            plProcessStatus.Visible = true;     //展示进度panel            SetControlStatus(true);             //设置其他功能控件暂时为只读状态            _isCurrentLoadEnd = false;          //标识正在加载数据            bgwQuery.RunWorkerAsync();          //开始执行后台查询        }
      2. 引入BackgroundWorker组件执行后台查询 ,关于构建EF查询条件可参见之前文章[使用EF构建企业级应用(三)].
        /// <summary>/// 后台线程查询数据/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void bgwQuery_DoWork(object sender, DoWorkEventArgs e){   int total;   _queryList = ExecQuery(_pageIndex, _pageSize, out total);   _rowCount = total;}/// <summary>/// 执行分页查询方法,返回当前查询结果,/// </summary>/// <param name="pgIndex">当前页码</param>/// <param name="pgSize">每次查询分页大小</param>/// <param name="total">记录总数</param>/// <returns></returns>private List<OrderDetail> ExecQuery(int pgIndex, int pgSize, out int total){    List<OrderDetail> lst = null;    var queryParam = BuildQueryExpression(pgIndex, pgSize);    using (var services = new KYEService())    {        lst = services.GetOrderDetailList(queryParam, out total);    }    return lst;}/// <summary>/// 构建查询条件/// </summary>/// <param name="pgIndex">当前查询第几页</param>/// <param name="pgSize">当前查询分页大小</param>/// <returns>当前查询条件</returns>private EFQueryParam<OrderDetail> BuildQueryExpression(int pgIndex, int pgSize){  //计算查询时间段  var queryBeginDate = new DateTime(_queryYear, _queryMonth, 1);  var queryEndDate = queryBeginDate.AddMonths(1).AddDays(-1);  //构建查询条件  var exp = QueryBuilder.Create<OrderDetail>();  exp = exp.Equals(t => t.PaymentCompanyId, (int)txtCompany.Tag);//结算公司  exp = exp.GreaterThanOrEqual(t => t.FromDate, queryBeginDate); //纳入月份转化为开始日期  exp = exp.LessThanOrEqual(t => t.FromDate, queryEndDate);      //纳入月份沾化为结束日期  if (_queryPaymentType != EPaymentType.ALL)  {      exp = exp.Equals(t => t.PaymentType, _queryPaymentType);   //付款方式  }  //执行查询  var queryParam = new EFQueryParam<OrderDetail>(exp, "FromDate", true, pgIndex, pgSize);  return queryParam;}/// <summary>/// 查询数据线程结束后,开始UI绑定数据/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void bgwQuery_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e){    plProcessStatus.Visible = false;                 //隐藏进度条    if (_queryList != null && _queryList.Count > 0)  //当前查询有数据    {        //后台线程异步展示数据到UI        Thread thBindGrid = new Thread(() =>        {            lblTotalCount.Invoke(new Action(() =>            {                lblTotalCount.Text = string.Format("当前共[{0}]条数据", _rowCount);            }));            //循环绑定数据            for (int i = 0; i < _queryList.Count; i++)            {                gridData.Invoke(new Action<OrderDetail>(FillData), _queryList[i]);            }            _isCurrentLoadEnd = true;     //标识当前查询加载结束            //绑定结束恢复其他功能按钮为可用状态(设置为非只读)            btnExport.Invoke(new Action(() =>            {                SetControlStatus(false);            }));        });        thBindGrid.IsBackground = true;        thBindGrid.Start();    }    else    {        SetControlStatus(false);    }}
        /// <summary>/// 绑定具体行数据/// </summary>/// <param name="detail">具体行数据</param>private void FillData(OrderDetail detail){    var index = gridData.Rows.Add();    gridData["dgcIndex", index].Value = http://www.mamicode.com/index + 1;    //TODO... 具体绑定到Grid的代码略}
        /// <summary>/// 处理滚动条移动的时候,自动加载下一页数据/// </summary>/// <param name="sender"></param>/// <param name="e"></param>private void gridData_Scroll(object sender, ScrollEventArgs e){    //已经加载结束或者水平滚动,则不处理    if (!_isCurrentLoadEnd                      //当前查询数据还未绑定结束        || _rowCount <= gridData.Rows.Count     //当前符合条件的数据已经查询完毕        || e.ScrollOrientation == ScrollOrientation.HorizontalScroll)    {        return;    }    var gridPageRowCount = gridData.DisplayRectangle.Height;    //Grid每页能显示的记录数    //当前滚动到最后一页    if (gridData.FirstDisplayedScrollingRowIndex >= gridData.Rows.Count - gridPageRowCount - 1)    {        _pageIndex += 1;        LoadQueryData();            //加载下一页数据    }}
    3. 导出功能
      /// <summary>/// 执行导出操作/// </summary>/// <param name="p"></param>private void ExecExport(string fileName){    plProcessStatus.Visible = true;    SetControlStatus(true);    IExport rpter = new ExcelExporter();    var formater = BuildExportFormater();    bool isQueryEnd = false;                    //当前是否查询结束    var templateFieName = Path.Combine(Application.StartupPath, "Template", "Rpt_CustomerList.xls");    //创建供导出的队列    Queue<List<OrderDetail>> exportQueue = new Queue<List<OrderDetail>>();    #region 查询线程    //处理后台查询    Thread thQuery = new Thread(() =>       {           int tempTotal = 0;           int tempPgIndex = 0;           int queryPageSize = 3000;      //每次查询3k           var tempList = ExecQuery(tempPgIndex, queryPageSize, out tempTotal);           if (tempList != null && tempList.Count > 0)           {               lock (locker)               {                   exportQueue.Enqueue(tempList);                   Monitor.PulseAll(locker);               }               tempPgIndex += 1;               //循环查询直至查询结束               while (tempPgIndex * _pageSize < tempTotal)               {                   var temp_tempList = ExecQuery(tempPgIndex, queryPageSize, out tempTotal);                   if (temp_tempList != null && temp_tempList.Count > 0)                   {                       lock (locker)                       {                           exportQueue.Enqueue(temp_tempList);      //将查询结果加入到队列                           Monitor.PulseAll(locker);                       }                   }                   tempPgIndex += 1;               }           }           isQueryEnd = true;       });    #endregion    #region 导出excel线程    //处理将查询的结果写入到文件中    Thread thExport = new Thread(() =>       {           rpter.Export(templateFieName, fileName, formater);//读取模板,并创建新文件,           int tempRowIndex = 0;           while (!isQueryEnd || exportQueue.Count > 0)     //未查询结束及队列不为空,执行导出           {               if (exportQueue.Count > 0)               {                   List<OrderDetail> tempExpotLst = null;                   lock (locker)                   {                       tempExpotLst = exportQueue.Dequeue();        //取队列数据,导出excel操作                   }                   if (tempExpotLst != null && tempExpotLst.Count > 0)                   {                       formater.DetailRowBeginIndex += tempRowIndex;                       rpter.ExportByAppend(fileName, formater, tempExpotLst);          //执行导出操作(追加形式)                       tempRowIndex = tempExpotLst.Count;                   }               }               else               {                   Thread.Sleep(200);               }           }           //导出贴图片           var imgRow = formater.DetailRowBeginIndex + tempRowIndex + 8;           formater.ImageCellFormaters.Add(new ImageCellFormater(imgRow, 2, Resources.ywz));           rpter.ExportByAppend(fileName, formater, null);           //导出结束 恢复按钮可用状态           btnExport.Invoke(new Action(() =>           {               plProcessStatus.Visible = false;         //隐藏进度栏               SetControlStatus(false);               if (MessageBox.Show(this, "数据已成功导出至[" + fileName + "],是否立即打开导出文件?",                   "提示",                   MessageBoxButtons.YesNo,                   MessageBoxIcon.Question) == DialogResult.Yes)               {                   Process.Start(fileName);               }           }));       });    #endregion    thQuery.IsBackground = true;    thExport.IsBackground = true;    thQuery.Start();            //开始查询线程    thExport.Start();           //开始导出线程}
  4. 后台查询数据方法 (基于EF实现),
    /// <summary>/// 获取物流明细记录/// </summary>/// <param name="queryParam">查询条件</param>/// <param name="total">返回符合条件的总记录数量</param>/// <returns></returns>public List<OrderDetail> GetOrderDetailList(EFQueryParam<OrderDetail> queryParam, out int total){    total = 0;    var lst = GetRepository<OrderDetail, Int64>().Get(queryParam, out total).ToList();    //组织其他冗余数据    if (lst != null && lst.Count > 0)    {        //冗余公司信息,供前台UI使用        var companyList = GetCompanyList();        if (companyList != null && companyList.Count > 0)        {            var companyDic = companyList.ToDictionary(p => p.Id);               //转化为字典,提高效率            var tempbgIndex = queryParam.PageIndex * queryParam.PageSize + 1;   //生成排序            lst.ForEach(t =>            {                t.Index = tempbgIndex;                //寄件公司                if (companyDic.ContainsKey(t.FromCompanyId))                {                    t.FromComoany = companyDic[t.FromCompanyId];                }                //收件公司                if (companyDic.ContainsKey(t.ToCompanyId))                {                    t.ToCompany = companyDic[t.ToCompanyId];                }                //付款公司                if (companyDic.ContainsKey(t.PaymentCompanyId))                {                    t.PaymentCompany = companyDic[t.PaymentCompanyId];                }                tempbgIndex += 1;            });        }    }    return lst;}/// <summary>/// 获取公司信息/// </summary>/// <returns></returns>public List<Company> GetCompanyList(){    //从缓存中获取    var lst = ApplicationRuntime.Instance.CurrentCache.Get<List<Company>>(KYEConsts.CachesKey.Company,        () => this.GetRepository<Company>().Get().ToList());    return lst;}

结语: 经过这一系列改造后, 性能上大大改进了,查询响应耗时<=1s, 导出<=8s, 每次符合条件的数据大概在2W条左右.

至于本文中提到的

1. EF构建查询条件表达式,及查询数据库方式,可参见之前文章.http://www.cnblogs.com/xie-zhonglai/archive/2012/04/07/2435903.html

2. 导出Excel.本文使用了NPOI 这个组件,详情可参见文章:


联系
我们