Microsoft KB Archive/314752

= FIX: Poor Performance with VFP OLEDB Provider LockType Set to adLockBatchOptimistic =

Article ID: 314752

Article Last Modified on 2/27/2002

-

APPLIES TO


 * Microsoft Visual FoxPro 7.0 Professional Edition

-



This article was previously published under Q314752



SYMPTOMS
If you use the Visual FoxPro 7.0 OLE DB Provider to create an ActiveX Data Objects (ADO) recordset that uses a lock type of adLockBatchOptimistic, when the recordset performs a query that returns a single record from a large table, the query may take a long time to complete. Creating an index on the field in the WHERE clause of the query can improve performance, but not significantly.



RESOLUTION
To resolve this problem, obtain the latest service pack for Visual FoxPro for Windows 7.0. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

316964 How to Obtain the Latest Visual FoxPro for Windows 7.0 Service Pack



STATUS
Microsoft has confirmed that this is a problem in Microsoft Visual FoxPro for Windows 7.0. This problem was first corrected in Visual FoxPro for Windows 7.0 Service Pack 1.



Steps to Reproduce Behavior
To reproduce the behavior, run the following code. The code creates a large FoxPro table and then queries the table using the Visual FoxPro 7.0 OLE DB Provider. Although the query returns only a single record, it takes a long time to complete. *!* Comment the 2nd #DEFINE and uncomment the first
 * !* to see increased performance.
 * !* #DEFINE LOC_LockType 1 && adLockReadOnly
 * 1) DEFINE LOC_LockType 4 && adLockBatchOptimistic

LOCAL loConn AS ADODB.CONNECTION, ; loRS AS ADODB.Recordset, ; lnStartTime AS NUMBER, ; lnTotalTime AS NUMBER, ; lnLooper AS INTEGER

CD JUSTPATH(SYS(16)) CLOSE DATA ALL IF !FILE('GreatBigTable.DBF') WAIT WINDOW &quot;Creating and populating table...&quot; NOCLEAR NOWAIT CREATE TABLE 'GreatBigTable.DBF' (EID I NOT NULL, ;       NUM I NOT NULL, ;        PYEAR I NOT NULL, ;        ESTATU C(2) NOT NULL, ;        ECODE I NOT NULL, ;        DDATE D NOT NULL, ;        DDATET T NOT NULL, ;        TYPEC C(1) NOT NULL, ;        PLAN I NOT NULL, ;        OPTION I NOT NULL, ;        EOI L NOT NULL)

FOR lnLooper = 1 TO 1000000 INSERT INTO GreatBigTable VALUES(lnLooper, ;           lnLooper, ;            lnLooper, ;            &quot;CA&quot;, ;            lnLooper, ;            DATE + lnLooper, ;            DATETIME + lnLooper, ;            &quot;X&quot;, ;            lnLooper, ;            lnLooper, ;            .T.) ENDFOR USE IN SELECT(&quot;GreatBigTable&quot;) WAIT CLEAR ENDIF

lnStartTime = 0 lnEndTime = 0 loConn = CREATEOBJECT(&quot;ADODB.Connection&quot;) loRS = CREATEOBJECT(&quot;ADODB.RecordSet&quot;)

loConn.OPEN(&quot;PROVIDER=vfpoledb.1;DATA SOURCE=&quot; + JUSTPATH(SYS(16)))

WITH loRS .CursorType = 3 && adOpenStatic .CursorLocation = 3 && adUseClient .LockType = LOC_LockType lnStartTime = SECONDS WAIT WINDOW &quot;Query is running...&quot; NOWAIT NOCLEAR .OPEN(&quot;Select * from GreatBigTable where EID= 178441&quot;, loConn) lnTotalTime = SECONDS - lnStartTime

WAIT CLEAR MESSAGEBOX(TRANSFORM(.RecordCount) + &quot; record return in &quot; + ;       TRANSFORM(lnTotalTime) + &quot; seconds.&quot;) ENDWITH

loRS.CLOSE loConn.CLOSE RELEASE ALL

Additional query words: OLEDB kbVFP700sp1fix

Keywords: kbbug kbfix kbcodesnippet KB314752

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.