0

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.

3
  • On a side note, it sounds as if you don't actually need temp tables or table variables here, and you could do the whole thing in one step. But we can't see your query and tables or sample data so can't help. Feb 13 at 13:50
  • @Charlieface Indeed. My honest claim that I can just use CTEs is proof that I don't need temp tables or table variables. I consider them only under the presumption that being able to tell the optimiser that something that it doesn't know is a primary key actually is one should be great for performance. Said presumption has proven true for temp tables but not table variables. My question is why it has been true for one but not the other.
    – J. Mini
    Feb 13 at 20:29
  • For simply a "pivot", see if this link provides an efficient way to achieve your goal: mysql.rjweb.org/doc.php/pivot . (I will need adapting from MySQL to SQL Server.)
    – Rick James
    Feb 20 at 21:49

3 Answers 3

1

In facts table variables are close to the concept of ARRAYs that does not exists in SQL Server.

Temp table triggered a recompilation every time there is a DDL statement (CREATE, ALTER, DROP) and when some rows has been added, updated or deleted. When the recomilation is achieved, the optmizer does know how many rows is inside the temp table. This is not the case (whicth some recent exception) for table variables that are stable objects and in which the cardinality is estimated between 1 and 100... But they are lighter !

When you add a primary key on a temp table, the statistics (cardinality) collected on the PK is updated which is not systematically the case for table variables...

But in some cases, table variables are better, because lighter, if the cardinality is close to 1 or 100...

Last, since version 2017, 2019, 2022 and the intelligent query optimizer features, the defered variables table compilation can help to improve cardinality of those objects...

1
  • When you add a primary key on a temp table, the statistics (cardinality) collected on the PK is updated which is not systematically the case for table variables... - Can you show evidence for this?
    – J. Mini
    Feb 21 at 8:19
0

Table Variable : Suitable for small dataset.

Temp Table : Suitable big dataset.

Table variable performance are better when there is small dataset.When dataset is big then table variable do not perform will because there is no Statistics maintain for table variable.Even if create primary key on table variable,there is no statistics for this primary key.

Optimizer will use hard coded estimation.When dataset is big in table variable then there will be discrepancies between Estimated rows and Actual number of rows.Hence optimizer plan choice will become sub optimal as the dataset size grows.

Yes ,In Sql Server 2019, Table Variable Deferred Compilation feature has improve Optimizer Estimated Rows significantly, still it is not accurate.

In Table variable still there is parameter sniffing issue and Table Variable Deferred Compilation has not resolved this issue. Option Recompile is a fix for parameter sniffing issue but it is not feasible

I tried to answer the general answer for general question.Each individual example has to be evaluated.

2
  • 1
    My question already admitted that I know that table variables do not have statistics. Your point about cardinality is irrelevant as I have already mentioned OPTION(RECOMPILE). Even without that, your claim that the estimation is hard coded isn't always true because I'm on SQL Server 2019.
    – J. Mini
    Feb 13 at 20:31
  • @J.Mini read my edited
    – KumarHarsh
    Feb 14 at 16:06
-2

Generally many factors affects on your query performance and i try my best to mention some of that and link to source for more explanation :

  • Primery Key when we create primery key we create constraints too. We can create check constraints on the SQL table variables during the declaration of the table variable but these constraints can not be used by the optimizer. A deep dive into SQL Table Variables

  • CTE (SubQueries) as you know and mention it no statistics are kept on table variables. the point is when you use CTE Or SubQuery the optimizer doesnt generate plan by subquery order , the optimizer generate best plan for whole query and because of lack of statistics maybe your plan not optimized But when use temp table you change order of execution and provide full index support and statistics (Example : you can use (Select Top 2147483648 * From @Table Order by Id) to change optimizer order)

Microsoft Learn :

Starting with SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. Using this new syntax, you can create indexes on table variables as part of the table definition. In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics. For more information about temporary tables and inline index creation, see CREATE TABLE (Transact-SQL).

  • SQL Server version (Database compatibility level) is important the query optimizer can’t generate a parallel query plan automaticlly when we want to populate data into the SQL table variables but If we are using SQL Server 2019 version, there is no need to worry about this issue. The SQL Table Variable Deferred Complication feature resolves this problem without any changes.

And of course so many other things definitely we have to pay attention to execution plan and find bottlenecks and check diffrent senarios.

1
  • 1
    This reads like a collection of relevant statements placed in seemingly random order. It reminds me of college students trying to produce an essay by cut and pasting lecture notes together.
    – J. Mini
    Feb 20 at 22:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.