Board index » delphi » Client side cursor slower than server side cursor!?

Client side cursor slower than server side cursor!?

Hi,

I have a peculiar issue with ADO. When I use a client-side cursor, record
traversals are much slower than when I use a server-side cursor. Logic
dictates that traversing through records with a server-side cursor should be
slower than when all the records are buffered client-side. However, my
experiments have shown otherwise.

When I use client-side cursors (Cursorlocation = clUseClient, CursorType =
ctStatic, LockType = ltReadOnly) the following code takes 5 minutes to
complete (the tadodataset1 query returns just under 130000 records):

  while not adodataset1.EOF do
    adodataset1.next;

When I set it to use CursorLocation = clUseServer and try various cursor
types the process completes after 30 to 60 seconds - at least 5 times faster
depending on the cursor type.

A client-side query should obviously take longer to open, but record
traversals should be faster. This doesn't appear to be the case. Anyone know
why?

The configuration (tested on more than one PC and server):
Athlon XP 1600+
512MB RAM
Win2K Pro SP2
Delphi 6 Update 1
MSSQL 2000 SP2 (developer edition)
MDAC 2.6

Thanks,
Pierre le Riche

 

Re:Client side cursor slower than server side cursor!?


Pierre le Riche <moc.liamtoh@ehcirelp> wrote in message
news:3c6cf20b_2@dnews...

Quote
> Hi,

> I have a peculiar issue with ADO. When I use a client-side cursor, record
> traversals are much slower than when I use a server-side cursor.
...
>   while not adodataset1.EOF do
>     adodataset1.next;

Call adodataset1.DisableControls before your loop and performance will be
much, much faster - even when you don't have any controls connected to the
dataset. This is a long-standing "feature" (bug ?) with ADOExpress.

Andy Mackie.

Re:Client side cursor slower than server side cursor!?


All I can say is: WOW!

I put in a adodataset1.disablecontrols before the loop and the execution
time dropped from 5 minutes to less than a second! Now that's what I call a
speed improvement!

Thanks a million Andy, this one really had me baffled. I just couldn't
figure out why it could be so slow even though the dataset wasn't connected
to anything (not even a datasource). Now I'm wondering if there's any way to
fix this bug in the ADOExpress code, maybe by creating a new descendant
component and overriding some of the methods? Or is it too deeply embedded
to fix?

Now if only I can speed up the rest of my code 100x by adding a single line
of code ;).

Thanks again,
Pierre

Quote
> Call adodataset1.DisableControls before your loop and performance will be
> much, much faster - even when you don't have any controls connected to the
> dataset. This is a long-standing "feature" (bug ?) with ADOExpress.

Re:Client side cursor slower than server side cursor!?


SQL Server is probably giving you a server-side "firehose" cursor
(server-side, forward-only, read-only) in one instance, which is the
native server cursor and operates very, very fast. There's good
information about this type of cursor on MSDN, but one of the
drawbacks is you can only have one firehose cursor open per
connection. ADO gets around this limitation by spawning a new
connection. This is probably what's happening.

Matt Jacobs

--

My reply-to address is purposely mangled to thwart auto-reply bots.
Please remove the two leading underscores if you wish to reply via
e-mail.

Re:Client side cursor slower than server side cursor!?


Pierre le Riche <moc.liamtoh@ehcirelp> wrote in message
news:3c6d03fd_1@dnews...

Quote
> All I can say is: WOW!

> I put in a adodataset1.disablecontrols before the loop and the execution
> time dropped from 5 minutes to less than a second! Now that's what I call
a
> speed improvement!

> Thanks a million Andy, this one really had me baffled. I just couldn't
> figure out why it could be so slow even though the dataset wasn't
connected
> to anything (not even a datasource). Now I'm wondering if there's any way
to
> fix this bug in the ADOExpress code, maybe by creating a new descendant
> component and overriding some of the methods? Or is it too deeply embedded
> to fix?

Well, the problem is the call to Recordset.AbsolutePosition in the function
TCustomADODataSet.InternalGetRecord in ADODB.pas. Look for these lines:

  if ControlsDisabled then
  RecordNumber := -2 else
  RecordNumber := Recordset.AbsolutePosition;

But this is one of those "Is it Microsoft's or Borland's fault ?" kind of
issues. Is it Microsoft's fault for making AbsolutePosition so slow, or is
it Borland's fault for calling it all the time ?

Cheers,
Andy Mackie

Other Threads