DOCUMENT:Q202795 04-MAR-2002 [foxpro] TITLE :PRB: REPLACE Command is Slow in Some Instances PRODUCT :Microsoft FoxPro PROD/VER::3.0,3.0b,5.0,5.0a,6.0 OPER/SYS: KEYWORDS:kbDatabase kbvfp300b kbvfp500 kbvfp600 kbXBase KbDBFDBC ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a, 6.0 ------------------------------------------------------------------------------- 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." Additional query words: KBDSE ====================================================================== Keywords : kbDatabase kbvfp300b kbvfp500 kbvfp600 kbXBase KbDBFDBC Technology : kbVFPsearch kbAudDeveloper kbVFP300 kbVFP300b kbVFP500 kbVFP600 kbVFP500a Version : :3.0,3.0b,5.0,5.0a,6.0 Issue type : kbprb ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2002.