Re: Function Vs Expressions

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks, Your answer was good to don't change my fundamental law.

I know, some time we change code, statement and the performance increase.



But, also I listen to increase the speed, store calculations results. I know
it's easier, but is not programming and the consistency is lower.

jose perdigao


"Klatuu" <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6D19F4FB-1BCF-465D-B695-54A47F3845AE@xxxxxxxxxxxxxxxx
Don't change the idea. Storing calculated values is never a good idea.
What
everyone is trying to explain is the order of preference for which method
you
use.
As Allen Browne said, pure SQL provides the best performance. User
defined
functions are best used when the evaluation of your data would be too
complex
for an expression. I tend to use UDFs when an expression would be
difficult
to understand and maintain. And the last (and slowest) choice is Domain
Aggregate Functions.

For example, just recently, I modified the code for a query that had up to
six DLookUps for each row. It took about two minutes to return a
recordset
from a table with less than 7000 rows. By restructuring the query's SQL
and
removing the DLookUps, the recordset now returns in under two seconds.

"Jose Perdigao" wrote:

So, if I understood, is good idea using domain aggregate function in the
form to entry data and from the calcultaion result store it in a field.
make
sense this ratiocination?

Normally, i don't store the calculations results to save memory, but I
think, I must change this ideia.

Thanks
José Perdigão


"Rick Brandt" <rickbrandt2@xxxxxxxxxxx> wrote in message
news:GjL0g.48008$_S7.12044@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Jose Perdigao wrote:
I don't understand why domain aggregate functions is slower than
expressions, they should be faster because is aggregated in access.

There is overhead associated with the use of a domain aggregate
function.
When used singularly in code or a form/report expression this overhead
is
not an issue. When used in a loop or a query that overhead is incurred
for each and every row or loop iteration and then it becomes a
significant
performance problem.


--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com






.

Relevant Pages

  • Re: Info Not updating in table when calculation done in form.
    ... If you store it, it will be wrong ... You can use the table data in a query and can ... perform calculations as well in that query. ... I wanted to use the table as my base to build reports ...
    (microsoft.public.access.forms)
  • Re: avgrec/avgblk history ?
    ... SPACE=means you want enough space to store 1024 blocks, ... "The system allocates DASD space in whole tracks. ... The track balance overhead depends on whether the access method ...
    (bit.listserv.ibm-main)
  • Formula Calculation Database
    ... I am trying to create a database that will store calculations temporarily ... is the correct way, but on my begining form, I enter todays prices in five ... including todays prices mileage, rate, yield and weight. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Add queries to new crosstab every month
    ... I turn this spreadsheet into a CrossTab query using the ACCOUNT ... The additional calculations, however, use the column headings in their ... Let's forget about the store count right now. ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: OpenMac Promises Mac Pro pefromance at Mac Mini prices!
    ... You can also order online so you don't even have to go to a store of any ... account other real-world factors, such as its overhead. ... Well obviously only an idiot is going to put a slow CPU in a high end ...
    (comp.sys.mac.system)