topic Re: Dropping tables in QlikView

Dropping tables

cbushey1 — Mon, 24 Sep 2018 16:54:54 GMT

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?

Re: Dropping tables

andrey_krylov — Mon, 24 Sep 2018 17:47:07 GMT

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

Re: Dropping tables

cbushey1 — Mon, 24 Sep 2018 17:56:14 GMT

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.

Re: Dropping tables

marcus_sommer — Tue, 25 Sep 2018 11:40:38 GMT

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

Re: Dropping tables

timpoismans — Tue, 25 Sep 2018 11:45:02 GMT

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.

Re: Dropping tables

Miguel_Angel_Baeyens — Tue, 25 Sep 2018 11:46:32 GMT

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.

Re: Dropping tables

cbushey1 — Tue, 25 Sep 2018 14:12:27 GMT

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