Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbushey1
Creator III
Creator III

Dropping tables

Is there a performance benefit to dropping a temp table as soon as you are done with it or would it be fine to drop them all as part of say an exit script?

1 Solution

Accepted Solutions
marcus_sommer

As far as you have always enough RAM available there won't be any performance issues by dropping all the temporary tables at the end.

In general I do the same within the last script-tab as well as dropping fields, deleting variables, renaming fields and so on. But if some of my temporary tables are quite big and/or there are multiple ones (often created within loops) I drop them immediately. Further there could be logically reasons for example in regard to formattings or certain fieldvalues - by using exists() - or to avoid noconcatenate statements and so on to drop tables before the end of the script.

- Marcus

View solution in original post

6 Replies
andrey_krylov
Specialist
Specialist

Hi, you can drop all tables starting with Temp, like this

  FOR vTableNo = 0 to NoOfTables()

  LET vTableName = TableName($(vTableNo)) ;

    IF vTableName like 'Temp*' Then

        Drop table [$(vTableName)] ;

    ENDIF

  NEXT

cbushey1
Creator III
Creator III
Author

Thank you but I am looking to see if there is a benefit to dropping the tables earlier in the script or not. We currently drop them at the end but I am wondering if we are getting a performance lag due to these tables floating around until the end.

marcus_sommer

As far as you have always enough RAM available there won't be any performance issues by dropping all the temporary tables at the end.

In general I do the same within the last script-tab as well as dropping fields, deleting variables, renaming fields and so on. But if some of my temporary tables are quite big and/or there are multiple ones (often created within loops) I drop them immediately. Further there could be logically reasons for example in regard to formattings or certain fieldvalues - by using exists() - or to avoid noconcatenate statements and so on to drop tables before the end of the script.

- Marcus

timpoismans
Specialist
Specialist

As Marcus said, as long as you have the RAM, you can drop your tables at the end of the script.

Synthetic tables are formed at the end of the script as well, and not during, so shouldn't have any effect on the loading speed.

But dropping them at the end, you do have to watch out for things like auto-concatenating.

Miguel_Angel_Baeyens

Agree with Marcus.

Unless you are already experiencing longer loading times or server resource exhaustion, rather than performance, having those tables around will impact more the data model itself as Marcus mentioned.

When I have to handle big tables, I usually store them, drop them and load them as I need them, instead of keeping them in memory and then doing a RESIDENT load. More as a personal preference and clean code than a performance issue.

If your data model is working fine and also the servers don't see any unexpected increase by the load, you can delete the tables at the end safely.

cbushey1
Creator III
Creator III
Author

Thanks all. I figured as much but wanted to confirm.