Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    12

    Unanswered: Will running a long query slow my app?

    Hi,

    Maybe someone could give me some pointers on the preferred route in development.

    I'm working on a HR system, where I use a query to calculate the monthly Bonus ( no bankers :-) for every employee. To me this query looks rather extensive, its a UNION of 5 queries, on of them holding a sub query. Since the bonus is only one of the parameters to return, this Bonus query is then used as a sub query in the query linked to the monthly payment form.

    The easiest way is to run the Bonus Query to calculate all bonuses for all employees for all months, and then retrieve the Bonus for the specific Empl and Month from this set. And repeat for every employee. Since the company has 250 employees, after a couple of years the set of Bonuses to calculate will be really big. Not very efficient IMO.

    Question: Is this something I should worry about, and should I work out one of the Alternatives below, or is this no problem for current processor speeds?

    One alternative, I guess, would be something like retrieving the Bonus Dataset set once in VBA and then keep this Dataset in memory and use this for every Employee. Not sure how to do that, but I would probably get there.

    Another solution of course is to use parameters in the Bonus Query. However if I would run this for all employees, the Bonus Query would still be calculated for all employees, for every employee. This is because Bonuses from different employees interact. Is that a problem? I could still parametrize the Month. Is running a query for WHERE Month LIKE "*" taking much more time then a query for WHERE Month = "Jan 2010"?

    I'm quite new in this, but I already learned it is better to think first, then code.

    Maybe somebody could share his thoughts?

    Thanks,

    Jens.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    That UNION query will be slowing thing down
    What i do
    make a new table form the UNION query and then fun the rest from the newly created table

    rem that the table will have to be created each time you run it
    hint (make table) this will delete the table each time and created

    so you have to

    DoCmd.SetWarnings False this will turn off the yes/no dialog boxes
    ...
    ...
    ...
    DoCmd.SetWarnings True don't for get to turn it on again

    what you could do is

    build a program that run at nite time. created the tables
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Apr 2009
    Posts
    12
    Hi Myle, That's an easy ( and rather obvious ) solution. I should have thought about that myself. Thanks for pointing it out!

    Jens.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    no probs

    been there done that
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •