Q: 'DoEvents' on Access queries?

[MarkD]

Hi,

I use Access 2000. Every now and then, I'll have to run a
multi joined-table select query that takes somehwere
around 5-10 minutes to run. The problem is, Access seems
to be such a CPU hog with these queries. I'd be willing to
sacrifice a few minutes of query time if I could put a
DeEvents sort of thing in an Access query. Can that be
done?

Thanks,
-Mark

 

[M.L. Sco Scofield]

Nope. I'm afraid you're out of luck on this one.

If you can't take a coffee or potty break "every now and then" and there is
really something you can still do in this database that is *not* dependant
on this query completing:

1 - Look into breaking the query into pieces and doing it in code. You can
litter the code with DoEvent.

2 - Split the database as we do for all shared databases, and start a second
copy of the front-end database. Let one run in the background and work in
the other.

Also, unless you have a lot, like 500,000 or more, or a *very* slow
computer, this is a long time for a query to run. You might look into seeing
if it can be optimized a little.

Take a look at
http://support.microsoft.com/default.aspx?scid=kb;en-us;209126.

One of the most overlooked items is having the proper indexes. Not too few.
Not too many. Just the right number. (Oh, wait, that was about beds and
being too soft or too hard. Sorry about that.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Chris Mills]

"DoEvents" can be usefull in a code-loop. It gives Windows a chance to do
processing, such as displaying (your own) progress messages, or a chance to
analyse keystrokes either by Windows or your program, say an "Esc" key was hit
and what then to do?

It won't make your routine or query any faster. It might help to interrupt it
and ask whether to continue or abort in a controlled way (for instance).

If your query calls code written by yourself, there's a good chance you can
<CTRL><BREAK> into it. It's sorta chance, only usefull for developers forget
it for production install the last paragraph.

You seem to be worried about the 5-10 minutes. So have a joke on me:
<ring-ring>
Chris: "hello?"
Joe: "Your program locked up!"
Chris: "Ahhh...how long you run it?"
Joe: "5-10 minutes. It locked up! Task Manager says so!"
Chris: "Next time, leave it alone for 1/2hr or 1hr"
Joe: "You can't be SERIOUS" (he must have been John McEnroe)
Chris: "Hey, just do it for a test, go out to lunch or something. If it
completes Eventually, then maybe you need to compact like it says in my
manual)
Joe: (no feedback)

"Task Manager says so!"
Task Manager always says that regardless of what you do!

....but do make every effort to see if you can further "efficiencise" the
query...networks are a lot slower than local, and some ODBC connections can be
so slow as to be impractical, comparatively. DoEvents can't really speed
things up except to assist aborting or amusing them with progress messages.

Someone recently sent me an unsolicited 7mB e-mail enclosure. I'd like you to
assist me...
Chris

 

[Chris Mills]

Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

Hi Sco,
I've carefully preserved #1 to #14. You're up to #17, so what happened to #15
and #16? They are all gems.

Regards
Chris

 

[MarkD]

Hey Chris,

Thanks for the reply and joke (or is it based on a true
tech support story?).

Anyway, the query is pretty efficient enough. There's just
a bunch of records and it's on a network (can't change
that). I'm okay with it being slow, I was just hoping I
could do other stuff while the query hamster was running.

Maybe my boss will let me take a 5 minute nap while it
runs... I'll ask her.

-Mark

 

[MarkD]

Our company doesn't believe in potty breaks. Ever see that
picture of an office cubicle with a toilet inside? that's
how it is here.

seriously, yeah, I can take a potty break, but I have
trouble timing my day such that I'm running the query just
when I start to feel the urge to pee.

we can't really do much in terms of optimization. the
tables have the right indexes (PKs), it's on a netowk
(can't really change that). It'll take time, which is
fine, but well, the pinball game slows down too much when
I'm running the query.

Thanks for your help!
-Mark

 

[M.L. Sco Scofield]

Inline...

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

Our company doesn't believe in potty breaks. Ever see that
picture of an office cubicle with a toilet inside? that's
how it is here.

Ouch! I'm *really* sorry!

seriously, yeah, I can take a potty break, but I have
trouble timing my day such that I'm running the query just
when I start to feel the urge to pee.

Definitely a *personal* problem...

we can't really do much in terms of optimization. the
tables have the right indexes (PKs),

Are you saying that there are only indexes on the keys?

Or are you saying there are indexes on the keys *and* every field used for
any criteria and sorting?

Also, did you look at the article I put in the link to? There are some
things that force Jet to ignore the indexes and bring *every* record down
the wire to be processed. Sometimes they can be worked around. The article
talks about this.

it's on a network
(can't really change that).

Sorry again. Networks, arrrg. If you can prove that's the bottle neck, I
have a couple of clients that have switched to 1 gig. Although it does cost
a few $ to get new NICs and switches, it does make a *big* difference.

It'll take time, which is
fine, but well, the pinball game slows down too much when
I'm running the query.

Ah! *Now* it's clear!

Get a second computer to run pinball on. Two port KVM switches are *real*
cheap these days.

Thanks for your help!

You're welcome.

 

[M.L. Sco Scofield]

Chris,

My posting was down a little back when, so that may have been how you missed
#15.

However, I was using #16 for months. Not sure how you missed that one.

Glad you got #17. I won't be keeping this one very long. The double
"billion" keeps tripping the "duplicate word" message which is *real*
annoying.

Only two more to go! And the next one is my personal favorite.

OK. Are you ready?

#15 - 5 dialogues = 1 decalogue

# 16 - 2 monograms = 1 diagram

Enjoy...

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Chris Mills]

Only two more to go!

Ah well, you stretched it out years so you could always start again.
I'm not one to tell!

 

[M.L. Sco Scofield]

LOL!

It's late.

I'm going home.

Take care Chris.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Useful Metric Conversion #17 of 19: 1 billion billion picolos = 1 gigolo
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Chris Mills]

the pinball game slows down too much whenFWIW, I have 3 computers with a mechanical switch so as to use the same
monitor,kb,& mouse.
Made in Taiwan, $10 or something, you can lookup KVM on a search engine. I see
Sco is onto it, only one computer has a problem with the kb needing
re-plugging after mechanical switching...sigh...
And no I don't play pinball, neither can I really keep 3 fed...
-----
I don't know of a way (which would be in Windows) to set tasks to low
priority. In Windows3.1 I vaguely recall there was a setting like that...(but
I can't recall later events...)

(my story in a sub-thread was true and still bites me. It involves so many
sub-queries and processes that I got quite confused as to what indexes might
help-some customers have to archive once-a-month to keep the speed up ouch)
Chris