关于大数据查询与导出

2014-08-13 15:04 谢中涞阅读(4148) 评论(15) 编辑收藏举报

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

背景:

数据库服务使用: SqlServer2008 ;

查询的流水表总数据量约在 800W 条左右 ;

需要展示的字段需要从流水表+基础资料表中

导出需要加载指定模板 ;

要求查询响应时间<=2s,导出<=10s; (当然每次仅处理符合条件的数据) .

该系统运行了大概2年时间,系统刚上线的时候,各项性能指标还ok,目前该功能点查询和导出时直接卡死.

该项目为常规 winform 类型,普通三层架构.

改造步骤:

数据库,
1. 该功能主要查询的表为日常业务流水,增长比较大,且查询多已产生的时间段作为查询条件,首先考虑使用创建时间段(每半年做一个分区)做分区表处理.
2. 为提高数据文件读写性能,将该业务流水表存储为独立的数据文件,
3. 创建查询条件 ”创建时间”,“付款公司Id”(int 类型) 和 “付款方式” 字段的索引.
程序方面,
1. 首先引入后台线程, 将耗时的查询从主线程[UI线程]中移除,转为后台线程处理,
2. 采用分页查询数据,每次固定加载1000条数据,待滚动条滚动至当前结果集中最后一页的时候,自动加载下一页数据,
3. 导出需要处理查询和填充文件两个操作.而这两个操作都比较耗时.引入队列+生产/消费模式处理.
4. 去掉关联查询,查询的数据,采用仅查询业务流水表,内存中读取缓存的基础数据组合为前段UI需要的数据,
5. 简化程序,同时也为降低sql语句复杂度,引入ORM,这里引入微软自家的EntityFramework(版本6.0) .

下面略微介绍一下程序中的处理代码片段.

在原界面上增加一个友好提示,

查询功能按钮代码片段

查询按钮点击.

/// <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();          //开始执行后台查询
        }

引入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 = 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();            //加载下一页数据
    }
}

导出功能,队列+生产/消费模式处理

/// <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();           //开始导出线程
}

后台查询数据方法 (基于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 这个组件,详情可参见文章: http://www.cnblogs.com/xie-zhonglai/p/3979771.html

刷新页面返回顶部

九叔大龄九叔

关于大数据查询与导出

About

九叔 大龄九叔

关于大数据查询与导出

About

九叔大龄九叔