Temporary Tables

In this section we will discuss about Temporary tables in Sybase ASE.
I found temporary tables are very useful, many times in my work experience.

From name itself you can guess that Temporary table is temporary but not permanent in database.
Temporary tables are created in tempdb database (one of the default system database).

There are two types of Temporary tables:

  • Tables that can be shared among Adaptive Server sessions
  • Tables that can be accessible only by the current Adaptive Server session or procedure

Note: For easy reference, will replace “Tables that can be accessible only by the current Adaptive Server session or procedure” with “hash table”.
Note: For easy reference, will replace “Tables that can be shared among Adaptive Server sessions” with “Global Temporary table”. Term “Global Temporary table” is not an official Sybase ASE term; we will be using this term in this post just for easy reference.

Tables that can be shared among Adaptive Server sessions (Global Temporary Tables):
This type of table can be shared among ASE server sessions. If you need to share data either between code segments or between stored procedures (during execution in a single session) or between users, you should use a global temporary table.  Global temporary tables are permanent in tempdb.

We can create and drop global temporary table as follows
CREATE TABLE:
(if you are connected to user database)
CREATE TABLE tempdb..accounts
(id int)
go

OR

use tempdb
go
CREATE TABLE accounts
(id int)
go

DROP TABLE:
(if you are connected to user database)
DROP TABLE tempdb..accounts
go

OR

use tempdb
go
DROP TABLE accounts
go

Note: You cannot DROP or ALTER a global temporary table while other connections are using the table.
Note: If you don’t mentioned tempdb.. in create statement, it will create accounts table in permanent user database.

Tables that can be accessible only by the current Adaptive Server session or procedure:
These types of tables are preceded with a “#” (which is why they are sometimes called hash tables). They BELONG to the user connection, the spid. They are not intended to be shared, either with other users, or with other code segments, stored procedures, etc.

Hash tables are accessible only by the current Adaptive Server session or procedure. The table exists
until the current session or procedure ends, or until its owner drops it using drop table.

Create a non-shareable temporary table by specifying a pound sign (#) before the table name in the create table statement.
CREATE TABLE:
CREATE table #accounts
(id int) 

OR

(Creating a temporary table from another table)
SELECT * into #accounts FROM accounts

Above created table #accounts will be dropped if current session ends or if user drops it by using
drop table command like
DROP TABLE:
DROP TABLE #accounts

Note: Drop table command should be executed from the same session in which table was created.

Hope you did understand, what is the difference between “ Tables that can be shared among Adaptive Server sessions” and “ Tables that can be accessible only by the current Adaptive Server session or procedure”.

Where and why do we use “Global temporary tables” and “hash tables”?

If there is a scenario where you want to see data in a temporary table after execution of a procedure
or after specific connection closes (assuming we are using temporary table inside the procedure). What would you like to use for above scenario?

If you are thinking to use # (hash) table, I would say it’s not a good idea as hash table won’t be available after connection closes or after execution of procedure. So shall we use global temporary table(Tables that can be shared among Adaptive Server)?

Now you may have question that if multiple users are accessing this global temporary table, it may result in something wrong (incorrect data).

For example:
(if you are connected to user database)
CREATE TABLE tempdb..accounts
(id int)

If we use above table in a procedure (assume below script is written in a procedure and
permanent tables used in this script already exists in database and @current_user is a parameter passed to a procedure)

———-Procedure script————
DELETE FROM tempdb..accounts

INSERT INTO tempdb..accounts (id) VALUES (SELECT id FROM user_accounts WHERE user_name=@current_user)

SELECT * FROM account_details ad, tempdb..accounts tacc
WHERE ad.id = tacc.id

DELETE FROM tempdb..accounts

———-Procedure script end————

If above script (procedure) will be executed by two users at the same time then it may give unexpected results to one of the user.

Assume user ‘ABHI’ and user ‘Mike’ run above script at the same time.
Data in user_accounts table is as below image.
Table_user_accounts
Above procedure script will delete data from tempdb..accounts on every run and insert data from
user_accounts table for specified user_name (i.e. ‘ABHI’ or ‘Mike’) . So it may result in unexpected results.

What can we do to avoid such issue?
To avoid issues like this we can add session_id (@@spid) in global temporary tables.
(if you are connected to user database)
CREATE TABLE

tempdb..accounts
(spid int,
id int)

Note: @@spid is a global variable and identifies the current user process id .

And script would be like below. This will solve this issue and many users/processes can use a global temporary table at the same time. Each process will work(select/delete/update etc) on only those data rows which belong to their spid (process id).

———-Procedure script start————
DELETE FROM tempdb..accounts WHERE spid = @@spid

INSERT INTO tempdb..accounts (spid, id) VALUES (SELECT @@spid, id FROM user_accounts WHERE user_name = @current_user)

SELECT * FROM account_details ad, tempdb..accounts tacc
WHERE ad.id = tacc.id
AND tacc.spid = @@spid

DELETE FROM tempdb..accounts WHERE spid = @@spid

———-Procedure script end————

Suggestion: Create an Index on such tables. If the table is for partitioned use, place the spid as the first column in the Index.

Note: We can use hash tables, where we want to use temporary table in the same session.

Sometime you may have faced an issue that you created a permanent table in tempdb (global temporary table) and after few days when you try to access that table, it doesn’t exist.

Do you know why?

Temporary database itself is temporary. The contents are lost when the server exits (either gracefully, under SHUTDOWN; or without clean up, under power failure). The temporary database, being temporary, is not recovered. Instead it is created fresh, when the server is booted. It is created from the model database (imagine LOAD DATABASE tempdb FROM model, on each server boot). Therefore, if you want your permanent tables which reside in the temporary database to be available in temp database when the server reboots, simply create them in model database.

Global temporary tables for Performance Enhancement:
There is another scenario where I create permanent tables in the temp db (global temporary tables). This is purely for performance reasons. Temp db is the most heavily used db on any server: therefore, anything you can do to enhance performance there will enhance the overall performance of the entire server. Let’s say you have some stored procedures that utilize temporary tables (#tables), that are heavily used, many times every day, by many users. These tables get created and destroyed all the time, millions of times a day, and contain a very small amount of information (the scenario is even worse if the amount of data is large). If you implement these temporary tables as permanent tables in temp db, you achieve two significant performance enhancements:

  • Eliminate that creation/destruction millions of times per day
  • update statistics allow those stats to be used when the queries are optimized (hash temporary tables have no statistics; they default to 10 pages, 100 rows, which leads to incorrect optimizer decisions). This is significant for larger tables, as it ensures the decisions made by the optimizer when producing the Query Plan, are correct.

Hope this post was useful for you. Please do comment if you have any doubts/suggestions.

 

 

This entry was posted in Sybase ASE and tagged , , . Bookmark the permalink.

Leave a Reply