Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss. print.
FROM ZERO TO HERO IN 26.84 SECONDS

This article is about code optimization and the introduction of a tool written by Maurice de Beyer, a Dutch VFP developer who is extremely knowledgeable in the use of VFP and other tools. I thank Tariq Mehmood, one of the visitors of Foxite who willingly agreed that I took the code-sample he posted in Thread 34705. I will show the use of Maurice's coverage analyzer here so you will get an idea of how to use it to optimize your own code.

The code shown will sometimes be a bit quirky to begin with, during the process in which I take you through the optimizations, the code will become cleaner and cleaner, and, as that is the goal of this article, faster as well. Accompanied with this article you will find, with Tariq's permission, the code and the tables he used to run this code. Also a link to Maurice's file. You can find cover.zip at http://www.theproblemsolver.nl/files/cover.zip. It is a small file with only one readme.txt and cover.prg

I made some minor adjustment to the code Tariq wrote before running it. I took the start in seconds (nStart = seconds() ) and, at the end of the code I took the current seconds() and calculated the difference between the starting time and the time the code finishes and show that on screen:


?seconds() - nStart

The second adjustment I made was 4 lines of code to open all the dbf's as they are assumed open in the code where in the original code they were not. However, all this will hardly add to the total execution time as I will show later. The last adjustment I made had to do with two variables, F_DATE1 and T_DATE1. In his situation, Tariq would take those dates from a form with two textboxes, I hardcoded those dates so the code could run without the use of the form.

First thing we do, as we want to analyze the code is create a coverage file We do this by typing in the command window:

SET COVERAGE TO covertest.log

Running the testcode for the first time we generate a covertest.log of 12.971 kB. The code itself (testcode.prg) took about 39 seconds to run. Without creating a logfile it took about 27 seconds to run. So let's see whether we can find anything that would take a long time and see whether we can optimize this code.

You may discover that running a program file without creating a coverage log file runs remarkably faster then when you run it while creating such a log file. On my machine the difference was varying from 15.5 to 16.5 seconds. So, although the results are not reliable in the time they show, the purpose of such a logfile is to provide an insight in the slowest parts of the code that could use some optimization.

We start the analysis by running cover.prg. The result is shown in a cursor. Keeping the 80/20 rule in mind (80% result with 20% effort) the lines of code that take the longest are placed at the top.


first run, long times in the upper few lines

- The cClass field is not filled here as we run from a prg.
- The cMethod field either points to the method in a class or to the name of the prg running (as is the case here).
- nLine points to the line in the codesnippet.
- nCnt is the number of times the line is called.
- nTot is the total time the code for that particular line took.

First optimization


The code for this part is found in testcode1.prg. Now, looking at the above picture you see that lines 186, 176, 167 and 158 are responsible for more then 75% of the time the code took to run. So let's optimize those lines first. The codesnippet that we have to care about is:

INDEX ON ALLTRIM(SUBSTR(CODE,1,7)) TO MASTERB1

This indexing is done many times in the code, indeed lines 176 and 167 do show the same code. Indexing a file this way creates a file with IDX extension, containing only one index. For years, already since the days of FPW 2x, the sly Fox has the possibility for compound index files. The biggest advantage of these types of files is that they are opened automatically once the DBF is opened. The second advantage, and I consider that an advantage just as big as the previous one, is that indexes are maintained automatically in CDX files. The third advantage is that those CDX files can contain more then one index on a table. So let's create such a CDX file, going through the code and remove every piece of code that has anything to do with reindexing or indexing code.
This single improvement brought the time down to 23.5 seconds already, while creating a logfile, when running without creating a logfile it took a little over 2 seconds to run.

Before running the code however, I placed the code coverage to another logfile: covertest2. Let's see what cover.prg finds now:


second run, many improvements already

Second improvement
The code for this chapter is in testcode2.prg. On several places in the code you find the REPLACE statement. Most occasions are replacements on 1 field, showing consecutive REPLACE statements per field. Bringing those together might improve the code's readability AND, serving our purpose, the speed.

Compared with the original code you will find remarkably less replace statements. Every replace statement has an effect on the indexing, so the more replace statements you create the more work is done internally for maintaining the index. The logical conclusion is then that any reduction of replace statements will lead to speed improvement.

Another improvement I made in the code was removing some select statements. Sometimes the area selected by this statement was active already. Running code to select that area was, quite obvious, not useful.

The last improvement in this code was to replace all "goto top" statements with a single LOCATE statement. GOTO TOP does a sequential type of skipping back until the top is reached, LOCATE jumps to the top immediately, the latter may not be a big improvement but every bit helps.

Running testcode3 returns a 1.7 seconds without creating a logfile. 25 seconds improvement already compared to the original code. 32% faster compared to the first improvement we made. Let's look at the logfile this time.



Third improvement
The code for this improvement can be found in testcode3.prg. In the above picture we find that the lines 80-85 and 88-89 run 18628 times. This is the code:

SELECT CRSALP
SCAN FOR DATE<=T_DATE1
SCATTER MEMVAR
SELECT MASTER1
SEEK ALLTRIM((ACC_CODE))
IF FOUND()
    REPLACE DEBIT WITH DEBIT+DRAMT, CREDIT WITH CREDIT+CRAMT
ELSE
    UK = UK+1
ENDIF
SELECT CRSALP
ENDSCAN

Let's see whether we can add some improvements to that. What happens here is:

- CRSALP is selected.
- The records where date is smaller then or equal to T_DATE1 are scattered into memory.
- The MASTER1 table is selected.
- The ACC_CODE from CRSALP is looked for in that table.
- If it is found some fields in the MASTER1 table are replaced with new values, otherwise a counter (UK) is incremented.

The new SEEK() function will bring some relief here. Also an extra option in the replace statement can provide some speed improvements here.

In the new code I will look, with help of the SEEK() function for the value of ACC_CODE in the MASTER1 table. Scattering the fields into memory will not be needed then anymore. Also, the last select CRSALP will be over complete and can thus be removed! The code that remains is:

SCAN FOR DATE<=T_DATE1
IF SEEK( ALLTRIM((CRSALP.ACC_CODE)), "MASTER1", "MASTERB1")
    REPLACE MASTER1.DEBIT WITH MASTER1.DEBIT+CRSALP.DRAMT, ;
            MASTER1.CREDIT WITH CREDIT+ CRSALP.CRAMT IN MASTER1
ELSE
    UK = UK+1
ENDIF
ENDSCAN

I did the same code-correction in the remainder of the code where the same structure was used. Let's see how fast it runs now. 1.4 seconds on my machine, an improvement on the previous improvement of 0.3 seconds. Not much, you're right, but still 17.6% faster compared to the previous code.

In the code are several SCAN - ENDSCAN loops. One of the things of a SCAN - ENDSCAN structure is that this code brings the record pointer to the top of the table BEFORE running the code. Therefore any locate statement placed before that loop is obviously not needed, therefore we will remove that code first. Also, on lines 132 to 135 you find the building of a box for displaying the code that is processed. I completely removed that code. Informing the user about the process is useful indeed, but only on occasions where the process might take a considerable amount of time. I have yet to meet the first user who became impatient if a piece of code took a bit over a second to run.
One line that can be completely removed from the code is the replace statement where several fields are filled with 0 values. Also the locate statement that immediately follows that line can be removed. The reason for doing that is that those very fields are later all filled with values from other fields. Giving them one value first (zero) and another value later serves no purpose. This again brought a slight reduction for the time to run. It took now 1.32 seconds. Another 5.7% off the time.

Final improvement
The accompanying code for this chapter can be found in testcode4.prg. Looking at the results in the cursor we find that there are some lines running quite often. Lines 72 to 79 run 18628 times. If we could reduce that we might gain even more speed. The Update SQL statement comes to the rescue here. As you can see in this code there is a mysterious parameter "UK" that is incremented every time the code in the tables is not found.
I just checked, it remains 0. So I use that as a justification to allow myself the SQL statement. It should look like this:

UPDATE master1 ;
SET master1.debit = master1.debit + CRSALP.dramt, ;
    master1.credit = master1.credit + CRSALP.cramt ;
WHERE ALLTRIM(master1.code) == ALLTRIM(CRSALP.acc_code) ;
    AND CRSALP.date <= T_DATE1


But hey, wait a minute. I can apply this same type of code as well to the other SCAN - ENDSCAN structures that basically do the same.

For sake of readability I did not put those lines together into 1 line, which I could do of course, but this leaves me with 5 lines of code. That might give me some gain in speed. Let's see! 0.16 seconds! Compared to the previous code an improvement of 87.8%. Compared to the original code this code shows a speed improvement of somewhere around 19200%. This made me curious whether placing these 5 lines together in one line might bring speed improvements, It did, but not significantly, only 0.01 seconds gain. Code readability was at stake here however, so I omitted that.

Even though many more optimizations are possible I will leave it here as it is. I think you got my point. Covering your code in a logfile and doing some analysis on that will help you to bring out the blazing power of the Fox. Cover.prg, written by Maurice de Beyer, is a very helpful tool in that process.

Enjoy! Happy coding, happy foxing! See you again at www.foxite.com - The Home Of The Visual FoxPro Experts

Download Code
You can download the code and other files discussed in this article here. The download is a zipfile. Its size is 438,918 bytes.

ABOUT THE AUTHOR: BOUDEWIJN LUTGERINK

Boudewijn Lutgerink Programming is one of the many hobbies of Boudewijn. He has worked with computers since 1985 and is the author of two books from Sybex. He has a weblog at http://weblogs.foxite.com/boudewijnlutgerink.

FEEDBACK


Your Name: 
Your Feedback: 

Spam Protection:
Enter the code shown: