Re: ADO server side cursor sloooow

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:

> Bill wrote:
>
> > Bill Bach wrote:
> >
> > > 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
> >
> > 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?
>
> 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@xxxxxxxxxxxxxxxxxxxx
> http://www.goldstarsoftware.com
> *** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
> *** Chicago: Pervasive DataExchange Class - March, 2006 ***
.

Relevant Pages

  • Re: ADO server side cursor sloooow
    ... > larger table) it is three times faster with a client side cursor than ... > with a read only forward only server side cursor. ... requiring over 1000 trips over the network. ...
    (comp.databases.btrieve)
  • Re: cancel ADO2.8 asynchron queries over WAN, takes long time
    ... >> the .open method first creates the recordset at the server. ... I use clientside cursor to minimalize the traffic... ... You do not want to be managing any types of cursors on the client ... > doing so over a slow link is a bad choice. ...
    (microsoft.public.data.ado)
  • Re: cancel ADO2.8 asynchron queries over WAN, takes long time
    ... >> the .open method first creates the recordset at the server. ... I use clientside cursor to minimalize the traffic... ... You do not want to be managing any types of cursors on the client ... > doing so over a slow link is a bad choice. ...
    (microsoft.public.sqlserver.programming)
  • Re: cancel ADO2.8 asynchron queries over WAN, takes long time
    ... >> the .open method first creates the recordset at the server. ... I use clientside cursor to minimalize the traffic... ... You do not want to be managing any types of cursors on the client ... > doing so over a slow link is a bad choice. ...
    (microsoft.public.vb.database.ado)
  • Re: VB connection to SQL server
    ... > the client machine begins to lose its relevance and accuracy as soon as it ... > aware that the data is probably out of date, a client sided cursor might ... > design minimises the possibility that records will have changed in the ... >> The Database server is in the office, and people use the Vb program from ...
    (microsoft.public.vb.database)