×

Loading...
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。
Ad by
  • 最优利率和cashback可以申请特批,好信用好收入offer更好。请点链接扫码加微信咨询,Scotiabank -- Nick Zhang 6478812600。

Although SQL looks like very simple, from data perspective, it’s very powerful. SQL itself is (data) set/table oriented. If you still try it in common language’s logic, it’s really waste.

About E-bay’s comments of put joins operation in application layer, I really doubt about it. For example, MS has being putting many efforts in ADO.NET on application side. Its one object DataSet is very powerful. The many functions like referential integrity, sorting, data filtering can be done there but not joins.

One good example using SP is multi-tables insert operation (some late inserting relies on result(s) of previous inserting. If you put the operations in application layer, you need many database calls. On the other hand, you only need one database by using SP. One of other benefits is security. Using SP can efficiently prevent from malicious SQL attacks.

Of course, nothing is perfect. In order to utilize recourse efficiently, it’s better to put as much tasks as possible in application layer to save expensive database resource.
Report

Replies, comments and Discussions:

  • 工作学习 / 专业技术讨论 / 有没有人愿意讨论一下stored procedure在大型web项目中的使用问题。最近公司里有人担心将来scaling以及可迁移性的问题,建议把sp全部从数据库中迁移出来。
    网上search了一下,有关这个问题有很多争论,有的论点很极端,比如这个:http://www.tonymarston.net/php-mysql/stored-procedures-are-evil.html,但听着也不无道理,再加上听说ebay就是避免用任何sp和trigger,心里很是疑惑,想听听大家的意见。

    顺便说一下,我们在开发一个类似于myspace的social network网站,那位大侠知道那里有有关现有的一些大网站,如yahoo,google,myspace,facebook,youtube的设计架构之类的资料(当然是公开的),指点一下,本人不胜感激。
    • 那个只是再给mysql的sp缺陷找借口,属于PR的文章,不能算专业文章。
    • 那些资料,如果是公开的,作为业者你自己应该能找的;如果不是公开的,那就有巨大的市场价值,谁会告诉你? 同学,你自助吧
      • 任何技术讨论我都欢迎,但是冷嘲热讽就算了吧. 我当然自己也作research,但是每个人的research都有可能有局限性和盲点,我想如果有人愿意指点一下就指点一下,不想费事就算了,这个要求也不过分吧?
        至于说专业资料,我也说了我想看“公开的”资料,也就是网上公开的,我有可能遗漏的资料,而且我也没有要求多么详细,能有一些idea就行,我想这样的资料还是有的,比如这个:http://www.addsimplicity.com/downloads/eBaySDForum2006-11-29.pdf

        也许是我的表述不太准确吧,如果是就向您道歉了。
    • scaling:很多数据库都有cluster; 可迁移:即使不用SP,换数据库也不是简单的事; SP:效率高,可充分利用数据库的性能;trigger:尽量不用
      • 我现在的公司就是一个SP都不用,不过到时有很多 trigger,哈哈。算了,反正公司大,也不到我说了算
        • 你们公司迟早会吃苦头的……触发器应该只用作简单的审计或数据校验。
          • 恩,我的公司也是,原则就是一个trigger都不用。
          • 我同意 - trigger还是能不用就不用,trigger不利于维护而且用得不好容易引起出乎意料的问题。但是sp - 说老实话,一下子不让我用还真有点接受不了。
            一个不使用sp 的理由就是business logic和data access layer的分离,也就是说business logic 要尽可能的放在数据库外,我的理解是这样做利于将来的horizonal scaling,但是这样做要损失performance(不能充分利用数据库的功能)以及安全性,但我不知道具体损失有多少,两方面得失孰轻孰重...
            • 与我心有戚戚焉。补充一点,我主张stored procedure仅用于后台数据处理。在real time的web application里不要直接使用stored procedure。
              • 我们的realtime事务全用存储过程。这不是最需要performance的地方吗?
                • 真正的performance来源于数据库的合理规划和设计以及应用程序清晰简洁的逻辑,而不是靠stored procedure来获得。避免实时调用stored procedure是避免stored procedure被滥用。许多程序员对于
                  现有的functions、stored procedures、objects methods等等的都当作black box,只管哪来用,而不管这样的用法是否是有效。也许,一个简单的含有table join的query可以取代多个stored procedures的调用也未必。我见到过太多被滥用的例子,所以不主张stored procedure被real time application直接调用。
                  • 我们的开发人员都有明确分工,存储过程也有明确的用途,不存在滥用问题。"一个简单的含有table join的query可以取代多个stored procedures的调用"那只说明这些sp都是低级的table api而不是事务级的。
                    • 我上面只是举个例子而已。事务级的SP,时间比较长,也不适合real time application。尤其是流量比较大的web application,一个0点几秒的SP都太慢。
                      • 这理由就更不成立了。我至今还没发现一个这种例子。不使用SP,实际上就是把实现business logic的部分用另一种语言实现,当然这种做法见仁见智,但那些SQL一条都少不了的,最终还是要提交给数据库运行,怎么会更快?
                        如果business logic要用到的数据不在application server cache,那就必须再读出来,就更慢了
                        • 当然不是简单地“把实现business logic的部分用另一种语言实现”,如果是那样,还是用SP吧。我给你一个例子吧。如内:

                          Product Table

                          Product IdCategory IDProduct namequantity
                          ....

                          Category Table

                          Category IdParent IDCategory name
                          ...

                          如果要实时查询任一category(含所有descendant categories)下商品数量总和,通常做法就是用个SP,接受category id,输出quantity,SP里面recursive。如果category层数太多,速度就很慢。假如需要一个report page,树状列出所有category with the total quantity,嘿嘿,你去隔壁block喝杯咖啡再回来未必结果出得来。

                          • 哈哈,这个我做过。用ORACLE的self join 一个SQL搞定。即使没有自连接,也用不着递归。只需对category表反复扫描(通常parent id有很好的索引)并将你的结果存放到中间表,最后把中间表和product表join就可以了。
                            • 如果你不用SP,实现方法肯定类似,无法就是把category表放到内存里遍历罢了。到最后,COUNT一次和多次你说哪个快?
                            • 是的,这是一个很典型的例子。相信很多人都碰到过。不用SP就会迫使application developer用一个更好的方法来实现。假如有那么个SP在,谁会不去用呢?另外,反复扫描外加使用中间表也不是最好的solution。:)
                              • 哦,那你说说你的更好方法。如何遍历并生成树?最后SELECT COUNT的时候是一个SQL还是多个?
                                • 需要遍历的肯定快不了。俺的专利办法是~~~
                                  咳咳,既然是专利,就不能随便乱说的啦。:)不过可以提示一下,帮大家开拓一下思路。有没有办法让ID本身包含更多的信息以避免遍历?
                                  • 这哪里是什么专利?不就是 1.2.3.4 这样的ID表示它的上级和祖先吗?那你完全可以把这个信息存在数据库里,只扫描一遍。
                                    • close了,但不是。只扫描一遍? 拜托,real time application,数据随时会变的,包括category本身,可以随时添加、删除,甚至把整个树叉从一个树干挪到另一个树干上去。
                                      • category的修改是后台管理系统的事情。如果你的OLTP有cache就得注意同步的问题。所以最准确的数据还是得查询数据库。这个查询不管你写在SP之内或之外开销都是一定的。如果你在ID上用了什么技巧,同样也适用于SP。你光说有秘方又不肯示人,当然把自己立于不败之地了!
                                        • “所以最准确的数据还是得查询数据库” - 所以我说用中间表不好。什么败不败的,只是一个用SP不好的例子而已。简单的东西不需要用SP,不得不用SP的performance必定好不了,不适合high traffic real time web application。that's my point.
                                          • 1.如果能用单个SELECT(connect by或multi-segment id)肯定要优于中间表,这也可在SP中做到;2.修改category的事情极少发生,你放在这里有点钻牛角尖;3.你的例子不能说明sp不好,至多说明某个蹩脚程序员没写好;4.your point我已经知道,但你没能举出有说服力的例子
                                            • 还挺押韵挺上口的呀。:)1. SP当然能做到,我的例子中有两种情况,SP怎么写?还是用两个不同的SP?Point不是如何实现而是如果有效率地实现。2.我当时的实例category更新很频繁。3.蹩脚程序员太多了,本地职场还没碰到过好的程序员,
                                              估计高手都不写程序了。4. 不如你帮我想个好点的例子?LZ扔一砖跑了,咱俩掐什么掐?
                                              • 你这两种情况: 1.返回一个总数,2.返回一个结果集,当然是两个SP。如果发现它们有很多共同点,可以抽出来再写一个sub sp, 或把共同的子查询做成view. 我的观点是sp能够有效率地实现。我当然不能替我的反方举例:) 楼主虽然跑了,但我们的帖子过后也许能给他一些idea.
                                                • 那你就以上例子show一下你的SP(s),让我看看如何能有效的实现,不受数据多少和层数的影响。
                                                  • 我只懂ORACLE,思路就是那样的了,没有时间写
                                                    • 这跟什么DB没关系吧。何况,你不是说做过嘛。
                                                      • 好吧,SQL在这,别忘了把你的玉抛出来:
                                                        几个子查询还可以合并,没时间作比较了。

                                                        OPEN p_result FOR
                                                        WITH vw_category AS
                                                        (SELECT c.*
                                                        ,SYS_CONNECT_BY_PATH(category_id, '/') AS connection_path
                                                        ,level
                                                        FROM category c
                                                        START WITH category_id = :p_category_id
                                                        CONNECT BY PRIOR category_id = parent_id
                                                        )
                                                        ,vw_quantity AS
                                                        (SELECT product.category_id
                                                        ,SUM(quantity) AS quantity
                                                        FROM vw_category
                                                        INNER JOIN product
                                                        ON vw_category.category_id = product.category_id
                                                        GROUP BY product.category_id
                                                        )
                                                        ,vw_all_quantity AS
                                                        (SELECT vw_category.*
                                                        ,vw_quantity.quantity AS quantity
                                                        FROM vw_category
                                                        LEFT JOIN vw_quantity
                                                        ON vw_category.category_id = vw_quantity.category_id
                                                        )
                                                        SELECT v1.*
                                                        ,(SELECT SUM(quantity) FROM vw_all_quantity v2 WHERE v2.connection_path||'/' LIKE v1.connection_path||'/%') AS quantity
                                                        FROM vw_all_quantity v1;
                                                        • 完了,俺的oracle算是彻底过时了,咋看不懂了涅。这是SP?嗯,算了,#3859671
                                                          • 这是SP的body,从WITH开始是一个SQL。我记得MS SQL是支持WITH子查询的。self join语法见内:
                                                            http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10759/queries003.htm#i2053935

                                                            http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10759/functions149.htm

                                                            还有什么不理解的尽管问。

                                                            我这砖都砌成堆了,您就把玉亮一亮吧,要不咱们再开一贴?
                                                            • 俺都说了不掐了呀#3859671。你要觉得没过瘾就另开一贴吧。
                                              • 看你们两个掐的正过瘾呢,怎么又把我扯出来? - 我不抛这一“砖”,怎么能引出你们的“玉”呢?
                                                说实话,看你们掐还是挺学东西的,接着干。
                                                • 楼梯长得没法看,不掐了。:)
                                                  • 这种问题弄不出一个标准答案的,乐趣就在于争论过场大家能听到不同意见。不能求有结果.
                                      • 另外我说只扫描一遍是说只SELECT一次,和category的修改没有任何关系(况且修改category的情形并不经常发生)
                          • hierarchy data? does "connect by" clause do this job?
                            • 这就是我说的ORACLE方法啦。
                          • how about this:
                            define the table as

                            product table:
                            productid, category_code,....,

                            category table:
                            categoryid, category_code,...


                            category_code is like 'A',B','AA'...

                            AA is A's sub catrgory.

                            you can get the total quantity for category 'AACC' like:
                            select sum(quantity) from product where category_code like ('AACC%');

                            the restriction is you can only have limited( 26, or 26+26+10 ) sub categorys.
                            • 你是谁的马甲?BTW,也可以弄得没有restriction的。
                              • 我也在这里混了很久了,虽然大多潜水。
                                • 应该问,你还有没有别的马甲? 俺觉得你有可能是。。。。。。:)
                  • "避免实时调用stored procedure是避免stored procedure被滥用" ? sounds very "Abandon eating to prevent choking" to me....
                    • 不是Abandon eating,而是force to get a better way to eat。:)
          • 那怎么做audit?
    • 链接里的文章是不懂数据库的人写的,他自己也不否认。里面有很多错误观点。我想一个大型系统必定要有数据库的人才参与,哪怕不用SP,也得懂得数据库知识。
      • Agree. The author is a guy working at DAL/BL layer and he wants to collect all things into his hand rather than others'. SP is much more effecient because of eliminating a lot of communication workload between DB and AS
    • 请大家看看这个链接,关于ebay的,关于他们的做法,大家有什么看法?
      他们的做法和他们使用的j2ee系统架构有关系吗?
      • 这简直不可理喻:referential integrity, joins, sorting done in the application layer! 特别是这个join,集合操作是SQL的强项
        • Me too. The database are already ordered in database, why not waste time and I/O to reload them and order in memory?
        • I guess they are using EJB to do this, and might need to update database with results.
        • Not really. InformaticaETL already process data outside SQL, I guess with huge memory, SQL wouldn't do anything better than line-by-line process, even for summary.
          • ebay是个巨型的OLTP,跟ETL没啥关系吧。
        • 这个也能理解,凭ebay的实力可以作出比数据库更实用的join和排序算法
          • 你也可以说:凭ebay的实力可以作出更实用的数据存取方案。还要数据库干吗?把已经很成熟的技术再发明一遍,这就是吃力不讨好。
            • Google就自己做了不少轮子,卡迪拉克也自己作轮子,你还是先问问他们吧
              • EBAY的主营业务和GOOGLE不同,他们的IT投资应该在于应用而不是研发。
    • 触发器太多了不好,但存储过程还是要用吧,如果一个sp运行很长时间,完成同样的功能,在其他层做不会更长?为了移植方便和结构清晰,还说的过去。Java写的数据库存储过程怎么没有人用呢?估计大部分人认为应该放在其他层吧
    • 楼主引用的文章里面已经说明了理由,我也是赞成 少用存储过程的。 理由很多:难维护,难测试,存储过程的语言是一种简单的语言,不能完成复杂的任务
      • 你的理由同样适用于non-sp方案。这完全取决于程序怎么写了
    • 有意思,我们这正在尽量把business rule 用sp来实现呢。
    • Although SQL looks like very simple, from data perspective, it’s very powerful. SQL itself is (data) set/table oriented. If you still try it in common language’s logic, it’s really waste.
      About E-bay’s comments of put joins operation in application layer, I really doubt about it. For example, MS has being putting many efforts in ADO.NET on application side. Its one object DataSet is very powerful. The many functions like referential integrity, sorting, data filtering can be done there but not joins.

      One good example using SP is multi-tables insert operation (some late inserting relies on result(s) of previous inserting. If you put the operations in application layer, you need many database calls. On the other hand, you only need one database by using SP. One of other benefits is security. Using SP can efficiently prevent from malicious SQL attacks.

      Of course, nothing is perfect. In order to utilize recourse efficiently, it’s better to put as much tasks as possible in application layer to save expensive database resource.
      • agree with the 2nd paragraph and there after.
    • I used to work in a huge enterprise and was told to use SP whenever and wherever we can. Now I work here in a small company but still like the concept. SP is easier to develop, test and maintain not like someone mentioned above
      because not need to re-compile the application in IDE like VS
      • 这个,怎么说呢,只能说时代变了。俺以前也是那么被教导的。所以上面咕咚说“sp - 说老实话,一下子不让我用还真有点接受不了”,我是很有同感的。
        • I don't have chance to consider system scalability kind of things. But if need to move SP to BL, it's really a challege. I re-wrote a SP with more than 1k rows and improve the performance
          from 1.5 hours to just 5 minutes in this company. But if move to BL, I really don't know how to achieve and how long it will take me to do that.
          • 1. 对已有的系统,尤其是大公司,不可能说改就改说移就移的,除非是象你这个例子中的critical situation(1.5hour)。所以这个你不用担心。New rule will only apply to the new systems. 2. if your DB only serves 1 or few AS,
            SP is still the best place to implement the business logic.
    • 对于大型网站架构设计,一直是很令人关注的话题
      我正好收集了一些文章,供参考:
      http://netsoft.cnstar.ca/techdoc.php?a=browse&root=31
      • 太谢谢了!我就想看一些这样的文档。顺便也谢谢楼上所有的人,尤其是不多不少和newkid,很喜欢看你们掐仗啊:)
      • 好人啊
    • 大型web程序,SP和Triger都不应该使用。
    • 如果你考虑用Oracle的话,强烈建议读一下Tom Kyte的Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions因为其中有关于如何考虑可扩展性的思路。并非简单的 用 或 不用