Originally published in FoxTALK December 1998
To Open the Tables or Not Open the Tables, That is the question.
Have you ever wondered if there is any benefit to opening the tables for an SQL SELECT command before you execute the SELECT? Does the SELECT use the source tables that are already open? Interesting questions.
How fast is fast?
A simple test to find out what effect opening tables ahead of time has on SQL performance in Visual FoxPro is shown in the code example below.
FOR lnCnt = 1 TO 100
lnStart = SECONDS()
SELECT * FROM BigFile WHERE cState = "NY" INTO CURSOR Result
lnEnd = SECONDS()
laTimes(lnCnt) = lnEnd - lnStart
* Calculate the average time
lnTime = 0
FOR lnCnt = 1 TO 100
lnTime = lnTime + laTimes(lnCnt)
lnTime = lnTime/100
In the above test program the table BigFile is a table of 200,000 records with indexes on the cState field and on DELETED(). The program can be called with a parameter that tells it whether or not to close the tables between runs of the SELECT command. It runs the SELECT 100 times and then averages the time that the SELECT took.
On my machine (Pentium II 300MHz with 128 MB RAM and a SCSI hard disk) I got an average time of 1.23 seconds when the tables were closed between SELECTS. If the tables were left open the average time was 0.67 seconds, twice as fast!
Conclusion: Open your tables first and leave them open especially if you will be executing multiple SELECTS that access these tables.
Does SELECT actually use the tables that are open?
No, it doesnít. It issues a USE AGAIN to open the table in another work area. To see this you can try the following test.
1 Open the Data Session Window from the Window menu.
2 In the command window execute a simple SELECT command, like SELECT * FROM SomeTable INTO CURSOR Result.
3 In the Data Session window click on the source tableís name and watch the command echoed to the command window (it will be a SELECT 1 or something like that). The number is the work area that the cursor is open in.
4 Click on the Result cursor and note the work area (it may be 2).
5 Type USE in the command window to close the Result cursor.
6 Rerun the SELECT command.
7 Do steps 3 and 4 again.
You will find that the second time there was a gap in the work areas that the SELECT command used. If you did this when no other tables were open, the first time the work areas would be 1 for the source table and 2 for the result. The second time it would be 1 for the source table and 3 for the result.
The reason that the result is in a different work area the second time is that SELECT opened the source in work area 2 during the processing of the query. This was done as a USE AGAIN operation. The benefit of this behavior is two things, 1) the second select will execute faster and 2) the record pointer in the open source tableís work area is not effected by the SELECT command.
Why is it faster to open the tables?
Because when a table is open, with SET OPTIMIZE ON, VFP tries to cache as much of the index as it can to provide Rushmore with what it needs. So each time you open a table, a bunch of stuff is read into memory. When you USE AGAIN a table that is already open, all of that stuff is already in memory and doesnít need to be read from disk.
You may have noted, in the test program, there was no USE Bigfile command. That shows another nuance of the SQL SELECT command. If the SELECT is issued and the source table(s) are not open, it will open them and leave them open. If the source table(s) are open, they will be used again in another work and those other work areas will be closed when the SELECT completes.
A final note
There is some recent concern regarding the use of SYS(2015) for generating unique cursor names. There was a report that SUBSTR(SYS(2015),3) will begin with a digit in the near future. Well, this is true, but it is irrelevant. Cursors created with the SQL select or the CREATE CURSOR command use the name to set the alias for the work area that the cursor is placed in, NOT the name for the disk file that may be used by the cursor for its data. The file names for cursors will always be unique and will be created in the userís Temp directory. The aliasí are always local to the current machine and do not need to be unique.
Try this in the command window;
CREATE CURSOR MyCsr (Test C(10))
I got the result of Alias() being MyCsr and DBF() being C:\Temp\2E4H000C.TMP. You can see form this that 1) the file for the cursor was created in my temp directory, not on the network drive, and 2) the name I used in the CREATE CURSOR command was not the name given to the dbf file.
It is not necessary to use any algorithm to create a unique name for a cursor, you can use meaningful names in your code and you will not encounter a problem in a multi-user environment.