Improve Oracle Database Performance: Temp Table Recompiles
It is quite common knowledge among DBAs that temp tables are likely to cause recompilation. It occurs due to the treatment they get from the SQL Server Engine, which is similar to that of regular tables.
With a substantial change in the tables that are involved with underlying queries, the SQL Server finds this alteration with the aid of auto-update stats and denotes the reliant queries to be recompiled. This is done to improve Oracle database performance so the following execution can develop an optimum execution plan. This is somewhat similar to what occurs in a temp table.
SQL tuning for MySQL: Dealing with Temp Table Recompiles
Ever since SQL Server 2005+, recompilation has been happening at a separate statement-level instead of the stored procedure level. It may have proved beneficial due to the alteration in input parameters in a saved process, because of which a large part of the saved procedure is still able to use the stored execution plan.
It is particularly useful for stored procedures which include several statements. There are several examples that prove helpful in SQL tuning for MySQL in such cases (situations with temp tables involved). These not only demonstrate the occurrence of recompilation but also help one identify ways in which it can be detected and avoided.
To begin with, a procedure is initiated that picks a parameterized quantity of rows from a table. For the example below, a temp table is created as -
CREATE PROCEDURE spGetPrsdnt
AS
BEGIN
SET ROWCOUNT @Rows
SELECT *
INTO Prsdnts
FROM dbo.Prsdnts_Tmp
SELECT *
FROM Prsdnts
SET ROWCOUNT 0
END
Steps to Identify Recompiles and Improve Oracle Database Performance
After this, a trace up is placed to uncover any recompiling instance prior to procedure execution. The followings steps help -
Step — 1: Select SQL Server Profiler from the tools section in the management studio.
Step — 2: Click on the events selection option once the server is connected.
Step — 3: Untick every checkbox and select the one under ‘show all events’.
Step — 4: Slide to the bottom, where you will find the TSQL event, and enlarge the selection if needed.
Step — 5: Choose the event named SQL: StmtRecompile. It will display the individual queries that recompile in the process.
Step — 6: Select Run.
Results
Once the procedure is executed, not much in the name of recompilations is detected in the trace, where at first, each query is compiled. However, the search doesn’t turn up completely empty in the trace, which is still not entirely worth considering since it is not relevant to those looking to improve Oracle database performance or this example.
For a more favorable outcome, the parameter is switched to twelve in place of ten. However, there are no recompilations visible yet because the SQL Server Engine has detected that the stored execution plan utilized in the previous execution is sufficient for the current execution as well.
To check for recompilations again, the parameter is changed to two thousand this time and the procedure is executed. Now, a recompilation will be spotted because the Query Engine considers the cached execution plan insufficient for supporting this particular query.
Conclusion
It may be worth noting here that a table variable cannot cause recompilation inherently. Still, it may prove a better alternative in certain situations. The unspoken rule while performing SQL tuning for MySQL and using temp tables has been to do so for result sets in a quantity higher than a thousand, and for table variables to be used in result sets that are less than 1000 in number.
Despite a few disagreements on this rule, many experts prefer to utilize it, due to past experiences regarding poor performance. Temp tables keep stats and are also capable of being indexed which far exceeds the expense of recompilation.
