Improve Your Teradata Load Performance
Teradata allows only a limited number of bulk loads to be executed together as they consume a lot of resources. The precise limit depends on your Teradata system. It will be somewhere in the lower 2-digit area.
Whenever the utility limit is reached, additional requests will end up in a workload queue. On a heavily loaded system, this will lead to an unstable load behavior: Run times for the same load can vary considerably.
Depending on the number of rows and the size of the rows, it may be a better choice to use TPUMP or BTEQ. You should consider this transactional loading types for a small number of rows(a few thousand rows only). This way you can avoid wasting utility load slots.
The good idea is to analyze your existing “loading landscape”, searching for possibilities to improve performance. The QueryLog will help you to detect Fastloads and Multiloads which you should switch to Bteq or TPump.
The SQL below will do the job for you:
SUBSTR(t01.QUERYTEXT,15) AS TABLE_START,
SUBSTR(TABLE_START,1,MINDEX(TABLE_START,' ‘)) AS TheTABLE,
(t02.FIRSTRESPTIME – t01.STARTTIME DAY(2) TO SECOND(6) ) RUNTIME,
60*60*EXTRACT(HOUR FROM RUNTIME) + 60*EXTRACT (MINUTE FROM RUNTIME) +
EXTRACT(SECOND FROM RUNTIME) (DECIMAL(18,2)) AS RUNTIME_SECS
FROM DBC.DBQLogTbl t01
t01.SESSIONID = t02.SESSIONID
t01.STARTTIME(DATE) BETWEEN DATE-21 AND DATE
AND t01.StatementType = ‘BEGIN Loading'
AND t02.STARTTIME(DATE) BETWEEN DATE-21 AND DATE
AND t02.StatementType = ‘END Loading'
The result set shows you all Fastloads (or Multiloads) and the number of rows which have been bulk loaded into each target table (I restricted the result set to the last 21 days).
This query helps to detect the bulk loads which you should change to transactional loads (BTEQ, TPUMP). “Mytable2” is such a candidate.
One hint: Be sure that filtering on CPU seconds is turned off for the query log, otherwise most “Begin Loading” statements will be missing as they only consume small amounts of CPU and will not be recorded in the DBC.DBQLOGTBL.
|MyTable||4.352.814,00||16.07.2015 00:25:24||16.07.2015 00:25:59||0 00:00:35.450000|
|MyTable2||100,00||16.07.2015 00:04:25||16.07.2015 04:25:30||0 00:00:05.450000|