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
Last activity: Feb 21, 2024, 08:02 AM