Temp table or permanent tables?

Question

For my company I am redesigning some stored procedures. The original procedures are using lots of permanent tables which are filled during the execution of procedure and at the end, the values are deleted. The number of rows can extend from 100 to 50,000 rows for calculation of aggregations.

My question is, will there be severe performance issues if I replace those tables with temp tables ? Is it feasible to use temp tables ?

Answer 1

It depends on how often your using them, how long the processing takes, and if you are concurrently accessing data from the tables while writing.

If you use a temp table, it won't be sitting around waiting for indexing and caching while it's not in use. So it should save an ever so slight bit of resources there. However, you will incur overhead with the temp tables (i.e. creating and destroying).

I would re-examine how your queries function in the procedures and consider employing more in procedure CURSOR operations instead of loading everything into tables and deleting them.

However, databases are for storing information and retrieving information. I would shy away from using permanent tables for routine temp work and stick with the temp tables.

The overall performance shouldn't have any effect with the use case you specified in your question.

Hope this helps,

Jeffrey Kevin Pry