Paul White (imported from SE)
Given the following heap table with 400 rows numbered from 1 to 400:
```
DROP TABLE IF EXISTS dbo.N;
GO
SELECT
SV.number
INTO dbo.N
FROM master.dbo.spt_values AS SV
WHERE
SV.[type] = N'P'
AND SV.number BETWEEN 1 AND 400;
```
and the following settings:
```
SET NOCOUNT ON;
SET STATISTICS IO, TIME OFF;
SET STATISTICS XML OFF;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```
The following `SELECT` statement completes in around **6 seconds** ([demo][1], [plan][2]):
```
DECLARE @n integer = 400;
SELECT
c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
N.number <= @n
AND N2.number <= @n
AND N3.number <= @n
OPTION
(OPTIMIZE FOR (@n = 1));
```
*Note: The `OPTIMIZE FOR` clause is just for the sake of producing a sensibly-sized repro that captures the essential details of the real problem, including a cardinality misestimate that can arise for a variety of reasons.*
When the single-row output is written to a table, it takes **19 seconds** ([demo][3], [plan][4]):
```
DECLARE @T table (c bigint NOT NULL);
DECLARE @n integer = 400;
INSERT @T
(c)
SELECT
c = COUNT_BIG(*)
FROM dbo.N AS N
CROSS JOIN dbo.N AS N2
CROSS JOIN dbo.N AS N3
WHERE
N.number <= @n
AND N2.number <= @n
AND N3.number <= @n
OPTION
(OPTIMIZE FOR (@n = 1));
```
The execution plans appear identical aside from the insert of one row.
All the extra time seems to be consumed by CPU usage.
Why is the `INSERT` statement so much slower?
[1]: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=3a1563d5014e69c90fc13e4fa33459d7
[2]: https://www.brentozar.com/pastetheplan/?id=ByCrEMbIE
[3]: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=35e46790034e0345c64ea2ca6fa78570
[4]: https://www.brentozar.com/pastetheplan/?id=HJLT4fb84