All Forums General
AMIT_TERSE 17 posts Joined 08/13
30 Dec 2014
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!!

Regards, Amit Terse
Glass 225 posts Joined 04/10
30 Dec 2014

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

AMIT_TERSE 17 posts Joined 08/13
30 Dec 2014

Thanks for the comment RGlass!!
Is using stored Proc recommendable in terms of resource consumption and performance or not. If not then why?

Regards,
Amit Terse

dnoeth 4628 posts Joined 11/04
30 Dec 2014

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.

Dieter

Raja_KT 1246 posts Joined 07/09
31 Dec 2014

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.

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.

AMIT_TERSE 17 posts Joined 08/13
01 Jan 2015

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.
 

Regards,
Amit Terse

Raja_KT 1246 posts Joined 07/09
02 Jan 2015

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 :)

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.

dae 13 posts Joined 07/12
23 Jan 2015

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 ':'.
 

dnoeth 4628 posts Joined 11/04
23 Jan 2015

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

Dieter

dae 13 posts Joined 07/12
23 Jan 2015

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 ! :) 
 

dnoeth 4628 posts Joined 11/04
24 Jan 2015

Caution: The SP manual mixes syntax for Embedded SQL and SPs, you should skip over the Embedded SQL parts.
 
You don't need Dynamic SQL to pass parameters to this cursor, only for dynamic object names.
The syntax for dynamic cursors would be a bit different:

                 ; DECLARE   SQLstmt             VARCHAR(2000)
                 ; DECLARE   CURS_STTS           CURSOR FOR c
                 ; SET SQLstmt = 
                         '
                           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
                           ;
                         '
                 ;         
                 PREPARE CURS_STTS FROM SqlStmt;

                   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  

Btw, remove the colons in front of the parameters (only needed with DML statements).
 
The UPDATE doesn't need Dynamic SQL, too:

  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                

 

Dieter

dae 13 posts Joined 07/12
24 Jan 2015

Dieter,
thanks again for your help and support !!! 

insac 3 posts Joined 10/11
14 Aug 2015

Going back to the initial statement: "single amp operation"
Maybe there was a misunderstanding: could it be that they were warning you that the stored procedure was stored in a single AMP?
(search: "Where are stored procedures stored in this site")
BR,
Fabio

You must sign in to leave a comment.