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 ?
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.
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
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;
- 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
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 ........
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.