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.

ODBC/OLE DB Speed Problem

4 posts in FoxPro Last posting was on 2008-03-17 22:13:30.0Z
Simon White Posted on 2006-02-09 18:21:55.0Z
From: Simon White <SimonWhite@dCipherComputing.com>
Newsgroups: Advantage.FoxPro
Message-ID: <200629132155.609748@Simon2>
Date: Thu, 9 Feb 2006 13:21:55 -0500
X-Newsreader: PocoMail/4.0.3350
Subject: ODBC/OLE DB Speed Problem
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 69.199.207.142
X-Trace: 9 Feb 2006 11:18:44 -0700, 69.199.207.142
Lines: 23
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!69.199.207.142
Xref: solutions.advantagedatabase.com Advantage.FoxPro:204
Article PK: 1109557

Hi

I have the ADS 8.0 running on a remote server which I access over the internet. The speed to open tables etc. is excellent. However, when I try to use ODBC to open the same tables in Visual Foxpro the speed is terrible. For example, the Data Architect can open a table with 2297 records and scroll to the last record in less than a second. To open this same table using the ODBC driver in VFP takes more than 1 minute. I tried the same test using the OLE DB client and it also took over 1 minute to load the recordset. I have tried using SQL PassThrough with the ODBC driver and execute the query on the server and retrieve the results into a VFP table and it also takes over 1 minute. I have tried the OLE DB client if Visual Basic 6 and the results are the same as the Data Architect.

I have tried the same tests with MS SQL and the results are available in less than 20 seconds in VFP. So what is causing the slow ODBC/OLE DB speed in VFP?

Thanks,
Simon White


Joachim Duerr (ADS Support) Posted on 2006-02-10 13:05:38.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: ODBC/OLE DB Speed Problem
Newsgroups: Advantage.FoxPro
References: <200629132155.609748@Simon2>
Organization: iAnywhere
User-Agent: XanaNews/1.17.6.5
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 195.2.185.25
Message-ID: <43ec8fa2@solutions.advantagedatabase.com>
Date: 10 Feb 2006 06:05:38 -0700
X-Trace: 10 Feb 2006 06:05:38 -0700, 195.2.185.25
Lines: 15
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.FoxPro:205
Article PK: 1109559


Simon White wrote in <200629132155.609748@Simon2>:

> I have tried the same tests with MS SQL and the results are available
> in less than 20 seconds in VFP. So what is causing the slow ODBC/OLE
> DB speed in VFP?

Try to set the cursor location to clUseServer in your ADO application.
This should speed it up. I guess you're fetching all records which
takes that amount of time.

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de


Simon White Posted on 2008-03-17 21:02:09.0Z
From: Simon White <SimonWhite@dCipherComputing.com>
Newsgroups: Advantage.FoxPro
Message-ID: <20083171729.768230@HPA6200N>
Date: Mon, 17 Mar 2008 17:02:09 -0400
X-Newsreader: PocoMail/4.5.3910
References: <200629132155.609748@Simon2> <43ec8fa2@solutions.advantagedatabase.com>
Subject: Re: ODBC/OLE DB Speed Problem
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 99.239.57.192
X-Trace: 17 Mar 2008 14:56:29 -0700, 99.239.57.192
Lines: 26
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!99.239.57.192
Xref: solutions.advantagedatabase.com Advantage.FoxPro:252
Article PK: 1109606

Hi

I was just wondering whether any improvements have been made in version 9 in regards to this problem. The problem was not fixed when I tried server side cursors.

I do remember that tech support looked into this problem as I sent them a VFP project for testing. At the time it was determined there was something different in the way VFP handled OLE DB but no solution was ever found.

Thanks,
Simon

> Simon White wrote in <200629132155.609748@Simon2>:
>
>> I have tried the same tests with MS SQL and the results are
>> available in less than 20 seconds in VFP. So what is causing the
>> slow ODBC/OLE DB speed in VFP?
>>
>
> Try to set the cursor location to clUseServer in your ADO
> application. This should speed it up. I guess you're fetching all
> records which takes that amount of time.


Mark Wilkins Posted on 2008-03-17 22:13:30.0Z
From: "Mark Wilkins" <mark@no.email>
Newsgroups: Advantage.FoxPro
References: <200629132155.609748@Simon2> <43ec8fa2@solutions.advantagedatabase.com> <20083171729.768230@HPA6200N>
Subject: Re: ODBC/OLE DB Speed Problem
Date: Mon, 17 Mar 2008 16:13:30 -0600
Lines: 71
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3198
NNTP-Posting-Host: 10.24.38.228
Message-ID: <47deebb8@solutions.advantagedatabase.com>
X-Trace: 17 Mar 2008 16:07:52 -0700, 10.24.38.228
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.228
Xref: solutions.advantagedatabase.com Advantage.FoxPro:253
Article PK: 1109607

Hi Simon,

I don't think there have been any major architectural changes that would
affect the situation you are describing. If you are talking about executing
a statement such as "select * from sometable", then I don't think there
should be much of a change in performance. On the other hand, it's not
clear to me that there should be that big a difference (20 seconds to over 1
minute) between different ODBC drivers and database servers. With Visual
FoxPro, I believe that all cursors will be client-side cursors. When you
execute the query, VFP will start reading all the data from the server to
the client. However, there are VFP settings to change if that happens
synchronously or asynchronously.

I think that with ODBC, Advantage will default to a forward-only cursor in
that situation, which (in general) will end up reading approximately 100
records at a time from the server. It seems that pretty much any ODBC
driver with any database backend would take approximately the same amount of
time in a situation where the network is the bottleneck.

The reason ARC would be much faster in opening a table and going to the last
record is because it would only be reading the first few and then the last
few records (a benefit of a connected recordset). With VB, it is possible
to get a server-side cursor, so some of the same benefits would be seen.

There are a couple of things that come to mind that could make a performance
difference. Make sure that you are using compression in this situation.
That is one thing that could make a difference in speed, since it could
significantly reduce the amount of data that must be transferred (although
the round-trip time might be the more significant factor here).

Another thing to try might be to change the communications type to TCP/IP.
That is one thing that has been added since 8.0 that might be a factor in
this situation. If there was some kind of problem with UDP connections and
the large chunks of data, then TCP/IP communication might avoid that
problem. Or if UDP is still desired, it might not hurt to try setting
packet_size=1024 in the ads.ini file (on the client) to limit the size of
packets in case the speed problem was due to packet fragmentation.

HTH,
Mark Wilkins
Advantage R&D

"Simon White" <SimonWhite@dCipherComputing.com> wrote in message
news:20083171729.768230@HPA6200N...
Hi

I was just wondering whether any improvements have been made in version 9 in
regards to this problem. The problem was not fixed when I tried server side
cursors.

I do remember that tech support looked into this problem as I sent them a
VFP project for testing. At the time it was determined there was something
different in the way VFP handled OLE DB but no solution was ever found.

Thanks,
Simon

> Simon White wrote in <200629132155.609748@Simon2>:
>
>> I have tried the same tests with MS SQL and the results are
>> available in less than 20 seconds in VFP. So what is causing the
>> slow ODBC/OLE DB speed in VFP?
>>
>
> Try to set the cursor location to clUseServer in your ADO
> application. This should speed it up. I guess you're fetching all
> records which takes that amount of time.