SQL Performance Tuning

2017-07-07 02:10:07

There are three ways to allay or prevent constraints: defer , disable, drop.

Defer

The standard way to allay or prevent constraints is to defer the constraint. To use this method, you first have to explicitly name the constraints on a table and define them as deferrable, like this:

CREATE TABLE Table1 ( column1 SMALLINT, ... CONSTRAINT Constraint1 CHECK ( column1 < 1000) DEFERRABLE INITIALLY DEFERRED)

A constraint defined as DEFERRABLE allows you to specify when you want the DBMS to check the constraint for violationthe choices are after the SQL statement is executed or at transaction end. The INITIALLY DEFERRED keywords ensure that the DBMS will defer checking the constraint either until transaction end or until you activate the deferrable constraints in your program with a SET CONSTRAINTS statement, like this:

SET CONSTRAINTS ALL IMMEDIATE

Remember that constraint deferring only happens within a transactionas soon as you COMMIT, the DBMS will check for constraint violations. With deferrals, though, you can gain some control over the timing of constraint checks, which is especially an advantage if cyclic references are in your table definitions, or if there is an assumption that the transaction might have to be canceled with ROLLBACK anyway.

Portability

At the moment, SQL Standard-compliant deferral is supported only by IBM and Oracle. Informix supports the SET CONSTRAINTS statement, but all Informix constraints are deferrable by definition. Microsoft supports deferrable constraints, but not with standard SQL syntax.

Deferral Transaction End

If you defer constraints, do not wait for COMMIT to check for violations. If COMMIT fails, the transaction will ROLLBACKand you won't get a chance to fix anything! So:

SET CONSTRAINTS ALL IMMEDIATE

is the statement you want.

Disable

The nonstandard way to allay or prevent constraints is to disable the constraint. For example, if you put this nonstandard Oracle SQL-extension statement in your program:

ALTER TABLE ... DISABLE <constraint list>

the constraints in the list cease to have any effect. The disablement happens over transaction boundaries.

Disablement is attractive for loading, because it is more efficient to do 1,000 INSERTs and then check all the data at once, than to do an INSERT followed by a constraint check one thousand times. Also, if you're doing a bulk UPDATE on a replicated database, or a database recovery, then the constraints have already been checked for the same data. In such cases, it becomes reasonable to hope that second checks are unnecessary. Keep in mind, though, that if you disable PRIMARY KEY or UNIQUE constraints, the DBMS won't update their associated indexes. At the moment, disablement is supported only by Oracle, but Microsoft has this equivalent nonstandard SQL-extension:

ALTER TABLE ... NOCHECK CONSTRAINT

Fine-Tuning

A bit of fine-tuning is possible with both the defer and the disable methods . By naming specific constraints, you can control the order of checking. Why? Because the first constraint you want to process is the one that's most likely to fail. The earlier a process fails, the less time has been wasted on it.

Drop

Another standard, but inconvenient, way to allay or prevent constraints is to drop the constraint, like this:

ALTER TABLE Table1 DROP CONSTRAINT Constraint1 CASCADE

As with disabling constraints, this method helps speed the process of large-file INSERTs, bulk UPDATEs, and database recoveries . It also has the same disadvantage : Automatically updated indexes for PRIMARY KEY and UNIQUE constraints will be left out of sync with your data. But if you can't use constraint disablement, you can still drop constraints and then re-create them once your bulk work is completed.

The Bottom Line: Disabling Constraints

Of the three possible ways to allay or prevent a constraintdefer, disable, droponly deferral and dropping the constraint are supported by standard SQL.

You can gain control over the timing of constraint checks by defining them as DEFERRABLE constraints. Deferred constraints can enhance performance especially when cyclic references are in your table definitions, or if there is an assumption that a transaction might have to be canceled with ROLLBACK anyway.

If you defer constraints, do not wait for COMMIT to check for violations. If COMMIT fails, the transaction will ROLLBACKand you won't get a chance to fix anything! Always use SET CONSTRAINTS ALL IMMEDIATE before COMMIT.

To speed up data loading, bulk UPDATEs on a replicated database, and database recoveries, either disable or drop (and then re-create) your constraints.

To save time, ensure your DBMS checks the constraint that's most likely to fail first. Control the order of constraint checking by either deferring or disabling the constraints.