Performance Tips Sharing..

[andreateh]

Hi everybody, here are some tips sharing. So lets try to post our most frequnce use tips here. I think this kinda tips is very useful for beginer like me:

USE EVALUATE OR () instread of & (MACRO ASSIGN)

CREATE CURSOR t (cText C(3),nNumeric N(4),cText2 C(3))
INSERT INTO T (cText,nNumeric,cText2) VALUES ("456",9,"789")
 
CLEAR
X="T"
L_SEC = SECONDS()
FOR i=1 TO 10 
 ??&X..ctext 
ENDFOR 
?SECONDS()-L_SEC
?""
L_SEN = SECONDS()
FOR I=1 TO 10
 ??EVALUATE(X+".CTEXT")
ENDFOR 
?SECONDS()-L_SEN

*Conclusion EVALUATE is faster

USE INSERT instead of APPEND BLANK follow by REPLACE or GATHER MEMVAR

CREATE CURSOR t (cText C(3),nNumeric N(4),cText2 C(3))
INSERT INTO T (cText,nNumeric,cText2) VALUES ("456",9,"789")
SCATTER MEMVAR  
CLEAR
X="T"
L_SEC = SECONDS()
FOR i=1 TO 10 
APPEND BLANK 
GATHER MEMVAR 
ENDFOR 
?"APPEND BLANK, FOLLOW BY REPLACE"
?SECONDS()-L_SEC
?""
L_SEN = SECONDS()
FOR I=1 TO 10
INSERT INTO t FROM MEMVAR 
ENDFOR 
?"INSERT INTO"
?SECONDS()-L_SEN

*Conclusion INSERT is faster.
Just copy code above test run.

 

[jimoo]

Here are a couple.

Item 1.
Avoid opening and closing tables. If you are likely to use the table, open it up-front and leave it open. Test code to see results.

CREATE TABLE boo (mydata C(10))
FOR lni = 1 TO 50 
   APPEND BLANK
   REPLACE mydata WITH SYS(2015)
ENDFOR

clear
lnStart= SECONDS()
FOR lni = 1 TO 10000
   USE boo
   USE
ENDFOR     
lnEnd = SECONDS()
? lnEnd - lnStart

USE

lnStart= SECONDS()
FOR lni = 1 TO 1000000
  IF !USED('boo')
     USE boo
   ELSE
      SELECT boo
   ENDIF        
ENDFOR     
lnEnd = SECONDS()
? lnEnd - lnStart

NOTE: The faster method is processing the loop a million times and the slower method is only processing the loop ten-thousand times. Therefore, the faster loop is even much faster than the result show because it is performing the operation many more times.



Item 2.
If you are only retrieving a smaller subset of records, from a table with lot of records:

The SEEK() / DO WHILE (or scan/endscan if which is even faster) will almost always out perform a select statement.

Rather than:

Select field1, field2, field3 from my table where companyid = "abc"

* Try this.

lcID = "abc"
SELECT mytable
SEEK lcID
IF FOUND()
   DO WHILE myTable.id = lcID AND NOT EOF()
      * append or whatever
      SELECT Master
      SKIP
   ENDDO
ENDIF

Summary - Seek to the starting point and processing until it the key does not match if one of the fastest access methods - usually faster than SELECT * on large tables.

However, remember that indexing is the key. Rushmore is based on indexing.



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[andreateh]

Here is another:

Use LOCATE Instead off GO TOP to go top the first RECORD. LOCATE is faster.

From VFP help document :

If you use the LOCATE command without the FOR expression, Visual FoxPro positions the record pointer at the first logical record. This is faster than using GO TOP when a filter is in use or when DELETED is ON.

cOldSetting = SET ("DELETED")

CREATE CURSOR t (cText C(3),nNumeric N(4),cText2 C(3))

FOR i =1 TO 10
    INSERT INTO T (cText,nNumeric,cText2) VALUES;
                  ("456",9,"789")
ENDFOR 

LOCATE && equal GO TOP
DELETE NEXT 5
 
CLEAR

L_SEC = SECONDS()

FOR i=1 TO 10 
	GO TOP 
	GO BOTTOM 
ENDFOR 
GO TOP 
?"RECNO :" + TRANSFORM(RECNO())
?SECONDS()-L_SEC

L_SEN = SECONDS()
FOR I=1 TO 10
	LOCATE 
	GO BOTTOM 
ENDFOR 
LOCATE 
?"RECNO :" + TRANSFORM(RECNO())
?SECONDS()-L_SEN
SET DELETED &cOldSetting

 

[jimoo]

Here is another one. I read this in one of the VFP Magazines several years ago, and the changed some of my code to test it, and it really sped things up.

For compound conditions, such fiscal year and customer number, rather than this:

Select field1, field2 from mytable where FY = lcFY and custID = lcID into boo

Change it to this>

Select field1, field2 from mytable where FY + custId = lcFY + custID = lcID into boo

Due to the way foxpro handles indices. This is typically much faster.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[jimoo]

Rushmore works its best off indexed fields. As a rule of thumb I index any field that I will query on so it will produce the fastest results.

I am not aware of any speed differences between an IDX or CDX file, but other may have or experience testing the two.

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[jimoo]

I just realize the goof I had in the 2nd query.

Select field1, field2 from mytable where FY + custId = lcFY + custID into boo

Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com

 

[terosoft]

 Select field1, field2 from mytable where FY + custId = lcFY + custID into boo

Where you check for the value of custID? (forgot lcID?)

I can't understand this, as for me can lead to improper results.

Let's say that lcFY = 1 and lcID = 2, so in

Select field1, field2 from mytable where FY = lcFY and custID = lcID into boo

The only correct values for FY and custID are 1 and 2 respectively.

But in

 Select field1, field2 from mytable where FY + custId = lcFY + lcID into boo && Is this correct, jimoo?

FY could be 2 and custID could be 1 (FY+custID = 3 in both cases).

See what I mean?

Gerardo Czajkowski

 

[jimoo]

Yes. I am firing my editor.

This is the correct statement:

Select field1, field2 from mytable where FY + custId = lcFY + lcID into boo

The concept is field+field = variable+variable is usually (much) faster than field+variable = field+variable



Jim Osieczonek
Delta Business Group, LLC

http://www.deltabg.com