关于创建动态统计报表的若干问题性能效率讨论

[zlt982001]

因需要建立一个 可被用户自定义的 动态统计报表系统 ,用户可以通过定义单元格取数公式来动态生成各种统计报表,现在就向数据库取数方式以及效率有些疑问,希望大家可以探讨一下(

前提条件: 本报表需要兼容于 Access 和 SQLServer ,用同一套代码需要进行向两种数据库分别取数,所以不能够使用不兼容的语句和方法.而且也不能使用存储过程.
只能使用程序代码和 ADO 加上 标准的sql语句.在此基础上提供性能较优的解决方案


假设当前需从 销售单,采购单,等等单据中统计出各个产品的销售/采购 数量,金额 的和计
用户通过定义 设置了以下报表 预期产生大致下列格式

产品编码 销售数量 销售金额 采购数量 采购金额
A 100 200 200 400
B 5 10 2 5
....
其中涉及 数据表有 产品资料表,销售主表+销售明细表, 采购主表+采购明细表
其中 主表和子表使用表ID 关联, 产品资料 和销售单,采购单 使用 产品编码 关联
索引: 所有表的产品编码均设置为索引,所有Select 都只选择需要的字段
测试的表: 产品 900 条, 销售单明细 50000 条 采购单明细 100 条
机器:赛扬1.0G,256m 内存 win2000 ,本地 Access 数据库, ado 2.7


问题: 好像没有 纯粹的 SQL 语句能够直接实现以上查询

解决方式一: 1.首先从产品资料 提取所有产品编码 填充到 报表中 (如此是为了保证每一可能发生的产品都得到统计)
2.循环所有已经提取的产品编码, 再通过独立的查询 获得该产品 的销售,采购数量等SUM值,填充到该行中,

效率分析:
1次读取产品资料 + 每条商品至少调度一次记录集(900 * 2 因为数量和金额可以一次获得) 乘以 需要取数的列数,效率相当低,
但比较灵活(如财务上的资产负债表取数则是该种方式)
实际运行: 20-30 秒 (如果需要填充的行数或列数多,数字呈倍数增加)

解决方式二: 1.还是先提取所有产品编码 填充到 报表中..

2,分别打开 记录集 所有销售单 和 采购单 (如: select 产品编码,sum(数量) as 数量1,sum(金额) as 金额1 from 销售单 inner join 销售单明细 on 销售单.id=销售明细.id Group by 产品编码)

循环 所有表格行
使用 ADO.Find(Access 好像不支持seek) 方法 进行查找 产品编码= 表格行中的产品编码, 填充表格行相关列

效率分析: 一次性读入 销售单,再进行查找,避免了反复调用 ADO ,反复返回记录集,以及反复编译SQL语句的操作,速度比较快
find 没有使用索引,索引起不到作用,可能需要更多的内存(cache读入多个记录集)
调度 次数 : 1次读取产品资料+1次读取销售单+1次读取采购单+900 次find
实际运行:5秒以内(有没有索引一样)

求解决的其他方式

[daijingjie2002]

你的前台用什么？

[Softlee81307]

up

[zlt982001]

以上方式还有一个通病: 没有发生销售和采购的 空行太多,还要进行去除

[skm]

水晶报表，可以自定义报表

[zlt982001]

没人会答吗?

[samuelpan]

先估算查询的数据量。
再考虑是否先构造sql语句直接取数。

或者将数据拉到客户段再进行处理。

[zlt982001]

aa

[zjcxc]

如果你要通用,而且用户可以自定义公式,而且要兼容

那可能没有什么好办法,在公式上,你可以做做文章,为所有的单独取数据的公式做一个公式表
比如,用户定义如下公式:
期初数量
采购数量
销售数量
期末数量=期初数量+采购数量-销售数量

那么,你可以将它放入公式表中,单独先计算前三个公式,后一个公式再从计算的结果中取数得到最终结果,这样可以减少取数据的工作

但设计处理上就麻烦很多

[zjcxc]

或者考虑用现成控件,看看chinaexcel这个东东是否对你有用,它可以自定义公式