Re: ADO / Clientdataset slowness

From: Bill Todd (no_at_no.com)
Date: 11/23/04 

Date: 23 Nov 2004 11:18:19 -0700

brag wrote:

> So, are you saying that when I use the the Query and iterate through
> that result set that the same network traffic doesn't occur? Wouldn't
> looping through the entire Query result set bring down the same data?

In both cases you are reading the query result set. However, with the
CDS you incur the overhead of formatting the data in the variant data
packets that pass between the DSP and the CDS and the several thousand
memory allocations by the CDS to load the data into memory. The CDS
memory manager is very inefficient with large datasets.

>
> I agree that pulling down 6,000 rows is generally not a good idea. To
> defend myself a bit, the data being queried here is fairly static and
> is setup-type data. The entire table (hence the SELECT *) is pulled
> down and stored in a local area (we call it a cache) so another query
> for that data never has to go across the pipe to the database again,
> saving huge traffic during normal day-to-day user operations. I guess
> whether that's good design or not could be debated, but this design
> has enabled us to performance test more than a half-dozen times to
> volumes in excess of 5,000 concurrent (not connected) database users
> at a time.

For static data I agree that this is a good approach. I have used it
also with very good results.

>
> The reason this has become an issue for us is that when our
> application was migrated from BDE to ADO, queries like this which
> populate our 'cache', if it doesn't exist, have slowed by 300-500%.

Some part of that may just be the overhead of ADO, which is
considerable. When you moved from BDE to ADO did you change databases
as well? 

-- 
Bill (TeamB)
TeamB cannot answer questions received via email

Relevant Pages

  • Re: Delphi 6 - ADO - Memory leakage issue
    ... if your file format is csv, you can use ADO to load data in memory and ... accordingly can run any query you want ... ADO is better than TMemData or any equivalent component ...
    (borland.public.delphi.database.ado)
  • Re: FTS Performance in SQL 2005
    ... Can you post you query plans and the output of statistics IO ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... its the pipe between the CPU and Memory that could be the bottleneck. ... and that you have a covering index on the columns in the query. ... Plan window will then give you a percentage query cost relative ...
    (microsoft.public.sqlserver.fulltext)
  • Re: FTS Performance in SQL 2005
    ... Now if I do a query directly to the field I would theoretically need: ... SQL Server MVP ... Because i've set the MAX sql-server memory to 3.5 GB instead of 4.0 GB ... cost relative to the whole batch, ...
    (microsoft.public.sqlserver.fulltext)
  • RE: ODBC query in VB code Need HELP
    ... Everything for ADO is in the first 2 messages that I gave you. ... Since your pass-through query already exists (including the ... Dim STRSQL As String ...
    (microsoft.public.access.formscoding)