Microsoft KB Archive/216408

= Slow performance in VFP ODBC Driver with SET DELETED ON =

Article ID: 216408

Article Last Modified on 5/11/2007

-

APPLIES TO


 * Microsoft Visual FoxPro 6.0 Professional Edition, when used with:
 * Microsoft Open Database Connectivity Driver for Visual FoxPro 5.0
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q216408



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://msdn2.microsoft.com/en-us/vfoxpro/bb190233.aspx



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.

