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

I may have stated it badly: there is an index on Title, an index on
FullIssue, and I tried a grouped index on Title, FullIssue. None of this
seemed to have the slightest effect (nor did dropping all indexes).

If it helps anyone to help me: the problem appears to go away if I cut down
on the size of the data sets (e.g. SELECT UI FROM Issues I RIGHT JOIN
Update_Issues UI on (I.FullIssue = UI.FullIssue) nad (I.Title = UI.Title)
WHERE UI.Title < 'M' and I.FullIssue IS NULL ORDER BY UI.Title, UI.FullIssue"

followed by a second round where I test for >= 'M' and process each set
accordingly. In those cases, the query runs in about a second each.

What is there, however, which is choking so very hard when sifting through
250,000 records or so, and which runs instantly when sifting through 125,000
records? (It's not at all linear--it's either a couple of seconds or a couple
of hours to run--there is no in-between). Are we exceeding the allocation of
some hash table inside Jet? Or..?

-Pete
--
Peter Bickford
Principal, Human Computing


"DWS" wrote:

> Peter Bickford,
>
> You've got
> order by title, fullissue
> you need to be indexed by title, fullissue for a performant solution instead
> of fullissue, title.
>
> Good Luck
> DWS
>
>
> "Peter Bickford" wrote:
>
> > 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
.