We received a lot of feedback emails for article “Working with temporary tables in InterBase 7.5″ which was published in issue 1. The one of them is impressed me and with permission of its respective owner I’d like to publish it.

Alexey Kovyazin, Chief Editor

Volker Rehn wrote:

One thing I’d like to ask you to change is re temp tables. I suggest you even create another myth box for it. It is the sentence

‘Surely, most often temporary tables were necessary to those developers who had been working with MS SQL before they started to use InterBase/Firebird.’

This myth does not want to die. Temporary tables (TT) are not a means for underskilled DB kids, who cannot write any complex SQL statement. They are *the* means of dealing with data that is *structurally* dynamic, but still needs to be processed like data with a fixed structure. So all OLAP systems based on RDBMS are heavily dependent on this feature - or, if it is not present, it requires a whole lot of unnecessary and complicated workarounds. I’m talking out of experience.

Then, there are situations where the optimizer simply loses the plot because of the complexity of a statement. If developers have a fallback method to reduce complexity in those cases, that’s an advantage. Much better than asking developers to supply their own query plans.

Also, ’serious’ RDBMS like Informix had them at least 15 years ago, when MS’s database expertise did not go further than MS Access. Certainly those MS database developers who need TTs to be able to do their job would not have managed to deal with an Informix server if complexity was their main problem.

The two preceding paragraphs were about local temp tables. There are also global temporary tables (GTTs). A point in favour of GTTs is that one can give users their own workspace within a database without having to setup some clumsy administration for it. No user id scattered around in tables where they don’t belong, no explicit cleanup, no demanding role management. Just setup tables as temp, and from then on it is transparent to users/applications that the data inside is The argument reminds me a bit of MySQL reasoning when it comes to features their ‘RDBMS’ does/did not have. Transactions / Foreign Keys / Triggers etc were all bad and unnecessary because they did not have them (officially: they slow down the whole system and introduce dependencies). Of course they do. To call a flat file system a RDBMS is obviously good for marketing. Now they are putting in all those essential database features which were declared crap by them not long ago. And you can see already that their marketing now tells us how important those features are. I bet we won’t see MySQL benchmarks for a while ;-) .

We should not make a similar mistake. Temporary tables are important if the nature of a system is dynamic, either re user/session data isolation, or re data where the structure is unknown in advance, but needs to be processed like DB data with a fixed structure. That Firebird does not have them is plainly a lack of an important feature, in the same category as cross DB operations (only through qli, which means ‘unusable’). Both features could make Firebird much more suitable as the basis for OLAP systems, an area where Firebird is lacking considerably.

Well, to be fair, Firebird developers are working on both topics.