Re: ADO / Clientdataset slowness

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

Date: Tue, 23 Nov 2004 13:55:15 -0500

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?

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.

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%.

"Bill Todd" <no@no.com> wrote in message
news:41a370e9$1@newsgroups.borland.com...
> The delay is caused by the ClientDataSet reading all 6,000 records from
> the server into local memory. First, that is a lot of network I/O.
> Second, the ClientDataSet was never intended to store so many records.
>
> There are two things that will improve performance. First, change the
> query from SELECT * to SELECT FIELD_FOR_MEMO so you are only returning
> one field, not the entire row. Second, change the WHERE clause to
> return a smaller result set. A well designed client/server application
> should very rarely, if ever, return more than 100 rows.
>
> --
> Bill (TeamB)
> TeamB cannot answer questions received via email

Relevant Pages

  • RE: Multiply qty of filtered records
    ... If you should want to try doing this with a query (which is probably the more ... etc in the bottom part of the design view. ... I do know how to get this information in a Report, ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)
  • Re: Query Design View is Slow to Open
    ... same query takes about 20 minutes to open in datasheet view. ... took only seconds to open in both design view and datasheet view. ... The troublesome queries are based entirely on local tables (i.e. no ... HOUSEHOLD_PersonalInfo opens slowly in design ...
    (comp.databases.ms-access)
  • Re: Is this join valid?
    ... > complex set of tables and queries, but I've boiled down the behavior ... you describe a problem with the Design View that really should not ... affect what happens when running a query from ASP. ... > the SQL view after creating the query in Design View. ...
    (microsoft.public.inetserver.asp.db)
  • Re: query to combine values if true
    ... What if you keep your data tables in a well-normalized form (to help Access ... you can give them a form and a way to query and they'd be ... i am open to suggestions of a better design. ...
    (microsoft.public.access.gettingstarted)
  • Re: query where end user puts in selection criteria
    ... I'd like for a user to be able to select specific competencies so ... am used to working in design view and building queries for myself but this is ... build their own query from a form. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.gettingstarted)