This issue's featured tip offers much more than its title suggests. On its most superficial level, it presents some interesting facts about SQL Views. Mike's code contrasts two methodologies for achieving a single objective: returning an empty result set. In doing so, he demonstrates some not-so-routine commands to manipulate the View's environment to get the results he's looking for.
In addition, the tip shows a progression in thinking from a concrete, single-use solution, to a more abstract approach that can be applied generally to a variety of cases. Although this has long been the mark of good programming, it's all the more important in an object-oriented environment.
Finally, Mike's code is a clear and simple example of benchmarking. He shows how to quantify performance, and in doing so, provides the basis for evaluating the merits of a technique. As developers, after we get an application running, it's time to turn our attention to fine-tuning its performance. So, this is something we all need to know how to do and remember to use.
-- Ceil Silver, Contributing Editor, FoxPro Advisor
ZAP to Clear a View
I have a form in which the user can enter some criteria and click on a Search button to requery a local view. The results display in a grid. There's also a Clear button the user can click on to clear the criteria and the grid before doing a new search. To date, I've been setting any view parameters to dummy values that yield no results, then issuing a requery to clear the view. This technique, however, requires knowledge of the view parameter names, data types, and valid values, making it difficult to abstract into generic code for a class.
My new solution (which applies to Visual FoxPro 7.0/6.0/5.0/3.0) clears the view with the ZAP command. The new strategy uses SET SAFETY to prevent VFP from issuing a warning. It also requires setting and resetting the cursor's SendUpdates and Buffering properties. ZAP won't work on a cursor in table buffering mode, and we certainly don't want to send these delete updates to the underlying table.
Actual production code assumes the search can't be performed until any pending changes have been committed or rolled back, but this example doesn't address concerns about dirty buffers:
* Determine if ZAP or REQUERY() is faster
LOCAL lcComp_PK, lnStart, lnI
USE v_Companies NODATA
* Get all records
lcComp_PK = ""
=REQUERY()
lnStart = SECONDS()
FOR lnI = 1 TO 1000
* Clear the view by setting its
* parameter to an "impossible" value
lcComp_PK = "!!!!!"
=REQUERY()
NEXT lnI
? SECONDS() - lnStart
* Get all records again
lcComp_PK = ""
=REQUERY()
lnStart = SECONDS()
FOR lnI = 1 TO 1000
* Clear the view using ZAP
=CURSORSETPROP("Buffering", 3)
=CURSORSETPROP("SendUpdates",.F.)
SET SAFETY OFF
ZAP
SET SAFETY ON
=CURSORSETPROP("SendUpdates",.T.)
=CURSORSETPROP("Buffering", 5)
NEXT lnI
? SECONDS() - lnStart
Running this code on my system took between 4.845 and 4.909 seconds for the 1,000 REQUERY()s, and between .020 and .021 for the 1,000 ZAPs. I also tested it by rearranging the methodologies, and by quitting and relaunching VFP between trials with each strategy -- all with similar results. -- Mike Yearwood, Toronto, Ontario, Canada
Typically, when changing an entity's state or environment, I advocate saving the original value to restore at the end of processing. In the second half of Mike's example, he needs to ensure the View is in row buffering mode for the benefit of the ZAP command. I applaud his decision to simply set it back to table buffering afterwards, especially as this is a situation where the data is presented in a grid. Row buffering has no place in a grid, since row buffered changes are automatically committed whenever the record pointer moves. This could lead to some unexpected and highly undesirable conditions when a user moves from one record to another while editing.-- Ceil