Lyle,
Many thanks for your detailed response.
The fields were indexed.
The query was a simplified version as you correctly guessed, but it was
tested in the form posted and does in fact work as indicated.
The '10' value returns 102 records as indicated in my post. But using
different criteria to return a different number of records has virtually no
effect.
I will look at your suggestion for SHOWPLAN over the next day or so.
However, since my original post, I have been able to test on another PC and
found no problems. The query opens virtually immediately.
So, I have changed RAM and reloaded the OS and all programs on to a new hard
drive so that I have a clean install of everything on my development PC.
And still have the identical problem.
"lyle fairfield" <ly************@gmail.comwrote in message
news:d5**********************************@x35g2000 hsb.googlegroups.com...
If you set the JETShowPlanJet registry value to ON
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engi nes\Debug]
"JETSHOWPLAN"="ON"
after a fast run and a slow run have occured you can examine the
SHOWPLAN.OUT file (text, open with notepad) in the documents folder or
the current directory and it's quite likely that you will see what the
problem is.
(
http://articles.techrepublic.com.com...1-5064388.html)
My 5% guess is that one or more of these fields, tTenants.LAN,
tTenantDetails.LAN, tTenants.PropNum, is not indexed and that you are
doing a table scan somewhere in the execution. Indexing all those
fields would solve that problem.
Another 2% possibility is that you change the "10" value and the query
is optimized for returning many fewer or many more records. A solution
to this is to use dynamic queries (query strings). I almost use almost
no saved JET queries for this reason.
A third possibility is that you have simplified or modifed the query
string for our benefit. Much of the time when someone does this the
cause of the problem is left out of the simplification. An example
would be where 10 is not a literal but a parameter. But I'm sure you
wouldn't do that, would you?
Intermittent hardware problems? Why not leprechauns? Houdini's ghost?
I have been databasing and programming for quite a few more than 25
years. 99.999999999999999999999999999999999999999999999 per cent of
all problems I have had are directly attributable to the same source:
ME!
On Aug 2, 3:27 am, "Bob Darlington" <b...@dpcnowhereman.com.auwrote:
The following query opens slowly the first time it is opened (6-7
seconds),
but then is less than one second for the next random number of openings
before slowing (6-7 seconds) again.
SELECT tTenantDetails.LAN, tTenants.Archive, tTenants.PropNum
FROM tTenants INNER JOIN tTenantDetails ON tTenants.LAN =
tTenantDetails.LAN
WHERE (((tTenants.PropNum)=10));
I'm using Access 2002 with XP Pro OS and 2GB RAM.
tTenants and tTenantDetails have a one to one relationship with a total of
only 550 odd records.
The query returns 102 records.
I've tried importing the tables into the front end for testing and get the
same result, so its not a link or network problem.
All relevant fields are indexed.
I'm starting to think intermitent hardware problems (RAM or CPU), but I'm
out of my depth here. Has anyone experienced similar problems?