时间戳

首页 > 解决方案 > 表值构造函数性能

sql-server - 表值构造函数性能

问题描述

我得到了使用表值构造函数选择一些静态值的简单查询：

SELECT c_id, c_type
FROM(VALUES
('8E0D2FD7-4D25-4FE5-8E01-8E07926E3D6B', 1),
('04FB3E91-3825-4EF3-B5A4-B42FBAEEE816', 1),
('8425047F-0DBD-463E-A7FE-EAE8812834CB', 1)) AS c(c_id, c_type);

如果你执行它，你会立即得到结果，但我的实际查询有超过 7000 对值，它在我的机器上运行超过 30 秒，它只是简单的持续扫描。有什么办法可以改善吗？不幸的是，我不允许重写此查询（例如使用临时表）。它就是这样，问题是我可以做些什么来提高它的性能？

执行计划

标签： sql-server

解决方案

Unfortunately this is a bug in older versions of SQL Server.

It spends a lot of time during compilation working out properties of the values that are unlikely to be useful to you (and does this in an inefficient way too). I looked into this in my answer here.

The execution plan you show in the question has 8,001 rows. Below are parse and compile times for that number of rows on various versions of SQL Server I have installed (script used). This time spent grows non linearly and if you are on a version preceding SQL Server 2014 you will be much better off reducing the batch size to maybe a 1,000 rows and doing 8 different batches than executing one batch of 8,000.

Even better will be to both reduce the row count and use parameters in place of the literal values as then it doesn't waste time at compile time examining the values at all.

+-----------------------------------------------------------------------+-----------------------------------------------+
|                                Version                                |      SQL Server parse and compile time:       |
+-----------------------------------------------------------------------+-----------------------------------------------+
| Microsoft SQL Server 2008 (SP3) - 10.0.5890.0 (X64)                   | CPU time = 28000 ms, elapsed time = 28798 ms. |
| Microsoft SQL Server 2012 (SP3-GDR) (KB4019092) - 11.0.6251.0 (X64)   | CPU time = 26203 ms, elapsed time = 26845 ms. |
| Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)  | CPU time = 2796 ms, elapsed time = 2798 ms.   |
| Microsoft SQL Server 2017 (RTM-CU17) (KB4515579) - 14.0.3238.1 (X64)  | CPU time = 500 ms, elapsed time = 784 ms.     |
| Microsoft SQL Server 2019 (RTM-CU2) (KB4536075) - 15.0.4013.40 (X64)  | CPU time = 600 ms, elapsed time = 600 ms.     |
+-----------------------------------------------------------------------+-----------------------------------------------+

推荐阅读