Skip to main content

Datometry Documentation

Optimize Teradata DELETE Statements and Transaction Commands

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

Deleting large numbers of records on Azure Synapse is very slow. 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).

To address this issue, the Hyper-Q hint disable_user_transactions lets you:

  • Ignore explicit transactions control statements issued by a client application by enabling or disabling auto-commit mode.

  • Execute all statements in auto-commit mode, thereby enforcing transaction integrity at the Teradata statement level.

  • Execute multi-statement requests (MSRs) one statement at a time.

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.

The syntax for disable_user_transactions is:

disable_user_transactions = true | false

where:

Parameter

Description

true

Enables the feature such that Hyper-Q ignores transaction commands from a client application.

false

Hyper-Q accepts transaction commands sent from a client application. This is the default behavior.

Hint Scope

The disable_user_transactions hint can be specified at the request, session, and global (or application) level. To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

Example 47. Specify that Hyper-Q Ignore Transactions as a Global Setting

You can specify disable_user_transactions as a global (or application level) setting in the Hyper-Q configuration file (dtm.ini). To enable disable_user_transactions, set it to true.

"gateway".disable_user_transactions = true

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.



Example 48. Specify that Hyper-Q Ignore Transactions at the Query Level

You can specify disable_user_transactions as a query hint at either the session or query level. This example specifies that the hint be used at the for session level within a DELETE FROM statement.

DELETE FROM table_name 
-- @dtm_disable_user_transactions=true for session
; 
INSERT INTO table_name SELECT 1; 
SELECT 1/0;

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.