All Forums Tools
a2kz 16 posts Joined 05/13
27 Aug 2014
Teradata Bteq Optimization

Hi All,
We have a requirement to apply optimization to the exting Bteq Scripts.
What are all possible scope of optimization in a Bteq ?
Is there any best practices document or coding standard guidelines in Teradata Official documentations ?
Thanks,
Ambuj

prasadkool123 4 posts Joined 08/14
27 Aug 2014

Hi Ambuj,
Can you please share some details about this?
Does your bteq have only SQL's or IMPORT/EXPORT as well?
In case of SQLs - Most of the performance tuning optimizations of TD SQL can be applied.
In case you have EXPORT - Things like selection only required columns, trimming whereever possible, apply compression to large db columns for faster retrieval, use fastexport if data volume is more etc. can be recommended.
In case of import - Use fload+sql insert-select / mload if possible. Check file for basic data quality checks to avoid bteq failures, Use PACK n (n for no. of rows) option to fast track loading etc. can be suggested.

Prasad M

Raja_KT 1246 posts Joined 07/09
27 Aug 2014
I am not sure exactly about the optimization scope.

In short, some of the best practices at the top of my mind

- capturing the statuses of queries
- Error Code, Error level assignments, activitycount if necessary
- Know the sessions, how many  to be specified for a job.
- Follow the requirement for export whether Recordmode,Report mode, Indicator mode.
- ANSI mode or Teradata mode
- Put good Remarks,maxerror,label,if..then,goto.
- Proper Indentation for code readability
- If required, set formats for header,footer  .....and folow same standards.
-  Have common separator for all jobs
- Follow organization's way of handling login script.
- Indentify SSR or MSR
- DB objects used inside like SP, macros etc must have proper documentation
- The scripts if any invoking bteq, must have proper information as header information
commented like number of parameters, creator,date, history.. or os command......
- NULL specification, Titles....
- Proper log filenames for important operation.
- Naming conventions, standards to be followed as per org.
.......
.....


We need to prepare a detailed document

Do you need tuning? then look for explain, diagnostic or use tools

 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

a2kz 16 posts Joined 05/13
27 Aug 2014

Raja,
Could you please explain more on SSR and MSR.
Can we pack the delete table and insert to table in a single transaction as below ? What is the advantage and disadvantage of this ?

Delete from table all
;insert into table select * from source_table;

Thanks,
Ambuj

Raja_KT 1246 posts Joined 07/09
28 Aug 2014
- A single-statement request(SSR) is a single Teradata SQL statement sent as a request.
- A multi-statement request(MSR) is two or more statements that are sent as a request.
Semicolon placement in relation to the rest of a line (for example, at the beginning or end)
determines whether a statement is processed as a single-statement request or a multistatement
request . 
In your example, BTEQ sends only one request at a time to any one available Teradata Database session.

I suggest you go through the bteq document. It will be helpful and you can get more ideas on how to draw lines on taking advantages of optimization, coding standards ........
 
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

a2kz 16 posts Joined 05/13
28 Aug 2014

In this case if the insert statement fails, does the delete transaction get rolled back ?

Raja_KT 1246 posts Joined 07/09
28 Aug 2014

Ambuj,
You could have tried and tested it :)
create dummy tables.
login to bteq(here it is bteq)
do the above two operations and see the errors.
confirm the delete part.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.