Question:
I was out on the Fox newsgroup and somebody posted some ADO code in Visual FoxPro that alarmed me. VFP seems to have a major problem repeatedly accessing the Field collection on the ADO recordset. There seems to be a huge memory leak. I ran the following code in Visual Basic and in VFP and the results were astonishing.
The time it takes VFP to complete the program grows exponentially as you increase the iterations of the loop, while VB stays the same. If you comment out the FOR EACH loop where it goes through the fields collection, VFP gives the same nice results as VB. What is wrong?
Here is the VFP code:
local oConnection, oRecordSet, intOuter, oField, intLoopoConnection = createobject("adodb.connection")with oConnection .Provider = "SQLOLEDB.1" .ConnectionString = "Persist Security Info=False;"+; "User ID=sa;Initial Catalog=Pubs;Data Source=(local)" .open()endwithfor intOuter = 1 to 20 StartTime = seconds() oRecordSet = createobject("adodb.recordset") with oRecordSet .open("Select * From Customer", oConnection) for intLoop = 1 to 5 do while not .eof for each oField in .Fields next oField .movenext enddo endfor .close endwith oRecordSet = Null ? intOuter,seconds() - StartTimeendforoConnection.closeoConnection = null
I’d appreciate any workarounds. The newsgroup is eating me alive as I’m trying to defend VFP as a serious middle-tier solution.
Answer:
The Visual FoxPro FOR EACH..ENDFOR construct appears to be slow when accessing ADO collections. Try using the FOR..ENDFOR construct instead.
For example, change the following loop (from the code sample you included):
for each oField in .Fields next oField
to the following
for lnField = 1 TO .Fields.Count oField = .Fields(lnField) ...next lnField