0

I am working with a java based web application and we use Microsoft SQL Server as the database server. In there we have couple of long running SQL queries and we are looking for options to improve the performance of those queries.

When I start reading about the query performance improvements, I found that SQL Server caches the query execution plans and the next time it can reuse it from the cache without creating a new execution plan. I tried it on MSSQL management studio console with SET STATISTICS TIME ON; and it clearly shows that SQL Server caches the execution plans. Does it automatically happens with JDBC? Or do we need to follow any specific instructions? I tried googling with out luck.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
keth
  • 793
  • 2
  • 11
  • 36

1 Answers1

1

Yes, it happens with JDBC. Just take care and use prepared statements (https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html) with bind variables. For example:

select * from tab where col = 'somevalue'

Is different from:

select * from tab where col = 'someothervalue'

But both can use the same plan when written as:

select * from tab where col = ?
ewramner
  • 5,810
  • 2
  • 17
  • 33
  • Hi user2612030, Thanks for the comment. In my query i have couple of string concatenations for IN clauses along with prepared statements. Does this have an effect ? is there any way we can handle IN clause to work with execution plan cache – keth Apr 18 '18 at 11:07
  • 1
    Depends on what you mean. As long as the SQL you pass to the server is the same the plan will be cached. If the SQL varies depending on input parameters you have a problem. For a long-running query the cache is not that important, though, it is more important for quick queries that run very often where it has a higher relative impact. Milliseconds for the new plan are not critical when it takes minutes to run the query! – ewramner Apr 18 '18 at 12:37
  • 1
    BTW, see https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause. It may give you some ideas, though again I don't think this is vital for a long-running query. – ewramner Apr 18 '18 at 12:59