Board index » delphi » ado performances vs BDE - ADO is faster!

ado performances vs BDE - ADO is faster!

Hi,

However ADO is very fast, unfortunately the Delphi VCL DB layer, slower it
down.
I made some tests and saw that if you using normal components, (like
ADOTable,
ADOQuery and etc.) but access the data through RecordSet property,
(directly with COM interfaces), you will achieve incredible performance.
The test box is Celeron, 128 RAM, W2K Server, D5 Enterprise, Access 2000.
MDB file with about 10000 items.

The first code is executed in about 10 sec, but second one - about 170ms.
Isn't it fast? After a little jigging with ADO properties, I put
ClientDataSet and
set the same test. Result was about 150ms..... (not including fetching data
from ADOTables).

If you get/put data directly to Recordset you will be very fast.

Test procedure:
// Very simple, just scroll the data set from beginning till EOF (Client
site scroll)
// This is the slow one!
with TADOTable1 do // TADOQuery, TADODataSet - no big differences
  // if you using TClientDataSet, time will be about 150 ms
  // But don't forget, when using ClientDataSet you will have to fetch all
records
  // and that will be the bottleneck - may be? :)
  // If your records are not fetched, than the time highers to 15 sec
  Open;
  T:=GetTickCount;
  while not Eof do Next;
  T:=GetTickCount-T; // About 10 secs
end;

// Here is fast one - Execution time is 170ms
with ADODataSet1  do begin
    T:=GetTickCount;
    while not Recordset.EOF do
        Recordset.MoveNext;
    ShowMessageFmt('%d', [GetTickCount-T]);
  end;

So,
Borland Delphi ADO's Developers - would you please explain us,
WHY are you putting so much slowness in Delphi's ADO layer???
We all see that the backend (JET 4.0) is very fast, so what's wrong with
client site (Delphi)?

Regards,

--
Bobby B.
Datecs' Senior Programmer and Software Manager
bobb...@datecs.bg

 

Re:ado performances vs BDE - ADO is faster!


Quote
Bobby B. <bobb...@datecs.bg> wrote in message

news:7tb20d$kqa33@forums.borland.com...

Quote
> The first code is executed in about 10 sec, but second one - about 170ms.
> Borland Delphi ADO's Developers - would you please explain us,
> WHY are you putting so much slowness in Delphi's ADO layer???

In my testing a straight scroll through a  fairly large (40,000 records) SQL
Server table is anywhere from 15 to 20 percent slower when using TADODataset
then when using the recordset directly.  If you also read data then it can
be 50% slower or more but nothing like the 10secs vs. 170ms you are seeing.

Did you have a grid connected or something?  That could really skew the
results because of all the UI updating that would happen with the Dataset.

There are a few places in the VCL where there is some room for  small
optimizations, but there is not a whole lot to be gained.  However, it's
important to remember that one of the primary benifits of the ADOExpress
components is that they allow you to use ADO with the existing base of
Delphi Data Aware controls.  If you a writing an Order Entry form, the time
it takes to read and write the data will not affect the performance of the
application.

For non-ui, batch oriented applications I highly recommend that you use the
ADO API directly if performance is an issue (because that 50+% number I
mentioned above can make a big difference in those types of applications).
For applications where performance is absolutely critical you even need to
consider coding directly to the database's client library API.

You have the source code to the ADO Express components.  If you can identify
something that you see is a bottleneck for performance, I'd be happy to hear
about it.

Mark

Re:ado performances vs BDE - ADO is faster!


HI,

Quote
> Did you have a grid connected or something?  That could really skew the
> results because of all the UI updating that would happen with the Dataset.

Yeah... I know that.
And No (I haven't got any DataSources connected to the DataSets, at all)....
I have two pure TADODataSets, of course one TDataSetProvider and one
TClientDataSet.
Mark, with all my respects, I can definitely say, I'm not from the Moon :),
I've been working with Delphi for quite long time (All versions of Delphi +
All versions
of Borland Pascal). So you can be sure my tests are REAL.
I'm the last person on the Earth, who will say something "bad" for Delphi
and Borland.
However, if you want to I can send you (by email) the source code.

Quote
> There are a few places in the VCL where there is some room for  small
> optimizations, but there is not a whole lot to be gained.  However, it's
> important to remember that one of the primary benifits of the ADOExpress
> components is that they allow you to use ADO with the existing base of
> Delphi Data Aware controls.  If you a writing an Order Entry form, the
time
> it takes to read and write the data will not affect the performance of the
> application.

You are very right about this! But don't you think performance is very
important???
And don't you think the main benefits of Delphi is not only RAD programming
but the
speed of programs (which is almost like VC++, especially if you know how to
write "good" code)
I will not agree that VB program with ADO access should run/work faster than
Delphi program,
only because of some internal implementation problems of Delphi's ADO
framework.
Look at the newsgroups, everybody is asking for speed..........there are
even questions about
using 3rd party ADO components, only because some little problems with
Borland's one.

Quote
> In my testing a straight scroll through a  fairly large (40,000 records)
SQL
> Server table is anywhere from 15 to 20 percent slower when using
TADODataset
> then when using the recordset directly.  If you also read data then it can
> be 50% slower or more but nothing like the 10secs vs. 170ms you are

seeing.
Ok... below I'm going to talk about 12 secs vs. 680ms.... (see the tests)

Quote
> You have the source code to the ADO Express components.  If you can
identify
> something that you see is a bottleneck for performance, I'd be happy to
hear
> about it.

:).. Sure! Thanks for confidence.

So, let's go back to the main point. Here are some tests/results I've made.
As you can see the main problem is when TADODataSet has CursorLocation =
clUseClient.
In this case performance difference is about 95% (which is very noticeable)
When CursorLocation is Server, then performance percents drop to about
25%....

In Tests from 1 to 4 the bottleneck is filling the TListView - very slow...
to my regret.
Next tests use only pure reading.... not putting any data in TListView....
After minutes of waiting TListView I decide to change the "view" control to
TStringGrid.
Now everything works fine (with filling). Of course if CursorLocation is
clUseClient,
results were the same......

// MS SQL 7.0, 100MB Network, 10000 Items, 6 Columns
// All results are in "ms" - (GetTickCount - measurment)

// Test 1: Filling TListView, Cursor Location: Client Side
// Getting data with OleVariant
Opening ADO pure RecordSet: 535
Scrolling in ADO pure RecordSet: 38004
Closing ADO pure RecordSet: 0
// Getting data with Text property
Opening ADO DataSet: 531
Scrolling in ADO DataSet: 60938
Closing ADO DataSet: 0
Opening ClientDataSet: 13479
Scrolling in ClientDataSet: 42852
Closing ClientDataSet: 10

// I changed all "gets" to be with Variant type
// Test 2: Filling TListView: Server Side Connection
Opening ADO pure RecordSet: 100
Scrolling in ADO pure RecordSet: 48751
Closing ADO pure RecordSet: 0
Opening ADO DataSet: 91
Scrolling in ADO DataSet: 61708
Closing ADO DataSet: 0
Opening ClientDataSet: 13870
Scrolling in ClientDataSet: 43953
Closing ClientDataSet: 0

// Test 3: Filling TListView: Server Side Connection
// CacheSize 100 records
Opening ADO pure RecordSet: 100
Scrolling in ADO pure RecordSet: 36202
Closing ADO pure RecordSet: 0
Opening ADO DataSet: 100
Scrolling in ADO DataSet: 48560
Closing ADO DataSet: 0
Opening ClientDataSet: 2233
Scrolling in ClientDataSet: 43803 // Incredible.....cause of 100 additional
fetches
Closing ClientDataSet: 10

// Test 4: Filling TListView: Server Side Connection
// CacheSize 1000 records
Opening ADO pure RecordSet: 140
Scrolling in ADO pure RecordSet: 35862
Closing ADO pure RecordSet: 0
Opening ADO DataSet: 140
Scrolling in ADO DataSet: 48740
Closing ADO DataSet: 10
Opening ClientDataSet: 2003
Scrolling in ClientDataSet: 43582
Closing ClientDataSet: 0

// Test 5: Just read data, Cursor Location: Server
// CacheSize 100000
Opening ADO pure RecordSet: 270
Scrolling in ADO pure RecordSet: 521
Closing ADO pure RecordSet: 10
Opening ADO DataSet: 261
Scrolling in ADO DataSet: 1592
Closing ADO DataSet: 10
Opening ClientDataSet: 1682
Scrolling in ClientDataSet: 471
Closing ClientDataSet: 0

// Test 6: Read data with Value property, Cursor Location: Server
// CacheSize 1
Opening ADO pure RecordSet: 91
Scrolling in ADO pure RecordSet: 11396
Closing ADO pure RecordSet: 10
Opening ADO DataSet: 10
Scrolling in ADO DataSet: 12828 // very little difference
Closing ADO DataSet: 0
Opening ClientDataSet: 13199
Scrolling in ClientDataSet: 250
Closing ClientDataSet: 0

// Test 7: SQL 7.0, Read data through Value property
// Cursor Location: clUseClient!!!
// Here is the bottleneck!!
Opening ADO pure RecordSet: 501
Scrolling in ADO pure RecordSet: 681
Closing ADO pure RecordSet: 0
Opening ADO DataSet: 500
Scrolling in ADO DataSet: 12138  //????????? You can see the pure RecordSet
is about 700 ms
Closing ADO DataSet: 0
Opening ClientDataSet: 12879     //?????????
Scrolling in ClientDataSet: 240
Closing ClientDataSet: 0

// Test 8: Read from Jet 4.0 Local DB, The same database format and data
// Cursor Location clUseClient
Opening ADO pure RecordSet: 250
Scrolling in ADO pure RecordSet: 541
Closing ADO pure RecordSet: 10
Opening ADO DataSet: 231
Scrolling in ADO DataSet: 11937 // The same slowness
Closing ADO DataSet: 0
Opening ClientDataSet: 12478
Scrolling in ClientDataSet: 240
Closing ClientDataSet: 0

// Test 9: Jet 4.0, The same as Test 7, but Cursor Location is Server
// CacheSize 1
Opening ADO pure RecordSet: 20
Scrolling in ADO pure RecordSet: 631
Closing ADO pure RecordSet: 10
Opening ADO DataSet: 20
Scrolling in ADO DataSet: 2674
Closing ADO DataSet: 0
Opening ClientDataSet: 2954
Scrolling in ClientDataSet: 241
Closing ClientDataSet: 0

// Test 10: Jet 4.0, Now Cache size is 100k, Cursor Location: Server
Opening ADO pure RecordSet: 270
Scrolling in ADO pure RecordSet: 481
Closing ADO pure RecordSet: 0
Opening ADO DataSet: 250
Scrolling in ADO DataSet: 2444
Closing ADO DataSet: 0
Opening ClientDataSet: 2965
Scrolling in ClientDataSet: 230
Closing ClientDataSet: 10

// Test 11: Jet 4.0, Filling TStringGrid, CursorLocation: Client
Opening ADO pure RecordSet: 230
Scrolling in ADO pure RecordSet: 2714
Closing ADO pure RecordSet: 0
Opening TADODataSet: 221
Scrolling in TADODataSet: 14821 // The same slowness
Closing TADODataSet: 0
Opening TClientDataSet: 12378
Scrolling in TClientDataSet: 1983
Closing TClientDataSet: 0

// Test 12: Jet 4.0, Filling TStringGrid, CursorLocation: Server
// Cache Size 100k
Opening ADO pure RecordSet: 251
Scrolling in ADO pure RecordSet: 2724
Closing ADO pure RecordSet: 0
Opening TADODataSet: 250
Scrolling in TADODataSet: 5458 // Only 50%
Closing TADODataSet: 0
Opening TClientDataSet: 2964
Scrolling in TClientDataSet: 1953
Closing TClientDataSet: 0

// Test 13: Jet 4.0, Filling TStringGrid, CursorLocation: Server
// Cache Size: 1
Opening ADO pure RecordSet: 20
Scrolling in ADO pure RecordSet: 3015
Closing ADO pure RecordSet: 0
Opening TADODataSet: 20
Scrolling in TADODataSet: 5769
Closing TADODataSet: 0
Opening TClientDataSet: 2914
Scrolling in TClientDataSet: 1933
Closing TClientDataSet: 0

That's all.....
I'm going to work on these problems and If I find something I'll let you
know...

Have a nice VCL day!

Best regards,

--
Bobby B.
Datecs' Senior Programmer and Software Manager
bobb...@datecs.bg

Other Threads