Why does query take 1 minute or two hours to run?

Hi folks,

Working with a MS Access 2002-format database, I'm firing off the following
query (VB format):

SQL = "SELECT UI FROM Issues I RIGHT JOIN Update_Issues UI ON (I.FullIssue =
UI.FullIssue) AND (I.Title = UI.Title)" & vbCrLf & _
"WHERE I.FullIssue IS NULL" & vbCrLf & _
"ORDER BY UI.Title, UI.FullIssue;"

newRS.Open SQL, myConn, adOpenForwardOnly, adLockReadOnly

The tables in question contain about 250,000 issues, indexed on both
FullIssue and Title. In most cases, the query runs in under a minute. Every
so often, however, it takes about an hour or more to run. During that time,
the CPU is running at about 50%, memory is about 75% free, and the disk is
nearly silent.

What's going on? What system resource is being exhausted which is causing
the slowdown, and what can I do to prevent it?

Thanks for any help folks can offer. A bottle of bubbly goes to the first
person who help me get to the bottom of this one!

-Pete
--
Peter Bickford
Principal, Human Computing
.

Relevant Pages

  • Re: Why does query take either 1 minute or 2 hours to run?
    ... > query (VB format): ... the query runs in under a minute. ... > Thanks for any help folks can offer. ...
    (microsoft.public.vb.database.ado)
  • Why does query take either 1 minute or 2 hours to run?
    ... newRS.Open SQL, myConn, adOpenForwardOnly, adLockReadOnly ... the query runs in under a minute. ... Thanks for any help folks can offer. ...
    (microsoft.public.vb.database.ado)
  • Current Year criteria
    ... Am trying to create a query to retrieve records using the current year from ... Any help folks? ... Piers ...
    (microsoft.public.access.queries)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • Re: Populating a list -- table structure?
    ... I had made a report already and figured out about adding the ... your responce below, but thanks to your help with SQL, I was able to get the ... It takes a summary from a select query and gives the ... KitID, long integer ...
    (microsoft.public.access.forms)