主题：NB哄哄的分组与交叉统计，统计效率未必NB

      我一直期待的分组与交叉统计的百分比计算功能终于推出了。乍一看确实NB，各种比例的计算，老六考虑的也确实周到。但经过一个上午的测试，其统计效率实在不敢恭维。问题就出在同比、环比等各种比例的计算上。
     以一个近20万条的数据库为例，现按照“年、月”分组、统计“面积”及同比增幅：
     1、用foxtable的分组统计，只要一运行，电脑风扇就开始忽忽的转，进程显示CPU占用基本都在50%以上。统计完成时间大概在20多分钟。
     2、同样的统计项目，如果用SQL的话，只要不到5秒！
     差距怎么会这么大呢？请老六检查并看看是否有改进的空间。
     等会我把数据库及测试实例发上来。

数据库在这里，附件为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

还是SQL好！

不知道老六的比例统计是怎样实现的。放着高效的SQL不用，着实有点让人想不通。分组统计、交叉统计无非是由用户定义了一些参数，把它们套用到SQL语句中不就行了吗？
foxtable的定位比较高端，尤其是对大型数据库的支持。几十万条的数据库很常见，查询统计功能必须也要非常高效才行。请老六考虑改进一下。

foxtable是折腾了很久才出来

我看看能不能提升性能，不过需说明几点：
1、SQL直接操作后台的数据，对于已经记载的数据，是没有办法用SQL的。
2、统计这么多的数据，还是用SQL比较好，几万的数据，用GroupTableBuilder比较好。
3、FoxTable本身也支持SQL语言直接统计的，大容量，高效率的统计，掌握SQL是应该的。
4、最好不要在Foxtable的一个表中加载20万的数据，这也是Foxtable提供动态加载功能的原因。
5、今天20万，明天100万行呢，难道也全部加载进来统计？


另外，对于这种统计，事先增加年月列，效率会高很多。

不管怎么样，效率还是要努力提高一下！

几十万条的数据库如果一下子加载到foxtable中确实不是一个好的办法，但在实际工作中，这种情况也会经常碰到。比如，要统计连续几年的年度增长率，必须要把这几年的数据都加载进来才行。
现在的问题是，在20万条数据已经加载的情况下，用QueryBuilder和foxtable自身的分组交叉分别进行统计，其效率差别是非常大的。我的意思是，能否改进一下目前的分组、交叉统计。

这是当然的。

关键还是日期的处理问题问题，如果事先增加年月列，尽管20万行的数据，一样可以几秒钟出来。




此主题相关图片如下：012.gif