|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/13/2006 7:59:00 AM
Posts: 3,
Visits: 1
|
|
I have heard some people talking about the fact that usage of stored procedures is not recommended in Teradata.
Being new to Teradata i really dont know much about all this and i would like some guidance on this.
Is this true that stored procedures are not recommended in Teradata and if yes what is the reason for that?
Talal Ahmad
|
|
|
|
|
Supreme Being
Group: PAC and SFT Members
Last Login: Yesterday @ 4:20:22 PM
Posts: 327,
Visits: 504
|
|
It's really not Stored Procedures that are the issue, it's the "cursor processing" you want to minimize. Whether you use embedded SQL in an application program or a Stored Procedure, cursor logic is sequential - so you don't get the benefits of Teradata's massively parallel architecture.
Consider a simple case where you only want to process a small subset of the rows in a table. The SQL for your cursor could include a WHERE clause to filter out the other rows. Or the cursor could return all rows and the procedural code could check to see if the row should be processed or skipped. In other databases, using the WHERE clause should perform somewhat better; but in Teradata that difference can be huge (parallel processing versus serial).
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/12/2008 4:01:17 PM
Posts: 91,
Visits: 118
|
|
|
Another disadvantage is the inability of Teradata stored procedures to return a data-set. The typical approach to circumvent this limitation is to insert to a temporary table and then - while maintaining the connection - retrieve said temporary table. This is an issue when implementing stored procedures used in a web-page. I've heard that Teradata will correct this limitation in V2R7.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/9/2007 11:28:00 AM
Posts: 1,
Visits: 1
|
|
In debugging a procedure, how do I see the return value using Teradata sql assistant. I know you can print a value for debugging but I don't know how to create a procedure with print option enabled. I need to see the result of number_days.
call pqualtier.get_number_days(1060528,1060528,number_days)
create procedure pqualtier.get_number_days(IN pick_date INTEGER, IN deliv_date INTEGER, OUT number_days INTEGER)
begin
declare weekday_cnt, holiday_cnt INTEGER DEFAULT 0;
IF pick_date <= 0 OR deliv_date <= 0 THEN
set number_days = 0;
ELSEIF pick_date = deliv_date THEN
set number_days = 1;
ELSE
select count(*) INTO :weekday_cnt
from ltl.calendar_date_ref
where clndr_dt > :pick_date and clndr_dt <= :deliv_date and
day_of_wk_nbr between 2 and 6;
select count(*) INTO :holiday_cnt
from ltl.corporate_holiday
where clndr_dt > :pick_date and clndr_dt <= :deliv_date;
set number_days = weekday_cnt - holiday_cnt;
END IF;
end;
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 10/7/2008 6:20:30 PM
Posts: 136,
Visits: 11
|
|
Configure your ODBC connection to return the result set as
Start > run > odbcad32
This launches ODBC Adminstrator window
Select the connection setting (Your ODBC connection setting for accessing TD)
This will launch ODBC Driver Setup for Teradata RDBMS
Select Options - Launches teradata ODBC Driver Options
Check "Return Output Parameters As ResultSet"
Vinay
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/31/2006 5:46:00 AM
Posts: 5,
Visits: 1
|
|
I'm studying the SQL Reference: Stored Procedure and Embedded SQL Release V2R6.1. It mainly teach us how to write the stored procedure. Also, mention the stored procedure can provide Better Performance, Better Application Maintenance, Better Transaction Control.
So, I think Teradata suggest us to write the Stored Procedure.
|
|
|
|