Using SSMS or Azure data Studio I can insert half a million rows into a temp table in 2 ms but retrieving the rows to screen from the table or the temp table take 13 to 15 seconds. I am unsure where to look next as to where the performance has been lost as this was not and issue a few months ago.
This is a production server that has been running for a few months now SQL 2017. This occurs whether I am on a client or directly on the server but on a basic PC with standard disks & Just 8 gig of RAM its up to three times quicker
SELECT SML.CONTACT_Id
INTO ##slr
FROM dbo.Stage_MailingLists AS SML;
SELECT *
FROM ##slr AS S;
DROP TABLE ##slr;
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 2 ms.
Table 'Stage_MailingLists'. Scan count 9, logical reads 13482, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(521001 rows affected)
(1 row affected)
SQL Server Execution Times:
CPU time = 1187 ms, elapsed time = 365 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
(521001 rows affected)
Table '##slr'. Scan count 1, logical reads 1493, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row affected)
SQL Server Execution Times:
CPU time = 329 ms, elapsed time = 8296 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Turns out, Sophos antivirus had an update 'pending restart' that was causing a bottle neck restarted the server and all back to expected base line.