FINDSET(TRUE, FALSE) with COMMIT inside the loop

Eugene · 2007-07-09

I want to ask if the following code is ok

IF FINDSET(TRUE, FALSE) THEN
REPEAT
...
COMMIT
UNTIL NEXT = 0;

and if it still uses the SQL optimization advantage over standard

IF FIND('-') THEN

and in general - do the FINDSET command's parameters stay valid after COMMIT ?

stryk · 2007-07-09

Well, what FINDSET is doing, is to create a SQL cursor - client site - querying a SELECT TOP 500 * FROM ... (500 depends on the database setup).
With the parameters of FINDSET you could controll if the cursor is just used for reading or if the recordset is to be changed, thus you could optimize the cursor definition.

FINDSET(TRUE, FALSE) creates a cursor which is used for UPDATE of records.

A COMMIT has no influence on the cursor, as long as the property "CLOSE CURSOR ON COMMIT" is set to FALSE (default).
You should take care that the transactions are neither too long nor too short!
Hence, basically the loop design is OK, but it also depends on what is really happening there.
A COMMIT usually implicates that data was written previously. If you change a value which was a definition criteria for the cursor, the cursor gets invalid and will be recreated - decreasing performance ...

The "old" FIND('-') also generates a cursor, but without the advanced features of FINDSET, so you could not differ between a read-only cursor or an update-cursor.
The major difference is, that FIND('-') is loading the full recordset into the cursor, while FINDSET is loading "chunks" at 500 records each.
There could be specific queries, where it could be feasible to still use the FIND('-'), e.g. if the resultset calls 1.000.000 record it might be faster to have one huge cursor instead of fetching 2.000 "chunks" at 500.

Howdy, Stranger!

Quick Links

Categories

FINDSET(TRUE, FALSE) with COMMIT inside the loop

Comments