需求:统计所有渠道底下的商户总数
数据库情况:
一张user表,通过userTpye来区别是渠道还是商户,并且渠道是有多层级的。
一张机构表,每个机构有个orgCode字段来展示渠道层级,比如:1,12,135:表示135机构的上级是12,12的上级是1
当时库里有28000多条数据,公司是最高层级,底下有100多个渠道。
如果对每个渠道做数状查询计算底下商户总数:
s.org_id in (select c.id from sys_organization c start with c.id = #{code} connect by prior c.id = c.pid)
这种数状查询需要检索整个user表,100多渠道就需要检索100多次,每次浏览2万多条数据,计算量非常大。
所以当时我换一个做法,我把user表里的用户org_id对应的机构表orgCode取出来,那么一共有28000个orgCode,然后通过java的for循环来进行统计,比如1,12,135出现的时候,对应的1渠道商户数+1,对应的12渠道和135渠道的商户数也+1.这样只要做一次for循环就能把所有商户的归属渠道统计出来。
这个看似可以解决问题,但是却遇见了一个新的问题,就是我要把商户的code集合从oracle中取出来时,2万多条的数据集合从数据库取到java服务器耗费了大量的时间。我用PL/SQL时也是,查询条件可以0.2秒出结果,但是要展示所有的列却花了十几秒。我猜测这可能是由于数据库需要展示数据时要进行I/O操作,这个操作的读写浪费了大量的时间。
所以我考虑在数据库存储过程里面完成这个统计操作。下