Using PSQL 9.1 I am comparing the PDAC SQL components to ADO. Selecting
and looping through 1,000 rows takes about the same amount of time
using PDAC SQL and ADO with a client side cursor. However, ADO with a
server side forward only cursor takes 2.5 times more time to select and
iterate the same rows. That makes no sense at all. A server side
forward only cursor should be faster if anything.

Any suggestions? I can't help thinking that I must have something set
wrong.

--
..Bill.

A client side cursor will stream the data in bulk down the the
workstation. When you need the ENTIRE data set sent across the line,
you should use the Client-Side Cursor. Server-side cursors are much
faster when only a small portion of the data is needed.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***

Bill wrote:

Bill Bach wrote:

Thanks for your answer Bill. I am an experienced database developer and
I understand the basics of ADO. My problem is that if I run exactly the
same SQL statement (which selects 1,000 rows from a much larger table)
it is three times faster with a client side cursor than with a read
only forward only server side cursor.

This makes no sense because the client side cursor must dynamically
allocate memory to hold the data then load the data into that memory on
the client.

Try the same test with SQL Server and the server side cursor is faster
as I would expect. I do not understand why the server side cursor is
slower with Pervasive 9. Is there anything I can do to improve the
performance of a server side cursor?

--
..Bill.

Bill wrote:

There are two possibilities:
1) The query is complex enough, and the options selected are forcing
the server-side cursor to build a temp table on the server first,
before passing any data. This is less likely, IMHO.
2) More likely, the problem is simply related to the data stream. When
you use client-side cursors, the data comes over in bulk, using 8K (or
larger) data transfers. This can easily grab 100+ records at a time
for small data sets. Using this number as an estimate, we would expect
only 10 round-trips across the network. A server-side cursor sets of
the data on the server, and the records come down one at a time,
requiring over 1000 trips over the network. Since the network is the
slowest link, I can easily forsee the additional delays in each packet
increasing the total time to that amount.

The way to tell for sure is to enable a network analyzer when you run
both queries. With that, it is possible to clearly see the number of
round-trip packets, along with the round-trip-time for each packet.
Additionally, you can see how the ODBC calls differ, and you'll see
EXACTLY where the time difference comes from.

If you don't have a network analyzer, you can get the free Ethereal
from the web. If you don't have anyone who can read it, let me know
and I can help.

I would ALSO be curious to see a network trace of the same query
hitting SQLServer to see if Microsoft is doing bulk transfers and then
caching data on the client...
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***

Bill,

FYI, I finally opened a support case and Pervasive confirmed that
server side cursors have a performance problem with the OLE DB
provider. Their recommendation is to use the ODBC driver with ADO.
Using the ODBC driver my tests show that both client and server side
cursors are faster than with the OLE DB provider and a read only
forward only server side cursor is faster than a client side cursor as
one would expect.

--
..Bill.


Bill Bach wrote:

Nice. Did they give you a Bug Number for tracking purposes?
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***

Bill wrote:

Bill Bach wrote:

Apparently Pervasive does not consider it a bug. At least there was no
indication in my dialog with support that they consider it a bug.

From my limited experience with Persavive to date it appears that the
ODBC driver is the only Win32 driver that they have spent much time
tuning and the only one they really support. Using ADO the OLE DB
provider is much slower than the ODBC driver (which should not be the
case).

The performance of the PDAC components is pretty good but they appear
to have a serious deployment problem. I just tried to deploy a program
compiled with the version 8.5 PDAC components at a site that is running
PSQL 8.1 and it will not connect. My client, who is a big enough
customer of Pervasives to get attention, had a conference call with the
senior people in product management and engineering and they did not
seem to consider that a bug either. Apparently they think it is quite
reasonable to have to have a different version of the program for every
version of PSQL you might have to deploy on. Thankfully no other
database vendor I know of has that attitude.

The other problem with PDAC is that the last version I can find is for
Delphi 7. Apparently they never produced a version for Delphi 2005. I
was just told by support, who was told by the product manager, that
there will not be an update for Borland Developer Studio 2006, which
shipped a month and a half ago, until mid-year at the earliest.
Needless to say we have abandoned PDAC.

--
..Bill.