很怪的问题：本机运行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不用的，真怪了。
...全文
1121 33 打赏 收藏 转发到动态 举报
写回复
用AI写文章
33 条回复
切换为时间正序
请发表友善的回复…
发表回复
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）
代码执行很慢但是数据库很快_为什么你的SQL执行很慢
SQL语句执行很慢原因分析先来回答第一个问题，如果一条SQL语句执行会很慢，会有哪些可能的原因。为了方便说明问题，这里先给出建表语句和初始化语句：CREATE TABLE `t` ( `id` int(10) NOT NULL AUTO_INCREMENT, `a` int(10) DEFAULT NULL, `b` varchar(16) DEFAULT NULL, PRIMARY KEY (`...
解决同一条sql在pl/sql工具中执行很快，在程序中却很慢
今天因为程序设计漏洞，把一张表得数据给truncate掉了，然后再重跑的过程中发现有一条sql再pl/sql工具执行很快（1秒内），程序中却需要200-300秒。 sql如下： MERGE INTO t_qt_second t1 USING (select d.sn_no ,c.stair_org_id ,c.stair_org_name , a.assetpro_name , a.org_no , a.assetpro_no from t_org_sn a, t_device b, t_org_inf
sql语句在navicat中执行快，但是在程序中执行慢问题
百万数据，查询问题处理的方式和问题的排查。仅我遇到的在navicat中执行sql快在程序中执行慢问题
sql在java里面查询很慢_SQL Server查询从Java运行缓慢
我有一个Java程序，可以对sql服务器数据库运行一堆查询。其中第一个查询视图返回大约750k条记录。我可以通过sql server managementstudio运行查询，大约30秒后即可得到结果。但是，我从昨晚开始运行该程序。当我今天早上检查它时，大约15小时后，该查询仍未将结果返回给Java程序。我可以访问数据库以执行几乎任何我想做的事情，但是我真的不确定如何开始调试它。人们应该怎么做才能...
sql server 运行中的慢sql查询
之前一直用的mysql，所以对sql server不熟悉，结果新公司又有mysql 又有 sql server，瞬间感觉自己短板来了。 问题： 突然某天用户反馈系统部分功能无法使用了，第一反应是不是服务把系统cpu和内存占满了，因为之前出现过一次这种情况，当时就是服务器cpu占满了，后来重启服务暂时先解决了。 但是这次我们去服务器上看cpu和内存很正常，但是的确部分功能使用不了，最后查到是sql server数据库iops太高，当时想查询一下有哪些慢sql结果找了很久才找到，今天记录一下，sqlserver
Java

51,386

社区成员

85,743

社区内容

发帖
与我相关
我的任务
社区描述
Java相关技术讨论
javaspring bootspring cloud 技术论坛（原bbs）
社区管理员
  • Java相关社区
  • 小虚竹
  • 谙忆
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章