Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

ADO cursor adapters performance

3 posts in FoxPro Last posting was on 2009-08-05 16:01:04.0Z
Eric Posted on 2009-08-03 14:55:38.0Z
From: Eric <emoss@gen-data.com>
Newsgroups: Advantage.FoxPro
Subject: ADO cursor adapters performance
Date: Mon, 3 Aug 2009 09:55:38 -0500
Message-ID: <MPG.24e0b66293333f84989684@DevZone.AdvantageDatabase.com>
Organization: GDS
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
User-Agent: MicroPlanet-Gravity/2.80.1
NNTP-Posting-Host: 174.141.33.218
X-Trace: 3 Aug 2009 08:54:30 -0700, 174.141.33.218
Lines: 20
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!174.141.33.218
Xref: solutions.advantagedatabase.com Advantage.FoxPro:298
Article PK: 1109652

Hello,

I'm working on trying to get a functional advantage database backend
setup for the software I currently work in, and I'm using remote views
(to replace views we normally use) and ADO cursor adapters to replace
the tables. But I'm having some issue with ADO data access and
performance, with large tables (upwards of 1 million records) it can
take up to about 10 minutes to load. The ADO recordset is using a
server-side cursor, and I don't want to add any where clauses to my
select statement (i basically want everything available in the cursor as
if it were a table opened from a VFP table with the 'use' command)

Is there anything I can do in this sitaution to speed up performance
(either from the advantage side of things, or VFP settings to pull the
data), or is that just how it's going to work when it loads up the and
creates the cursor?

Thanks,

--Eric M.


Mark Wilkins Posted on 2009-08-03 17:21:11.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.FoxPro
References: <MPG.24e0b66293333f84989684@DevZone.AdvantageDatabase.com>
Subject: Re: ADO cursor adapters performance
Date: Mon, 3 Aug 2009 11:21:11 -0600
Lines: 37
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: 10.24.38.228
Message-ID: <4a771c43@solutions.advantagedatabase.com>
X-Trace: 3 Aug 2009 11:20:03 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.FoxPro:299
Article PK: 1109653

Hi Eric,

I don't think there is any good solution to that. Maybe there is some
mechanism that I do not know about, but I am pretty sure that FoxPro will
always load the entire result set no matter if you use a client side or
server side cursor. If you can specify a forward-only cursor, then I believe
it might load faster (it will use a larger read-ahead size), but that will
only provide an incremental improvement. It will still read all the records
in the table.

Mark Wilkins
Advantage R&D

"Eric" <emoss@gen-data.com> wrote in message
news:MPG.24e0b66293333f84989684@DevZone.AdvantageDatabase.com...
> Hello,
>
> I'm working on trying to get a functional advantage database backend
> setup for the software I currently work in, and I'm using remote views
> (to replace views we normally use) and ADO cursor adapters to replace
> the tables. But I'm having some issue with ADO data access and
> performance, with large tables (upwards of 1 million records) it can
> take up to about 10 minutes to load. The ADO recordset is using a
> server-side cursor, and I don't want to add any where clauses to my
> select statement (i basically want everything available in the cursor as
> if it were a table opened from a VFP table with the 'use' command)
>
> Is there anything I can do in this sitaution to speed up performance
> (either from the advantage side of things, or VFP settings to pull the
> data), or is that just how it's going to work when it loads up the and
> creates the cursor?
>
> Thanks,
>
> --Eric M.


Mark Wilkins Posted on 2009-08-05 16:01:04.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.FoxPro
References: <MPG.24e0b66293333f84989684@DevZone.AdvantageDatabase.com> <4a771c43@solutions.advantagedatabase.com>
Subject: Re: ADO cursor adapters performance
Date: Wed, 5 Aug 2009 10:01:04 -0600
Lines: 77
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: 10.24.38.228
Message-ID: <4a79ac7a@solutions.advantagedatabase.com>
X-Trace: 5 Aug 2009 09:59:54 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.FoxPro:300
Article PK: 1109654

Hi Eric,

I have to correct myself. Thanks Eric for providing this information (at
least I think you are the one who asked about the setting ... it got back
around to me eventually). In any case, there is a FetchAsNeeded property
that does seem to do just what you were asking. I should have been aware of
it, but if I have seen it also then forgot about it. Some information is
at: http://msdn.microsoft.com/en-us/library/aa975670.

<code>
connectionString = "Provider=Advantage OLE DB Provider;"+;
"Data Source=\\server\share\data;"+;
"ServerType=ADS_REMOTE_SERVER;"+;
"TableType=ADS_VFP;"

conn=CREATEOBJECT("ADODB.Connection")
conn.connectionString = connectionString
conn.OPEN()
rs=CREATEOBJECT("ADODB.RecordSet")
rs.CursorLocation = 2 && adUseServer
ca = CREATEOBJECT("CursorAdapter")

ca.FetchAsNeeded = .T.

ca.DataSourceType="ADO"
cmd = "select * from bigtable"

res = rs.Open(cmd,conn,3,3)
ca.Alias = "qqq"
ca.CursorFill(,,,rs)
</code>


Mark Wilkins
Advantage R&D

"Mark Wilkins" <mark@no.email> wrote in message
news:4a771c43@solutions.advantagedatabase.com...
> Hi Eric,
>
> I don't think there is any good solution to that. Maybe there is some
> mechanism that I do not know about, but I am pretty sure that FoxPro will
> always load the entire result set no matter if you use a client side or
> server side cursor. If you can specify a forward-only cursor, then I
> believe it might load faster (it will use a larger read-ahead size), but
> that will only provide an incremental improvement. It will still read all
> the records in the table.
>
> Mark Wilkins
> Advantage R&D
>
> "Eric" <emoss@gen-data.com> wrote in message
> news:MPG.24e0b66293333f84989684@DevZone.AdvantageDatabase.com...
>> Hello,
>>
>> I'm working on trying to get a functional advantage database backend
>> setup for the software I currently work in, and I'm using remote views
>> (to replace views we normally use) and ADO cursor adapters to replace
>> the tables. But I'm having some issue with ADO data access and
>> performance, with large tables (upwards of 1 million records) it can
>> take up to about 10 minutes to load. The ADO recordset is using a
>> server-side cursor, and I don't want to add any where clauses to my
>> select statement (i basically want everything available in the cursor as
>> if it were a table opened from a VFP table with the 'use' command)
>>
>> Is there anything I can do in this sitaution to speed up performance
>> (either from the advantage side of things, or VFP settings to pull the
>> data), or is that just how it's going to work when it loads up the and
>> creates the cursor?
>>
>> Thanks,
>>
>> --Eric M.
>
>