JDBC-driver / SQL Server recompiles my prepared statements all the time when they contain ROW_NUMBER()

Question

I've noticed that prepared statements containing ROW_NUMER() code often gets recompiled although their SQL-code hasn't changed.

(example from book Inside Microsoft SQL Server 2008: T-SQL Querying):

WITH SalesRN AS (
 SELECT
  ROW_NUMBER() OVER (ORDER BY qty, empid) AS rownum,
  empid,
  mgrid,
  qty
 FROM
  dbo.SalesOrder
)
SELECT 
 rownum,
 empid,
 mgrid,
 qty
FROM
 SalesRN
WHERE
 rownum > 100 * (?-1)
 AND rownum <= 100 * ?
ORDER BY
 rownum

I'm using the latest version of the jTDS JDBC-driver (1.2.3) and noticed the problem with both SQL Server 2005 and 2008.

Has anyone a clue what is going on? Why does it recompile the statements although their code doesn't change? For one of my queries the recompilation takes about 1200ms which is a lot compared to the execution time which is as low as 31ms.

Answer 1

I suspect it being recompiled to optimise for paging because of this bit:

rownum > 100 * (?-1)
 AND rownum <= 100 * ?

However, I'd also qualify SalesRN too (dbo.SalesRN). This may also be the cause.

Finally, you could use a query hint. This is a black art. I'd start with KEEP PLAN or OPTIMIZE FOR UNKNOWN.