Slow queries with preparedStatement but not with executeQuery

Viewed 5417

I'm having a weird problem with an Grails application accessing data. Going deeper I've isolated the problem to a plain java8 small application using PreparedStatement.executeQuery vs Statement.executeQuery.

Consider the following snippet of code:

                  
    // executes in milliseconds                  
    directSql   = "select top(10) * from vdocuments where codcli = 'CCCC' and             serial = 'SSSS' ORDER BY otherField DESC;";
    stmt = con.createStatement();
    rs = stmt.executeQuery(directSql);


    // More than 10 minutes
    sqlPrepared = "select top(10) * from vdocuments where codCli = ? and  serial = ? ORDER BY otherField DESC;";
    PreparedStatement pStatement = con.prepareStatement( sqlPrepared );
    pStatement.setString(1, "CCCC");
    pStatement.setString(2, "SSSS");
    rsPrepared = pStatement.executeQuery();

Same query.

Data comes from a view on SqlServer (2008, I think, have no access right now) from a table with more than 15 Million records. There are indexes for all needed fields and the same query (the first one) executed from console runs also quite fast.

If I execute the slow PreparedStatement query without the ORDER clause it also runs fast.

It looks clear to me that for any cause the database it's not using indexes and make a full scan when using preparedStatement, but maybe I'm wrong so I'm open to any idea.

I thought maybe the driver (sqlserver official latest and jtds has been tested) was holding the data waiting for any kind of EOF from connection but I've checked with tcpdump on my side and no data is received.

I can't find why this is happening so any idea will be welcomed.

Thank you in advanced!

1 Answers
Related