关于报表里SQL执行速度的问题

1. 问题概述
之前有个客户反应报表计算慢,通过看后台日志,发现时间都是花在了执行sql上面,但同时客户会说,这个sql拿到数据库端去执行,速度很快,对于含参数的sql,执行时间的差异会显得尤为明显.但是,客户将sql拿到数据库端去执行,往往是模拟不了传参的过程,检索条件都是在sql里写死的.
2. 解决办法
为了证明这个sql执行速度慢,不是润乾的关系,可以建议客户写一段java代码,不调用润乾的任何接口,采用PreparedStatement执行这个sql,这样客户会发现,即使不用润乾,执行一个带参数的sql所花的时间与报表里所花时间是差不多的.
同一张表,同样的条件,详见代码里query1,query2方法
A: 条件直接写在SQL语句上,执行结果

B: 条件传递在SQL语句上,执行结果

3. 程序代码

package api;

import java.sql.*;

publicclass TestSQL {

    public TestSQL() {

    }

    publicstaticvoid main(String[] args){

       query1();

       query2();

    }

publicstaticvoid query1(){

       Connection conn = null;

       PreparedStatement stat = null;

       ResultSet rs = null;

       try {

           Class.forName("com.newatlanta.jturbo.driver.Driver");//数据库驱动

           conn = DriverManager.getConnection(

              "jdbc:JTurbo://127.0.0.1/DBA/charset=GBK", "sa ", "sa");

           String sql_noParam = "SELECT *  FROM 订单明细 where 单价 > 10";

           //直接写定参数的SQL语句

           stat = conn.prepareStatement(sql_noParam);

           long startTime = System.currentTimeMillis();

           System.out.println("query1   begin   time: " + startTime);

           rs = stat.executeQuery();

           while (rs.next()) {

              //遍历一遍结果集，这个循环可以去掉

           }

           long costTime = System.currentTimeMillis() - startTime;

           System.out.println("Query1 cost time: " + (costTime/1000) + "s" + (costTime%1000) + "ms");

       }

       catch (Exception e) {

           e.printStackTrace();

       }

       finally {

           //任务结束，关闭连接、结果集等项

           try {

              if ( rs != null ) rs.close();

              if ( stat != null ) stat.close();

              if (conn != null ) conn.close();

           }

           catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

 

    publicstaticvoid query2(){

       Connection conn = null;

       PreparedStatement stat = null;

       ResultSet rs = null;

       try {

           Class.forName("com.newatlanta.jturbo.driver.Driver");

           conn = DriverManager.getConnection(

              "jdbc:JTurbo://127.0.0.1/DBA/charset=GBK", "sa ", "sa");

           String sql_withParam = "SELECT *  FROM 订单明细 where 单价 > ?";

           //带参数sql

           stat = conn.prepareStatement(sql_withParam);

           stat.setInt(1, 10);//设定第一个参数为Int，值为10，其它类型用stat.setString,stat.setTimestamp等等

           long startTime = System.currentTimeMillis();

           System.out.println("query2   begin   time: " + startTime);

           rs = stat.executeQuery();

 

           while (rs.next()) {

              //遍历一遍结果集，这个循环可以去掉

           }

           long costTime = System.currentTimeMillis() - startTime;

           System.out.println("Query2 cost time: " + (costTime/1000) + "s" + (costTime%1000) + "ms");

       }

       catch (Exception e) {

           e.printStackTrace();

       }

       finally {

           try {

              if ( rs != null ) rs.close();

              if ( stat != null ) stat.close();

              if (conn != null ) conn.close();

           }

           catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

}