Slow performance in VFP ODBC Driver with SET DELETED ON (216408)
The information in this article applies to:
- Microsoft Visual FoxPro for Windows 6.0, when used with:
- Microsoft ODBC 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 %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>
<%
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
%>
</BODY>
</HTML>
<BR/>
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:
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.
| Modification Type: | Major | Last Reviewed: | 4/4/2006 |
|---|
| Keywords: | kbprb KB216408 |
|---|
|