The FastExport utility is usually seen as a batch-oriented job to use when you need to return large numbers of rows from the database to a user. It is the ideal tool for efficiently and quickly returning large answer sets, sorted or otherwise, back to the client from the database. That’s why it was named the way it was: “FastExport”.
It might surprise you that FastExport can also be used against small data volumes. And when it is, Teradata 13.10 includes an important enhancement that gives you a more efficient way to get those rows back quickly.
Utilities generally come with some small start-up overhead that disappears pretty quickly as the data volume processed goes up. But this start-up overhead can be annoying, an often unnecessary, when you are processing very small volumes.
FastExport was designed to get big data volumes back to the client quickly. Here’s how it does that: After the final spool file has been built, the result rows will be redistributed across all AMPs, so that each AMP will have approximately the same number of data blocks to return. There is always a horizontal redistribution, which is designed to speed up the export of a large answer set. In addition, there will be a preceding vertical redistribution if ORDER BY is specified.
During the vertical redistribution process, additional AMP worker tasks are spawned on each AMP to receive rows, rows are sampled, a vertical redistribution map is built, and the rows are relocated based on this map. Horizontal redistributions, which happen whether or not vertical redistributions have taken place, pack rows from the spool file of each AMP into response blocks and ensure they are evenly spread across all AMPs for efficient return to the client.
The high level execution flow of FastExport includes these steps:
When using FastExport, locks are released before the export process begins.
There’s a change in the 13.10 release of the Teradata Database for FastExport to help performance against small volumes. This enhancement includes a “No Spool Mode” option, appropriate when just a few rows are going to be processed from a single table. Common usage for no spool mode is primary or secondary index access.
The No Spool Mode will prevent final spool files from being redistributed across all AMPs. In-memory buffers will be used to store the final answer set rows, rather than spool files. Only the AMPs that actually process rows will be involved in final processing, which will reduce unnecessary AMP-level overhead when the number of AMPs in the configuration grow.
The no spool option is appropriate only for simple selects, that do not contain joins. There are several benefits to this enhancement:
If you wish to take advantage of this enhancement, the TPT Export Operator has a new optional attribute called SpoolMode. If you don’t chose to use this option, your FastExport job will use the traditional spooling approach described above. If you specify NoSpool as your spool mode, then no spooling will take place as long as the SELECT statement supports that approach. If the query includes joins, a GROUP BY or ORDER BY, or a statistical function step, the no spool option is ignored.
You can read more about the No Spool option in the Teradata FastExport Reference manual or the Teradata Parallel Transporter Reference manual.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.