Optimizations for Teradata DELETE ALL, DELETE without a WHERE clause statements

Datometry Hyper-Q executes Teradata full table DELETE statements (DELETE FROM table ALL / DELETE FROM table) statements as TRUNCATE on Synapse when they are not part of a transaction.

This optimization follows Synapse guidelines and transaction flow constraints.  Please see link below for performance and the transactional differences between DELETE and TRUNCATE :

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15

Transaction context may either be controlled by the client or be established by Hyper-Q to provide the atomicity for executing a Teradata MACRO. 

Full table DELETE statements are passed through as DELETEs without WHERE clause when transaction limitations are not met, which may result in slower performance. 

Please review the transaction context and revise the source application or scripts if the performance gains for DELETE -> TRUNCATE translation are necessary. 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.