Once upon a time, xBase wasn't a programming language, it was a tool to automatically retrieve and manipulate data. Users of xBase tools weren't primarily developers; they were experts in a huge variety of different areas that used FoxBase, dBase, and similar tools to manage their data. xBase was steadily improved and finally developed into a full-blown programming language. FoxPro became a professional development environment which reached its heights with FoxPro 2.5/2.6. Almost ten years ago in 1995 the paradigm of the tool changed again. A procedural programming language became an object oriented tool that continues to develop into a component based design.

Visual FoxPro is not just an object oriented environment like Delphi or Visual Basic.NET. Visual FoxPro still contains its roots. Just try to run a Turbo Pascal 3.0 program in Delphi 7.0. What about your GW-BASIC programs in Visual Basic.NET? But Foxbase? Till today you can run code unchanged in Visual FoxPro that you have written in the eighties. Screen output doesn't look as nice, but you can still run code in VFP 8 almost 20 years after you have written it.

Visual FoxPro is almost fully backward compatible. Thinking about it this means that a good deal of the code in FoxPro and FoxBase is still part of Visual FoxPro. This means that object orientation sits on top of FoxPro, and not vice versa. Many strange behaviors of Visual FoxPro only become explainable if you think how you would have done something in FoxBase, only to realize that Visual FoxPro doesn't do it any different.

One warning in advance: The following articles tries to describe how Visual FoxPro works internally. The actual internals of FoxPro are the intellectual property of Microsoft and are not publicly disclosed. Everyone who really knows how Visual FoxPro works inside is prohibited to talk about this by signing a Non-Disclosure Agreement (NDA). I've collected the following information from a variety of public sources. Some information are in the MSDN Library that Microsoft publishes quarterly (some articles only exist in older versions of MSDN Library). Other information come from sample code that Microsoft ships. Most pieces, however, come from tests and observation, not only from me, but many, many developers in various forums. Especially differences in the behavior of various versions allow to make conclusions on the internal structure of VFP. Some of the following structures have been extended in the latest version of FoxPro.

The Name Table Index (NTI)

In Visual FoxPro we can name various items. To those items, Visual FoxPro assigns something called a name. These items are variables (not properties), array names, procedures, functions, aliases of tables, field names and objects (not classes). Each of these elements has got a label and a scope. The visibility (scope) of a variable, for instance, depends on its type, whereas the scope of an alias is the data session. A field name must be unique in a table and procedures are scoped to a program file.

Whenever you create a variable, open a table, and so on, Visual FoxPro creates a new entry in an internal list, the Name Table. The position inside this list is the Name Table Index – or NTI for short. In this list, every name is assigned a unique number between 1 and 65,000, because it is maintained as a 16-bit value. There's just a single global list. This is why you can only create up to 65,000 variables. Since aliases and object names (till Visual FoxPro 6.0) are also included in this list, the actual number of variables is reduced by the number of instantiated objects and allocated work areas.

The management of this list has been optimized in the various versions of FoxPro. When you release a name by closing a table or because you don't need a variable anymore, Visual FoxPro doesn't remove the entry immediately. It only marks the entry as invalid, just like it does with deleted records.

Items are finally removed by a process called Garbage Collection. This term refers to the process of removing invalid entries in lists, free out-dated cache entries, compact memory by moving memory blocks around, checking access to temporary files, and so forth. Visual FoxPro executes the garbage collection in its idle loop. This loop is entered whenever Visual FoxPro is in a waiting condition caused by READ, READ EVENTS, INKEY(), or WAIT WINDOW. This is still true if you use the command with the options not to wait at all. You can use this trick to force Visual FoxPro to clean up memory by issuing a WAIT WINDOW "" NOWAIT.

It wasn't until Visual FoxPro 7.0 that SYS(1104) made it into the documentation even though the function is available since FoxPro 2.6, at least. SYS(1104) manually triggers a garbage collection. It's not the same thing as the idle loop, though, as in the idle loop Visual FoxPro does more than executing the garbage collection like additionally processing Windows messages. During program execution, Visual FoxPro is not in an idle loop and therefore doesn't perform a garbage collection. Until Visual FoxPro 5.0 this had the effect that more and more entries in the name table have been marked as being invalid, but have not been released.

This had far reaching consequences on performance, but also on stability. Every time a new entry is added to the name table, Visual FoxPro has to search all existing entries to find conflicting names. For variables this means to check if a new variable with a lower scope already exist, because you can't, for instance,  create a PUBLIC variable when a LOCAL variable of the same name exist. For aliases this means to verify that the alias name is not in use in the current data session. This search process caused exponential performance degradation. While you could measure the creation of the first object in milliseconds or even nanoseconds, it took Visual FoxPro already minutes to create the 60,000th object.

Application that never reach an idle state like import routines or service providers became slower the longer they ran. Some functions, too, demanded a new entry in the name table, like the REQUERY function when reloading a view. The slow down wasn't the only problem. The closer an application came to the limit, the more instable it became. If you were lucky, Visual FoxPro issued a "too many names" error, but usually it simply crashed.

Visual FoxPro 6.0 significantly improved this behavior. When the number of items in the name table approaches the 95% limit, Visual FoxPro automatically initiates a garbage collection. In a program that creates 65,000 objects you notice this as a longer break when creating that object.

A major improvement came in Visual FoxPro 7.0. All previous versions counted objects. When you create two labels

LOCAL loLabel1, loLabel2

loLabel1 = CreateObject("Label")

loLabel2 = CreateObject("Label")

Visual FoxPro adjusts the name property. The first label is named "Label1", for the second label Label2.Name results in "Label2". To make this happen, Visual FoxPro registered each object in the name table. The consequence is that each object creation takes longer than the previous one. You can bypass this to a certain degree by assigning a unique name like SYS(2015) in the Init event. Nonetheless, this behavior limits the number of objects that can be instantiated to 65,000. In Visual FoxPro 3.0 through 6.0 you can't create more than 65,000 objects including all objects that are on forms. When that limit has been reached, you can't even open the Form Designer as it also tries to create objects.

Visual FoxPro 7.0 and 8.0 do neither search a unique name when you create objects with CREATEOBJECT() or NEWOBJECT(), nor do they register objects in the name table. Therefore you can create far more than 65,000 objects… and even faster than in Visual FoxPro 6.0, too. The disadvantage is that object names are not unique anymore. A small price to pay.

Variables, Arrays and Objects

One of the most frequently asked questions that developers ask who have just learned that Visual FoxPro can only manage up to 65,000 variables is if that applies to array elements, too. The answer is a definite NO. One array counts as a single variable, no matter how many elements it contains. Because the number of elements is limited to 65,000, too, you can create 65,000 arrays with 65,000 elements each, at max. Even in Visual FoxPro you can stores masses of data in memory. Due to performance reasons no real application will come even close to these limits, though. The reason for all of these limitations is the name table, once again. Actually, Visual FoxPro only knows to deal, with variables – simple variables, that is. The support of arrays, and later objects, has been fitted into this design.

But what is a variable in Visual FoxPro? Variables in FoxPro can store values of any data type. However, as Visual FoxPro is written in C itself, FoxPro variables, as well, have to be stored in a C format at some point. Visual FoxPro stores variables in a C structure. Even though the following structure comes from the Library Construction Kit, it's probably close to what Visual FoxPro uses internally, if not even identical:

typedef struct {

  char        ev_type;

  char        ev_padding;

  short        ev_width;

  unsigned    ev_length;

  long        ev_long;

  double      ev_real;

  CCY         ev_currency;

  MHandle      ev_handle;

  unsigned long ev_object;

} Value;

In addition to the type, there's an entry for each supported data type. Visual FoxPro uses different fields depending on which data type should be stored. The structure explains why strings can contain any characters (because their length is stored separately) and how Visual FoxPro deals with the number of decimal places in floating point values to allow for differences between 1.0 and 1.00. The actual string and objects are not stored directly in this structure as their size can vary widely. To access strings, Visual FoxPro uses a so called memory handle which will be explained in just a moment. Objects are identified using a not closer documented 32-bit number. It might surprise that arrays are not mentioned at all.

Every time a program creates a new memory variable, Visual FoxPro allocates a memory block with the above structure. Its address has to be stored somewhere just like the name, which is not part of the structure above. Saving these information is the job of the name table. Every entry in the name table contains besides name and scope, details on the type of the entry (variable, field, alias, etc.) and its location in memory. The list is limited to 65,000 entries, hence, the maximum limit of variables.

An array cannot be stored in a single structure, though. Each array element is stored in a structure like a single variable. This is the only way to be able to store items of various data types in a single array. In the name table Visual FoxPro create a single entry for the entire array. The following sample sets all elements in the array to NULL:

LOCAL laArray[3]

laArray = .NULL.

This single entry is the one used to pass an array by reference. The actual array, on the other hand, is a simple list table that contains pointers to all individual elements. The array entry in the actual name table contains a pointer to this list. There's obviously no way to directly access an array element like you can access a variable. When passing the name table index (NTI) which Visual FoxPro uses to uniquely identify an entry in the name table to a function that returns an element from the name table, this function could only return the entry for the entire array. Another function receives this entry and returns a particular element.

Objects are stored in a similar fashion. The value in the structure is the address of another name table. For each object Visual FoxPro seems to create a new complete name table. Probably that path had been chosen as the name table already manages name and scope. Properties in Visual FoxPro are actually variables that are kept in a hidden place. More surprisingly, this is true for methods, too, which are also variables. For this reason you can't create a class that has more than 65,000 methods, properties, and events.

This design has a lot of advantages as otherwise arrays and properties would quickly eat up the available name table space. The most obvious disadvantage, though, is passing parameters by reference using the @-character. In this case, Visual FoxPro doesn't pass a copy of the value as it does normally, but merely the NTI, the name table index. The calling function is kindly asked to put the result into variable number x. An array, though, only has a single such name table index. There's no possibility to also specify in which element the result should be written. Consequently, you can only pass an entire array by reference. In Visual FoxPro it's impossible to pass a single array element by reference. You have to copy the element into a variable, pass a reference to that variable and update the value in the array.

The same applies to object properties. In theory, you can't pass a property by reference, because it's an integral part of the object. Passing a property by reference, hence, would break encapsulation. The real answer, though, is that a property doesn't have a dedicated entry in the name table and passing by reference is therefore impossible. Especially affected by this design are array properties. Neither arrays, nor properties can be passed by reference. You therefore have no other possibility than to copy the array into a local variable, pass that instead, and copy the entire array back using ACOPY() for both ways. Combined with assign and access methods this method does have even more disadvantage than just reduce execution speed. Fortunately, Visual FoxPro 8 added collections which look much like an array, but can easily passed around.

The access to variables has been optimized by Visual FoxPro. Already when compiling a program Visual FoxPro translates names into easy to handle integer values. The lines:

LOCAL lcName, lcZIP

? lcName

Are converted into the following pseudo code during compilation:

LOCAL #1, #2

? #1

In the resulting FXP file all variables are listed at the end of the file. Inside the compiled code, 16 bit values indicating the position in the list are used instead.

0x00000000 : FE F2 FF 20 02 01 00 00 00 B0 00 00 00 8F 00 00    ώς .....°.....

0x00000010 : 00 21 00 00 00 00 00 00 00 00 00 00 00 00 00 00    .!..............

0x00000020 : 00 00 00 00 00 00 00 94 11 00 00 00 00 25 00 00    .......”.....%..

0x00000030 : 00 00 00 00 00 00 00 00 00 56 00 00 00 03 00 00    .........V......

0x00000040 : 00 50 00 00 00 C2 8B 56 2B 11 00 00 00 FC 18 00    .P...Β‹V+....ό..

0x00000050 : 0B 00 AE F7 00 00 07 F7 01 00 FE 0A 00 02 F8 03    ..χ...χ..ώ...ψ.

0x00000060 : 01 F7 00 00 FE 03 00 55 02 00 06 00 4C 43 4E 41    .χ..ώ..U....LCNA

0x00000070 : 4D 45 05 00 4C 43 5A 49 50 B1 00 A1 00 31 00 00    ME..LCZIP±.ʽ.1..

0x00000080 : 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 65    ...............e

0x00000090 : 3A 5C 74 65 6D 70 5C 00 76 61 72 2E 66 78 70 00    :\temp\.var.fxp.

0x000000A0 : 65 3A 5C 74 65 6D 70 5C 76 61 72 2E 70 72 67 00    e:\temp\var.prg.

0x000000B0 : 00 29 00 00 00 8F 00 00 00 00 00 00 00 09 00 00    .).............

0x000000C0 : 00 00 00 00 00 00 00 00 00                         .........

Instantly recognizable is that the actual code starts at position 0x50 and ends in 0x64. The following lists the meaning of the bolded part of the file:

0B 00     length of first line (11 bytes)

AE        code for the LOCAL statement

F7        expression: a variable follows

00 00     variable #1 (lcName)

07        comma in list of parameters

F7        another variable follows

01 00     variable #2 (lcZIP)

FE        end of expression

 

0A 00     length of second line (10 bytes)

02 F8 03  code for the ? statement

01        number of parameters: 1

F7        expression: a variable follows

00 00     Variable #1 (lcName)

FE        end of expression

When executing such a program, Visual FoxPro creates a list that assigns each variable in the code to the name table index. Internally, FoxPro calls a function that receives the index as parameter and returns a filled out structure containing the value. As you can see the length of the variable name is not relevant. It's not used in the actual program code, only listed at the end once. Longer (and often more readable) variable names therefore have no significant effect on the execution speed.

Once more the situation is different when looking at arrays. The internal functions to determine the NTI can still be used, but not until then Visual FoxPro knows that this variable is an array. In the next step the index parameters are evaluated and passed to yet another function that copies the value structure to load an array element. Accessing an array element therefore is always slower than accessing a variable. To complicate matters further, Visual FoxPro supports square and round brackets for array as well as functions. There's no clear distinction between the two beforehand. Because arrays have precedence, Visual FoxPro has to check for an array of the same name on every function call.

It's more difficult for object accesses. Once again property names are converted to numbers using the same algorithm. However, object1.name is hardly the same as object2.name. If Visual FoxPro encounters code like this

LOCAL loLabel

loLabel = CREATEOBJECT("Label")

? loLabel.Caption

It's not difficult to resolve the reference to loLabel in the third line. The function to obtain a value for an NTI returns a structure that represents the entire object. The next step is significantly more complex. Objects maintain their own name table. The available list to convert names into the NTI is of no use, anymore. Therefore, Visual FoxPro has to locate the name in the code (CAPTION, in this example) in the private name table and load the structure there. This requires resolving the actual name instead of using the index value and the results cannot be cached like for simple values. Therefore, accessing an object property is even slower than accessing a variable or array element.

That explains why WITH…ENDWITH can increase performance quite notable. It simply keeps the structure of values in memory and makes it unnecessary to resolve the first part. This also explains why it makes much more sense to keep deep references in a local variable. Such a variable can be resolved much faster than a property.

Memory management

Not a bit less complex is memory management in Visual FoxPro. There have been two versions of FoxPro 2.x, a 16-bit and a 32-bit version that used a DOS extender. The DOS extender even changed between 2.0 and 2.6. For DOS application there are various memory types that all are managed by different memory managers. There is XMS and EMS as concurrent memory models. There's HIMEM which extended conventional memory and DPMI, the DOS Protected Mode Interface, which is still supported by Windows. All these types of memory have been supported by various versions of FoxPro; there's still code remaining in Visual FoxPro albeit not active. For example, most of the undocumented memory functions between SYS(1001) and SYS(1016) still work.

Windows added further memory models depending on the version of Windows. Even in the current 32-bit version of Windows there are various kinds of memory types available. The most known types are physical and swapped memory. Memory can be divided even more granular, like in local and global heaps, virtual memory, mapped memory and many types more.

On top of physical memory and various memory models implemented by the operating system, Visual FoxPro has got its own memory management. Visual FoxPro distinguishes five types of memory: stack, off-screen buffer, handle pool and temporary files. The stack is used for temporary processes and is not relevant for Visual FoxPro developers. Usually, you notice the stack in error conditions such as "insufficient stack space" or "Mismatched PUSHJMP/POPJMP call". Only C/C++ developers writing an FLL have to deal with the stack.

The Handle Pool is the actual Visual FoxPro memory management. To cope with all the various memory modules, FoxPro implements a clever model that prohibits direct access to memory. Due to this model, FoxPro could easily be ported to other platforms that used entirely different memory models, such as Windows, Unix or the Mac.

There's a lot of data to keep in memory. This includes variables, menus, windows, strings, but also objects, class definitions, forms, and much more. Every time Visual FoxPro demands memory, the memory manager is called with the size of the desired block of memory. Instead of returning an address, though, it's returning a memory handle. You can imagine this as kind of a primary key. Whenever a program wants to access this memory it must lock memory before, much like you lock a record. After that it can determine the address using an internal function. Once done with accessing memory the code has to unlock the handle, much like unlocking a record.

The purpose of these locks is a different one than with records, though. Not accessing memory parallel is what needs to be prevented, but moving the block around. When memory blocks are constantly allocated and freed memory becomes fragmented over time. The same applies to hard disks when you create and delete files. After running a long time this could lead to the situation that there's enough memory available, but no single block that is huge enough to hold the requested data. This would be fatal for a database system that should run unattended for days or weeks, as such a situation would crash the system.

To prevent this from happening, the garbage collection might move memory blocks around. Internally, Visual FoxPro performs some sort of defragmentation while it's idle. But defragmentation wasn't the only reason for implementing this model. Additionally, memory is not always readily available. For example, in DOS extended memory had to be mapped into conventional memory before the extended version of FoxPro could access it. When a FoxPro handle is locked, FoxPro's management functions can take care of ensuring that this memory block is readily available to the calling program. When unlocked, FoxPro can move the block back to its original position.

Part of the handle pool is visible to us developers with the LIST MEMORY command, but not all handles. Internally, Visual FoxPro makes heavy use of handles, too. All editor windows, for instance, are such handles, too. Therefore we can use ACTIVATE WINDOW not only to activate our own windows, but all windows provided by FoxPro including toolbars. In Visual FoxPro the handle pool is only limited by the available physical and virtual memory.

The SYS(1001) function returns the maximum size of the handle pool. The undocumented function SYS(1011) returns the number of allocated handles. This function should return the same value before and after a function call. Anything else indicates a memory leak. SYS(1016) returns the size of the allocated part of the handle pool. SYS(1104) manually initiates a garbage collection. This function is officially documented since Visual FoxPro 7.0. All of these functions return varying values when executed in the Command Window, because the Command Window permanently enters the idle loop and therefore runs a garbage collection. Some functions filter on internally used handles, others don't do that. Nonetheless, these functions are good indicators for memory usage.

Basically, everything that is not temporary is stored in the handle pool somewhere. This includes transactions and uncommitted changes in a buffer that have to be written back with TABLEUPDATE().

The Page Pool is the area of memory that is controlled by SYS(3050). Visual FoxPro stores in it bitmaps created by Rushmore and uses it to cache tables and indexes. This part of the memory is mainly responsible for blasting speed of Visual FoxPro. Everything that is read from disk is cached here for later reuse. You can control the maximum size of this memory area separately for foreground and background processing. That means depending on whether the user is working with your application or not you can tell Visual FoxPro to use less or more memory. That memory is not allocated immediately but is allocated as needed. You can specify a huge value without having to fear that this amount of memory is immediately gone.

SYS(3050) tries to allocate memory in Windows that is not swapped to disk. However, there's no guarantee. What could happen to you is that Visual FoxPro is assigned memory for caching purposes that only exists on the local hard disk as virtual memory. Obviously, this has quite an impact on performance. Reducing the SYS(3050) value immediately releases all superfluous memory. As the minimum value is 256 KB (not MB), you can use the following code to free memory beyond 256 KB:

lcMemory = Sys(3050,1)

Sys(3050,1,1)

Sys(3050,1,Val(lcMemory))

Even though it's true that you have full control over the page pool this is only half the truth as far as memory consumption is concerned. If Visual FoxPro is of the opinion it needs more memory, for instance, to store an optimization bitmap, then it doesn't hesitate to create temporary files if the page pool is not sufficiently large. FoxPro continues to run, even if the SYS(3050) value you specified is too low, eventually with the same speed, maybe slower, or even faster. This depends on what kind of memory is assigned to Visual FoxPro and which drive is faster. Is it the one used by Windows for virtual memory or the one that is used by Visual FoxPro to store temporary files? As Windows, too, might cache access to TMP files, you might suddenly notice that your application runs faster.

Which directory is used for temporary files depends on the registry settings, the TMPFILES=… setting (not TEMPFILES!) and various settings in Windows. Under normal circumstances you can use SYS(2023) to find out the temporary directory. Sometimes, however, Visual FoxPro might silently switch to a different directory. That seems to be caused by a number of factors. In any case, you should watch out for the current SYS(2023) setting as it might differ from the normal temporary directory used by Windows. The worst case is that Visual FoxPro uses the user's home directory or the program directory, both often being on the server. To really be sure where temporary files go, you could create a cursor and determine its position with the DBF() function. Even though a cursor is usually created in the same directory as SYS(2023) points to, this isn't always the case.

Rushmore

Rushmore is more than a technology, it's a myth. This shouldn't stop us, though, from figuring out what's behind that myth. Especially with this topic it's important to distinguish facts from fiction. In the past decade the rule was to create an index on DELETED(), just the opposite is true in this decade. None of both, however, is really the truth.

The reason for Visual FoxPro's performance has two foundations. One foundation is Rushmore, the other foundation is the truly aggressive usage of the cache. The performance difference to other databases with bitmap centered search algorithm (as Rushmore is one) is mostly not caused by Rushmore, rather due to its caching strategy and an optimized network access.

Rushmore is a bitmap oriented algorithm. Visual FoxPro creates a list for every condition that can be optimized with Rushmore. This list determines if a record fulfills the search criteria or not. Visual FoxPro uses a single bit for each record, as there are only two possible states for each record. Eight records fit into one byte. If you have an 8-million-record table, each search condition takes up 1 MB of memory.

Every bit starts as being 0 – not set – and the corresponding record is therefore in the result set. Once the bitmaps have been determined for all conditions, those bitmaps are bitwise combined. If you use the following condition in a query:

NAME = "Smith" AND InvoiceDate < DATE()-60

This query results in two independent bitmaps being created that contain all the records for "Smith" and all the records with an invoice date less than 60 days ago. In each map it doesn't matter if the other condition is not fulfilled. After that both bitmaps are bitwise combined with AND. The result is a bitmap that only has a bit set for those records that fulfill both conditions. It's not hard to imagine that memory usage to build up these bitmaps can quickly grow and slow down a query. If you only have a single condition, you can use the old dBase style:

SET ORDER TO RequiredIndex

SEEK Value

SCAN WHILE Field=Values

  * do something

ENDSCAN

In many cases this is even faster than a Rushmore optimized query because Visual FoxPro doesn't have to create a bitmap. On the other hand, this technique doesn't use the cache in the same way as Rushmore does making repeated queries to the same data faster in Rushmore. Rushmore works much like the SCAN loop above setting the bit inside the loop.

How is this bitmap really created? The most important factor is that the index in Visual FoxPro is stored as a b-tree, a balanced tree. Every node is a 512 byte block that may contain up to 100 pointers to subordinate nodes. The nodes close to the root refer to key values, only leaf nodes refer to records. Because every node does not refer only to two other blocks, as one might have thought, the hierarchy in the index file can usually remain very flat. Additionally, all nodes are linked horizontally.

When Visual FoxPro searches a value, it navigates through the tree vertically top to down until it found the record. Then it continues to read horizontally as long as the search value matches the one in the index. This strategy of reading down and sideways reduces the number of index nodes it has to read, but increase the number of nodes to change when updating the index. While reading horizontally, Visual FoxPro sets the corresponding bit for each record found. This is possible because the index entry contains record number as well as key value. Data is stored compressed though. This is how the equal operator works.

Other operations work in a similar fashion. For instance, if you want to find records that are not equal, Visual FoxPro starts out with a bitmap in which all records are set. Then it performs the same search as when searching for equal records except that it clears bits when it finds a record. When searching for less or greater than, it simply keeps reading the list to the right or the left until FoxPro reaches the end of the file.

With Rushmore Visual FoxPro can determine which records are not in the result set, not which records are in the result set. That's why the Rushmore phase is followed by a post-scan phase. Every record that has been determined by Rushmore is read completely from disk. Without an optimized expression these are all records in all tables. Each of these records is then checked for the remaining unoptimizable expressions. Additionally, all optimizable expressions are evaluated once again, as someone else might have change the record while Visual FoxPro created the map. Therefore a result set might not contain all records that currently match the search criteria, but you never get records that do not match the search criteria.

There's one exception from this rule, tough. When counting records, Visual FoxPro tries to avoid the post-scan phase. When the bitmap has been built and no unoptimizable expressions are left over, Visual FoxPro starts counting the bits set. Therefore COUNT FOR and SELECT CNT(*) FROM are extremely fast if a query is fully optimized. A partial optimization is not sufficient. It also doesn't help to use any other counting method like SELECT SUM(1) FROM…

When creating the bitmap Visual FoxPro has to read all index blocks that meet the search criteria. Repeated access makes Visual FoxPro retrieve these blocks from the cache. However, the cache is discarded, if one station changes an indexed field or adds a new record. Visual FoxPro can only determine that another work station had changed the index, but not what exactly had changed. Therefore the entire cache is discarded. You can exploit this to measure performance. By using a second instance of FoxPro that replaces one index field in the first record in a loop.

Some time ago people started to realize that an index on DELETED() is not always the optimal solution. It appears that there's kind of an anti-movement completely condemning the index, which is also not a good idea. Whether Rushmore is a good idea is actually quite easy to determine. If you read more bytes in the index file than you remove in records during the post scan, Rushmore decreases performance. You have to compare the bytes that are actually transferred.

You therefore need to keep in mind that the cache has an impact with Rushmore and that CDX indexes are stored compressed. Equal beginnings of a word are stored only once. The actual amount of data can be determined with the system monitor in Windows. Even more precise results are possible with a network monitor such as NETMON.EXE which comes with Windows 2000 server, or Ethereal which is available for free from http://www.ethereal.com. Such a network monitor reveals which part of a file is actually read. Combined with the structure of the files from the help file you can determine lots of details.

If an application changes lots of data this more frequently invalidates the cache. Hence, such an application benefits way less from Rushmore than, for example, a CD catalog application that has put all data into the cache after a short time of using it.

If your application has many deleted records it also benefits from Rushmore. If your application visits a record multiple times because, for instance, the algorithm requires navigating to the next and previous records, Rushmore is superior as it can reuse the bitmaps while old-style xBase code has to re-read all records over and over.

If you want to determine the number of hits before actually executing the query giving your users a chance to avoid long lasting queries, your query should be fully optimizable. This gives you a real speed advantage as creating a bitmap takes significantly less time than reading the entire table, and the bitmap can even be reused should the user decide to execute the query.

Another important factor is the distribution of values in the indexed field. There's a huge difference if you search for a unique value like a primary key or in a status field that only can take ten different values. An extreme example is the index on DELETED() for which usually all fields have the same value. In general you should avoid such indexes unless you need them for counting purposes.

Final words

This document can only give a little insight in the workings of Visual FoxPro. A lot of things I only mentioned briefly. Some things might not even make sense when you read them the first time. Remains the question: Do you need to know all this stuff? Certainly, not. But knowing some of these issues could help you to evaluate risks more precisely. It could explain why certain errors occur that appear to be random. And it's just plain interesting to know what is going on inside.