- Foxtable(狐表) (http://foxtable.com/bbs/index.asp)
-- 专家坐堂 (http://foxtable.com/bbs/list.asp?boardid=2)
---- NB哄哄的分组与交叉统计，统计效率未必NB (http://foxtable.com/bbs/dispbbs.asp?boardid=2&id=3235)
-- 发布时间：2009/6/22 13:23:00
-- NB哄哄的分组与交叉统计，统计效率未必NB
我一直期待的分组与交叉统计的百分比计算功能终于推出了。乍一看确实NB，各种比例的计算，老六考虑的也确实周到。但经过一个上午的测试，其统计效率实在不敢恭维。问题就出在同比、环比等各种比例的计算上。
以一个近20万条的数据库为例，现按照“年、月”分组、统计“面积”及同比增幅：
1、用foxtable的分组统计，只要一运行，电脑风扇就开始忽忽的转，进程显示CPU占用基本都在50%以上。统计完成时间大概在20多分钟。
2、同样的统计项目，如果用SQL的话，只要不到5秒！
差距怎么会这么大呢？请老六检查并看看是否有改进的空间。
等会我把数据库及测试实例发上来。
-- 发布时间：2009/6/22 13:49:00
--
数据库在这里，附件为ACCESS格式，请将其导入到SQL数据库中再测试。
| 下载信息 [文件大小： 下载次数： ] | |
 点击浏览该文件:测试.rar |
测试方法：
1、设置外部数据源，将以下SQL语句复制到新增外部查询表中，点“预览”，不出5秒钟就可看到查询结果：
SELECT a.区间, a.面积 AS 面积_本期, b.面积 AS 面积_同期, CONVERT(numeric(10, 2), CASE WHEN b.面积 <= 0 THEN 0 ELSE (a.面积 / b.面积 - 1) * 100 END) AS 面积_同比 FROM (SELECT CONVERT(varchar(15), replace(str(datepart(year, 日期), 4)+\'年\'+str(datepart(month, 日期), 2) + \'月份\', \' \', \'0\')) AS 区间, SUM(面积) AS 面积 FROM cl_广告明细 GROUP BY CONVERT(varchar(15), replace(str(datepart(year, 日期), 4) + \'年\' + str(datepart(month, 日期), 2) + \'月份\', \' \', \'0\'))) a LEFT OUTER JOIN (SELECT CONVERT(varchar(15), replace(str(datepart(year, 日期), 4) + \'年\' + str(datepart(month, 日期), 2) + \'月份\', \' \', \'0\')) AS 区间, SUM(面积) AS 面积 FROM cl_广告明细 GROUP BY CONVERT(varchar(15), replace(str(datepart(year, 日期), 4) + \'年\' + str(datepart(month, 日期), 2) + \'月份\', \' \', \'0\'))) b ON CONVERT(int, LEFT(a.区间, 4)) = CONVERT(int, LEFT(b.区间, 4)) + 1 AND SUBSTRING(a.区间, 6, 2) = SUBSTRING(b.区间, 6, 2) order by a.区间
2、如果觉得上面的语句太乱，也可在命令窗口执行以下代码：
Dim q As new QueryBuilder
q.TableName = "面积统计"
Dim bm as string = "convert(varchar(15),replace(str(datepart(year,日期),4)+\'年\'+str(datepart(month,日期),2)+\'月份\',\' \',\'0\'))"
Dim yj as string = ("select " & bm & " AS 区间,sum(面积) as 面积 from cl_广告明细 group by " & bm)
Dim sql as string = ("select a.区间,a.面积 as 面积_本期,b.面积 as 面积_同期,convert(numeric(10,2),case when b.面积<=0 then 0 else (a.面积/b.面积-1)*100 end) as 面积_同比 from (" & yj & ") a left join (" & yj & ") b on convert(int,left(a.区间,4))=convert(int,left(b.区间,4))+1 and substring(a.区间,6,2)=substring(b.区间,6,2) order by a.区间")
q.SelectString = sql
q.C \'外部数据源名称
q.Build
MainTable = Tables("面积统计")
这是在外部数据载入到foxtable以后、采用QueryBuilder进行统计的，统计时间同样不超过5秒钟！
3、用foxtable本身的分组统计，请按以下设置进行统计。估计需要20分钟左右才能出结果：
此主题相关图片如下：snap3.gif
-- 发布时间：2009/6/22 14:10:00
--
还是SQL好！
-- 发布时间：2009/6/22 14:44:00
--
不知道老六的比例统计是怎样实现的。放着高效的SQL不用，着实有点让人想不通。分组统计、交叉统计无非是由用户定义了一些参数，把它们套用到SQL语句中不就行了吗？
foxtable的定位比较高端，尤其是对大型数据库的支持。几十万条的数据库很常见，查询统计功能必须也要非常高效才行。请老六考虑改进一下。
-- 发布时间：2009/6/22 14:52:00
--
foxtable是折腾了很久才出来
-- 发布时间：2009/6/22 14:52:00
--
以下是引用reachtone在2009-6-22 14:44:00的发言：
不知道老六的比例统计是怎样实现的。放着高效的SQL不用，着实有点让人想不通。分组统计、交叉统计无非是由用户定义了一些参数，把它们套用到SQL语句中不就行了吗？
foxtable的定位比较高端，尤其是对大型数据库的支持。几十万条的数据库很常见，查询统计功能必须也要非常高效才行。请老六考虑改进一下。
不知道老六的比例统计是怎样实现的。放着高效的SQL不用，着实有点让人想不通。分组统计、交叉统计无非是由用户定义了一些参数，把它们套用到SQL语句中不就行了吗？
foxtable的定位比较高端，尤其是对大型数据库的支持。几十万条的数据库很常见，查询统计功能必须也要非常高效才行。请老六考虑改进一下。
我看看能不能提升性能，不过需说明几点：
1、SQL直接操作后台的数据，对于已经记载的数据，是没有办法用SQL的。
2、统计这么多的数据，还是用SQL比较好，几万的数据，用GroupTableBuilder比较好。
3、FoxTable本身也支持SQL语言直接统计的，大容量，高效率的统计，掌握SQL是应该的。
4、最好不要在Foxtable的一个表中加载20万的数据，这也是Foxtable提供动态加载功能的原因。
5、今天20万，明天100万行呢，难道也全部加载进来统计？
另外，对于这种统计，事先增加年月列，效率会高很多。
[此贴子已经被作者于2009-6-22 15:17:49编辑过]
-- 发布时间：2009/6/22 15:00:00
--
不管怎么样，效率还是要努力提高一下！
-- 发布时间：2009/6/22 15:01:00
--
几十万条的数据库如果一下子加载到foxtable中确实不是一个好的办法，但在实际工作中，这种情况也会经常碰到。比如，要统计连续几年的年度增长率，必须要把这几年的数据都加载进来才行。
现在的问题是，在20万条数据已经加载的情况下，用QueryBuilder和foxtable自身的分组交叉分别进行统计，其效率差别是非常大的。我的意思是，能否改进一下目前的分组、交叉统计。
-- 发布时间：2009/6/22 15:04:00
--
以下是引用wcs在2009-6-22 15:00:00的发言：
不管怎么样，效率还是要努力提高一下！
不管怎么样，效率还是要努力提高一下！
这是当然的。
-- 发布时间：2009/6/22 15:11:00
--
关键还是日期的处理问题问题，如果事先增加年月列，尽管20万行的数据，一样可以几秒钟出来。
此主题相关图片如下：012.gif