诸神的博客
诸神
我的首页
文章
相册
圈子
留言
管理
 
    当前所在页面:首页>>文章>>继承类查询的性能问题
继承类查询的性能问题
    作者:stef_wu 来源: 发表时间:2007-12-06

 
 

昨天没有事情干,测试了一把CMS的性能,靠,问题就出来了,

我先插入一条数据,然后只复制了3W次,然后用NU.sq.dir("xxx").list一查,居然打开页面的时间我可以上次厕所了。

使用:

System.out
    .println("=====================begin query===================");
  Long beginTime = System.currentTimeMillis();
  NewsDocQueryObject qo = parseNewsDocQuery();
  IPageList pageList = newsUtil.getService().getNewsDocBy(qo);// 执行查询
  System.out.println(qo.getQuery());
  Long endTime = System.currentTimeMillis();
  System.out.println("all query use " + (endTime - beginTime));

发现查询居然用了50秒,

看看输出的SQL:

Hibernate: select newsdoc0_.id as id43_, newsdoc0_.title as title43_, newsdoc0_.subTitle as subTitle43_, newsdoc0_.dir_id as dir21_43_, newsdoc0_.contentTypes as contentT4_43_, newsdoc0_.url as url43_, newsdoc0_.displayTime as displayT6_43_, newsdoc0_.content as content43_, newsdoc0_.intro as intro43_, newsdoc0_.author_id as author22_43_, newsdoc0_.source_id as source23_43_, newsdoc0_.dirPath as dirPath43_, newsdoc0_.sequence as sequence43_, newsdoc0_.expiredTime as expired11_43_, newsdoc0_.readTimes as readTimes43_, newsdoc0_.reviewTimes as reviewT13_43_, newsdoc0_.inputTime as inputTime43_, newsdoc0_.template_id as template20_43_, newsdoc0_.owner_id as owner24_43_, newsdoc0_.elite as elite43_, newsdoc0_.auditing as auditing43_, newsdoc0_.status as status43_, newsdoc0_.updateTime as updateTime43_, newsdoc0_.topTime as topTime43_, newsdoc0_.shop_id as shop1_67_, newsdoc0_.clazz_ as clazz_ from ( select template_id, readTimes, title, contentTypes, reviewTimes, source_id, dir_id, displayTime, null as shop_id, sequence, id, updateTime, topTime, owner_id, url, author_id, dirPath, intro, content, inputTime, auditing, status, expiredTime, elite, subTitle, 0 as clazz_ from NewsDoc union select template_id, readTimes, title, contentTypes, reviewTimes, source_id, dir_id, displayTime, shop_id, sequence, id, updateTime, topTime, owner_id, url, author_id, dirPath, intro, content, inputTime, auditing, status, expiredTime, elite, subTitle, 1 as clazz_ from CompantNews ) newsdoc0_ where 1=1 and (newsdoc0_.dirPath in ('mldcha@mldcha-yz@mldcha-yz-fw@' , 'mldcha@mldcha-yz@mldcha-yz-fw@fw-xz@' , 'mldcha@mldcha-yz@mldcha-yz-fw@fw-zc@' , 'mldcha@mldcha-yz@mldcha-yz-fw@fw-jc@' , 'mldcha@mldcha-yz@mldcha-yz-fw@fw-cs@')) and newsdoc0_.status>=0 and newsdoc0_.displayTime<? order by newsdoc0_.displayTime desc limit ?

呵呵,问题出来了,里面有一个select ...from (select ... union),原来是继承搞的鬼。

这里有一个compantNews继承了NewsDoc,在查询NewsDoc的时候,就会联合去查询NewsDoc+CompantNews两个表

导致查询的时间达到了50秒左右。

找到问题了,

做如下修改:创建一个基类NewsDocBase,吧NewsDoc里面的属性全部放进去,然后让NewsDoc继承NewsDocBase,NewsDoc可以是空的,

然后CompantNews继承NewsDocBase,OK,再查询一次,只用了0.4秒左右,

再修改QueryBy,去掉了PageObject的包装,采用直接执行查询,并把Like换成了in,速度基本在0.2秒左右。

 
 

(阅读 )   评论数(:1)
评论】 【收藏】
评论:共1条
50秒??
评论人: 天一     评论时间: 2007-12-07 09:18:48

发表评论:
发表人:
评论: 
验证码:
请输入前面图中的四位验证码,字母不区分大小写
  
 
关于我们 | 诚聘英才 | 联系我们 | 广告业务 | 网站地图 | 法律声明

EasyJF开源团队版权所有  建议使用1024*768分辨率