PRB: REPLACE Command is Slow in Some Instances (202795)
The information in this article applies to:
- Microsoft Visual FoxPro for Windows 3.0
- Microsoft Visual FoxPro for Windows 3.0b
- Microsoft Visual FoxPro for Windows 5.0
- Microsoft Visual FoxPro for Windows 5.0a
- Microsoft Visual FoxPro for Windows 6.0
This article was previously published under Q202795 SYMPTOMS
When using the APPEND BLANK and REPLACE commands to add data to a table, adding a large number of records could take much longer in some cases versus others. If the SET TALK command is ON and a REPLACE command is used on each field when you add values to the record, then the amount of time greatly increases versus using one REPLACE command with commas separating each field and its value. Of course, using SET TALK OFF greatly increases speed regardless of whether you use one or more REPLACE commands.
CAUSE
Using the REPLACE command on each field while SET TALK is set to ON makes multiple REPLACE's slower because of the echoing to the desktop of when each REPLACE executes.
RESOLUTION
Try one of the following to increase performance when you use the REPLACE command:
- Use the SET TALK OFF command.
- Use a single REPLACE command with commas separating the fields and values
- Use the INSERT INTO command, which is much faster.
STATUS
This behavior is by design.
MORE INFORMATION
If SET TALK is set to ON and 5000 records are added to a table with the APPEND BLANK command and a REPLACE command for each field, the time it would take on an average computer would be around 20 seconds. Adding the same amount of records using a single REPLACE command for all the fields would take a couple of seconds to complete. The reason that the individual REPLACE commands take so long is that each REPLACE is echoed to the screen. Using the INSERT command would be an even faster way to add data to the table.
Steps to Reproduce Behavior
Run the following code to build the table and then time the REPLACE command.
SET TALK ON &Change this to OFF for faster REPLACE's.
CREATE TABLE bigtable ;
(Fld1 c(10), ;
Fld2 c(10), ;
Fld3 c(10), ;
Fld4 c(10), ;
Fld5 c(10), ;
Fld6 i, ;
Fld7 i, ;
Fld8 i, ;
Fld9 i, ;
Fld10 i)
USE bigtable
CLEAR
beg_counter = SECONDS()
FOR x = 1 TO 5000
APPEND BLANK
REPLACE Fld1 WITH 'aaaaa' + ALLTRIM(STR(x))
REPLACE Fld2 WITH 'bbbbb' + ALLTRIM(STR(x))
REPLACE Fld3 WITH 'ccccc' + ALLTRIM(STR(x))
REPLACE Fld4 WITH 'ddddd' + ALLTRIM(STR(x))
REPLACE Fld5 WITH 'eeeee' + ALLTRIM(STR(x))
REPLACE Fld6 WITH x
REPLACE Fld7 WITH x
REPLACE Fld8 WITH x
REPLACE Fld9 WITH x
REPLACE Fld10 WITH x
ENDFOR
counter = SECONDS() - beg_counter
? "Individual REPLACE commands took: " + ALLTRIM(STR(counter,4,2)) +" seconds."
beg_counter = SECONDS()
FOR x = 1 TO 5000
APPEND BLANK
REPLACE Fld1 WITH 'aaaaa' + ALLTRIM(STR(x)), ;
Fld2 WITH 'bbbbb' + ALLTRIM(STR(x)), ;
Fld3 WITH 'ccccc' + ALLTRIM(STR(x)), ;
Fld4 WITH 'ddddd' + ALLTRIM(STR(x)), ;
Fld5 WITH 'eeeee' + ALLTRIM(STR(x)), ;
Fld6 WITH x, ;
Fld7 WITH x, ;
Fld8 WITH x, ;
Fld9 WITH x, ;
Fld10 WITH x
ENDFOR
counter = SECONDS() - beg_counter
? "One REPLACE command: " + ALLTRIM(STR(counter,4,2)) + " seconds."
beg_counter = SECONDS()
FOR x = 1 TO 5000
INSERT INTO bigtable (Fld1, Fld2, Fld3, ;
Fld4, Fld5, Fld6, ;
Fld7, Fld8, Fld9, ;
Fld10) VALUES ('aaaaa' + ;
ALLTRIM(STR(x)), 'bbbbb' + ;
ALLTRIM(STR(x)), 'ccccc' + ;
ALLTRIM(STR(x)), 'ddddd' + ;
ALLTRIM(STR(x)), 'eeeee' + ;
ALLTRIM(STR(x)), x, x, x, x, x)
ENDFOR
counter = SECONDS() - beg_counter
? "The INSERT command: " + ALLTRIM(STR(counter,4,2)) + " seconds."
| Modification Type: | Major | Last Reviewed: | 5/12/2003 |
|---|
| Keywords: | kbDatabase kbprb kbXBase KB202795 |
|---|
|