InterBase Temporary Tables

This last week, InterBase 2020 has been released bringing the awesome Tablespaces feature into play. This new feature enables splitting the database into groups of tables (a Tablespace) that can then be put onto different physical disks (to aid performance) but also enables partial backup of a database. I plan to cover this new feature, and some useful ideas about how to use them in an article in the coming weeks, but first, I want to address something a bit older in InterBase that I’ve not blogged about before. Why? Well recently, I was at a UK roadshow event, when content about InterBase 2020 was being previewed, and a developer said “This new stuff is cool, but what I really need is the ability to put data into a table temporarily in InterBase, and have it isolated from other transactions…. Other databases have it, when will InterBase get it?” Well, InterBase has had this for years!

InterBase Temporary Tables, (or to give them their full name InterBase Global Temporary Tables) are a type of tables that exist in the InterBase database. The table structure is visible to any connection (just like a normal table), however, they are designed for use in a specific transaction. After use, they empty themselves when the transaction or connection ends. They are in essence, a pre-defined table that you start with empty, do your work, and then forget about (a bit like a structured notepad).

Creating a Global Temporary Table in InterBase

CREATE GLOBAL TEMPORARY TABLE {{Placeholder|table}}
({{Placeholder|<col_def>}} [, {{Placeholder|<col_def>}} | {{Placeholder|<tconstraint>}} ...])<br/> [ON COMMIT {PRESERVE | DELETE} ROWS

To create a global temporary table, you use similar syntax to creating a normal table (but with a few extra options). These options tell InterBase how to treat the data in the table. after you commit the transaction.

This creates a table that is visible to all users, but, the data is only visible to your connection. There are two options that then exist to define the length of visibility during the connection, following COMMIT (which ends the current transaction). PRESERVE and DELETE

The ON COMMIT DELETE  enables data to exist in a single transaction and would be used as follows. ON COMMIT PRESERVE allows data to be shared to other transactions within the connection, importantly, if you need to keep data for the long term, save into a proper table.

Using a Temporary Table (ON COMMIT DELETE)

Using the tables is simple. You first need to start a long lived transaction, (so ensure you don’t have auto commit enabled and that its in isolation mode), then you just need to use it, commit and forget.

1. Start a transaction in isolated mode.
2. Put data into the table
3. Modify / use the data
   • e.g. Append more records,
   • Delete records
   • Update records.
   • Run further queries joining to the data etc
   • Update normal table data based on what you have created (as needed)
4. When done, commit any changes you have made to normal tables by calling Commit, and close the transaction.

The temporary tables job is now done, and it will automatically empty.

Using a Temporary Table (ON COMMIT PRESERVE)

You first need to start a connection,  then you just need to use it Once you close you connection to the database, the data will clear from the table.

1. Start a connection,
2. Put data into the table
3. Modify / use the data
   • e.g. Append more records,
   • Delete records
   • Update records.
   • Run further queries joining to the data etc
   • Update normal table data based on what you have created (as needed)
4. When done, commit any changes you have made to normal tables by calling Commit, and close the transaction AND connection.

Why use a temporary table?

SQL statement can hit limitations. E.g. there is an internal limit on the number of values that can be returned within a Select statement for use as a nested sub select.

e.g. Select * from tableA where ID in  (Select ID from tableB )

Removing a Temporary Table

The point of Global Temporary Tables is that they exist for specific types of work. You can trust they are there as an empty table to work with, however, If you need to remove them, then just use drop table to remove it.

DROP TABLE <table name>

Why are InterBase Global Temporary Tables often over looked?

InterBase 2017 introduced the Truncate SQL keyword to rapidly empty a table. The Truncate Table command performs faster, requires less I/O, and journals and archives get much less information than an equivalent DELETE FROM table command.

In part this feature was a spin off from a customer who used Temporary tables a lot in their application to process high volumes of records. Global Temporary tables use Truncate behind the scene to they are (since 2017) very very fast to use.  In short, if you are using Temporary tables in earlier versions of InterBase, you should upgrade.

Alternatives to Global Temporary Tables

If you only need to create a subset of data to optimise query speed, then you might also consider looking at SQL Derived Table.  Added back in InterBase 2017, you can consider a derived table as a View with statement-level scope.

The post InterBase Temporary Tables appeared first on Stephen Ball's Technical Blog.