Performance?

In one of our applications we have a choice as to the type of access to the
foxpro database. What is the difference in performance for these different
options:

- a VFP dll direct accessing the data
- an ODBC connection
- OLE/dB connenction
- Other options?

The performance I'm interested in is select statements with complex where
clause, with or without grouping of data. The calling application will be
written in C++

Thanks in advance
Zeke

turiya36
Mon Aug 09 20:15:03 CDT 2004

Hi Zeke

The VFP DLL is going to be the fastest out of all of the options you
presented. I assume this is a multi-user system? If so, explore the
posibility of using a DLL with DCOM - this way, all data access is happening
on the same machine as the data.

Zeke
Mon Aug 09 13:02:10 CDT 2004

OK - that's what I assumed (VFP DLL being fastest). The application in
question will run only with local data, so everything will happen on the
same machine anyway.

In this case there would be other advantages to use something that would not
require VFP programming skills. But what would be the trade-off - i.e. how
much would performance suffer?

Zeke


"turiya36" <turiya36@discussions.microsoft.com> wrote in message
news:9792747B-B631-4D96-AEDC-9BFBFCB5615E@microsoft.com...
> Hi Zeke
>
> The VFP DLL is going to be the fastest out of all of the options you
> presented. I assume this is a multi-user system? If so, explore the
> posibility of using a DLL with DCOM - this way, all data access is
happening
> on the same machine as the data.
>
>

turiya36
Mon Aug 09 22:25:02 CDT 2004

Performance will drop somewhat by using ODBC and OLEdb, however, if you keep
everything on the machine that contains the data, it should be acceptable.
The bottom of the scale of "database performance" is ODBC and OLEdb over a
network. You can't get much slower than that.

"Zeke Galama" wrote:

> OK - that's what I assumed (VFP DLL being fastest). The application in
> question will run only with local data, so everything will happen on the
> same machine anyway.
>
> In this case there would be other advantages to use something that would not
> require VFP programming skills. But what would be the trade-off - i.e. how
> much would performance suffer?
>
> Zeke
>
>
> "turiya36" <turiya36@discussions.microsoft.com> wrote in message
> news:9792747B-B631-4D96-AEDC-9BFBFCB5615E@microsoft.com...
> > Hi Zeke
> >
> > The VFP DLL is going to be the fastest out of all of the options you
> > presented. I assume this is a multi-user system? If so, explore the
> > posibility of using a DLL with DCOM - this way, all data access is
> happening
> > on the same machine as the data.
> >
> >
>
>
>

Holger
Tue Aug 10 01:37:10 CDT 2004

Hi,

> foxpro database. What is the difference in performance for these different
> options:
>
> - a VFP dll direct accessing the data
> - an ODBC connection
> - OLE/dB connenction
> - Other options?
>
> The performance I'm interested in is select statements with complex where
> clause, with or without grouping of data. The calling application will be
> written in C++

IMHO the fastest option (just for the query) is the VFP DLL !
But the result of the query needs to be converted into a data format (ADO,
XML etc.), that can be passed to the calling application. This process takes
time as well.

--
Holger Vorberg
MS Visual FoxPro MVP, Germany

Ook
Tue Aug 10 09:27:12 CDT 2004

You may also notice that access speed with an ODBC connection is more then
adequate. It would certainly be simpler to do it this way then using a VFP
DLL. Try it and see if it meets your needs, sometimes slower but simpler is
best.


"Zeke Galama" <nospam@mymail.com> wrote in message
news:u43sg%23lfEHA.3632@TK2MSFTNGP09.phx.gbl...
> In one of our applications we have a choice as to the type of access to
the
> foxpro database. What is the difference in performance for these different
> options:
>
> - a VFP dll direct accessing the data
> - an ODBC connection
> - OLE/dB connenction
> - Other options?
>
> The performance I'm interested in is select statements with complex where
> clause, with or without grouping of data. The calling application will be
> written in C++
>
> Thanks in advance
> Zeke
>
>

Leemi
Tue Aug 10 10:15:09 CDT 2004

Hi Zeke:

I will not rehash the pros and cons of each option since you already
received some good suggestions. I would like to comment on using ODBC.
Normally, performance is not an issue with ODBC connections. However, to
maximize the speed you need to direct the VFP temp files locally as opposed
to writing them over a network. See this article:

259942 HOWTO: Set Where .tmp Files Are Created with the VFP ODBC Driver
http://support.microsoft.com/?id=259942

Also, use the SQLExec() function to optimize the VFP ODBC memory usage with
the SYS(3050) function.
See this article for more info on the SYS(3050) function: 176483 PRB: Large
Amounts of RAM Seem to Process Data Slowly
http://support.microsoft.com/?id=176483

You can integrate this function into a SELECT-SQL command you send to the
backend using SQLExec like this:

SELECT *, SYS(3050,1,10000000) as junk from cust2x

I hope this helps.

This posting is provided "AS IS" with no warranties, and confers no rights.

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- VFP9 Public Beta Now Available!! --*
Download the VFP9 beta here: http://msdn.microsoft.com/vfoxpro/

*-- VFP8 HAS ARRIVED!! --*
Read about all the new features of VFP8 here:
http://www.universalthread.com/VisualFoxPro/News/VFP8Release.asp
Purchase VFP8 here:
http://shop.microsoft.com/Referral/Productinfo.asp?siteID=11518

Keep an eye on the product lifecycle for Visual FoxPro here:
http://support.microsoft.com/default.aspx?id=fh;[ln];lifeprodv
- VFP5 Mainstream Support retired June 30th, 2003
- VFP6 Mainstream Support retired Sept. 30th, 2003

>You may also notice that access speed with an ODBC connection is more then
>adequate. It would certainly be simpler to do it this way then using a VFP
>DLL. Try it and see if it meets your needs, sometimes slower but simpler is
>best.


>"Zeke Galama" <nospam@mymail.com> wrote in message
>news:u43sg%23lfEHA.3632@TK2MSFTNGP09.phx.gbl...
> In one of our applications we have a choice as to the type of access to
the
> foxpro database. What is the difference in performance for these different
> options:
>
> - a VFP dll direct accessing the data
> - an ODBC connection
> - OLE/dB connenction
> - Other options?
>
> The performance I'm interested in is select statements with complex where
> clause, with or without grouping of data. The calling application will be
> written in C++
>
> Thanks in advance
> Zeke
>
>

Craig
Tue Aug 10 10:35:48 CDT 2004

Just to throw out a differing opinion here....

The direct access using the DLL will be the fastest to access the data, but
you will then need to convert the data to XML, ADO, etc to get it out of the
DLL and into your application. That conversion will take time, especially to
convert to ADO.

ODBC is "typically" slower than OLE DB/ADO. This is due to additional
requirements needed by ODBC iteself. The VFP ODBC driver has not been
updated since VFP 6.0 (current release is 8.0, with 9.0 in beta). If the VFP
data takes advantage of any features added since 6.0 (for example, AutoInc
fields), then ODBC will not work at all.

Given the above, I would choose OLE DB/ADO, especially if you don't have any
VFP experience to create a DLL.

--
Craig Berntson
MCSD, Visual FoxPro MVP
Author, CrysDev: A Developer's Guide to Integrating Crystal Reports
www.craigberntson.com
email iamcraig@craigberntson.com (remove iam)
Salt Lake City Fox User Group
www.slcfox.org


"Zeke Galama" <nospam@mymail.com> wrote in message
news:u43sg%23lfEHA.3632@TK2MSFTNGP09.phx.gbl...
> In one of our applications we have a choice as to the type of access to
the
> foxpro database. What is the difference in performance for these different
> options:
>
> - a VFP dll direct accessing the data
> - an ODBC connection
> - OLE/dB connenction
> - Other options?
>
> The performance I'm interested in is select statements with complex where
> clause, with or without grouping of data. The calling application will be
> written in C++
>
> Thanks in advance
> Zeke
>
>