Hi Aleksey


Here is the VB code that takes 1ms to open (Select * From UPDatDic) and display all 2297 records from the remote cursor which the VFP code below takes 120secs. So I find it hard to believe that there is not some problem with how VFP is handling things.

Thanks,
Simon White

Dim cnADS As ADODB.Connection
Dim rsTest As ADODB.Recordset

' Transaction flag
Dim bTransaction As Boolean

' Timer variables
Dim StartTime As Long
Dim EndTime As Long

' Data vars
Dim sAliasType As String
Dim bDictionary As Boolean

cnADS.ConnectionString = "Provider=Advantage OLE DB Provider" & _
                         ";Data Source=" & sDataSource & _
                         ";User ID=" & sUserID & _
                         ";Password=" & sPassword
cnADS.Open


Private Sub cmdExecute_Click()
On Error GoTo ExecuteErr
  
  ' Clear the error message
  txtError.Text = ""
  
  Me.MousePointer = vbHourglass
  
  ' Make sure connection is active
  If cnADS.State = adStateClosed Then cmdConnect_Click
  
  ' Close the recordset before opening again
  If rsTest.State = adStateOpen Then rsTest.Close
  
  LoadSettings
  
  ' Start the timer
  StartTime = Timer
  
  ' Open the recordset
  rsTest.Open txtSQL.Text, cnADS, adOpenDynamic, adLockPessimistic, adCmdText
    
  Set dgcResult.DataSource = rsTest
  
  Me.MousePointer = vbDefault
  
  ' Finish time
  EndTime = Timer
  
  ' Display the time
  sbQuery.Panels(1).Text = CalcTime(EndTime - StartTime)
  sbQuery.Panels(2).Text = "Record Count: " & rsTest.RecordCount
  
  txtError.ForeColor = &HFF0000
  txtError.Text = "SUCCESS!"
  Exit Sub
  
ExecuteErr:
  Me.MousePointer = vbDefault
  txtError.ForeColor = &HFF&
  txtError.Text = Err.Description
End Sub

>Hi Simon,
>
>VFP doesn't do anything special for ADO, calling ADO object is the same as calling any other COM object. If object works fast, VFP works fast. If object works slow, VFP works slow. It is possible that VB6 app doesn't use ADO, but calls into OLEDB directly.
>Usually, ADO Recordset with CursorType = adOpenForwardOnly, CursorLocation = adUseServer and LockType = adLockReadOnly provides best fetch performance. I would try this settings. Also, I would store all ADO field objects into vars before the loop to avoid doing lookup by name for each field for each record.
>In any case, I believe VFP is not responsible for this perf issue.
>
>Aleksey.
>
>>Hi Aleksey
>>
>>The following is some test code I used just to test the raw ADO performance.
>>As you can see it takes 2 minutes just to get to the point of inserting the records. If only takes 7 seconds to open the connection so that leaves just under 2 minutes to open the recordset for a client side cursor and 3.5 second to insert the records.
>>
>>In the case of a server side cursor the RecordSet is created almost instantly with 2 minutes being used to insert the records.
>>
>>However when I open the table in VB 6 using OLE DB I can open the table and scroll to the bottom in about 2 seconds.
>>
>>This means that the cursoradapter is not the problem but VFP is much slower retrieving the data than VB 6. So I would like to know what is causing VFP to be so slow using ADO in this case.
>>
>>If have emailed you the IP address of the test server if you would like to try it. I can also send you the VF6 test program.
>>
>>Regards,
>>Simon White
>>
>>
>>

>>Local loC,loD,lnSec1,lnSec2,lnSec3
>>lnSec1=Seconds()
>>loC=CreateObject("ADODB.Connection")
>>loC.ConnectionString="Provider=Advantage OLE DB Provider; Data Source=\\server1\apps\ads\FOPro.add; User ID=adssys; Password=teeka; Compression=ALWAYS; Server Type=6;Table Type=6"
>>loC.CursorLocation=3
>>loC.Open
>>loD=CreateObject("ADODB.RecordSet")
>>loD.ActiveConnection=loC
>>loD.Open("Select * From UPDatDic")
>>Use dbf\updatdic
>>Copy Structure to tmp\updatdic
>>Use tmp\UPDatDic
>>With loD
>>   lnSec3=Second()
>>   FOR ln=1 To .RecordCount
>>         Insert into UPDatDic (allownull,atype,autoinc,backcolor,boundcol,cdxfilter,cdxkey,cdxtype,;
>>         cdxunique,changed,cntrlclass,colwidths,conversion,dec,defaultcus,defaultVal,desc,;
>>         displayval,duplicate,editableby,encode,encrypt,filterable,;
>>         forecolor,format,id,inputmask,label,len,lookupord,method,name,pid,readonly,relatedto,;
>>         rowsource,rowstype,statusbar,table,type,usewith,visibleto,zip,_updated,_updatedby) ;
>>         Values (NVL(.Fields("AllowNull").Value,0),NVL(.Fields("AType").Value,""),NVL(.Fields("AutoInc").Value,0),;
>>         NVL(.Fields("BackColor").Value,-1),NVL(.Fields("BoundCol").Value,0),NVL(.Fields("CdxFilter").Value,""),;
>>         NVL(.Fields("CDXKey").Value,""),NVL(.Fields("CDXType").Value,""),NVL(.Fields("CDXUNique").Value,0),;
>>         NVL(.Fields("Changed").Value,0),NVL(.Fields("CntrlClass").Value,""),NVL(.Fields("ColWidths").Value,""),;
>>         NVL(.Fields("Conversion").Value,""),NVL(.Fields("Dec").Value,0),NVL(.Fields("DefaultCus").Value,0),;
>>         NVL(.Fields("DefaultVal").Value,""),NVL(.Fields("Desc").Value,""),NVL(.Fields("DisplayVal").Value,""),;
>>         NVL(.Fields("Duplicate").Value,0),NVL(.Fields("EditableBy").Value,-1),NVL(.Fields("Encode").Value,0),;
>>         NVL(.Fields("Encrypt").Value,0),NVL(.Fields("Filterable").Value,0),NVL(.Fields("ForeColor").Value,0),;
>>         NVL(.Fields("Format").Value,""),NVL(.Fields("ID").Value,""),NVL(.Fields("InputMask").Value,""),;
>>         NVL(.Fields("Label").Value,""),NVL(.Fields("Len").Value,0),NVL(.Fields("LookupOrd").Value,""),;
>>         NVL(.Fields("Method").Value,""),NVL(.Fields("Name").Value,""),NVL(.Fields("PID").Value,""),;
>>         NVL(.Fields("ReadOnly").Value,0),NVL(.Fields("RelatedTo").Value,""),NVL(.Fields("RowSource").Value,""),;
>>         NVL(.Fields("RowSType").Value,0),NVL(.Fields("StatusBar").Value,""),NVL(.Fields("Table").Value,""),;
>>         NVL(.Fields("Type").Value,""),NVL(.Fields("UseWith").Value,""),NVL(.Fields("VisibleTo").Value,-1),;
>>         NVL(.Fields("Zip").Value,0),NVL(.Fields("_Updated").Value,""),NVL(.Fields("_UpdatedBy").Value,""))
>>         .MoveNext
>>   EndFor
>>EndWith
>>lnSec2=Seconds()
>>? lnSec2-lnSec1    120sec
>>? lnSec2-lnSec3    3.5sec
>>

>>
>>
>>>Hi Simon,
>>>
>>>I can't answer your question without having the code you are comparing and the database. Try to play with ADO settings: cursor type, lock type, cursor location, etc. Try to call into ADO directly and compare performance with CursorAdapter. If there is no difference then it is not a problem with CursorAdapter.
>>>
>>>Aleksey.
>>>
>>>>Hi
>>>>
>>>>I have tried these items but not much change in the speed.
>>>>
>>>>I also came across another issue related to the Advantage Database server. If I use their Database Architect and open my table remotely over the internet it opens almost instantly. If I try the same thing using OLE DB in a VB applcation it is almost instantaneous. However, if I use the ODBC driver or the OLE DB driver in VFP it takes at least a minute or longer to open the same table. I tried using SPT with the ODBC connection and it is very slow it also takes a minute or longer. How is it that VB6 with OLE DB can open the table and browse to the end instantaneouly while VFP takes over a minute just to open the table?
>>>>
>>>>Thanks,
>>>>Simon White
>>>>
>>>>
>>>>>>Hi
>>>>>>
>>>>>>I am testing a VFP9 application built using cursoradapters. At present I am looking for suggestions of best practices to insure optimal performance. I realize that the less data you load into the application the better but what I want to improve is the speed of loading forms when the application is run remotely with data being obtained from SQL Server over a DSL internet connection.
>>>>>>
>>>>>>Presently it opens 15 tables and takes about 20-25 seconds to launch. Most of the tables are small (less than 100 records) but several have 2000 records.
>>>>>>
>>>>>>Thanks,
>>>>>>Simon White
>>>>>
>>>>>Hi Simon,
>>>>>
>>>>>Try to decrease CursorAdapter.FetchSize property, but, in ODBC case, to avoid "connection is busy" error you may need to use CursorAdapter.AllowSimultaneousFetch = .T. and shared connection (one statement handle per CursorAdapter).
>>>>>
>>>>>Thanks,
>>>>>Aleksey.