Declaring cursors in Informix 4gl

[aft5425]

In informix 4gl, when i declare a cursor (either declare with select or insert), does the cursor hold/consume memory that may impact system performance.

For example, i have a declare cursor statement with a select that should bring back over 1 million rows (each row with 20 columns that are at least 5 characters long).

Does the system hold these 1 million rows in memory until all rows are processed and the cursor is closed?

I have an informix 4gl program that has multiple Declare cursor with select statements and some of them result into large results sets.

Would it be better to use the PREPARE and FREE statements to declare and free my cursor?

Example of code if needed:
DECLARE field1_curs CURSOR FOR
SELECT
i_event, i_cust, i_field, i_frst_name, i_lst_name, i_dob, i_addr1, i_addr2, i_city, i_st, i_zip, i_zip4,
i_laxi, i_mem, i_title, i_phn1, i_phn2, i_type, i_reason, i_comments, i_nature, i_peep, i_contents
FROM sm_field
WHERE
i_event = pr_pack_rec.i_event AND
i_cust = pr_pack_rec.i_agnt
ORDER BY i_field

informix database version is 11.50.FC3
database is running on an IBM AIX unix server running the 5.3 operating system

 

[vpshriyan]

The data you fetch from disk is stored and controlled in server's limited shared memory. In single go entire data scope is not populated into shared memory but based on amount of free shared memory available, the online manages to store retrieved data.

It is a best coding practice to use PREPARE (optimization happens once) and FREE (releases unwanted memory handles) statements. This way an I4GL takes lesser memory foot print and thereby improves system performance