这是一段递归代码
public string obtainPartNumberInfo ( string leftobid ) { 省略…… stmt1 = con.createStatement();
Sql1 = "select /*+RULE*/ CLASS2,RIGHTID from PDMM31A.ASSMSTRC where LEFTID='"+leftobid+"'" ;
rs1 = stmt1.executeQuery( Sql1 ) ; stmt2 = con.createStatement();
Sql2 = "select PARTNUMBER from PDMM31A."+ rs1.getString( "CLASS2" ) + " where OBID='"+rs1.getString("RIGHTID")+"'" ;
rs2 = stmt2.executeQuery( Sql2 ); if( rs.getString( "CLASS2" ).equals( "W2ProMtr" ) ) String tableName = "W2PRODCT";
if( rs.getString( "CLASS2" ).equals( "W2AsmMtr" ) ) String tableName = "W2ASSM";
if( rs.getString( "CLASS2" ).equals( "W2CmpMtr" ) ) String tableName = "W2COMPT";
if( rs.getString( "CLASS2" ).equals( "W2FStMtr" ) ) String tableName = "W2FACSTD";
if( rs.getString( "CLASS2" ).equals( "W2BStMtr" ) ) String tableName = "W2BUYSTD"; stmt3 = con.createStatement();
Sql3 = "select OBID from PDMM31A."+ tableName +" where PARTNUMBER='"+rs2.getString( "PARTNUMBER" )+"'" ;
rs3 = stmt3.executeQuery( Sql3 ); obid = rs3.getString( "OBID" ) 省略…… obtainPartNumberInfo( obid ) }
/**
* 1、我用的是Oracle数据库,其中的PDMM31A为数据库实例,ASSMSTRC为实例中的一个表
* 2、ASSMSTRC表大约有20万条记录,TABLE1和TABLE2中各有几万条记录
* 3、CLASS2作为一个字段存在ASSMSTRC中,TABLE1的值有可能为W2ASMPTR,W2COMPTR、W2PROPTR、W2FCDPTR
* 4、退出条件省略没有写,一般情况,这个函数都会调用上千次
* 5、建立索引前后速度无改变,不知道为什么
* 6、LEFTID、RIGHTID、OBID都是唯一的
*/ 这段递归代码实现起来非常的慢,请问慢在哪里?用存储过程如何实现?
public string obtainPartNumberInfo ( string leftobid ) { 省略…… stmt1 = con.createStatement();
Sql1 = "select /*+RULE*/ CLASS2,RIGHTID from PDMM31A.ASSMSTRC where LEFTID='"+leftobid+"'" ;
rs1 = stmt1.executeQuery( Sql1 ) ; stmt2 = con.createStatement();
Sql2 = "select PARTNUMBER from PDMM31A."+ rs1.getString( "CLASS2" ) + " where OBID='"+rs1.getString("RIGHTID")+"'" ;
rs2 = stmt2.executeQuery( Sql2 ); if( rs.getString( "CLASS2" ).equals( "W2ProMtr" ) ) String tableName = "W2PRODCT";
if( rs.getString( "CLASS2" ).equals( "W2AsmMtr" ) ) String tableName = "W2ASSM";
if( rs.getString( "CLASS2" ).equals( "W2CmpMtr" ) ) String tableName = "W2COMPT";
if( rs.getString( "CLASS2" ).equals( "W2FStMtr" ) ) String tableName = "W2FACSTD";
if( rs.getString( "CLASS2" ).equals( "W2BStMtr" ) ) String tableName = "W2BUYSTD"; stmt3 = con.createStatement();
Sql3 = "select OBID from PDMM31A."+ tableName +" where PARTNUMBER='"+rs2.getString( "PARTNUMBER" )+"'" ;
rs3 = stmt3.executeQuery( Sql3 ); obid = rs3.getString( "OBID" ) 省略…… obtainPartNumberInfo( obid ) }
/**
* 1、我用的是Oracle数据库,其中的PDMM31A为数据库实例,ASSMSTRC为实例中的一个表
* 2、ASSMSTRC表大约有20万条记录,TABLE1和TABLE2中各有几万条记录
* 3、CLASS2作为一个字段存在ASSMSTRC中,TABLE1的值有可能为W2ASMPTR,W2COMPTR、W2PROPTR、W2FCDPTR
* 4、退出条件省略没有写,一般情况,这个函数都会调用上千次
* 5、建立索引前后速度无改变,不知道为什么
* 6、LEFTID、RIGHTID、OBID都是唯一的
*/ 这段递归代码实现起来非常的慢,请问慢在哪里?用存储过程如何实现?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货