Menu
Do I need to add DROP TEMPORARY TABLE IF EXISTS data; at the end of the stored procedure even though I have the check at the top? Is there a performance implication?
CREATE DEFINER=`TEST`@`%` PROCEDURE `TEST`() BEGIN
DROP TEMPORARY TABLE IF EXISTS data;
CREATE TEMPORARY TABLE data AS
...
END;
925
If you are wondering why it is not required to drop the temp table at the end of the stored procedure, well, it is because when the stored procedure completes execution, it automatically drops the temp table when the connection/session is dropped which was executing it. Well, that's it.
DROP TABLE MySQL Command Syntax. To remove a table in MySQL, use the DROP TABLE statement. The basic syntax of the command is as follows: DROP [TEMPORARY] TABLE [IF EXISTS] table_name [, table_name] [RESTRICT | CASCADE];
You can use these scripts to drop database, table, or other elements In the beginning of your stored procedure, and then create them freely during the SP. While recreating the table you have full control over the data types of the table columns (fields is a wrong word by the way in this context).
It's ok to explicitly include DROP TABLE or let SQL Server handle it for you each time the connection drops. If your stored procedures are already dropping temp tables, that's great. If they're not being dropped, I wouldn't bother spending the time and effort just to drop them.
In MySQL, temporary tables are dropped automatically when a database connection is closed. If you plan on leaving your connection open after the stored procedure, your temp table will exist on disk until that connection is closed. The performance implications depend on many factors, such as how you have configured temporary table storage on your server, how much data is in the table, etc.
It is considered best practice to just drop the temp table as soon as you are done with it. Then you save yourself the worry about those potential performance implications all together
76
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
