2

I got simple query that is selecting some static values using table value constructor:

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);

If you would execute it you will get results instantly BUT my actual query has over 7000 pairs of values and it runs over 30 seconds on my machine and it's just simple constant scan. Is there any way to improve it? Unfortunately I am not allowed to rewrite this query (to use temp table for example). It is how it is and the question is there something I can do to improve it's performance?

Execution plan

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
GrandmasterB
  • 21
  • 3
  • Have you tried generating an execution plan in SSMS ? https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15 – Steven Lemmens Apr 17 '20 at 14:13
  • Also, is that data saved in a table or are the 7.000 values you speak of generated by something like a script? If it's a table, please include the table structure and some sample data. – Steven Lemmens Apr 17 '20 at 14:14
  • It's generated by some script on the fly, the number of pairs may vary. Execution plan is very simple, it's just a constant scan. Posted it on original post just now. – GrandmasterB Apr 17 '20 at 14:24

2 Answers2

5

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.     |
+-----------------------------------------------------------------------+-----------------------------------------------+
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
3
SELECT c_id, c_type
FROM(VALUES
('8E0D2FD7-4D25-4FE5-8E01-8E07926E3D6B', 1),
('04FB3E91-3825-4EF3-B5A4-B42FBAEEE816', 1),
/*................*/
('CAE3C680-8A5B-4477-A03F-4E957ADCC38E',1),
(cast(null as varbinary(max)), null) --if you can inject this in the script....
) AS c(c_id, c_type)
where not(c_id is null and c_type is null)--......
lptr
  • 1
  • 2
  • 6
  • 16
  • 1
    This does actually work. Reduces compile time to 3 seconds rather than 30 on my 2008 instance, but simpler as `varchar(max)` instead of `varbinary(max)`. Both give an `CONVERT_IMPLICIT(varchar(max)` in the execution plan that presumably prevents it inspecting the values – Martin Smith Apr 17 '20 at 15:51
  • That's a pretty nifty hack :-) – TT. Apr 17 '20 at 15:53