What’s the best practice to avoid collisions in throwaway temp tables

best practicesoracletemporary-tables

In SQL Server, I'm used to creating a #temp table for temporary storage in a complex stored procedure. Not wanting to clutter up the system, I DROP the table when done.

However, if I do the same in Oracle:

CREATE GLOBAL TEMPORARY TABLE Temp.RecordsToBeDeleted
    (PK INT NOT NULL PRIMARY KEY, Value VARCHAR2(10) NOT NULL)
-- Populate Temp.RecordsToBeDeleted
-- Use Temp.RecordsToBeDeleted
DROP TABLE Temp.RecordsToBeDeleted

…in one stored procedure, and another stored procedure does something similar and also uses the name Temp.RecordsToBeDeleted, they could collide.

The actual data in the table will not be visible across stored procedures, but the schemas could be different, or I could DROP one while the other is running.

Is there an established best practice for this? I could prefix each table name with the name of the stored procedure, but that's clumsy. I could treat GTTs as first-level objects, created with the rest of the schema rather than being created and dropped as needed, but that would increase maintenance; stored procedures would no longer be wholly self-contained.

Best Answer

The standard approach in Oracle is that global temporary tables are first-level objects. They should be created at install time when you create all your permanent tables. Doing DDL at runtime is very much frowned upon. I'm not sure how this increases maintenance or how it makes stored procedures "no longer wholly self-contained"-- you don't need to do any maintenance on a temporary table and stored procedures presumably already have references to tables so I'm not sure what problem adding a reference to a temporary table creates.

It is unusual, though, in Oracle to use a temporary table in the first place. I've been developing in Oracle for years and I can count the number of temporary tables I've created there on one hand. Perhaps you would be better served by a collection of some type. Perhaps you just need a view that implements whatever logic you're using in SQL Server to populate the temporary table. Perhaps you want a pipelined table function. Perhaps you even want a materialized view. It will depend on why you're creating the temporary table to begin with. Here is another thread that talks about alternatives to temporary tables in Oracle in more detail.

Related Solutions

What reason is there to create a physical table for a report output

(+) Reasons to create a physical table to store report data:

The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding window of old reports for the inevitable requests along the lines of: "Can you generate last year's report again? I can't find the CSV I extracted at the time."

This is probably the main reason it's set up that way at your site. Crystal Reports may not be smart enough to cache the report data as users paginate through it or change the report's settings. So in the worst case CR is regenerating your report with each of those actions--a costly and time-consuming operation. With the physical table it just re-queries the table as many times as it needs.
Setting permissions on the report is easy. You want to see this report? Well, all you need is permission to read the results, NOT generate them. So here, have some read rights on this table in a locked down schema and filegroup/tablespace.

By manually caching the report, you control and isolate the process of generating it. You give your report readers more freedom to act and yourself less to worry about as a DBA.

(-) What you lose with a physical report table:

Flexibility. Wanna change the report? Argh, need some DDL changes.
Storage space. You're persisting the data on disk, so duh.

Sql-server – Why is using a table variable more than twice as fast as a #temp table in this specific case

The output of SET STATISTICS IO ON for both looks similar

SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10

Gives

V2
Table '#58B62A60'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#58B62A60'. Scan count 10, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

T2
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3

And as Aaron points out in the comments the plan for the table variable version is actually less efficient as whilst both have a nested loops plan driven by an index seek on dbo.NUM the #temp table version performs a seek into the index on [#T].n = [dbo].[NUM].[n] with residual predicate [#T].[n]<=[@total] whereas the table variable version performs an index seek on @V.n <= [@total] with residual predicate @V.[n]=[dbo].[NUM].[n] and so processes more rows (which is why this plan performs so poorly for larger number of rows)

Using Extended Events to look at the wait types for the specific spid gives these results for 10,000 executions of EXEC dbo.T2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| SOS_SCHEDULER_YIELD | 16         | 19             | 19             | 0              |
| PAGELATCH_SH        | 39998      | 14             | 0              | 14             |
| PAGELATCH_EX        | 1          | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

and these results for 10,000 executions of EXEC dbo.V2 10

+---------------------+------------+----------------+----------------+----------------+
|                     |            |     Total      | Total Resource |  Total Signal  |
| Wait Type           | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| PAGELATCH_EX        | 2          | 0              | 0              | 0              |
| PAGELATCH_SH        | 1          | 0              | 0              | 0              |
| SOS_SCHEDULER_YIELD | 676        | 0              | 0              | 0              |
+---------------------+------------+----------------+----------------+----------------+

So it is clear that the number of PAGELATCH_SH waits is much higher in the #temp table case. I'm not aware of any way of adding the wait resource to the extended events trace so to investigate this further I ran

WHILE 1=1
EXEC dbo.T2 10

Whilst in another connection polling sys.dm_os_waiting_tasks

CREATE TABLE #T(resource_description NVARCHAR(2048))

WHILE 1=1
INSERT INTO #T
SELECT resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id=<spid_of_other_session> and wait_type='PAGELATCH_SH'

After leaving that running for about 15 seconds it had gathered the following results

+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
|  1098 | 2:1:150              |
|  1689 | 2:1:146              |
+-------+----------------------+

Both of these pages being latched belong to (different) non clustered indexes on the tempdb.sys.sysschobjs base table named 'nc1' and 'nc2'.

Querying tempdb.sys.fn_dblog during the runs indicates that the number of log records added by the first execution of each stored procedure was somewhat variable but for subsequent executions the number added by each iteration was very consistent and predictable. Once the procedure plans are cached the number of log entries are about half those needed for the #temp version.

+-----------------+----------------+------------+
|                 | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run       |            126 | 72 or 136  |
| Subsequent Runs |             17 | 32         |
+-----------------+----------------+------------+

Looking at the transaction log entries in more detail for the #temp table version of the SP each subsequent invocation of the stored procedure creates three transactions and the table variable one only two.

+---------------------------------+----+---------------------------------+----+
|           #Temp Table                |         @Table Variable              |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE                    |  9 |                                 |    |
| INSERT                          | 12 | TVQuery                         | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable |  5 |
+---------------------------------+----+---------------------------------+----+

The INSERT/TVQUERY transactions are identical except for the name. This contains the log records for each of the 10 rows inserted to the temporary table or table variable plus the LOP_BEGIN_XACT/ LOP_COMMIT_XACT entries.

The CREATE TABLE transaction only appears in the #Temp version and looks as follows.

+-----------------+-------------------+---------------------+
|    Operation    |      Context      |    AllocUnitName    |
+-----------------+-------------------+---------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                     |
| LOP_SHRINK_NOOP | LCX_NULL          |                     |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1  |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2  |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2  |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL          |                     |
+-----------------+-------------------+---------------------+

The FCheckAndCleanupCachedTempTable transaction appears in both but has 6 additional entries in the #temp version. These are the 6 rows referring to sys.sysschobjs and they have exactly the same pattern as above.

+-----------------+-------------------+----------------------------------------------+
|    Operation    |      Context      |                AllocUnitName                 |
+-----------------+-------------------+----------------------------------------------+
| LOP_BEGIN_XACT  | LCX_NULL          |                                              |
| LOP_DELETE_ROWS | LCX_NONSYS_SPLIT  | dbo.#7240F239.PK__#T________3BD0199374293AAB |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_HOBT_DELTA  | LCX_NULL          |                                              |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc1                           |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2                           |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF    | sys.sysschobjs.nc2                           |
| LOP_MODIFY_ROW  | LCX_CLUSTERED     | sys.sysschobjs.clst                          |
| LOP_COMMIT_XACT | LCX_NULL          |                                              |
+-----------------+-------------------+----------------------------------------------+

Looking at these 6 rows in both transactions they correspond to the same operations. The first LOP_MODIFY_ROW, LCX_CLUSTERED is an update to the modify_date column in sys.objects. The remaining five rows are all concerned with object renaming. Because name is a key column of both affected NCIs (nc1 and nc2) this is carried out as a delete/insert for those then it goes back to the clustered index and updates that too.

It appears that for the #temp table version when the stored procedure ends part of the clean up carried out by the FCheckAndCleanupCachedTempTable transaction is to rename the temp table from something like #T__________________________________________________________________________________________________________________00000000E316 to a different internal name such as #2F4A0079 and when it is entered the CREATE TABLE transaction renames it back. This flip flopping name can be seen by in one connection executing dbo.T2 in a loop whilst in another

WHILE 1=1
SELECT name, object_id, create_date, modify_date
FROM tempdb.sys.objects 
WHERE name LIKE '#%'

Example Results

Screenshot

So one potential explanation for the observed performance differential as alluded to by Alex is that it is this additional work maintaining the system tables in tempdb that is responsible.

Running both procedures in a loop the Visual Studio Code profiler reveals the following

+-------------------------------+--------------------+-------+-----------+
|           Function            |    Explanation     | Temp  | Table Var |
+-------------------------------+--------------------+-------+-----------+
| CXStmtDML::XretExecute        | Insert ... Select  | 16.93 | 37.31     |
| CXStmtQuery::ErsqExecuteQuery | Select Max         | 8.77  | 23.19     |
+-------------------------------+--------------------+-------+-----------+
| Total                         |                    | 25.7  | 60.5      |
+-------------------------------+--------------------+-------+-----------+

The table variable version spends about 60% of the time performing the insert statement and the subsequent select whereas the temporary table is less than half that. This is inline with the timings shown in the OP and with the conclusion above that the difference in performance is down to time spent performing ancillary work not due to time spent in the query execution itself.

The most important functions contributing towards the "missing" 75% in the temporary table version are

+------------------------------------+-------------------+
|              Function              | Inclusive Samples |
+------------------------------------+-------------------+
| CXStmtCreateTableDDL::XretExecute  | 26.26%            |
| CXStmtDDL::FinishNormalImp         | 4.17%             |
| TmpObject::Release                 | 27.77%            |
+------------------------------------+-------------------+
| Total                              | 58.20%            |
+------------------------------------+-------------------+

Under both the create and release functions the function CMEDProxyObject::SetName is shown with an inclusive sample value of 19.6%. From which I infer that 39.2% of the time in the temporary table case is taken up with the renaming described earlier.

And the largest ones in the table variable version contributing to the other 40% are

+-----------------------------------+-------------------+
|             Function              | Inclusive Samples |
+-----------------------------------+-------------------+
| CTableCreate::LCreate             | 7.41%             |
| TmpObject::Release                | 12.87%            |
+-----------------------------------+-------------------+
| Total                             | 20.28%            |
+-----------------------------------+-------------------+

Best Answer

Related Solutions

What reason is there to create a physical table for a report output

Sql-server – Why is using a table variable more than twice as fast as a #temp table in this specific case

Temporary Table Profile

Table Variable Profile

Related Question