Re: ADO server side cursor sloooow

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: VB connection to SQL server
    ... "Chris Barber" wrote in message ... > recordsets (client side cursor) to allow you to persist the recordsets to ... aware that the data is probably out of date, a client sided cursor might fit ... That is because the SELECT statement is executed on the server and the data ...
    (microsoft.public.vb.database)
  • Re: ADO server side cursor sloooow
    ... Using the ODBC driver my tests show that both client and server side ... forward only server side cursor is faster than a client side cursor as ... requiring over 1000 trips over the network. ...
    (comp.databases.btrieve)
  • Re: ... after Opening a record set
    ... I will point out that as long as one can live with a client cursor (all records retrieved to the client machine during the ... it is better to loop based on EOF unless there is a compelling reason to do otherwise. ... >> It is not a reliable method of retrieving records from a recordset. ...
    (microsoft.public.vb.database.ado)
  • Re: ORATCL help needed!
    ... set cursor ... Wow, An Oratcl 3.3. ... It is coded with the the OCI layer released with Oracle ... Oracle instant client is available for many many platforms, ...
    (comp.lang.tcl)
  • 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)