Performance : Stored procedure Vs Bteq

Database
Enthusiast

Performance : Stored procedure Vs Bteq

What is the difference between executing any DML(INSERT or DELETE) within stored procedure and outside of stored procedure?

Will there be any performance difference? If yes then why?

Tags (1)
1 REPLY
Teradata Employee

Re: Performance : Stored procedure Vs Bteq

You can test this yourself with DBQL enabled and compare the captured DBQL data for the two cases.

I would not expect much difference in performance for single-row INSERT statements with literal bind values, or for DELETE statements, based on whether they are executed in a stored procedure versus executed from a client application.

If a stored procedure is executing many SQL statements, meaning dozens of SQL statements, then you might see a performance benefit due to fewer communication round-trips between the client application and the Teradata Database.

Generally speaking, however, improved performance is typically not one of the goals for locating your SQL commands in stored procedures. Centralized control and reuse of common SQL statements across multiple applications are the more typical reasons to use stored procedures.