Stored Procedures and I/O

Database
Enthusiast

Stored Procedures and I/O

Hello

I have a stored procedure in place that summarises data which can be found in other reports.

How it works:

I have a table in which is stored which data needs to be picked from which report

In the procedure I use a cursor to loop through that table.

Each row in this table will be used to generate a SQL-statement which will be executed within the procedure.

example

name        | input table  | period_column | input_column  | operator

-------------------------------------------------------------------------------------

tot_sales  | Salestable  | year_month      | Revenues       | sum 

The procedure will create the following sql

insert into <<outputtable defind in procedure-call>>
select
year_month
, tot_sales
, sum(revenues)
from Salestable A
group by 1,2

then the stored procedure will execute the generated SQL

CALL D0_BUS_EUDS_TECV.TECEXECSQL(<<above generated SQL>>);

Everything works fine, except for one thing: Our IT-department is complaining about CPU and IO-usage.

Indeed, this is generating loads of CPU-usage and IO-usage

Looking at the logs they are right, but when I take all generated SQL-statements and launch them in batch the generated IO is only 5% or the IO generated by the procedure, same for CPU-usage

(I got the logs from teraqueue.history - don't know if this is a system-table or not)

Is there anyone who knows what I can do to reduce the IO ?

We're running on Teradata 14

Tanks in advance 

Youri

7 REPLIES
Enthusiast

Re: Stored Procedures and I/O

I noticed that when one of the generated SQL's fails on exection no data at all is inserted in the target table.

It's kind of "all or nothing"

Can this have something to do with the increased IO ?

Senior Apprentice

Re: Stored Procedures and I/O

Can you share a bit more details?

Is it the same target table for all queries?

Do you run that SP in ANSI or Teradata mode?

Are there any Secondary/Join Indesex?

Do you also DELETE data?

Enthusiast

Re: Stored Procedures and I/O

Hi, I am new to stored procedures. I have understood the concept of stored procedure. But I am not able to understand that where to code the stored procedure. If I code it in SQL assistance it is giving me syntax error in creates statement itself. Same is the case with BTEQ utility. Please help me out. Thanks in advance

Teradata Employee

Re: Stored Procedures and I/O

You can code it in SQL Assistant just fine .... for the correct syntax you can have a look at my last post on following thread:

http://forums.teradata.com/forum/database/date-as-integer#comment-130052

Teradata Employee

Re: Stored Procedures and I/O

It is a good idea to post your code that is causing an error .... along with the error you are getting for a quick fix for your help.

Enthusiast

Re: Stored Procedures and I/O

sorry for the delay dieter, I added mail-alerts on my thread and - as I didn't get any mails - tought no response had been posted yet.

Hereby the (slightly cleaned-out) stored procedure

/*
CREATED BY : Youri Donders
PRM_PERIOD_YEAR
PRM_PERIOD_MONTH
PRM_TABLE_OUT

Conditions
- All columns (except for filter and sub_period) must contain data
- Output-table must exist before the script is executed
- format of the output-table: main_period, sub_period, KPI_RULE, KPI_Value

*/

Replace Procedure P0_BUS_EUDS_TECV.PRO_BUS_KPI_SUMMARY (PRM_KPI_GROUP VARCHAR(50), PRM_MAIN_PERIOD INTEGER, PRM_SUB_PERIOD INTEGER, PRM_TBL_OUT VARCHAR(60))
Main_proc:
Begin

Declare vcscript varchar(60000);

Declare chkColumn varchar(60);
Declare chkTable varchar(60);
Declare pTableOut varchar(60);
Declare pKPI_Group varchar(500);

/***************************************************************************/
/*********************** Calculation of the KPI ****************************/
/***************************************************************************/

set vcscript = '';
set chkColumn = '';

set pKPI_Group = ',' || PRM_KPI_GROUP || ',';

/* Looping though all KPI's defined in P0_BUS_EUDS_TEC.DSC_PMLT_R_KPI_RULES*/
For acc_cur As cursor1 Cursor For
sel a.*
from P0_BUS_EUDS_TEC.DSC_PMLT_R_KPI_RULES A
where a.KPI_NAME is not null
and a.input_table is not null
and a.kpi_variable is not null
and ( a.kpi_operation is not null or a.kpi_variable like '%.%' )
and a.column_main_period is not null
and :pKPI_Group like '%' || ',' || trim(a.kpi_group) || ',' || '%'
and a.kpi_active = 'Y'
Do
/* building insert-command using data found in the DSC-table */
set vcscript = 'insert into ' || PRM_TBL_OUT || '
(
MAIN_PERIOD
, SUB_PERIOD
, KPI_RULE
, KPI_VALUE
)
select
' || PRM_MAIN_PERIOD || ' as SPROC_MAIN_PERIOD
, ';
if PRM_SUB_PERIOD is null
then set vcscript = vcscript || ' null as SPROC_SUB_PERIOD
';
else set vcscript = vcscript || PRM_SUB_PERIOD || ' as SPROC_SUB_PERIOD
';
end if;
set vcscript = vcscript || ', ' || acc_cur.kpi_rule || ' as SPROC_KPI_RULE
, ';
set vcscript = vcscript || trim(coalesce(acc_cur.kpi_operation,'')) || '(' || trim(acc_cur.kpi_variable) || ') as SPROC_KPI_VALUE
';

set vcscript = vcscript || ' from ' || trim(acc_cur.Input_Table) || ' A
where ';

if PRM_SUB_PERIOD is null
then set vcscript = vcscript || 'a.' || acc_cur.column_main_period || ' = ' || PRM_MAIN_PERIOD || '
';
else
/* main period is mandatory, sub period isn't */
if acc_cur.column_sub_period is null
then set vcscript = vcscript || 'a.' || acc_cur.column_main_period || ' = ((' || PRM_MAIN_PERIOD || ' * 100) + ' || PRM_SUB_PERIOD || ')
';
else set vcscript = vcscript || 'a.' || acc_cur.column_main_period || ' = ' || PRM_MAIN_PERIOD || '
and a.' || acc_cur.column_sub_period || ' = ' || PRM_SUB_PERIOD || '
' ;
end if;
end if;

/* adding filter condition */
if acc_cur.kpi_filter is not null
then
set vcscript = vcscript || ' and ' || acc_cur.kpi_filter || '
';
end if;
set vcscript = vcscript || ' group by SPROC_MAIN_PERIOD
, SPROC_SUB_PERIOD
, SPROC_KPI_RULE
';
if acc_cur.kpi_operation is null
then set vcscript = vcscript || ' , sproc_kpi_value
';
end if;
set vcscript = vcscript || ';';

CALL P0_BUS_EUDS_TECV.TECEXECSQL(:vcScript);

INSERT INTO P0_BUS_EUDS_TEC.FACT_TEC_S_SQL_LOGS VALUES (CURRENT_TIMESTAMP(2),SESSION,USER , 'PRO_BUS_KPI_SUMMARY',:vcScript,:SQLCODE);

end for;

End;

In a small resume:

Theres 1 output-table (which is created before the procudure is started)

In most cases the table contains only 4 columns: main_period, sub_period, kpi_rule and kpi_value with the first 3 in a unique primary index

in the procedure I use a cursor to loop though another table which contains all necessary info for the SQL I need to generate. (1 line per KPI_Rule --> 1 SQL to generate)

After creation of the SQL in a local variable it is executed.

I have the impression that the total IO is finally the IO of statement 1 + the IO of statement 1+2 + the io of statement 1+2+3 etc

Recently I added a lot of "rules" - creating a lot more loops in the cursor - and the IO is now sky-high (and the It-department)

I logged all executed SQL-statements and executed these in a seperate job, but that IO-data is not yet available to me.

I'll post IO and CPU-data tomorrow

Scripts are launched in BTEQ-mode

No deletes are done within the stored procedure.

I hope you're still looking at this topic

regards

Youri

Enthusiast

Re: Stored Procedures and I/O

As promised the IO and CPU data

I get the numbers from teraqueue.history

Stored Procedure

IO: 7.253.180.525

CPU: 1.081.842

Logged SQL in a seperate script

IO: 64.015.848

CPU: 8.957

--> IO and CPU consumed of SQL executed in batch is only 1% of the measures taken after the stored procedure