Re: Prepared Stmts Or Sp

> > I need to optimize some applications already done using ESQL/COBOL > and SE 5.0. Most of these applications have a big loop (executed > the whole day) with a lot of SQL statements inside. The systems > we are using (MX 300 Siemens) have a small amount of memory and > suffer of memory contention problems. > From a performance standpoint are Stored Precedures better than > Prepared SQL Statements? > > Es. > > Program 1. > . > . > stmt1 = "update tab_name set col1 = ?" > prepare quid from stmt1 > > while true > var1 = host-var + 1 > execute quid using var1 > . > . > > Program 2 > . > . > create procedure proc1(param int) > update tab_name set col1 = param;> end procedure > . > . > while true > var1 = host-var + 1 > execute procedure proc1(var1) > . > . > > > From a performance standpoint;> > - which one will requiere less memory? > - which one will be faster? > - which one will cause less pippe traffic between the front-end and > the back-end? The first will be faster because calling a stored procedure has its own parsing overhead. Executing a prepared query has no parsing overhead. Pipe traffic will be the same in each case, ie once per EXECUTE. If you could get the whole of the WHILE loop inside a stored procedure that you just call once, then that would be quickest of all: no parsing, and only one set of pipe traffic. Since I assume that var1 changes for each call, the best way around this would be to save all the var1's in a temp table which you then read inside the SP. > - which one will cause less disk access? Neither - apart from parsing, the database access is identical. akent@cix.compulink.co.uk (Andy Kent) -------------------------------------