将数据处理逻辑SQL放在数据库后端的存储过程到底好不好? data logic SQL in Database vs in Web Application server

将数据处理逻辑放在数据库后端的存储过程到底好不好? @诸超_小石头爸爸 是反对的, @OracleAskTom 是坚决推广的.这一点,我和Tom站在一边. 希望有一天和ChaoPing兄弟来个辩论会,互相学习到有益的经验. 两个人辩论肯定比一个人讲更有意思,更能吸引到听众的注意力.

把问题具体细化一下. (data logic)SQL放在哪里?
追加一个细化条件: 99% 纯SQL, 基本没有CPU运算 的 数据处理逻辑.

关于如何区分 UI, business logic 和 data logic :
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:5662105500346364576

先谈谈我的亲身体会.

案例 1,

当今社会,网络成为瓶颈的机会很大. 我们有个登录事务,4个简单SQL,查序列号,删除旧曲奇,插入新Cookie,COMMIT.一共四次网络传输,如果改成存储过程,只需要一次网络传输,网络立刻畅通. @yong321 @诸超_小石头爸爸 你们怎么看?

案例 2,

十年前, Java developer写了一个客户订单查询系统, 没有做分页处理, 随着时间的推移,用户数据量越来越大,查询响应时间越来越长,极大的浪费了数据库资源. 可是开发人员不对数据库负责,总是提不上他们的议事日程.
这是一个简单而至关紧要的关于控制权和责任的问题, Control and Responsibility.
Facebook说了, Control and Responsibility是他们成功的最重要因素.

参考文章: Scaling Facebook to 500 Million Users and Beyond
http://www.facebook.com/note.php?note_id=409881258919

案例 3,

在我们的好多关键业务模块,同一条一模一样的SQL被执行了好多次,连绑定变量值都是一样的.
Java developer非常依赖ORM, 而且懒得写SQL,或者草草了事,不在乎或者不知道SQL的执行效率.
同上一个案例一样,除非该模块导致数据库停机,他们懒得修改,也不负责任.

相对来说, Java developer写出的SQL要比数据库专业开发人员写出的SQL效率低二到十倍.
(因为鄙人经常能够经过重新设计一些功能模块,提高数据库处理效率五到数十倍)
所以,很多情况下,你根本就不需要水平拆分split, 一个新的优化的设计,就帮助你解决扩展性问题.
还节省了购买新软硬件的成本,降低了因为拆分导致的系统复杂性.

补充一点, 就算是拆分了以后, 你依然可以调用后台存储过程, 中间件应用服务器只需要做一个分发调度器就可以了. 某书店的工作流软件就是这么实施的,大概拆分出十多个数据库.

案例 4,

接着 案例1 的上下文, 其实前3个SQL可以简化成一个SQL INSERT,就解决问题了,
取序列号可以省掉,替代主键索引可以省掉, DELETE可以省掉, 查询配合B+树索引 +INDEX_DESC hint, SELECT 取最新的一条记录就行了, 逻辑读恒定,不会随着数据的增长而增加. INSERT比UPDATE/DELETE引发更少的阻塞锁.
Java developer一般不在乎或者不知道高效SQL,也不是非常了解数据库是如何工作的.
而且他们控制了代码,又不对数据库的问题负责任, 这个, 很难...很难!  这个就是普遍的现实,我们不能活在虚幻当中.
所以要着手解决现实问题. 由数据库专业人员来控制数据库存取的SQL代码.

备注:

@yong321 要求拿出Profiling数据,证明网络是瓶颈.
这一点很好, Critical Thinking: Be critical to what you hear and read. "Critically Analyze What You Read and Hear" 要的就是积极主动的分析思考, 批判性学习. 而不是被动的接受信息.
我们有完整的 Profiling 数据, 剔除敏感信息以后, 性能剖析分析矩阵会被补充进来.

@诸超_小石头爸爸 建议用匿名PL/SQL块(Anonymous PL/SQL block), 也能减少网络传输, eBay就是这么做的.

反方意见:

@诸超_小石头爸爸: 关于业务逻辑作为存储过程数据库里面:如果database不需要licemse我不反对，如果database和app一样容易scale我不反对，如果我是oracle vendor我不反对。如果是传统企业或者很小业务我不反对，如果注定做不大或者就是尝试快速试错我不反对。如果没有7+24的可用性要求我不反对。如果 (11月4日 22:40)

@推车老王: 把业务逻辑放在数据库中？不谈别的，从可扩展性来说是增加APP服务器方便，还是split数据库方便？根本就没有可比性啊！从系统运维角度来看，系统规模越大，业务层与数据层分离就越重要。

@刘德伟goodxp:赚钱不等于正确。没听过技术债吗？欠了多少没人知道，我面试时就遇见过编个理由离开原公司的，因为已经搞不起了，老总还当功臣留他。存储过程效率高，但很难维护，如果逻辑复杂还是应该放在Daemon里，数量多的话可以考虑做一个业务规则引擎。


@yong321:I agree with 推车老王 and 刘德伟. If logic is or will be complicated, it's better stored at app layer; easier to scale up and make it OO (if needed). Otherwise, put it whichever has free CPU capacity.


各位老大说了: It depends.
一定要根据自身条件,设计出能够解决自身问题的合适方案.

这些反方意见对我们目前的情况来说,都不是问题. 我们目前最大的问题就是命名规范. 如何简洁明了的把相关功能特性组织在一个模块里面,使重用最大化.

欢迎各位看官猛烈评论, 给出积极的建设性意见.

-木匠, 2012年冬天

奇怪的Strange PL/SQL complier bug编译臭虫

今天终于完成了数据上载的智能修改, 就是说只修改数据有变化的列.

比如我们的库存表, 有50个列, 52% 的UPDATE仅修改"价格/price"一列.

可是遇到了一个极其古怪的臭虫, 如下所示, 一个简单的Loop循环, 漏掉了End Loop后面的分号";", 程序居然通过了编译,而且运行时也没有报错, 就是 End Loop 后面的语句被当成注释一样给跳过了. 这样的错误太难debug了, 幸好我充分的运用了code instrument技术, 几乎每一行代码都跟了一句debug (类似log4j), 很快找到了错误所在.
...

run_bulk_update;

FOR i in 1 .. lt_top_pattern.Count
Loop
run_bulk_update_tophit(p_hit_cols => lt_top_pattern(i));
End Loop ;

run_bulk_insert;
save_col_upd_pattern;
...

数据库版本是11.1.0.7.0, SunOS 5.10

PL/SQL代码可以有多大size limit

PL/SQL程序块的最大尺寸是67108864 (2**26) DIANA节点, 主要是受shared memory pool的限制.
PL/SQL程序块包括(Package)包头,包体,独立存储过程,或者匿名程序块. eBay好像是统统使用匿名PL/SQL块.

但是对于DIANA节点,我们只能获取一个大概的数字,通过查询视图USER_OBJECT_SIZE,PARSED_SIZE列里面是"扁平化的flattened" DIANA节点数:

SQL> SELECT * FROM user_object_size WHERE name = 'PKG1';

NAME TYPE        SOURCE_SIZE  PARSED_SIZE  CODE_SIZE  ERROR_SIZE
----------------------------------------------------------------
PKG1 PACKAGE              46          165        119           0
PKG1 PACKAGE BODY         82            0        139           0

根据Tom和Tim(http://oracle-base.com/)的理想境界,去除Java/C/.Net等前端应用代码里面的所有SELECT/INSERT/UPDATE/DELETE/MERGE,把那些丑陋的SQL扔到九霄云外.
--不是说Java Developer不会写SQL, 而是他们的强项不在数据库. 模块化,专业分工,高效合作,

鄙人最近三个月奋发图强,努力工作,大力推行Database API, 消灭Java应用里面的SQL代码,书写了很多PL/SQL代码,有一个接近100K,编译一遍耗时数秒, 就开始担心最大尺寸问题. 结果PARSED_SIZE只有6115, 离67108864(64M)还差点远着哩.

当然鄙人也在认真学习Agile编程里面的Refactoring代码重构, 尽量将重复使用的代码段抽象成子过程,或者工具包.

参考:
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

Appendix C PL/SQL Program Limits
.

PL/SQL ADT collection usage比较自定义类型数组与PL/SQL数组的性能

从今天开始, 木匠开始介绍BASE架构中数据库部分的基础部件.
有了这些基础准备知识以后, 我们再来谈(big picture)整个画面: 异步处理 和 数据复制.

PL/SQL 是最好的Oracle数据库数据处理和编程语言, 这里讲讲如何提取数据并放进用户数据类型数组ADT/UDT collection. 我仅知道3种方法, 我们通过一组Benchmark找到每个方法的优缺点,帮助指导实践.

先写了英文的,然后翻译成中文. 详细初始化设置和Benchmark的代码, 请参考英文版链接, 很多关键字看英文版会更清楚.
你可以暂时忽略create table里面的list partition分区部分, 以后会用到.

欢迎提出更多更有效的方法.

Benchmark方案
-- -- ---
* RunStats
* SQL trace, 推荐使用Christian Antognini的TVD$XTAT trace 分析工具 , 读过TOP的人都知道, ^_^.

测试结果
-------
PL/SQL 内置数组(Associative Array and Nested Table) 是从数据库获取结果集的最省资源的方法.
但是有些时候,你需要把数组当作表(table),来跑一个查询, 你就必须用到ADT/用户数据类型数组.
比如:

Declare
lt_item_tab item_tab;
Begin
select item_name, qty, price from TABLE(lt_item_tab);
End;

3种方法 和 他们的优缺点:

1) 将多个列转换成用户数据类型数组, 成批放进数组.
优点: 代码简洁.
缺点: 和第二种方法相比, 多消耗50%的CPU时间; Latch锁数差不多.

2) 使用multiset, 在SQL里面直接构造出用户数据类型数组, 作为一行/一个单元放进数组.
优点: 使用较少资源, 在我的测试案例里面, 比第一种方法CPU时间少50%.
缺点: 代码有点复杂; 在版本10.1中,不支持SQL Returning语法. (明天试一下11.1.0.7).

3) 成批放进 PL/SQL 内置数组, 然后复制数据到用户数据类型数组.
优点: 使用较少资源, 67% better than approach 1 in my test case; Latch锁数少一半.
缺点: PGA内存多一倍, 合乎逻辑, 代码有点复杂, 维护性较差.

注释: ADT: Abstract Data Type, 也叫作 User Define Datatype.

鄙人以为, 以上所有代码都很简单, 复杂只是相对而言. 嘻嘻.