Re: CLOSE and FREE
I was interested in the performance impact of using close and free so I
ran the following test:
database testdb
main
define x integer
declare aa cursor for
select field1 from table1
for x = 1 to 1000
begin work
open aa
fetch aa
close aa
free aa
commit work
end for
end main
I then ran more tests taking out the free and close respectively, and
also taking out the begin/commit work and using prepare/declare instead
of declare. The results (in seconds) were:
declare declare prepare prepare
trans no trans trans no trans
open/fetch/close/free 92 71 101** 81**
open/fetch/close 91 72 52 32
open/fetch 90 22 51 21
** = Program crash because cursor details no longer exist.
Time shown is execution time when prepare/declare is placed
inside the for loop.
Observations:
1. It looks as if free only frees up prepared statements.
2. The use of free for non prepared cursors does not affect performance.
3. Prepared cursors are faster
4. Using close within a transaction does not affect performance.
Environment:
ICL DRS 6000 running 5.4.2
Online 6.00.UE1
4GL 4.10.UC1
Database - raw chunks, buffered logging, sync replication.
David W Chan
Objective Research Ltd
dwc@cix.compulink.co.uk