//为ASSMSTRC表中的LEFTID建立索引
……
String ASSMSTRC_index = "create index ASSMSTRC_index on PDMM31A.ASSMSTRC( LEFTID )" ;
stmt.execute( ASSMSTRC_index ) ;
……接下来是一段递归代码
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都是唯一的
*/
希望高手,能帮我想想优化的方案,这段代码执行查询2000条记录需要15分钟,如果这样交工,估计得被领导骂死。
这段代码为什么会这么慢呢?????????????????
……
String ASSMSTRC_index = "create index ASSMSTRC_index on PDMM31A.ASSMSTRC( LEFTID )" ;
stmt.execute( ASSMSTRC_index ) ;
……接下来是一段递归代码
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都是唯一的
*/
希望高手,能帮我想想优化的方案,这段代码执行查询2000条记录需要15分钟,如果这样交工,估计得被领导骂死。
这段代码为什么会这么慢呢?????????????????
2.看的有的头大,3个sql ,努力写成 1个sql ,如果不能的话就是用存储过程
3.为什么很多不会写存储过程的人比我工资高那么多,我该反省了