By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Better multitasking when running queries?

P: n/a
Access tends to take over my machine when I run long queries. The
queries in question are Append queries.

Some of the jobs we're talking about run 45 minutes to an hour...so
the loss in productivity is significant.

When I pop TaskManager, it looks like it's only running 10-20%
capacity - so I'm guessing the horsepower is there but other apps just
aren't getting their shots at the CPU.

Any suggestions for remedying the situation besides
Start|Settings|Control Panel|System|Advanced|Performance
Options|Optimize performance for Applications?
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 15 Apr 2004 05:00:59 -0700, Go**********@FatBelly.com
(PeteCresswell) wrote:

After all these years, multitasking in Windows is still a joke. Not
much you can do about that. You can optimize the app, but you likely
already did that. Still it would perhaps help to show your
implementation to another strong Access developer and discuss
alternatives. You can shell out $1000 and buy a new computer just to
run these queries on. You'll easily gain it back in increased
productivity.

-Tom.

Access tends to take over my machine when I run long queries. The
queries in question are Append queries.

Some of the jobs we're talking about run 45 minutes to an hour...so
the loss in productivity is significant.

When I pop TaskManager, it looks like it's only running 10-20%
capacity - so I'm guessing the horsepower is there but other apps just
aren't getting their shots at the CPU.

Any suggestions for remedying the situation besides
Start|Settings|Control Panel|System|Advanced|Performance
Options|Optimize performance for Applications?


Nov 12 '05 #2

P: n/a
Go**********@FatBelly.com (PeteCresswell) wrote in message news:<74**************************@posting.google. com>...
Access tends to take over my machine when I run long queries. The
queries in question are Append queries.


Are these Jet SQL? You might try doing the process in code. While it
may seem counter-intuitive, I've found situations where a code (DAO)
solution actually runs faster than a SQL one. Pretty rare but depends
on the query and the attributes of the box it runs on and your
environment.
Nov 12 '05 #3

P: n/a
Go**********@FatBelly.com (PeteCresswell) wrote in message news:<74**************************@posting.google. com>...
Access tends to take over my machine when I run long queries. The
queries in question are Append queries.

Some of the jobs we're talking about run 45 minutes to an hour...so
the loss in productivity is significant.

When I pop TaskManager, it looks like it's only running 10-20%
capacity - so I'm guessing the horsepower is there but other apps just
aren't getting their shots at the CPU.

Any suggestions for remedying the situation besides
Start|Settings|Control Panel|System|Advanced|Performance
Options|Optimize performance for Applications?


Re-write the query using VBA code where you have a little more control
over the processer. The append process may be slower (or maybe not). A
little experimentation is required....
Nov 12 '05 #4

P: n/a
mi****@execpc.com (almish) wrote in message news:<95**************************@posting.google. com>...
Go**********@FatBelly.com (PeteCresswell) wrote in message news:<74**************************@posting.google. com>...
Access tends to take over my machine when I run long queries. The
queries in question are Append queries.

Some of the jobs we're talking about run 45 minutes to an hour...so
the loss in productivity is significant.

When I pop TaskManager, it looks like it's only running 10-20%
capacity - so I'm guessing the horsepower is there but other apps just
aren't getting their shots at the CPU.

Any suggestions for remedying the situation besides
Start|Settings|Control Panel|System|Advanced|Performance
Options|Optimize performance for Applications?


Re-write the query using VBA code where you have a little more control
over the processer. The append process may be slower (or maybe not). A
little experimentation is required....


Although I'm no expert, I strongly agree with this suggestion. I had a similar
situation with a SQL statement that tried to do too much. The query took 45
minutes to run. I changed the form so that it wrote basic information to a
temporary table instead. The code behind the form handled the logic that
required IIF statements in SQL. Then I changed the report to use the temporary
table as a Record Source. The new implementation took 45 seconds to run so no
new computers were needed :-). I wasn't running an append query, but you
can probably expect a significant speed increase by using VBA.

Pete, who seems to be quite proficient in VBA, should look into changing the
design of any query that takes 45 minutes. I remember when using Access 2.0
(has it been 10 years already?) I used to redesign any query that took longer than
two seconds to run.

James A. Fortune
Nov 12 '05 #5

P: n/a
mi****@execpc.com (almish) wrote in message news:<95**************************@posting.google. com>...
Go**********@FatBelly.com (PeteCresswell) wrote in message news:<74**************************@posting.google. com>...
Access tends to take over my machine when I run long queries. The
queries in question are Append queries.

Some of the jobs we're talking about run 45 minutes to an hour...so
the loss in productivity is significant.

When I pop TaskManager, it looks like it's only running 10-20%
capacity - so I'm guessing the horsepower is there but other apps just
aren't getting their shots at the CPU.

Any suggestions for remedying the situation besides
Start|Settings|Control Panel|System|Advanced|Performance
Options|Optimize performance for Applications?


Re-write the query using VBA code where you have a little more control
over the processer. The append process may be slower (or maybe not). A
little experimentation is required....


Although I'm no expert, I strongly agree with this suggestion. I had a similar
situation with a SQL statement that tried to do too much. The query took 45
minutes to run. I changed the form so that it wrote basic information to a
temporary table instead. The code behind the form handled the logic that
required IIF statements in SQL. Then I changed the report to use the temporary
table as a Record Source. The new implementation took 45 seconds to run so no
new computers were needed :-). I wasn't running an append query, but you
can probably expect a significant speed increase by using VBA.

Pete, who seems to be quite proficient in VBA, should look into changing the
design of any query that takes 45 minutes. I remember when using Access 2.0
(has it been 10 years already?) I used to redesign any query that took longer than
two seconds to run.

James A. Fortune
Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.