DOCUMENT:Q216408 04-OCT-2001 [foxpro] TITLE :PRB: Slow Performance In VFP ODBC Driver with SET DELETED ON PRODUCT :Microsoft FoxPro PROD/VER::2.5,2.6,5.0,6.0 OPER/SYS: KEYWORDS:kbODBC kbvfp600 kbODBC360 kbGrpDSFox kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, version 6.0, used with: - Microsoft ODBC Driver for Visual FoxPro, version 5.0 - Microsoft Data Access Components versions 2.5, 2.6 ------------------------------------------------------------------------------- SYMPTOMS ======== Queries that show as fully optimized in Microsoft Visual FoxPro might run very slowly through the Microsoft Visual FoxPro ODBC driver. CAUSE ===== The default for SET DELETED in the Visual FoxPro ODBC driver is "On," which reduces the optimization of most queries. The default in Visual FoxPro for SET DELETED is "Off." RESOLUTION ========== One way to set the Deleted property to Off through the ODBC driver is shown in the code below. This setting is connection specific: MyConn=SQLSTRINGCONN('driver=Microsoft Visual FoxPro Driver; ' ; + 'SOURCETYPE=DBC;sourcedb=c:\mydata\mydata.DBC;backgroundfetch=no') IF myconn <= 0 THEN MESSAGEBOX("Connection Failed") AERROR(myerr) DISPLAY MEMORY LIKE myerr RETURN ENDIF MyRes=SQLExec(MyConn,'SET DELETED OFF') Here is a sample in Active Server Pages using ADO: <%@ Language=VBScript %> <% set MyConn=server.CreateObject("adodb.connection") myconn.ConnectionString="driver=Microsoft Visual FoxPro DRIVER; " & _ "sourcetype=dbc;sourcedb=d:\program files\microsoft visual studio\" & _ "msdn98\98vsa\1033\samples\vfp98\data\testdata.dbc;BACKGROUNDFETCH=NO" MyConn.Open MyConn.Execute "SET DELETED OFF",,ADCMDTEXT SET MYRS=MYCONN.Execute("SELECT * FROM CUSTOMER",,ADCMDTEXT) Response.Write(MYRS.FIELDS(1).VALUE) SET mYRS=NOTHING SET MYCONN=NOTHING %>
Another way to accomplish this is to configure the DSN. The DELETED setting is available on version 6.00.8428.00 or later of vfpodbc.dll. A version of the driver that supports this setting can be obtained by downloading the latest version of the Visual FoxPro ODBC Driver from the following MSDN Web site: http://msdn.microsoft.com/vfoxpro/downloads/updates.asp (http://msdn.microsoft.com/vfoxpro/downloads/updates.asp) STATUS ====== This behavior is by design. MORE INFORMATION ================ Because this setting is the opposite of the setting in Visual FoxPro, it can create instances where the records returned seem to be incorrect. REFERENCES ========== For more help on the SET DELETED command, please search for Set Deleted in the Visual FoxPro ODBC Help File. This file is DRVVFP.HLP in the Windows\System, or Windows\System32 directory or search for SET DELETED in Visual FoxPro Help. Additional query words: ====================================================================== Keywords : kbODBC kbvfp600 kbODBC360 kbGrpDSFox kbGrpDSMDAC kbDSupport kbMDAC250 kbMDAC260 Technology : kbVFPsearch kbAudDeveloper Version : :2.5,2.6,5.0,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 2001.