为什么SQL聚合函数比Python和Java(或Poor Man的OLAP)慢得多,

SELECT count(id),avg(a),avg(b),avg(c),avg(d) FROM tuples;

使用SQL数据库时这是正常的行为吗？

模式(该表保存对调查的响应)：

CREATE TABLE tuples (id integer primary key,a integer,b integer,c integer,d integer);

copy tuples from '350,000 responses.csv' delimiter as ','

我在Java和Python中为上下文编写了一些测试,他们粉碎了SQL(纯python除外)：

java   1.5 threads ~ 7 ms    
java   1.5         ~ 10 ms    
python 2.5 numpy   ~ 18 ms  
python 2.5         ~ 370 ms

即使sqlite3与Postgres有竞争力,尽管假设所有列都是字符串(相反,即使在Postgres中只使用数字列而不是整数,导致10倍的减速)

我试过没有成功的调整包括(盲目遵循一些网络建议)：

increased the shared memory available to Postgres to 256MB    
increased the working memory to 2MB
disabled connection and statement logging
used a stored procedure via CREATE FUNCTION ... LANGUAGE SQL

所以我的问题是,我的经验在这里是正常的,这是我可以期待使用SQL数据库时？我可以理解,ACID必须付出代价,但这在我看来是一种疯狂.我不是要求实时的游戏速度,但由于Java可以在20毫秒内处理数百万的双打,我觉得有点嫉妒.

有没有更好的方法来做廉价的OLAP(在钱和服务器的复杂性方面)？我已经研究了Mondrian和Pig Hadoop,但是对于维护另一个服务器应用程序并不是非常兴奋,并不知道他们是否能够帮助.

没有Python代码和Java代码做所有的工作,所以可以这么说.我只需生成4个阵列,每个阵列具有35万个随机值,然后取平均值.我不包括一代在时间,只有平均的步骤. java线程计时使用4个线程(每个阵列平均一个),但是绝对是最快的.

sqlite3时序由Python程序驱动,并从磁盘(not：memory :)运行

我意识到Postgres在幕后做得更多,但是大部分工作对我来说并不重要,因为这是只读数据.

Postgres查询不会改变后续运行的时间.

我已经重新运行Python测试,包括将其从磁盘中脱机.时间大概减慢到近4秒.但是我猜测Python的文件处理代码在C中(尽管可能不是csv lib？),所以这表明Postgres没有从磁盘流出(或者你是正确的,我应该屈服于之前谁写了他们的存储层！)

解决方法

如果值不必是100％,或者如果表很少更新,但是您经常运行此计算,则可能需要查看物化视图以加速.

(注意,我没有在Postgres使用物化视图,他们看起来很小,但可能会适应你的情况).

Materialized Views

还要考虑实际连接到服务器的开销以及将请求发送到服务器并返回所需的往返行程.

我会考虑200ms这样的事情是相当不错的,对我的oracle服务器的快速测试,同样的表结构,大约500k行,没有索引,需要大约1 – 1.5秒,这几乎只是oracle吸数据关闭磁盘.

真正的问题是,200ms足够快吗？

– – – – – – – 更多 – – – – – – – – – –

我有兴趣使用物化意见来解决这个问题,因为我从来没有真正玩过他们.这是在oracle.

首先,我创建了一个MV,每分钟刷新一次.

create materialized view mv_so_x 
build immediate 
refresh complete 
START WITH SYSDATE NEXT SYSDATE + 1/24/60
 as select count(*),avg(d) from so_x;

虽然它的刷新,没有行返回

SQL> select * from mv_so_x;

no rows selected

Elapsed: 00:00:00.00

一旦刷新,它比执行原始查询更快

SQL> select count(*),avg(d) from so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:05.74
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL>

如果我们插入到基表中,结果不能立即查看MV.

SQL> insert into so_x values (1,2,3,4,5);

1 row created.

Elapsed: 00:00:00.00
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> select * from mv_so_x;

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899459 7495.38839 22.2905454 5.00276131 2.13432836

Elapsed: 00:00:00.00
SQL>

但等一下左右,MV会在幕后更新,结果可以快速返回.

SQL> /

  COUNT(*)     AVG(A)     AVG(B)     AVG(C)     AVG(D)
---------- ---------- ---------- ---------- ----------
   1899460 7495.35823 22.2905352 5.00276078 2.17647059

Elapsed: 00:00:00.00
SQL>

这不是理想的为了开始,它不是实时的,插入/更新将不会立即可见.此外,您有一个运行查询来更新MV是否需要它(这可以调整到任何时间范围或按需).但是,这显示了MV可以使最终用户看起来快得多,如果你能以不太可能达到第二个准确度的价值来生活.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!