Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 10 Nov 2003 @ 21:03:48 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Stored Procedures or Macros
 
From:   Ballinger, Carrie

Just to substantiate this point, we ran a test that executed 10,000 macros sequentially, and then the same SQL was placed in a stored procedure, and the SP was executed the same number of times. The SQL was a single row access by UPI, with each query using a different value.

Macro took 58 seconds to complete; the stored procedure took 100 seconds.

For simple SQL, where you don't need any of the special characteristics of stored procedures, macros will be a somewhat more efficient choice. And if you plan to return multiple rows in the answer set then macros become even a better choice. That test is a couple of years old (definitely pre-V2R5), but I would expect to see a similar pattern today.

Another consideration: If you plan to use query log (DBQL) in V2R5, currently the SQL from stored procedures is not logged, SQL from macros is logged. Stored procedure SQL will be logged starting in the V2R5.1 release.

Thanks, -Carrie



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023