ADO / Clientdataset slowness

From: brag (ggroshner_at_fnis.com)
Date: 11/23/04 

Date: Tue, 23 Nov 2004 09:41:59 -0500

We've got an exisiting program originally devloped against BDE for which we
access all of our data through the ClientDataSet components. When we
converted to ADO, we hit some speed issues.

To test, I've created a simple demo form with a TADOQuery and
TADOConnection. I do a select * on a table out of a database (Oracle with
Oracle provider) that contains about 6,000 rows and loop through the result
set - important note - I'm using a server side cursor for speed. Here's the
code snippet:

    idx := 0;
    Memo1.Lines.Add(DateTimeToStr(Now));
    ADOQuery1.Active := TRUE;
    While not ADOQuery1.EOF do begin
       inc(idx);
       Memo1.Lines.Add(ADOQuery1.Fields.Fields[0].AsString);
       ADOQuery1.Next;
    end;
    Memo1.Lines.Add('Total Records: ' + inttostr(idx));
    Memo1.Lines.Add(DateTimeToStr(Now));

This code completes in 2 1/2 seconds, which is acceptable - the query itself
is actually way faster than that, but since I'm looping through the result
set and populating a memo it takes a total of 2 1/2 seconds to complete.

When I tie this together with a provider and a clientdataset, performance
goes south. The snippet is basically the same, but uses the clientdataset to
access the data:

    idx := 0;
    Memo1.Lines.Add(DateTimeToStr(Now));
    ClientDataSet1.Active := TRUE;
    While not ClientDataSet1.EOF do begin
       inc(idx);
       Memo1.Lines.Add(ClientDataSet1.Fields.Fields[0].AsString);
       ClientDataSet1.Next;
    end;
    Memo1.Lines.Add('Total Records: ' + inttostr(idx));
    Memo1.Lines.Add(DateTimeToStr(Now));

This code takes 19 seconds to complete. The delay all occurs when activating
the clientdataset. Since I think the query itself is taking less than 3
seconds, where does the other 16 seconds come from? Does anyone know of a
way to speed this up?

Relevant Pages

  • Re: I get this message
    ... MS SQL, ADO components from D7. ... The clientdataset delete works but the applyupdates fails. ... Russ ...
    (borland.public.delphi.database.ado)
  • Re: Developer Use of Visual Basic Plummets
    ... The ClientDataset, as I understand it, is an in-memory dataset and it's not ... other server connected processes for mullti-tier apps. ... (their ADO has a thin ADODataSet that looks a lot like the ClientDataset). ...
    (microsoft.public.vb.general.discussion)
  • Re: usage of clientdataset with jet
    ... >merit /describe the usage of clientdataset with ado components ... >How to efficiently use TClientDataset with ADO? ... Using Tclientdatasets wtih ADO and Access isn't much different than any other ... TadoDataset with clientside cursors is really quite similar to Tclientdatasets. ...
    (borland.public.delphi.database.ado)
  • Re: ADO vs dbExpress
    ... cursors, a limitation that ADO has not. ... Only if you do not use a ClientDataSet. ... same behavior that you get with ADO and a client side cursor. ...
    (borland.public.delphi.database.ado)