Speed up imports in BTEQ or SQL Assistant

Tools
Enthusiast

Speed up imports in BTEQ or SQL Assistant

When using BTEQ or SQL Assitant to upload data into tables, I'm only getting about 2 records per second speed. Unfortunately, I don't have access to Fastload. I know its not the network as my down speed is blazing and Oracle is uploading normally. All of the files are in pipe delimited text and I've tried a few variations, but the speed doesn't change.

Are there any general rules for importing data with these tools that speeds things up? I tried importing the data into a volatile temp table with NO LOG but that didn't change anything. Also, this is a large corporate server - does Teradata have an admin option that caps upload speed? I get the feeling that the only option for fast uploads of flat files is fastload (hence it's existance, hah), but any help would be appreciated.

ck

11 REPLIES
Supporter

Re: Speed up imports in BTEQ or SQL Assistant

What is the data volume you want to process?

What is your PI on the table? Is is fairly unique?

Can you share the table DDL and BTEQ Script?

Senior Apprentice

Re: Speed up imports in BTEQ or SQL Assistant

Slow inserts are usually due to network latency, you could submit a "ping teradataname" on command line to get this timing.

In BTEQ there's a PACK, which greatly speeds up imports, e.g. .PACK 100;

In SQLA it's called "Maximum batch size for simple Imports" in Options -> Import

Both send a single INSERT plus a single parcel with all the data for PACK records (PACK should be set to the number of rows fitting into 64KB)

Dieter

Enthusiast

Re: Speed up imports in BTEQ or SQL Assistant

I have found SQLA 13.10 relatively very fast compare to its previous version . Also very long varchar fields can cause bottle neck.

Enthusiast

Re: Speed up imports in BTEQ or SQL Assistant

Thanks for the responses.

When I'm calculating the size for PACK, are there any leading / trailing control bytes I need to include? Also, the defined record size is 42 characters, but one of the fields is an integer that is usually 1 ro 2 chars in the flat file. Do I need to make any adjustment for that?

Here's the BTEQ script:

.logon xxxx;

SET session dateform=ansidate;

DATABASE xxxxx;

SEL date;

SEL time;

CREATE VOLATILE TABLE TMP_PREV_AFFIL, NO LOG
 (NPI VARCHAR(16),
 POS VARCHAR(16),
 VOL VARCHAR(10) )
ON COMMIT PRESERVE ROWS;

select count(*) from TMP_PREV_AFFIL;

.import VARTEXT file=xxxxx\PREV_AFFIL_Q12012.txt;

.quiet on

.repeat*

USING (col1 varchar(16), col2 varchar(16), col3 VARCHAR(10))
INSERT INTO TMP_PREV_AFFIL (NPI, POS, VOL)
values (:col1, :col2, :col3);

select count(*)from TMP_PREV_AFFIL;

DROP TABLE xxxxx.CLM_AFFIL_PREV;

CREATE TABLE xxxxxx.CLM_AFFIL_PREV
  (
 NPI VARCHAR(16),
 POS VARCHAR(16),
 VOL INTEGER
 );

INSERT INTO xxxxxx.CLM_AFFIL_PREV
SELECT NPI, POS, CAST INTEGER VOL FROM TMP_PREV_AFFIL;

DROP TABLE TMP_PREV_AFFIL;

.quiet off

.QUIT ERRORCODE ;

Here's an example of the data. The file size is 4.1MB:

12345678901|1234567|1

The log:

BTEQ 12.00.00.00 Wed Feb 01 03:53:16 2012

+---------+---------+---------+---------+---------+---------+---------+----

.logon xxxxx,

 *** Logon successfully completed.

 *** Teradata Database Release is 12.00.03.24                  

 *** Teradata Database Version is 12.00.03.18f                   

 *** Transaction Semantics are BTET.

 *** Character Set Name is 'ASCII'.

 *** Total elapsed time was 2 seconds.

+---------+---------+---------+---------+---------+---------+---------+----

SET session dateform=ansidate;

 *** Set SESSION accepted.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

DATABASE xxxxxx;

 *** New default database accepted.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

SEL date;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

Current Date

------------

  2012-02-01

+---------+---------+---------+---------+---------+---------+---------+----

SEL time;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

    Time

--------

03:53:18

+---------+---------+---------+---------+---------+---------+---------+----

CREATE VOLATILE TABLE TMP_PREV_AFFIL, NO LOG

 (NPI VARCHAR(16),

 POS VARCHAR(16),

 VOL VARCHAR(10) )

ON COMMIT PRESERVE ROWS;

 *** Table has been created.

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

select count(*) from TMP_PREV_AFFIL;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

   Count(*)

-----------

          0

+---------+---------+---------+---------+---------+---------+---------+----

.import VARTEXT file=xxxxxx\PREV_AFFIL_Q12012.txt;

+---------+---------+---------+---------+---------+---------+---------+----

.quiet on

 *** Type QUIET OFF; to resume output.

+---------+---------+---------+---------+---------+---------+---------+----

.repeat*

+---------+---------+---------+---------+---------+---------+---------+----

USING (col1 varchar(16), col2 varchar(16), col3 VARCHAR(10))

INSERT INTO TMP_PREV_AFFIL (NPI, POS, VOL)

values (:col1, :col2, :col3);

 *** Starting Row 0 at Wed Feb 01 03:53:19 2012

 *** Total elapsed time was 1 second.

 *** Total elapsed time was 1 second.

<lots of text deleted here>

 *** Total elapsed time was 6 hours, 44 minutes and 21 seconds.

 *** Total elapsed time was 6 hours, 44 minutes and 21 seconds.

 *** Total elapsed time was 6 hours, 44 minutes and 21 seconds.

I aborted at this point.

I will try the PACK suggestion. Unfortunately, I can't upgrade SQLA, it's a corporate desktop. Thanks again.

Supporter

Re: Speed up imports in BTEQ or SQL Assistant

There are different things to notice.

1. When I run your code I get much higher load rates.

 *** Starting Row 0 at Fri Feb  3 17:10:39 2012

 ...


 *** Warning: Out of data. 

 *** Finished at input row 1000 at Fri Feb  3 17:10:56 2012

 *** Total number of statements: 1000,  Accepted : 1000,  Rejected : 0 

 *** Total elapsed time was 17 seconds.

 *** Total requests sent to the DBC = 1000

 *** Successful requests per second = 58.823


so it might be as Dieter indicated. As long as this is the bottleneck you have a problem. 

2. You don't need to load into the volatile table first. You can load into the target table directly. 

3. .set session 10 

for example might speed up 10 times here - if you solved the network issue.

.set sessions 10;

.logon ...;

SET session dateform=ansidate;

DATABASE xxx;

SEL date;

SEL time;

DROP TABLE CLM_AFFIL_PREV;

CREATE TABLE CLM_AFFIL_PREV
(
NPI VARCHAR(16),
POS VARCHAR(16),
VOL INTEGER
);

select count(*) from CLM_AFFIL_PREV;

.import VARTEXT file=./bteqtest.txt;

.quiet on

.repeat*

USING (col1 varchar(16), col2 varchar(16), col3 VARCHAR(16))
INSERT INTO CLM_AFFIL_PREV (NPI, POS, VOL)
values (:col1, :col2, :col3);

.quiet off

select count(*)from CLM_AFFIL_PREV;

DROP TABLE CLM_AFFIL_PREV;

SEL date;

SEL time;

.QUIT ERRORCODE ;
*** Starting Row 0 at Fri Feb  3 17:21:47 2012

 *** Warning: Out of data. 

 *** Finished at input row 10000 at Fri Feb  3 17:22:04 2012

 *** Total number of statements: 10000,  Accepted : 10000,  Rejected : 0 

 *** Total elapsed time was 17 seconds.

so 10 times more rows in the same times.


Enthusiast

Re: Speed up imports in BTEQ or SQL Assistant

The number of sessions seems to have solved the issue, for BTEQ anyway. After about 30 seconds, I'm at 10k rows already.

THANK YOU! This has been a very big help.

ck

Enthusiast

Re: Speed up imports in BTEQ or SQL Assistant

Update.

Once I had some time, I started playing around with both packand session. Learned a few things which I'll note here in case anyone searches on this in the future.

  1. The default CLI pack size setting in our environment is very, very small. Increasing it improved performance even more dramatically than the number of sessions.
  2. You can change it in BTEQ, but if you do it as a standalone command, I believe the value you choose gets saved and will persist in future BTEQ sessions until you change it again. You can do a 'one time only' setting by including it in the repeat command (i.e. .repeat * pack 2000).
  3. You don't have to be exact on the sizing for your rows - it is an upper limit and BTEQ will take care of avoiding buffer overflow. I am not sure what the consequences are if you're way off.
  4. BTEQ locks rows at the hash value level, which may cause a deadlock if you're using a large PACK size with multiple sessions. If two sessions each have 300 inserts in them and the first one blocks the second on a row, then the second blocks the first on another row, you may get into trouble. You can avoid it by running a single session.

Thanks again for the assistance.

ck

Enthusiast

Re: Speed up imports in BTEQ or SQL Assistant

Great post!!

Enthusiast

Re: Speed up imports in BTEQ or SQL Assistant

Hi Dnoeth,

Can i use PACK command for BTEQ DML activities to avoid slow running lookups. I know PACK is same as Serialize , since it is grouping of all same row hash in to a same amp.

--So, can i use PACK command in BTEQ DML activity ?.

--How to use PACK in bteq, and please check the correct syntex of using PACK statement in BTEQ ?.

--How many sessions can i use with PACK in this scenario ?.

----

.set sessions 5;

.PACK 250;

.LOGON xxxxxx/xxxxxxx,xxxxxx;

BT;

Del from db.test_tbal;

Update  (complex join);

Insert stage._hist

select * from stage.source files;

ET;

.LOGOFF;

.Quit;