Sample Header Ad - 728x90

Why Do Primary Keys on Temp Tables Improve Performance Much More Than On Table Variables?

0 votes
3 answers
943 views
Many of my reports are essentially pivot tables. I take a set of big tables and transform each table individually so that a particular column (let's call it CaseID) that wasn't a primary key in the original tables will be a primary key in the output (e.g. use ROW_NUMBER() OVER([...]) and filter for where that equals 1). Let's call each output a "subtable". I then join these subtables together on the primary key to make my final output. Intuitively, telling the optimiser that CaseID is a primary key of each subtable (e.g. by storing the subtable in a temp table that is explicitly defined as having CaseID as a primary key) should give a massive performance boosts and promote the usage of merge joins. In practice, I only see this benefit when the subtables are temp tables. When I do the same with a table variable, the performance that I get is no better than saying nothing about keys and just making the subtables CTEs. OPTION(RECOMPILE) makes no difference. My temp tables are not memory optimised. I am aware that table variables do not hold statistics, but **it is as if giving a table variable a clustered primary key does nothing for performance, despite it being awesome for temp tables**. Why is this? Do I have a misconception about what "does not hold statistics" means? I have seen this exact problem in multiple contexts, so I have not given any code example. It makes little sense to give a practical example when my problem has occurred so many times that it is clear that I am ignorant of a matter of theory.
Asked by J. Mini (1239 rep)
Feb 12, 2024, 09:53 PM
Last activity: Feb 21, 2024, 08:02 AM