Query Performance affected by Tuning Applications

2

Here we discuss a few concepts that we can maintain for efficient query processing. A good mix  and match of the following will show improved performance. Also the Explain feature can be used on a query to analyse the optimal strategy that will be followed during execution.

In doing so the system uses metadata stored in various forms. Details of a tables indices are foremost consideration for strategy finalization. Also the record count and the statistical distribution of the record components are consulted in arriving at the final answer.

In a typical case when multiple joins are performed, selectivity would play a major role when joins of two derived tables are inevitable.

Let us see a few of the plain simple strategies that can help access time, query efficiency and database integrity

 

Referential Integrity(RI)

RI helps to maintain data consistency, data integrity, less development to enforce referential constraints and overall improves performance as the Teradata optimizer chooses the best efficient method .

However the overhead to consider are

• Building Reference Index Subtables. (All Indices are stored as a table in Teradata)
• Inserting ,deleting and updating the two linked tables would be consuming more time.

 

Correlated Subqueries(CS)

Correlated subqueries are those that has the select statement in the where clause referencing the row in the outer containing query. Teradata is well equiped to handle the CS in the optimizer.

Rewriting the queries with or without temporary tables but having as CS construct will work well with significant increase in speed of query execution.

 

Updatable Cursors

A cursor is a temporary placeholder for the data as it is retrieved. This cursor pointer enables the update or delete of record simpler. However the following points should be considered

·         Size of result and the number of updates.

·         The duration for which the cursor is held locked.

The idea is not to use when many updates per cursor are needed. Multiload utility is suggested.

 

Derived Tables

A derived table can be the original table, a part of it (Joins included) or a view. Such tables avoid the creating and dropping of the temporary tables. Also they assist the optimizer in making join decisions.

Also a derived table in a select statement forces the creation of a spool file, which gives all the benefits of a base table in joins etc.

 

Joins and Aggregates on a view

Continuing with the above topic, working of a join on derived table containing the aggregates ensures efficiency on two counts.

·         Creation and deletion of temporary tables.

·         Reading and writing off a temporary table.