Drop all tables at once or one at a time

[zagzebski]

It might not make a diiference but I was wondering if there was any efficienies with dropping all resident tables at end of script versus one at a time throughout the script.

[CELAMBARASAN]

Hi,

To drop multiple tables

Drop Tables TableName1, TableName2....;

To drop single tables

Drop Table TableName;

Celambarasan

[zagzebski]

Celambarasan -

Sorry I didn't make myself clear. What I am wondering does it make a difference one way or aonther to drop all temporary tables at one time at end of script versus one at a time throughout the script?  Is either way more efficient?

[Anonymous · Accepted solution]

I expect (maybe unreasonably) that dropping a table allows to free up some memory - hence always drop a table as soon as it's not needed anymore.

Regards,

Michael

[zagzebski]

Thanks!

[CELAMBARASAN]

Yes Michael is correct.

During the reload each and every table placed in memory(RAM) for faster access. If the data is huge then it slow downs further process.

It is a good practice to delete the tables often when the table is needed no more.

Celambarasan

[rbecher]

Btw. it also slows down with associations between tables on same column names. So, recommendation is always to drop temp tables at early stage (if not needed later).

- Ralf

[preminqlik]

first estimate the number of tables and use the following code ,

suppose my estimation is 250 tables so i wrote 250 in following code :

//code begins

for i = 0 to 250

          LET d = TableName(i);

          if isnull(d) then

                    i = 250

          end if

          if d <> null() then

         drop $(d);

          end if

next

Exit Script;

//code ends

(note: it works for less than or equal to 250 tables ..if you require more than that just replace the digit in code as per your requirement)

-Thanks

Premhas

[m2r4miller]

You don't have to estimate the number of tables if you use the NoOfTables() function:

//code begins

vTables = TableName(0);
FOR i = 1 to NoOfTables() - 1

     LET d = TableName(i);
     LET vTables = vTables & ', ' & d;
     TRACE $(vTables);
NEXT
DROP Tables $(vTables);

Exit Script;

//code ends

The TRACE statement isn't necessary, I used it to verify the string was building properly...

I'm not sure how many tables can be listed in one DROP Tables command, though...

-Mark

[#10]

Hi,

Try this script

Let vDropTables;

FOR i = 1 to NoOfTables()

    d=TableName(i);

    DROP Table $(i);

   NEXT i