While Hyper-Q strives to follow Azure Synapse transaction best practices, there are certain exceptions in which applications must be adapted for performance reasons. For example, large DELETEs enclosed in a transaction are not as performant. Among the options to handle such DELETE statements is to either change the code to a CREATE TABLE AS SELECT (CTAS) statement followed by a RENAME or DROP clause, or, for unconditional DELETEs to execute them outside of the explicit transaction in the query. In the latter case, the unconditional DELETE statement is transparently translated by Hyper-Q to a TRUNCATE statement, which is more performant.

To learn more about Azure Synapse Analytics best practices for transactions, see Optimizing transactions in dedicated SQL pool in Azure Synapse Analytics in the Microsoft documentation.

Datometry recommends the following best practices to optimize Teradata DELETE statements and transactions on Azure Synapse Analytics:

• Optimize Teradata DELETE ALL and DELETE without WHERE Clause Statements
• SQL Statements to Perform Unconditional and Conditional DELETEs
• Disable Transactions

Optimize Teradata DELETE ALL and DELETE without WHERE Clause Statements

Datometry Hyper-Q executes Teradata full table DELETE statements (DELETE FROM table_name ALL and DELETE FROM table_name) as TRUNCATE TABLE  on Azure Synapse Analytics when they are not part of a transaction. This optimization follows Microsoft's guidelines and transaction flow constraints. 

Transaction context can be controlled by either the client application or established by Hyper-Q to provide the atomicity with which to execute a Teradata MACRO. Full table DELETE statements are passed through as DELETEs without a WHERE clause when transaction limitations are not met, which may result in slower performance. 

You should review the transaction context and revise the source application or scripts if the performance gains for DELETE to TRUNCATE translations are necessary. 

To learn more about the performance and transactional differences between DELETE and TRUNCATE TABLE, see TRUNCATE TABLE in the Microsoft documentation.

SQL Statements to Perform Unconditional and Conditional DELETEs

The following examples illustrate how to perform unconditional and conditional DELETEs using the best practices published by Microsoft.

Move the Unconditional DELETE Out of the Transaction

Replace Unconditional DELETE with RENAME to Create a New, Empty Table 

Replace a Conditional DELETE with a CREATE TABLE AS SELECT (CTAS)Statement

Move the Unconditional DELETE Out of the Transaction

This example allows Hyper-Q to convert the unconditional DELETE into a TRUNCATE operation, performing orders of magnitude faster than a fully logged delete operation.

The original SQL query:

BEGIN TRANSACTION;
DELETE FROM table_name;
<additional statements>
END TRANSACTION;
<remaining script>

Rewritten to move the DELETE FROM clause outside of the transaction:

DELETE FROM table_name;
BEGIN TRANSACTION;
<additional statements>
END TRANSACTION;
<remaining script>

This method has the advantage of being easy to apply, however, if the execution of the statement fails in the <additional statements> code block, data from the table may be lost. For this reason, you must confirm that the data was properly moved using additional business logic.

Replace Unconditional DELETE with RENAME to Create a New, Empty Table 

This method replaces deleting the contents of a table with renaming and creating an empty table in place of it. As if the first example, this too is many times faster than a fully logged DELETE operation.

The original SQL query:

BEGIN TRANSACTION;
DELETE FROM table_name;
<additional statements>
END TRANSACTION;
<remaining script>

Rewritten to move the DELETE FROM clause outside of the transaction:

DROP TABLE table_name_old; --Remove backup table from previous run
RENAME TABLE table_name AS table_name_old;
CREATE TABLE table_name AS table_name_old WITH NO DATA;
BEGIN TRANSACTION;
<additional statements>
END TRANSACTION;
-- optional automatic rollback on error
.if errorlevel=0 then GOTO success
DROP TABLE table_name;
RENAME TABLE table_name_old AS table_name;
.exit 1
.LABEL success
<remaining script>

Note that this example includes an optional automatic rollback error to confirm that the data was properly moved to the new, renamed table.

Replace a Conditional DELETE with a CREATE TABLE AS SELECT (CTAS) Statement

In this example, we replace a conditional DELETE with a CTAS statement, as shown in Microsoft’s best practices (see link below). Copying the data to keep in a minimally logged CTAS operation is often faster than a fully logged conditional DELETE operation. Note that this optimization applies to all conditional DELETEs, including those that already run outside of transactions.

The example illustrates a possible implementation with backup, similar to the previous example, Replace Unconditional DELETE with RENAME to Create a New, Empty Table for unconditional DELETEs. You can also use a simplified version without backup, similar to the example Move the Unconditional DELETE Out of the Transaction, for unconditional DELETEs.

To learn more about Azure Synapse Analytics best practices for DELETE transactions, see Optimizing deletes in the article Optimizing transactions in dedicated SQL pool in Azure Synapse Analytics in the Microsoft documentation.

The orignal SQL query:

BEGIN TRANSACTION;
DELETE FROM table_name WHERE condition;
<additional statements>
END TRANSACTION;
<remaining script>

Rewritten to replace a conditional DELETE with a CTAS statement:

CREATE TABLE table_name_new AS (
    SELECT * FROM table_name WHERE not condition
) WITH DATA;
DROP TABLE table_name_old;   -- remove backup table from previous run
RENAME TABLE table AS table_name_old;
RENAME TABLE table_name_new AS table;
BT;
ET;
-- optional automatic rollback on error
.if errorlevel=0 then GOTO success
DROP TABLE table_name;
RENAME TABLE table_name_old AS table;
.exit 1
.LABEL success

Disable Transactions

As described earlier in this article, Microsoft recommends using TRUNCATE for table level DELETEs (for example,DELETE FROM table_name (ALL)*). Hyper-Q converts these table level DELETEs to TRUNCATEs. However, Azure Synapse cannot execute a TRUNCATE inside a transaction. This optimization is disabled in Hyper-Q when a DELETE statement is inside of a transaction (either macro emulation by Hyper-Q or a client controlled transaction).

You can disable Teradata DELETE statements and transaction commands using the Hyper-Q hint disable_user_transactions. 

Datometry recommends evaluating application code for areas such as transformation logic which may have large DELETE statements to use disable_user_transactions as either a Hyper-Q hint to disable transactions within a query or request, or disable transactions at the application level. The level (or scope) you choose depends on the particular workload. Datometry recommends seeking the guidance of our Customer Success team when determining the appropriate level with which to disable transactions.

To learn more about the disable_user_transactions hint, and the different levels of scope at which they can be applied, see Optimize Teradata DELETE Statements and Transaction Commands in the Datometry for Microsoft Azure documentation.