Pros and Cons of using Temporary Tables

[former_member182302]

Hi SCN,

Need your help in understanding Pros and Cons of usingTemporary Tables:

Creating this thread as a continuation with discussion in another thread mentioned below as suggested :

So these are the observations i had on usage of temporary tables : ( All the SQL i learnt till now was on SAP HANA as i don have prior experience using SQL on other databases ). So when i was working with colleagues of mine who worked more on databases like Teradata ( From whom i learnt SQL ) there were some views on Temporary tables as listed below :

1) Locking may happen when multiple users use that procedure, but i see it is a session specific structure , i did not see the possibility of "Locking"  ( need your inputs whether my understanding was correct )

2) We created a shell script to test the performance of the procedure when multiple users try to access it . ( Again there are some debates that the way we wrote the script is sequential and it is not useful for this testing ) However, When users increase i saw performance degrading a considerable rate for Temporary tables (when compared against procedure with EXEC IMMEDIATE) i was thinking it to be because of "insert" operation cost . is my understanding correct?

3) Only advantage with temp tables i saw is when you want to perform some logic instead of doing on entire data set, you can limit the scope of the data and insert into temporary table ( if dynamic filters we can use Exec ) and use it for further calculations which increases performance.  In this case i saw a performance difference when i tried to use EXEC IMMEDIATE instead of EXEC to escape from creating Temporary Tables ) 

Below is one blog where i showed using EXEC IMMEDIATE but it was first designed using EXEC and Temporary table approach which we later changed to remove the usage of temporary tables.

Help me in understanding this "Temporary Tables" in a better way.

Regards,

Krishna Tangudu

[former_member182302]

Any updates for this thread?

Regards,

Krishna Tangudu

[lbreddemann]

Hi Krishna

temp tables are just another tool for the database developer.

They guarantee that the data (global temp table) or both data and table definition (local temp table) are only available to your current session.

Also, modifying this table doesn't create any logging and - due to the guaranteed single session usage - no locking.

They are just a more lightweight data structure that can be used like a table but lives a "short and easy life" .

Hope that makes sense to you.

- Lars

[former_member182302]

Hi Lars,

Thanks for the clarification. I agree they are user session specific and hence I was mentioning that locking will not take place.  Just looking for those confirmation words from you to validate my understanding here.

Locking --> N/A

Memory Usage --> If 500 users , how does memory allocation happen for those 500 session dependent tables ? is there anyway i can see those details ?

Advantage i see when using temporary tables is, if am able to aggregate and get the required result set in the temporary table and then perform other complex logic on top of this data, then am able to see the performance gains here.

Apart from that is there any "disadvantage" or things i need to have a look out for while creating a temporary table?

Regards,

Krishna Tangudu

[lbreddemann]

Hey Krishna,

correct - there is no way you can create locks on a records in a temporary table, as only one session at a time has access to them.

Technically, even if you build models with the graphical modeler, upon execution SAP HANA may create temporary tables automatically.

Don't know any supported way to find the current used size though and sure enough, when 500 users fill a temp table in parallel, memory will be allocated for that.

To be honest, I don't get the pro-and-cons approach to temp tables or any other tool. It's just one of many tools you may or may not use.

They are not worse or better per se than any other feature.

At least that's how I see it

- Lars

[former_member182302]

Thanks Lars for your replies.

Agree with you. Any new feature, I just start looking for the advantages it brings along with any limations, which helps me to understand the tool better ( On Client "Demand" ). Guess already raised 2 threads one on this and another on using .hdbdd for table creation.

Regards,

Krishna Tangudu