很怪的问题:本机运行SQL很快,在服务器上运行很慢

ERR0RC0DE 2009-12-24 11:38:44
操作很简单,有张表,不到7W条
大概语句是:
select * from tablename where rownum < 100 and ....

该表数据在本机和服务器的数据一致,但在本机,运行语句的速度大不相同,本机0.1秒都不用,但远程,居然要5s,晕死。

然后打印出那SQL命令运行时候,

java.util.Date currTime = new java.util.Date();
currTime.setTime(System.currentTimeMillis());

logger.info(currTime.toString() + ":start execute cmd: " + sql);

rs = pstm.executeQuery();

currTime.setTime(System.currentTimeMillis());
logger.info(currTime.toString() + ": execute ok, go on...");


就两张表会这样,其它表不会,而且我将SQL copy到PL/SQL去运行,也是1s不用的,真怪了。
...全文
865 33 打赏 收藏 举报
写回复
33 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
ERR0RC0DE 2009-12-27
  • 打赏
  • 举报
回复
一直以为分页速度不咋地,原来是这个地方没处理好。hoho,不错,大有收获,不过此贴问题已跑题了,过两天结了。

感谢各位参与。
ERR0RC0DE 2009-12-27
  • 打赏
  • 举报
回复
应该是了,分页只将PK找出,再进行外层关联,速度就是很快了。已测试过了。找时间将这分页处理换掉。


我有个大表,里面有7000W左右。

测试了,row_number()效率确实不如用rownum
在500W的量分页中,只select PK字段,

SQL1:要11s
SQL2: 要2s
100W的量:
SQL1: 6s, SQL2:0.5s,

另:row_number最好用rownum作为order,比PK快

row_number() over(order by rownum) rn
改成:
row_number() over(order by CID) rn --CID是PK
需要时间更长, 11s的要变成32s




SQL1:
select yy.* from
(
select row_number() over(order by rownum) rn, zz.* from
(
select RecID from B_COMMEND partition(data200911)
) zz
) yy where rn <= 5000000 and rn >= 4999990;

SQL2:
select * from
(
select rownum rn, zz.* from
(
select RecID from B_COMMEND partition(data200911)
) zz where rownum <= 5000000
) where rn >= 4999990 ;
crazylaa 2009-12-27
  • 打赏
  • 举报
回复
[Quote=引用 28 楼 err0rc0de 的回复:]
以下两种写法差不多,速度。在没有函数后,取29990=>30000的那10条数据,不用0.5s.
有函数(函数其实就是从另张表,根据ID取Name),则效率变成2s,加一个函数差不多速度+1s.

同志们可以自已测试一下。

如果加入调用函数,就是F_Get..., pkg.F_Get...函数的话,效率直线下降(在查看后面页,我这里查的是3W条数据量的后面)
看来以后要将一些基础数据先期放到JAVA页面,由页面去完成这个函数get的操作,这样,SQL运行就快了,页面显示就会显示巨快。

测试了一下,感觉不错。

SQL我写成那样,是用来作分页的,分页是单独的处理,程序方面只是提供可运行的SQL即可,所以写成那样。

现在没环境测试,有机会再测试。
我是无聊,搞搞数据优化,呵呵。

SQL codeselect*from
(select rownumas rn, a.*from
(select--/*+ first_rows */ CID, CNAME, CMANAGERNAME, CMANAGERTEL, CPARENTCID,
COWNERAREAID,COWNERAREANAME,CTYPEID
COWNERAREAID,COWNERAREANAME,CTYPEID,--pkgJFChannel.GetCTypeNameByCTypeID(CTYPEID) as CTYPENAME,--pkgJFChannel.GetCTypeClassByCTypeID(CTypeID) as CTypeClass, FLID--, F_JF_GetOwnerAreaName(COWNERAREAID) as Areanamefrom T_Channel cwhere1=1orderby rownum
) awhere rownum<=30000
)where rn>=29990 ;select*from
(select row_number()over(orderby rownum) rn, zz.*from
(select
CID, CNAME, CMANAGERNAME, CMANAGERTEL, CPARENTCID,
COWNERAREAID,COWNERAREANAME,CTYPEID--pkgJFChannel.GetCTypeNameByCTypeID(CTYPEID) as CTYPENAME,--pkgJFChannel.GetCTypeClassByCTypeID(CTypeID) as CTypeClass,--F_JF_GetOwnerAreaName(COWNERAREAID) as Areanamefrom T_Channel
) zz
) yywhere rn<=30000and rn>=29990;
[/Quote]


如果函数作用仅仅是如此的话,我建议哈,把函数放到最外层,即分页之后取到你要的10条数据,再去调用函数。
为什么呢?放到最里层,则每个数据都要调用一次函数,那不是增加了开销么?而放在最外层,则只有你需要的那10个数据才会去调用啊。
我想这里在最外层加那些自定义函数,不会出现加一个函数+1s的状况,呵呵。
(笛卡儿乘积M×N与10×N,效率不言而喻)。

楼主试试看么?嘿嘿。

crazylaa 2009-12-27
  • 打赏
  • 举报
回复
[Quote=引用 29 楼 err0rc0de 的回复:]
》》pstm没有设置ResultSet的两个参数吧
这两参数,默认未加。
只有做导出操作时,我会加上。

我觉得吧,做分页操作,可以这样做:
条件:一张表:T_Channel, 有PK: CID

一:取出CID来,一般分页也就是10-100条
SQL codeselect*from
(select row_number()over(orderby rownum) rn, zz.*from
(select CIDfrom T_Channel
) zz
) yywhere rn<=30000and rn>=29990;
二:然后,取出CID后,再取一次表数据
SQL codeselect*, func_1(...)from T_Channelwhere CIDin (001,002,003...,010)

好像有点麻烦。不过我觉得应该会速度比较快。
呵呵,只是觉得。真是有100W级别的分页的话,我会考虑一下用这种方法。

[/Quote]

这么做的效率应该不如一次快。我来说说自己的理解:
第一步, select CID from T_Channel 就已经取出了全索引数据,然后order by,一次全索引扫描+order by
第二步取出10个ID再取表数据,又要进行一次索引扫描。
而如果采用一步到位的方式,则
order by ID 同样全索引扫描,则只需要一次全索引扫描就够了。

呵呵,楼主可以弄个100w数据测试下,看看两种方式的oracle本身的查询计划哪个效率更优,我没做过实验。
ERR0RC0DE 2009-12-27
  • 打赏
  • 举报
回复
》》pstm没有设置ResultSet的两个参数吧
这两参数,默认未加。
只有做导出操作时,我会加上。

我觉得吧,做分页操作,可以这样做:
条件:一张表:T_Channel, 有PK: CID

一:取出CID来,一般分页也就是10-100条

select * from
(
select row_number() over(order by rownum) rn, zz.* from
(
select CID from T_Channel
) zz
) yy
where rn <= 30000 and rn >= 29990;

二:然后,取出CID后,再取一次表数据

select *, func_1(...) from T_Channel
where CID in (001, 002, 003..., 010)


好像有点麻烦。不过我觉得应该会速度比较快。
呵呵,只是觉得。真是有100W级别的分页的话,我会考虑一下用这种方法。
ERR0RC0DE 2009-12-27
  • 打赏
  • 举报
回复
以下两种写法差不多,速度。在没有函数后,取29990=>30000的那10条数据,不用0.5s.
有函数(函数其实就是从另张表,根据ID取Name),则效率变成2s,加一个函数差不多速度+1s.

同志们可以自已测试一下。

如果加入调用函数,就是F_Get..., pkg.F_Get...函数的话,效率直线下降(在查看后面页,我这里查的是3W条数据量的后面)
看来以后要将一些基础数据先期放到JAVA页面,由页面去完成这个函数get的操作,这样,SQL运行就快了,页面显示就会显示巨快。

测试了一下,感觉不错。

SQL我写成那样,是用来作分页的,分页是单独的处理,程序方面只是提供可运行的SQL即可,所以写成那样。

现在没环境测试,有机会再测试。
我是无聊,搞搞数据优化,呵呵。


select * from
(
select rownum as rn, a.* from
(
select --/*+ first_rows */
CID, CNAME, CMANAGERNAME, CMANAGERTEL, CPARENTCID,
COWNERAREAID,COWNERAREANAME,CTYPEID
COWNERAREAID,COWNERAREANAME,CTYPEID,
--pkgJFChannel.GetCTypeNameByCTypeID(CTYPEID) as CTYPENAME,
--pkgJFChannel.GetCTypeClassByCTypeID(CTypeID) as CTypeClass,
FLID
--, F_JF_GetOwnerAreaName(COWNERAREAID) as Areaname
from T_Channel c
where 1=1
order by rownum
) a where rownum <= 30000
) where rn >= 29990 ;

select * from
(
select row_number() over(order by rownum) rn, zz.* from
(
select
CID, CNAME, CMANAGERNAME, CMANAGERTEL, CPARENTCID,
COWNERAREAID,COWNERAREANAME,CTYPEID
--pkgJFChannel.GetCTypeNameByCTypeID(CTYPEID) as CTYPENAME,
--pkgJFChannel.GetCTypeClassByCTypeID(CTypeID) as CTypeClass,
--F_JF_GetOwnerAreaName(COWNERAREAID) as Areaname
from T_Channel
) zz
) yy
where rn <= 30000 and rn >= 29990;

ERR0RC0DE 2009-12-26
  • 打赏
  • 举报
回复
木有用。
rebuild索引了,一样。

打印出来的日志是:select x from dual; 一下就出来,select上面的T_Channel,5秒(第一页,第X页都是),而在PL/SQL正常0.5s不到。
ERR0RC0DE 2009-12-26
  • 打赏
  • 举报
回复
不过这两张表,每天会更新,(从别的地方进行数据同步),我试试重建索引
ERR0RC0DE 2009-12-26
  • 打赏
  • 举报
回复
select * from (
select * from (
select rownum as rn, a.* from
(
select /*+ first_rows */
CID, CNAME, CMANAGERNAME, CMANAGERTEL, CPARENTCID, CPARENTCNAME, CLINKMAN, CLINKMANSIM,
COWNERAREAID,COWNERAREANAME,CTYPEID,
pkgJFChannel.GetCTypeNameByCTypeID(CTYPEID) as CTYPENAME,
pkgJFChannel.GetCTypeClassByCTypeID(CTypeID) as CTypeClass,
FLID,
F_JF_GetOwnerAreaName(COWNERAREAID) as Areaname
from T_Channel c
where 1=1
order by CID asc -- CID是PK
) a where rownum <= 10
) where rn >= 1
)


上面SQL在PL/SQL跑,1s不到出来了,不管本地远程。

我不认为rownum会慢,而且我之前试过row_number(),不如rownum,楼主可以试试,分页:10W条记录,第1页,第10页,第1000页和最后一页。
crazylaa 2009-12-26
  • 打赏
  • 举报
回复
对了你的pstm没有设置ResultSet的两个参数吧
crazylaa 2009-12-26
  • 打赏
  • 举报
回复
[Quote=引用 22 楼 err0rc0de 的回复:]
找到问题了
。。。

PL/SQL在运行SQL命令与JAVA的运行有点区别。
原因就是在:order by CID
去掉了结果就马上就出来了。

按理之前我找问题时,本来想着去掉order by CID,后面是发现本机测试很正常,没去管,也没想过这破问题会这样。

我是一点点的将SQL命令剪切:
select 1 as CID, 2 as CName from ...
就是没想过order by CID
ri,本机测试好好的。NND
数据结构本地与远程基本是一样的。

找到问题就行了。去掉这order by,速度就飞快了。
[/Quote]

是吗?试着加大点临时表空间,不要强制索引,用row_number()分析函数试试看?

select a.* from
(
select
CID, CNAME, CMANAGERNAME, CMANAGERTEL, CPARENTCID, CPARENTCNAME, CLINKMAN, CLINKMANSIM,
COWNERAREAID,COWNERAREANAME,CTYPEID,
pkgJFChannel.GetCTypeNameByCTypeID(CTYPEID) as CTYPENAME,
pkgJFChannel.GetCTypeClassByCTypeID(CTypeID) as CTypeClass,
FLID,
F_JF_GetOwnerAreaName(COWNERAREAID) as Areaname,
row_number() over(order by cid asc) rn
from T_Channel c
) a where a.rn <= 10 and a.rn >= 1
ERR0RC0DE 2009-12-26
  • 打赏
  • 举报
回复
按我想法是:在程序运行SQL,与PL/SQL运行是没区别的。

我都是先在PL/SQL先优化好,处理好,再丢到程序运行的。
结果变成这样。
fdsafsfw3rew 2009-12-26
  • 打赏
  • 举报
回复
在程序中执行一条sql花费的时间和用pl/sql执行该条sql语句,这两种方式比较执行时间有意义吗?恭喜楼主解决问题了,楼主能分析一下原因吗,谢谢
coveking 2009-12-26
  • 打赏
  • 举报
回复
呵呵,你确定表结构一样,是不是索引的问题哦
ERR0RC0DE 2009-12-26
  • 打赏
  • 举报
回复
找到问题了
。。。

PL/SQL在运行SQL命令与JAVA的运行有点区别。
原因就是在:order by CID
去掉了结果就马上就出来了。

按理之前我找问题时,本来想着去掉order by CID,后面是发现本机测试很正常,没去管,也没想过这破问题会这样。

我是一点点的将SQL命令剪切:
select 1 as CID, 2 as CName from ...
就是没想过order by CID
ri,本机测试好好的。NND
数据结构本地与远程基本是一样的。

找到问题就行了。去掉这order by,速度就飞快了。
crazylaa 2009-12-26
  • 打赏
  • 举报
回复
楼主的Sql至少有一个可以优化的地方,你用的是rownum来分页,rownum分页效率没有row_number()分析函数效率高哦,呵呵。你可以贴出你的sql,我给你优化下。


另外,看了楼上各位的回复,这里稍微对oracle的select处理说下:

oracle数据库,一般表结构相同,索引相同(包括函数索引,上面有位同志说to_char函数索引失效,是的,对列建索引,如果在该列上用了函数,则函数不会使用该列的索引,要想函数也用索引,请建相应的函数索引。),数据量相同,存放数据的数据文件分布相同(表所在的表空间对应的数据文件数量相同,分布的物理磁盘个数相同),机器硬件性能相同,同时只进行一个sql的处理(除此之外,两台机器都不干别的)出现查询效率很大差异的可能原因一般是,纯SQL方式下:
1。慢的机器的表,之前有过较大的数据delete,但是没有reorg index,因为delete data,oracle是不会同时更新索引的。也就是说,delete大量数据以后,要重新优化下索引,去掉那些已经被delete的数据在索引中的映射。
2。SQL存在order by,group by等分组、排序的操作,则涉及到临时表空间的问题。慢的机器,设置的临时表空间过小,因为排序、分组操作涉及数据量过大的话,会使用临时表空间来缓存。

楼主说select * from dual 速度快,这个sql速度慢,可以看看是不是这两方面的原因。

coveking 2009-12-26
  • 打赏
  • 举报
回复
你服务器执行查询的时候,现网业务对这个表的修改操作多吗。
crazylaa 2009-12-26
  • 打赏
  • 举报
回复
[Quote=引用 19 楼 err0rc0de 的回复:]
木有用。
rebuild索引了,一样。

打印出来的日志是:select x from dual; 一下就出来,select上面的T_Channel,5秒(第一页,第X页都是),而在PL/SQL正常0.5s不到。
[/Quote]

晕菜。。不晓得了,帮顶吧。你的sql都强制走索引了,sql也不复杂,就是排序,应该没什么问题。
对了,你能把sql里面的函数去掉,直接查表,看看jdbc速度快不快啊?
ERR0RC0DE 2009-12-25
  • 打赏
  • 举报
回复
DBCP,连接池什么的,设置了,但不知如何测试,先放着,
俺调的不是JAVA,是SQL。俺是JAVA临时工来的,大家见谅
ERR0RC0DE 2009-12-25
  • 打赏
  • 举报
回复
郁闷了,发现了不是那问题,还是SQL问题

我要那有问题的页面,在前面先执行select 'x' from dual,然后再运行正确的SQL,发现第一个select x是很快的,第二个正确SQL是需要5秒的

不是DBCP啥连接池问题,也不是DBConnection的初始问题。
就是SQL,NND,郁闷了。

这破SQL,我丢到PL/SQL都是不到1秒就出来的,没见过这破问题。
加载更多回复(13)
相关推荐
发帖
Java

4.9w+

社区成员

Java相关技术讨论
javaspring bootspring cloud 技术论坛(原bbs)
社区管理员
  • Java相关社区
  • 小虚竹
  • 谙忆
加入社区
帖子事件
创建了帖子
2009-12-24 11:38
社区公告
暂无公告