CODEWITHSUNDEEP
javasql-serverjdbc
jmiguel.rodriguez
jmiguel.rodriguez

Reputation: 178

Slow queries with preparedStatement but not with executeQuery

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!

Upvotes: 6

Views: 7070

Answers (1)

jmiguel.rodriguez
jmiguel.rodriguez

Reputation: 178

I've finally found a solution, at least in for my case. I got it here http://mehmoodbluffs.blogspot.com.es/2015/03/hibernate-queries-are-slow-sql-servers.html . Telling (driver? sqlServer?) not to send parameters as Unicode have resolved the problem.

Current connection string it's now: 

 String connectionUrl = "jdbc:sqlserver://server:port;databaseName=myDataBase;sendStringParametersAsUnicode=false";

And now both direct queries and preparedStatements runs at millisecond speed.

Thank you @DanGuzman for your suggestions!

Upvotes: 7

Related Questions