Sys(3054)

This cool function, introduced in VFP 5, makes it easier to tell whether your queries are as fast as they could be. It displays optimization information (known as “SQL ShowPlan”) in the main VFP window. VFP 7 enhanced this function to make it even easier to get the information you need.

Usage

cSetting = SYS( 3054 [, nSetting [, cOutputVar ] ] )

Have you ever had a query that was much slower than you expected it to be and you couldn’t figure out what was slowing it down? We sure have. SYS(3054) is the ticket to solving these problems. When you turn it on and run a query, VFP shows you exactly how it’s optimizing the query. The feedback tells you which tags it’s using and how well things are optimized. For joins, it tells you which tag is being used to optimize the join.

At first glance, it seems odd that there are so many settings for this function. Actually, it seems that way on second glance, too. Usually, when we work with SYS(3054), we want to know about both joins and filters. However, the designers of VFP thought you might want filter information only. In fact, the parameter 11 for filter and join information isn’t documented in VFP 5. For some reason, there’s no way to get join information only.

VFP 7 acknowledged that the information supplied doesn’t do you much good unless you know what query it applies to, and provided new settings for including the query itself in the results.

In some cases, with nSetting = 2 or nSetting = 12, the output cuts off the query arbitrarily. The optimization information is always complete, though.

You can use SYS(3054) on views as well as queries, but passing 2 or 12 for nSetting doesn't include the query itself in that case.

When you pass 2 or 12 to get the query itself included, the query does not retain its original formatting.

In VFP 5, issuing SYS(3054) always echoes some feedback to the active window (“SQL ShowPlan is enabled” for SYS(3054,1)). In VFP 6, that feedback is gone; instead, the function returns the value you pass it.

Once you turn on SQL ShowPlan, each time you execute a query, optimization information is collected. In VFP 6 and earlier, the information always appears in the active window. In VFP 7, you can provide a variable to hold the information rather than displaying it, if you choose.

You pass the variable name to SYS(3054), not the variable itself. That's because VFP doesn't have a mechanism for us to pass variables to built-in functions by reference, and the function needs to change the specified variable, not use its value.

There's no way to find out what variable you've set ShowPlan to.

Passing a variable name to SYS(3054) is good for only one query. That is, if you issue a call like SYS(3054,12,"cMyVar"), then run two queries, cMyVar contains information only about the first query. Microsoft says this is by design, but we think it's a really stupid design. The major reason we want to store this information in a variable is to run a whole process and check all the optimization. If you need to check on multiple queries, storing the results for each, you have to issue SYS(3054) before each one and either use separate variables or store the results from the variable before the next query.

For each table in the query, you get a report of whether it’s fully, partially, or not optimized, as well as the name of the tag or tags used to optimize it. Be aware that a table that’s not filtered at all shows an optimization level of “none.”

For each join, there’s a line indicating what tag is used to optimize the join. In some cases, VFP decides to build a temporary tag and use that—when it does, it tells you. Join information appears in the order in which the joins are physically performed, which often doesn’t correspond to the logical order in the query. See “Faster Than a Speeding Bullet” in “Franz and Other Lists” for information about speeding up your queries and using SYS(3054) to figure out what’s wrong.

Example

?SYS(3054,11)   && Echoes 11 in VFP 6 and later
* Using TasTrade data
SELECT * ;
   FROM customer JOIN orders ;
     ON customer.customer_id = orders.customer_id ;
   WHERE UPPER(company_name) = "C" ;
   INTO CURSOR test
?SYS(3054,0)

SYS(3054,2,"cResult") && Filters, including query – use var
SELECT * ;
   FROM customer JOIN orders ;
     ON customer.customer_id = orders.customer_id ;
   WHERE UPPER(company_name) = "C" ;
   INTO CURSOR test
SYS(3054,0)
MESSAGEBOX(cResult)

See Also

Select-SQL, Use