Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Stored Procedures vs. SQL Scripts

Status
Not open for further replies.

CapsuleCorpJX

IS-IT--Management
Jun 23, 2004
70
US
Are Stored Procedures really better than SQL/KSH Scripts (using the BTEQ function)?

I've found some sites that say Stored Procedures aren't even precompiled.

That and since I have to pass in parameters for database names to a stored procedure, I have to use dynamic sql to perform dynamic selects. But is that even less efficient then scripts?

Is there any real advantage to use Stored Procedures over Scripts?
 
SPs are compiled by a c-compiler, but the SQL within a SP is optimized when it is sumitted to Teradata by the optimizer like any statement submitted by BTEQ.

If you use dynamic SQL within a SP there's no performance gain/loss compared to BTEQ scripts.
There's just less network traffic using SPs: BTEQ sends each statement across the network and waits for response.

And a SPs resides within the Teradata, so you can call the SP from any client without copying the shell script or using telnet...

Dieter
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top