Stored Procedure in Teradata

General
Enthusiast

Stored Procedure in Teradata

Hi All,

My Company does not encourage me to use Stored proc since it a single amp operation and consumes lot of resource.

But I have also heard that single amp query works faster, so it sounds quite contradictory can some one expain me the same.

Thanks in Advance!!

Tags (1)
12 REPLIES
Enthusiast

Re: Stored Procedure in Teradata

Amit,

 A stored procedure is not a single AMP operation unless the code within only uses a single AMP.

A single AMP operation, usually via a unique primary index will be fast, inside or outside of a stored procedure.

RGlass

Enthusiast

Re: Stored Procedure in Teradata

Thanks for the comment RGlass!!

Is using stored Proc recommendable in terms of resource consumption and performance or not. If not then why?

Junior Contributor

Re: Stored Procedure in Teradata

Hi Amit,

if you can do the same in a macro, this would be prefered over an SP.

But if there's more complex logic there's no other way (at least within the database) than using an SP.

Most DBAs simply don't want end users to code SPs because this is the only place where loops and cursors are allowed. Both are processing data serially (= running on a single PE/node) which is worst case in a parallel DBMS) and are normally not needed to process data, but lots of programmers (e.g. with Oracle background) are used to do so.

Enthusiast

Re: Stored Procedure in Teradata

I like Dieter's advocacy for macro in Teradata. In stored procedure, if we can stuff all business logic(s) in one pass, without logging in or off many times, it is advantageous. Cursor-based  SQL or loops are not good in MPP and against Set-based processing.We have to try to avoid Cursors or loops. I worked in one  project in Teradata, where everything in Semantic layer was in stored procs.But we should code judicously for performance. Into the bargain, if you can convert flat logic  with ordered analytical functions, it will be advantageous.

Enthusiast

Re: Stored Procedure in Teradata

Thanks for your comments!!

so I can conclude that use of SP in Teradata is not recommended, if there are other options available,reason being , it holds looping and cursors and also it processes data serially (even in Parellel DBMS).

Feel free to add if you can see any other reasons to avoid using SP.

Enthusiast

Re: Stored Procedure in Teradata

Technology should  serve business, in the best possible way.

Stored proc objects are made for reasons, example-- In stored procedure, if we can stuff all business logic(s) in one pass, without logging in or off many times, it is advantageous--- network traffic is minimized between client and server, complex logic(s),dynamic result sets can be made use of...... End of the day it is all about getting end result with best performance. Just make a judicous choice :).

Just to share my experience in one  project in 2012(Banking project). One senior developer used  cursor JUST in one stored proc, saying the logic was too complex because of hierarchical and multi-level conditional checks and reading from few tables. No one said anything and it worked well, because the  volume of data was not big :)

dae
Enthusiast

Re: Stored Procedure in Teradata

Hi all,

I try to define a "Stored Procedure" S to collect statistics on a table T.

S has two parameters - "DatabaseName" and "TableName" - and would be able to (1) build dynamically the instruction to collect statistics (2) execute that instruction ... you are thinking that a very simple instruction already exists to perform that action - COLLECT STATISTICS ON T - but this is not the point !

The code of S uses the Request Form of DECLARE CURSOR ie "The Request to build dynamically the instruction to collect statistics is specified within an SQL string litteral" !

I am not able to understand why the compilation of the Cursor Definition fails (cf. below) !

Thanks a lot for any kind of help !

REPLACE  PROCEDURE STM_ADMN_PF1.PRCD_ADM_STTS_TABL (
IN IN_VAR_NM_BASE_STTS VARCHAR(128)
, IN IN_VAR_NM_TABL_STTS VARCHAR(128)
)

BEGIN
DECLARE VAR_CURS_STTS VARCHAR(2500)
; DECLARE VAR_NM_BASE_STTS VARCHAR(128)
; DECLARE VAR_NM_TABL_STTS VARCHAR(128)
; DECLARE VAR_NM_COLN_STTS VARCHAR(128)
; DECLARE LOC_NM_BASE_STTS VARCHAR(128)
; DECLARE LOC_NM_TABL_STTS VARCHAR(128)
; DECLARE CURS_STTS CURSOR FOR
'
SELECT ''COLLECT STATISTICS USING NO SAMPLE COLUMN (''
|| TRIM(BOTH FROM O_ADM_STTS.NM_COLN_STTS)
|| '') ON ''
|| TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS)
|| ''.''
|| TRIM(BOTH FROM O_ADM_STTS.NM_TABL_STTS)
|| '' ;''
, TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS)
, TRIM(BOTH FROM O_ADM_STTS.NM_TABL_STTS)
, TRIM(BOTH FROM O_ADM_STTS.NM_COLN_STTS)
FROM STM_ADMN_PF1.O_ADM_STTS O_ADM_STTS
WHERE 1 = 1
-- SELECTION EXCLUSIVE DES STATISTIQUES A COLLECTER POUR LA STRUCTURE CONSIDEREE
AND O_ADM_STTS.NM_BASE_STTS = ''' || LOC_NM_BASE_STTS || '''
AND O_ADM_STTS.NM_TABL_STTS = ''' || LOC_NM_TABL_STTS || '''
ORDER BY TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS) ASC
, TRIM(BOTH FROM O_ADM_STTS.NM_TABL_STTS) ASC
, TRIM(BOTH FROM O_ADM_STTS.NM_COLN_STTS) ASC
;
'
;

SET LOC_NM_BASE_STTS = IN_VAR_NM_BASE_STTS
; SET LOC_NM_TABL_STTS = IN_VAR_NM_TABL_STTS
;

OPEN CURS_STTS USING :LOC_NM_BASE_STTS, :LOC_NM_TABL_STTS
;

L1:

LOOP

FETCH CURS_STTS INTO :VAR_CURS_STTS, :VAR_NM_BASE_STTS, :VAR_NM_TABL_STTS, :VAR_NM_COLN_STTS
;

IF (SQLSTATE = '02000')
THEN
LEAVE L1
;
END IF
;

CALL DBC.SYSEXECSQL (VAR_CURS_STTS)
;
CALL DBC.SYSEXECSQL (
'

UPDATE STM_ADMN_PF1.O_ADM_STTS
SET DH_MSR_STTS = DBC.STATSV.LASTCOLLECTTIMESTAMP
WHERE 1 = 1
AND NM_BASE_STTS = DBC.STATSV.DATABASENAME
AND NM_TABL_STTS = DBC.STATSV.TABLENAME
AND NM_COLN_STTS = DBC.STATSV.COLUMNNAME
AND DBC.STATSV.DATABASENAME = ''' || VAR_NM_BASE_STTS ||'''
AND DBC.STATSV.TABLENAME = ''' || VAR_NM_TABL_STTS ||'''
AND DBC.STATSV.COLUMNNAME = ''' || VAR_NM_COLN_STTS ||'''
;

'
)
;

END LOOP L1
;

CLOSE CURS_STTS
;

END
;

SPL1007:E(L28), Unexpected text '

                                                              SELECT          'COLLECT STATISTICS USING NO SAMPLE COLUMN ('

' in place of cursor SELECT statement.

SPL1007:E(L28), Unexpected text '' in place of cursor SELECT statement.

SPL1007:E(L28), Unexpected text 'LOC_NM_BASE_STTS' in place of cursor SELECT statement.

SPL1007:E(L28), Unexpected text '' in place of cursor SELECT statement.

SPL1007:E(L29), Unexpected text ''

                                                            AND                O_ADM_STTS.NM_TABL_STTS' in place of cursor SELECT statement.

SPL1007:E(L29), Unexpected text '' in place of cursor SELECT statement.

SPL1007:E(L29), Unexpected text 'LOC_NM_TABL_STTS' in place of cursor SELECT statement.

SPL1007:E(L29), Unexpected text '' in place of cursor SELECT statement.

SPL1007:E(L34), Unexpected text ''

                                                            ORDER BY           TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS) ASC

' in place of cursor SELECT statement.

SPL1033:E(L41), Unexpected text ':'.

SPL1033:E(L42), Unexpected text ':'.

SPL1033:E(L48), Unexpected text ':'.

SPL1033:E(L48), Unexpected text ':'.

SPL1033:E(L48), Unexpected text ':'.

SPL1033:E(L49), Unexpected text ':'.

Junior Contributor

Re: Stored Procedure in Teradata

The cursor select is not dynamic, you need to remove the quotes:

                 ; DECLARE   CURS_STTS           CURSOR FOR

SELECT 'COLLECT STATISTICS USING NO SAMPLE COLUMN ('
|| TRIM(BOTH FROM O_ADM_STTS.NM_COLN_STTS)
|| ') ON '
|| TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS)
|| '.'
|| TRIM(BOTH FROM O_ADM_STTS.NM_TABL_STTS)
|| ' ;'
, TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS)
, TRIM(BOTH FROM O_ADM_STTS.NM_TABL_STTS)
, TRIM(BOTH FROM O_ADM_STTS.NM_COLN_STTS)
FROM STM_ADMN_PF1.O_ADM_STTS O_ADM_STTS
WHERE 1 = 1
-- SELECTION EXCLUSIVE DES STATISTIQUES A COLLECTER POUR LA STRUCTURE CONSIDEREE
AND O_ADM_STTS.NM_BASE_STTS = '' || LOC_NM_BASE_STTS || ''
AND O_ADM_STTS.NM_TABL_STTS = '' || LOC_NM_TABL_STTS || ''
ORDER BY TRIM(BOTH FROM O_ADM_STTS.NM_BASE_STTS) ASC
, TRIM(BOTH FROM O_ADM_STTS.NM_TABL_STTS) ASC
, TRIM(BOTH FROM O_ADM_STTS.NM_COLN_STTS) ASC
;

You might also have a look at this approach:

How to simplify your statistics collection with a queue table

dae
Enthusiast

Re: Stored Procedure in Teradata

Dieter,

thanks a lot for your answer ... but I am quite surprised to learn that the cursor can not be dynamic.

I understood that 4 types of Cursor are available - reading "SQL Stored and Embedded SQL" V14.10 - and among those ones:

(1) DECLARE CURSOR ( Request Form ) whose specification is : " DECLARE Cursor_Name CURSOR FOR 'Request_Specification' " where "Request_Specification" is a literal character string enclosed in apostrophes comprised of any number of SQL statements separated by semicolons.

This is the type of cursor I tried to construct and I think that form of cursor is allowing parameter.

Furthermore, the syntax for Request_Specification ( character string enclosed in apostrophes ) is, in that case, the same as in procedure DBC.SYSEXECSQL - there is an example in my script above -, and that one is allowing dynamic sql !

(2) DECLARE CURSOR ( Selection Form ) whose specification is compliant with your suggestion ( without apostrophes thus ) !!

Frankly, Dieter, I can not believe it would not be possible to use a cursor with parameters in the predicate ... on the other hand, I have never seen one ! ... never have looked for too ! :)