ADO and performance

I'm working with an ADO connection via ODBC into a 1.3GB VFP table, and I'm
finding it to be excruciatingly slow (don't ask why I'm doing it this
way...). The recordset has about 5000 records in it. Doing a MoveFirst takes
over 2 minutes. A query that retreives about 4700 records takes about 2 1/2
minutes. I'm wondering at this point if working via ADO is normally slow,
and this is "normal" performance?

Leemi
Mon Dec 29 13:49:46 CST 2003

Hi Ook:

I am far from an expert on ADO, but I will offer what I know.

I have heard that ADO.Net is optimized to work with recrodsets of around
100 records. Can you pull back less data?

Do the tables have indexes on them? Do the fields you are querying on have
index tags? Are you using the VFP ODBC driver or the OLE DB Provider?

You might want to post this question on an ADO forum (VB 6.0, VB.Net or C #
Net) to generate ideas from that side of the fence.

I hope this helps and have a Happy New Year.

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

Sincerely,
Microsoft FoxPro Technical Support
Lee Mitchell

*-- 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 retires Sept. 30th, 2003

>I'm working with an ADO connection via ODBC into a 1.3GB VFP table, and I'm
>finding it to be excruciatingly slow (don't ask why I'm doing it this
>way...). The recordset has about 5000 records in it. Doing a MoveFirst
takes
>over 2 minutes. A query that retreives about 4700 records takes about 2 1/2
>minutes. I'm wondering at this point if working via ADO is normally slow,
>and this is "normal" performance?

Carsten
Tue Dec 30 01:48:28 CST 2003

Ook,

i doubt that the problem is in the ADO-Layer. We are working on several
tables heading the 2 gig limit holding between 8 mio and 67 mio records with
no performance-problems.

Are you selecting records on a non-indexed field?

This small testprogram retrieves about 2400 records from the table with 67
mio records in less than 2 seconds:

<snip>
LOCAL loConn AS ADODB.Connection
LOCAL loRS AS ADODB.RecordSet

loConn = CREATEOBJECT( "ADODB.Connection" )
loConn.ConnectionString = "provider=vfpoledb;data source=d:\data\"
loConn.Open

loRS = CreateObject("ADODB.Recordset")
loRS.CursorLocation = 3 && adUseClient
lORs.CursorType = 3 && adOpenStatic
loRs.LockType = 3 && adLockOptimistic

loRS.ActiveConnection= loConn

? "Start ", Time()
loRS.Open( "SELECT * FROM accounts WHERE cAccount = '41A111' " )
? loRS.RecordCount

? "Done ", Time()
<snip>

Happy new year,

Carsten




"Ook" <outlookexpress@nospam@embertsdotcom> schrieb im Newsbeitrag
news:unP#qmjzDHA.3416@tk2msftngp13.phx.gbl...
> I'm working with an ADO connection via ODBC into a 1.3GB VFP table, and
I'm
> finding it to be excruciatingly slow (don't ask why I'm doing it this
> way...). The recordset has about 5000 records in it. Doing a MoveFirst
takes
> over 2 minutes. A query that retreives about 4700 records takes about 2
1/2
> minutes. I'm wondering at this point if working via ADO is normally slow,
> and this is "normal" performance?
>
>

Igor
Tue Dec 30 11:16:25 CST 2003

Hi, Ook!
You wrote on Mon, 29 Dec 2003 11:30:05 -0700:

O> I'm working with an ADO connection via ODBC into a 1.3GB VFP table

IMHO it have to be really slowly - TWO intermedate layes - ODBC AND ADO may
slow things tremendously.

O> and I'm finding it to be excruciatingly slow (don't ask why I'm doing
O> it this way...)

Hm. If you don't want to change the way you do this stuff, then why you ask
:)

O> The recordset has about 5000 records in it. Doing a
O> MoveFirst takes over 2 minutes. A query that retreives about 4700 records
O> takes about 2 1/2 minutes. I'm wondering at this point if working via ADO
O> is normally slow, and this is "normal" performance?

Try ODBC without ADO (if your programming tool allow it), and try ADO with
VFP OLE DB Provider (not via ODBC). Not BOTH of them in CHAIN.

P.S. Happy New Year

--
WBR, Igor

Brett
Tue Dec 30 16:43:09 CST 2003

"Ook" <outlookexpress@nospam@embertsdotcom> wrote:

>I'm working with an ADO connection via ODBC into a 1.3GB VFP table, and I'm
>finding it to be excruciatingly slow (don't ask why I'm doing it this
>way...). The recordset has about 5000 records in it. Doing a MoveFirst takes
>over 2 minutes. A query that retreives about 4700 records takes about 2 1/2
>minutes. I'm wondering at this point if working via ADO is normally slow,
>and this is "normal" performance?

What sort of recordset are you retrieving? If it's just for data
extraction, I've found forward only, read only recordsets are
noticeably quicker.