The frequency of the report or process will be a factor into how much of a performance hit you get by using the temporary tables. ![]() The disk writes involved in populating the temporary table might be more expensive than the reads would be if you were to modify your query to include the logic into one, larger, query. Temporary tables might be very similar to regular tables but most regular tables are not re-written into, every time they are queried. Temporary tables are session scoped which means that adding them into a process or report will probably cause them to be created multiple times. One difference between regular tables and temporary tables is how they are typically used. How to get the most out of your temporary tables Read / write behavior ![]() The same scan and join operators are used.Each step in the plan reads the exact same number of rows.The plans wouldn’t pass a text compare test but the important pieces are the same in each. > XN Seq Scan on temp_recent_sales rs (cost=0.00.0.60 rows=60 width=8) – Tables missing statistics: temp_recent_sales -– – Update statistics by running the ANALYZE command on these tables -– > XN Seq Scan on recent_sales rs (cost=0.00.0.60 rows=60 width=8) Inner join demo.dimsales ds on ds.orderkey = fs.orderkey Inner join demo.factsales fs on fs.orderkey = rs.orderkey To prove the point, the two below queries read identical data but one query uses the demo.recent_sales permanent table and the other uses the temp_recent_sales temporary table. They effectively are just regular tables which get deleted after the session ends. There is nothing inherently wrong with using a temporary table in Amazon Redshift. ![]() Should we use temporary tables in Redshift? They know that different database platforms handle temporary tables differently and there was concern that temporary table usage in Redshift would be an anti-pattern. One development team asked me whether they would be allowed to use temporary tables or not. Data warehouse design standards have been in question now that our platform is changing. My company is in the middle of a migration to Amazon Redshift.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |