在SQL Server上使用jdbc PreparedStatement获取查询计划 [英] Get the query plan using jdbc PreparedStatement on sql server

问题描述

使用语句，resultSet.getObject将查询计划返回为xml

Using Statment, resultSet.getObject returns query plan as xml

 Connection conn = getConnection();
        String query = " SET SHOWPLAN_XML on ";
        Statement st = conn.createStatement();
        boolean execute=st.execute(query);
        log.info("execute status {} " , execute);
         query = " SELECT ATMPROFILES.TERMID as COLUMNID, ATMPROFILES.TERMID as COLUMNNAME FROM ATMPROFILES (NOLOCK) "
                + " WHERE Authprocessname = 'ATMST' "
                + "ORDER BY ATMPROFILES.TERMID ";
        ResultSet rs = st.executeQuery(query);
        while(rs.next())
        {
            Object object = rs.getObject(1);
            log.info("Query Plan {}  ", object);
        }

但是，如果我通过PreparedStatement执行相同的操作，它将返回实际结果，而不是QueryPlan

But If I execute the same through PreparedStatement, it returns actual result insteadof QueryPlan

 Connection conn = getConnection();
        String query = " SET SHOWPLAN_XML on ";
        PreparedStatement ps = conn.prepareStatement(query);
        boolean execute = ps.execute();
        log.info("execute status {} " , execute);

         query = " SELECT ATMPROFILES.TERMID as COLUMNID, ATMPROFILES.TERMID as COLUMNNAME FROM ATMPROFILES (NOLOCK) "
                + " WHERE Authprocessname = 'ATMST' "
                + "ORDER BY ATMPROFILES.TERMID ";
        ps=conn.prepareStatement(query);
        execute=ps.execute();
        log.info("execute status {} " , execute);
        ResultSet rs = ps.getResultSet();
        while(rs.next())
        {
            Object object = rs.getObject(1);
             // here it returns selected object
            log.info("Query Plan {}  ", object); 

        }

任何通过PreparedStatement实现此目标的想法.

Any idea to acheive this via PreparedStatement.

推荐答案

我还没有找到为什么将SET SHOWPLAN_XML ON作为准备好的语句执行将不起作用的参考；但是，当直接运行此语句并将实际查询作为准备好的语句运行时，应该会获得所需的结果.在代码中:

I haven't found any reference why executing SET SHOWPLAN_XML ON as a prepared statement will not work; however, you should get the desired results when you run this statement directly and your actual query as a prepared statement. In code:

Connection conn = getConnection();
String showplanQuery = "SET SHOWPLAN_XML ON";
Statement st = conn.createStatement();
st.execute(showplanQuery);

String actualQuery = "SELECT ATMPROFILES.TERMID FROM ATMPROFILES (NOLOCK) ";
PreparedStatement ps=conn.prepareStatement(actualQuery);
ps.execute();
ResultSet rs = ps.getResultSet();
while(rs.next())
{
    Object object = rs.getObject(1);
    // should log the query plan
    log.info("Query Plan {}  ", object); 
}

希望有帮助.

这篇关于在SQL Server上使用jdbc PreparedStatement获取查询计划的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！