当我准备好的语句包含 ROW_NUMBER() 时，JDBC 驱动程序/SQL Server 总是重新编译它们
我注意到包含 ROW_NUMER() 代码的准备好的语句经常被重新编译，尽管它们的 SQL 代码没有改变。
（《Inside Microsoft SQL Server 2008：T-SQL 查询》一书中的示例）：
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
我正在使用最新版本的 jTDS JDBC 驱动程序 (1.2.3)，并注意到 SQL Server 2005 和 2008 都有问题。
有谁有线索吗？到底是怎么回事？尽管代码没有改变，为什么它会重新编译这些语句？对于我的一个查询，重新编译大约需要 1200 毫秒，这与低至 31 毫秒的执行时间相比要长很多。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我怀疑它被重新编译以优化分页，因为这一点：
但是，我也限定了 SalesRN (dbo.SalesRN)。这也可能是原因。
最后，您可以使用查询提示。这是一门黑术。我会从“保留计划”或“针对未知情况进行优化”开始。
I suspect it being recompiled to optimise for paging because of this bit:
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.