Faster retrieval of data

[Sreedhar]

Which is faster - Domain Aggregates (e.g.DLookup or DCount) or DAO.Recordsets
with SQL ?
If both have pros and cons, what should I keep in mind when I've to decide
which to use where ?

Thanks for any tips.

 

[Tim Ferguson]

Which is faster - Domain Aggregates (e.g.DLookup or DCount) or
DAO.Recordsets with SQL ?

If both have pros and cons, what should I keep in mind when I've to
decide which to use where ?

Looking up a single value, the domain functions are more compact, more
readable, and easier to maintain.

Looking up several values, then using a single recordset fetch will spare
the network.

Speed..? Who cares?


Tim F

 

[Sreedhar]

Hi Tim,

Thanks for the reply. I got your point about one request to the Jet Vs
several. Regarding the speed, do you mean to say the difference is negligible
or none at all ?

 

[John Spencer]

For humans, the time to do one versus the other is negligible as long as you
aren't grabbing a lot of data.

 

[Sreedhar]

Hello John,

For humans, the time to do one versus the other is negligible as long as you
aren't grabbing a lot of data.

OK. Now, what is **a lot of data** ? A hundred thousand records ? A million ?

********Why am I so concerned with the speed issue ? **********

'Cos, My db has just crossed the 1.2GB mark and of late, slowed down a lot
on the Search Forms in general and bulk updates to some extent. I've replaced
some of the DAO code for bulk updates with saved Update Queries (which I
didn't like much, I wanted my modules to be as independent as possible of
outside objects except Tables, of course). Also, I've used a lot of Domain
Functions which I now want to replace if need be.

I've gone through the routine of importing all of data and objects into a
brand new database, decompiling, compiling, compact&repairing etc. This was
my first Access db that I've developed as I learnt VBA simultaneously. That's
why, I've developed a feeling that my inefficient coding made it slow down.
Hence, I want to make it doubly sure that the code I now rewrite should be
flawless in principle.

 

[Tim Ferguson]

'Cos, My db has just crossed the 1.2GB mark and of late,

In that cause, you are on the verge of what is safe or sensible to store
in Access. Start looking at SQL Server or MySQL. Now.

slowed down a
lot on the Search Forms in general and bulk updates to some extent.

Remember that indexes will slow down an update and speed up a retrieval.
Are yours all appropriate?

I've replaced some of the DAO code for bulk updates with saved Update
Queries (which I didn't like much, I wanted my modules to be as
independent as possible of outside objects except Tables, of course).

Huh? If you are using vba loops instead of SQL updates then chuck them
all out and use SQL instead. It's what Jet was made for. Querydefs _are_
a part of the data model, being a kind of mutated combination of views
and stored procedures.

Also, I've used a lot of Domain Functions which I now want to replace
if need be.

Whatever floats your boat, but I don't see the point...

Hence, I want to make it doubly
sure that the code I now rewrite should be flawless in principle.

Flawless code means humanly readable, easily maintainable, well
documented. Programming 101 says that you don't bother optimising until
you have worked out where the bottlenecks are. In Access, this is nearly
always the network. In your case, it's probably also because the mdb is
far too close to its maximum size. You don't say whether you have split
the database: I hope you have because there's a real danger of trashing
the thing at this size. Faffing around with domain functions is probably
somewhere around 57th on your list of priorities.

Hope that helps


Tim F

 

[Sreedhar]

Hi Tim,

"Tim Ferguson" wrote:

In that cause, you are on the verge of what is safe or sensible to store
in Access. Start looking at SQL Server or MySQL. Now.

My office budgets won't allow for an SQLServer. MSDE is free, but
also has a 2Gig limitation, right ?

Remember that indexes will slow down an update and speed up a retrieval.
Are yours all appropriate?

I never gave much thought about Indices. Thanks for the eye-opener.

Faffing around with domain functions is probably
somewhere around 57th on your list of priorities.

That arbitrary "57" that you pulled from thin air succinctly confirms
that I was way off the target. Thanks again.

 

[Tim Ferguson]

Hi Tim,



My office budgets won't allow for an SQLServer. MSDE is free,
but also has a 2Gig limitation, right ?

MySQL is free. Can't remember what limitations are on SQL Server Express
Edition, although it's hard to get Access to play ball with it. Even the
SQL Server option is probably cheaper than trashing all your data, no?

Hope that helps.

Tim F