Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

Posted April 1, 2021

Tip 75 - Improving the Performance of a Table Variable using Optimizer Hint RECOMPILE

By Gregory A. Larsen

Table Variables have been known to run slowly when the table variable contains lots of rows. This is because when a batch is compiled the table variable has yet to be populated with any rows, and therefore the optimizer uses an estimated row count of 1 for table variables. This occurs because table variables don’t have statistics. Having an estimated row count of 1 works well for operators that have small row counts, like a NESTED LOOP operator for a join operation. Operators that perform well for small numbers of rows do not always scale when large numbers of rows are involved. Therefore, to improve the performance of your code that use table variables that contain lots of rows, you should consider adding the optimizer hint RECOMPILE as shown below:

SELECT O.[Order Key], TV.[City Key]
FROM Fact.[Order] as O JOIN @MyCities as TV
ON O.[City Key] = TV.[City Key]

By adding the optimizer hint RECOMPILE, a statement that uses a table variable will be recompiled after the temporary table has been populated. This will allow the optimizer to know how many rows are in the table variable. Once the optimizer has a better guess at the actual number of rows in a table variable, then it has a much better chance of picking an operator that works well for larger record sets, like a Hash Match for a join operation.  

If you find your Table Variable queries are not performing well then you might consider adding the optimizer hint RECOMPILE to see if your query starts performing better. Keep in mind with version 15 of SQL Server, Microsoft introduced a feature called “Table Variable Deferred Compilation” that solves the problem mentioned here, without the use of option RECOMPILE.

Latest Forum Threads
DB2 Forum
Topic By Replies Updated
tables get lock during backup in db2 v9.5 Hamsoo 0 May 8th, 01:03 AM
Query to Pull Last 7 days records from a table vgoushik 0 March 20th, 06:05 PM
DB2 Visual studio addin: ANSI/Unicode problem 10Pints 0 October 5th, 02:08 AM
Things that are everbodys labor day ideas? Lipsett197 0 September 1st, 09:42 AM