Menu
Ran into an interesting problem with a MySQL table I was building as a temporary table for reporting purposes.
I found that if I didn't specify a storage engine, the DROP TEMPORARY TABLE command would hang for up to half a second.
If I defined my table as ENGINE = MEMORY this short hang would disappear.
As I have a solution to this problem (using MEMORY tables), my question is why would a temporary table take a long time to drop? Do they not use the MEMORY engine by default? It's not even a very big table, a couple of hundred rows with my current test data.
432
If you are joining multiple tables with millions of rows of records in each, CTE will perform significantly worse than temporary tables. I've seen this from my own experience. CTE's perform significantly slower. CTE's also perform slower because the results are not cached.
Using the DROP TABLE command on a temporary table, as with any table, will delete the table and remove all data. In an SQL server, when you create a temporary table, you need to use the # in front of the name of the table when dropping it, as this indicates the temporary table.
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.
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.
Temporary tables, by default, will be created where ever the mysql configuration tells it to, typically /tmp or somewhere else on a disk. You can set this location (and even multiple locations) to a RAM disk location such as /dev/shm.
Hope this helps!
126
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
