Improving implementation of procedures and functions

 

These coding techniques help reduce the processing time of a function or procedure.

The following tips are especially important for functions because a function tends to be called multiple times from many different procedures:

• Use the NOT FENCED option so UDFs run in the same thread as the caller.

• Use the DETERMINISTIC option on procedures and UDFs that return the same results for identical inputs. This allows the optimizer to cache the results of a function call or order where the function is called in the execution stream to reduce the run time.

• Use the NO EXTERNAL ACTION option on UDFs that do not take an action outside the scope of the function. An example of an external action is a function that initiates a different process to fulfill a transaction request.

Coding techniques used for the SQL routine body can have a major impact on the runtime performance of the generated C program. By writing your routine to allow greater use of C code for assignments and comparisons, the overhead of an equivalent SQL statement is avoided. The following tips should help your routine generate more C code and fewer SQL statements.

• Declare host variables as NOT NULL when possible. This saves the generated code from having to check and set the null value flags. Do not automatically set all variables to NOT NULL. When you specify NOT NULL, also give a default value. If a variable is always used in the routine, a default value might help. However, if a variable is not always used, having a default value set may cause additional initialization overhead that is not needed. A default value is best for numeric values, where an additional database call to process the assignment of the default value is not needed.

• Avoid character and date data types when possible. An example of this is a variable used as a flag with a value of 0, 1, 2, or 3. If this value is declared as a single character variable instead of an integer, it causes calls to the database engine that can be avoided.

• Use integer instead of decimal with zero scale, especially when the variable is used as a counter.

• Do not use temporary variables. Look at the following example:

  IF M_days<=30 THEN 	SET I = M_days-7;
  	SET J = 23
  	RETURN decimal(M_week_1 + ((M_month_1 - M_week_1)*I)/J,16,7);
  END IF

  This example can be rewritten without the temporary variables:

  IF M_days<=30 THEN 	Return decimal(M-week_1 + ((M_month_1 - M_week_1)* (M_days-7))/23,16,7);
  END IF

• Combine sequences of complex SET statements into one statement. This applies to statements where C code only cannot be generated because of CCSIDS or data types.

  SET var1 = function1(var2);
  SET var2 = function2();

  Can be rewritten into one statement:

  SET var1 = function1(var2), var2 = function2();

• Use IF () ELSE IF () ... ELSE ... constructs instead of IF (x AND y) to avoid unnecessary comparisons.

• Do as much in SELECT statements as possible:

  SELECT A INTO Y FROM B;
  	SET Y=Y||'X';

  Rewrite this example:

  SELECT A || 'X' INTO Y FROM B

• Avoid doing character or date comparisons inside of loops when not necessary. In some cases the loop can be rewritten to move a comparison to precede the loop and have the comparison set an integer variable that is used within the loop. This causes the complex expression to be evaluated only one time. An integer comparison within the loop is more efficient since it can be done with generated C code.

• Avoid setting variables that might not be used. For example, if a variable is set outside of the an IF statement, be sure that the variable will actually be used in all instances of the IF statement. If not, then set the variable only in the portion of the IF statement that is it actually used.

• Replace sections of code with a single SELECT statement when possible. Look at the following code snippet:

  SET vnb_decimal = 4;
  cdecimal: 			FOR vdec AS cdec CURSOR FOR 
  			SELECT nb_decimal 			FROM K$FX_RULES 			WHERE first_currency=Pi_curl AND second_currency=P1_cur2
  			DO 				SET vnb_decimal=SMALLINT(cdecimal.nb_decimal);
  END FOR cdecimal;
  
  	IF vnb_decimal IS NULL THEN 		SET vnb_decimal=4;
  	END IF;
  	SET vrate=ROUND(vrate1/vrate2,vnb_decimal);
  	RETURN vrate;

  This code snippet can be more efficient if rewritten in the following way:

  RETURN( SELECT 			CASE 				WHEN MIN(nb_decimal) IS NULL THEN ROUND(Vrate1/Vrate2,4)
  			ELSE ROUND(Vrate1/Vrate2,SMALLINT(MIN(nb_decimal)))
  			END 			FROM K$FX_RULES 			WHERE first_currency=Pi_curl AND second_currency=Pi_cur2);

• C code can only be used for assignments and comparisons of character data if the CCSIDs of both operands are the same, if one of the CCSIDs is 65535, if the CCSID is not UTF8, and if truncation of character data is not possible. If the CCSID of the variable is not specified, the CCSID is not determined until the procedure is called. In this case, code must be generated to determine and compare the CCSID at runtime. If an alternate collating sequence is specified or if *JOBRUN is specified, C code cannot be generated for character comparisons.

• Use the same data type, length and scale for numeric variables that are used together in assignments. C code can only be generated if truncation is not possible.

  DECLARE v1, v2 INT;
  SET v1 = 100;
  SET v1 =  v2;

 

Parent topic:

Improving performance of procedures and functions