Suppose, when retrieving a procedure, a temporary table # is created, populated with some data.

If the procedure is restarted, DROP of this table variable occurs and it is created again and filled with data.

The structure of the table does not change.

Will there be a profit if you create a normal table from a temporary table and instead of DROP do TRUNCATE?

  • five
    That is, you have already determined that this is the bottleneck in your procedure? And then there is a suspicion that, compared with the rest of the procedure, deleting (or cleaning) the table will take negligible time. There should rather be guided by considerations of business logic. For example, if there is a possibility that the procedure can be run simultaneously by two users, then you definitely need to use temporary tables (otherwise you run into the situation when one procedure filled the table with its data, and at that moment the second one will clear it). - Yaant
  • one
    In 90% of cases when they make temporary tables they are not really needed and you can get by with a single query. And it's usually faster than the tables - Mike
  • one
    And why keep the state of the temporary table between procedure calls? - Anatol
  • @Anatol, how can you save it at all? Something I did not know this. - Qwertiy ♦

2 answers 2

In theory, a truncate should be somewhat faster than drop (and even more so than the pair drop + create ), since truncate only changes the information about the space allocated for storing the table data, transferring it from the occupied to the free state. While drop does the same thing, it also removes information about the object (entries in the system tables that there is such and such a table with such and such a set of columns).

However, as rightly noted in the comments, in practice, the costs of one thing, and the other may be negligible compared to other operations in the procedure.

In addition, I would be careful not to

create a normal table from a temporary table

even if there is confidence that there will be no conflicts due to parallel access, since temporary tables are “live” in the tempdb system database, and additional measures are used to improve performance ( refer to the section Increasing the performance of the tempdb database ).

    TRUNCATE clears the table without adding entries to the transaction log. This is faster than DROP-CREATE. But as noted, it is important that problems arise when sharing a table with several processes.

    • truncate [table] adds entries to the transaction log, but logging truncate more “lightweight” operation than delete from [table] . - i-one