Performance Characteristics

Soonyoung Lee

Performance Characteristics of Teradata RDBMS

1. Contrast with conventional Databases

Performance becomes extremely important when handling complex queries and large amount of data. Teradata's excellent performance is due to the parallelism- it loads data, processes data and backs up data in parallel.

Four common hardest things for a database are
- Joining tables
- Aggregating data
- Sorting data
- Scanning large volume of data.
Conventional DBs avoid these difficulties by
- Avoiding joins
- Using summarized data to avoid aggregation
- Storing data in sorted order
- Using index to avoid large scans.

Teradata does these:
- Avoiding joins? : Well, Teradata has a capacity to join tables up to 64 tables for a single query. With hash join perfromed in parallel, the performance can be improved up to 40%. Teradata enhances its performance efficiency by using star-schema joins when dealing with multiple tables and millions of records. It also uses dynamic bitmap indexing for very complex queries.
- Using summarized data to avoid aggregation? : 90% of data warehousing queries do aggregation. Teradata performs very efficiently by distributing data evenly in VPROCs with sophisticated hash algorithm and performs in parallel. For aggregation, the work is done easily by being shared across all VPROCs. This alleviates the burden of maintaining summary table to avoid aggregation.
- Storing data in sorted order? : No key sequence: Teradata hash algorithm frees users from the necessity of storing data in sorted order.
- Using index to avoid large scans?: Table scan is a critical factor for data warehousing where Ad Hoc queries is required for which there is no time to index. Teradata is free from the fear of table scan due to its parallel processing even when the table is very large. Teradata doesn't depend on index as much as other conventional DBs, especially heavy overheaded secondary index because table scan is no big deal for Teradata.
- Furthermore, Teradata can utilize both 3rd Normal Form physical model and star-schema model on databases of any size even for tera bytes. On the while, other conventional DBs uses only star-schema model for very large databases because of physical limitation, which fails to perform Ad Hoc queries and data mining.

2. Teradata performance characteristics - Query Parallelism

A single SQL statement is broken into a small components, and the components work at the same time to deliver one answer. Parallel execution reduce the response time dramatically when the query manages large amount of data. Teradata's basic unit of parallelism is VPROC(physically AMP). All relational operations such as scan tables, scan index tables, selections, joins, aggregations and sorts can be done in parallel across al the VPROCs at the same time.

Within-a-step Parallelism : The optimizer splits a SQL query into small base operations called steps, and dispatches these steps into VPROCs. If a step contains complicated sub operations, they are processed in parallel by pipelining.
Multi-step Parallelism : Multi-steps for the same query can be executed at the same time if they are not depend on the result of the previous step results.
Multi-statement request : When several different SQL statements are sent to the optimizer as a bundle, these can be executed in parallel. For example, five queries are sent as a bundle and they have the same subquery, the subquery is executed only once and used for the five queries. By eliminating common expression, this adds efficiency.
An example of parallel processing can be seen in the query below on the sample database Customer_service: EXPLAIN statement show the whole procedure of how the query is handled.

                    EXPLAIN select e.last_name, e.first_name, ep.area_code, ep.phone
                    from customer_service.employee e, customer_service.employee_phone ep,
                    customer_service.customer c
                    where e. employee_number = c. sales_employee_number
                    and e.employee_number = ep.employee_number
                    and c.customer_name = 'First American Bank';

*** Help information returned. 38 rows.

*** Total elapsed time was 1 second.

Explanation
---------------------------------------------------------------------------
1) First, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.ep.
2) Next, we lock a distinct customer_service."pseudo table" for read
     on a RowHash to prevent global deadlock for customer_service.e.
3) We lock a distinct customer_service."pseudo table" for read on a
     RowHash to prevent global deadlock for customer_service.c.
4) We lock customer_service.ep for read, we lock customer_service.e
     for read, and we lock customer_service.c for read.
5) We execute the following steps in parallel.
       1) We do an all-AMPs RETRIEVE step from customer_service.c by
          way of an all-rows scan with a condition of (
          "customer_service.c.customer_name = 'First American Bank'")
          into Spool 2, which is redistributed by hash code to all AMPs.
          Then we do a SORT to order Spool 2 by row hash. The size of
          Spool 2 is estimated with no confidence to be 8 rows. The
          estimated time for this step is 0.03 seconds.
       2) We do an all-AMPs JOIN step from customer_service.ep by way
          of a RowHash match scan with no residual conditions, which is
          joined to customer_service.e. customer_service.ep and
          customer_service.e are joined using a merge join, with a join
          condition of ("customer_service.e.employee_number =
          customer_service.ep.employee_number"). The result goes into
          Spool 3, which is redistributed by hash code to all AMPs.
          Then we do a SORT to order Spool 3 by row hash. The size of
          Spool 3 is estimated with low confidence to be 80 rows. The
          estimated time for this step is 0.06 seconds.
6) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
     RowHash match scan, which is joined to Spool 3 (Last Use). Spool
     2 and Spool 3 are joined using a merge join, with a join condition
     of ("(Spool_3.employee_number = Spool_2.sales_employee_number) AND
     (Spool_2.sales_employee_number = Spool_3.employee_number)"). The
     result goes into Spool 1, which is built locally on the AMPs. The
     size of Spool 1 is estimated with no confidence to be 8 to 640
     rows. The estimated time for this step is 0.20 seconds.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of

statement 1. The total estimated time is 0.26 seconds.