Andrea MartinHo appena scoperto che, avendo un SQL Server 2019, posso usare la formula
DROP TABLE IF EXISTS #tempTable
al posto del controllo:
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
Tre righe risparmiate.
PrecisionSQLHow to Use Temporary Tables to Replace Slow Subqueries
Materialize once. Query fast.
How to Use Temporary Tables to Replace Slow Subqueries #subquery
Dave Mason(I was reluctant to make the change because I didn't have a high volume non-production environment to do real world testing.
Plus, I knew the #TempTable sometimes held as many as a thousand rows...even though the guidance for choosing between a #TempTable and a @TableVariable has changed a bit over the last couple of major releases.)
/fin
Somewhat desperate, I decided to replace the #TempTable with a @TableVariable.
Boom! Problem solved. 😃
Yet here we were with the same problem once again. This time around, Pam Lahoud's #TempTable guidance was already in place within the stored proc code.
The server has 128 vCPU and 8 tempdb data files. Maybe I needed to add some more data files?
Well, I added two more files, but there was no appreciable improvement in performance. Bummer!
I guess it's too late to be brief, but long story short, when we had this problem before, I modified the stored proc to declare the #TempTable in a single statement (including all constraints, indexes, and compression) along with *not* dropping the #TempTable anywhere within the stored proc code.
Still, we had this one query near the top of the shit list: a CREATE #TempTable operation.
We'd had this once in the past, and I eventually realized it was a very high volume stored procedure causing tempdb metadata contention. Pam Lahoud explains the issue very well in this post: https://techcommunity.microsoft.com/blog/sqlserver/tempdb---files-and-trace-flags-and-updates-oh-my/385937
PiEmbSysTechUsing Temporary Tables (#TempTable) in T-SQL Server
Mastering Temporary Tables (#TempTable) in T-SQL Server: Creation, Usage, and Best Practices Hello, fellow SQL enthusiasts! In this blog post, I will introduce you to Temporary Tables in T-SQL Server - one of the most essential and versatile features in T-SQL Server: temporary tables (#TempTable). Temporary tables allow you to store and manipulate intermediate data during query execution. They are particularly useful…
https://piembsystech.com/using-temporary-tables-temptable-in-t-sql-server/
Simple Dictionary Bot#Temptable - Capable of being tempted; liable to be tempted.