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

Query slow to open - sometimes

P: n/a
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?
Aug 2 '08 #1
Share this Question
Share on Google+
9 Replies


P: n/a
On Aug 2, 12:27*am, "Bob Darlington" <b...@dpcnowhereman.com.au>
wrote:
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?
It is possible that the query optimizer is gathering statistics and
deciding on an execution plan the first time the query is run. On
successives runs of the query, the same execution plan is used--thus
speeding the query.

If there is a lot of insertion and deletion to the table, those
statistics need to be recalculated. Otherwise, performance would
suffer over time.

There is some useful information concerning the query optimizer at
http://support.microsoft.com/kb/209126.
Aug 2 '08 #2

P: n/a
Bob Darlington wrote:
>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?
For such a simple query, that is really slow. Maybe part of
the problem is that the memory cache has to be save to disk
before the table data can be loaded. Closing all other
programs/windows might give Access more RAM to work with.
Adding more RAM to your machine almost always helps a lot.

Double check to make sure that the LAN field is indexed in
both tables. Also index the PropNum field.

--
Marsh
Aug 2 '08 #3

P: n/a
Thanks for the reply. I'll have a look at the article.

<lo***************@gmail.comwrote in message
news:0f**********************************@j1g2000p rb.googlegroups.com...
On Aug 2, 12:27 am, "Bob Darlington" <b...@dpcnowhereman.com.au>
wrote:
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?
It is possible that the query optimizer is gathering statistics and
deciding on an execution plan the first time the query is run. On
successives runs of the query, the same execution plan is used--thus
speeding the query.

If there is a lot of insertion and deletion to the table, those
statistics need to be recalculated. Otherwise, performance would
suffer over time.

There is some useful information concerning the query optimizer at
http://support.microsoft.com/kb/209126.
Aug 3 '08 #4

P: n/a
Thanks Marsh.
I was toying with adding another 1GB RAM anyway, but do you think that
moving from 2 to 3 would have a significant effect?
Bob D

"Marshall Barton" <ma*********@wowway.comwrote in message
news:rt********************************@4ax.com...
Bob Darlington wrote:
>>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?

For such a simple query, that is really slow. Maybe part of
the problem is that the memory cache has to be save to disk
before the table data can be loaded. Closing all other
programs/windows might give Access more RAM to work with.
Adding more RAM to your machine almost always helps a lot.

Double check to make sure that the LAN field is indexed in
both tables. Also index the PropNum field.

--
Marsh

Aug 3 '08 #5

P: n/a
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?
Aug 3 '08 #6

P: n/a
2 GB is usually adequate (depending on what else you have
going on in your machine, e.g. IE), but 3 or 4 GB won't hurt
and it might(?) help. The reason I latched onto the index
and RAM aspects is because you said it is much faster the
second time you run the query.

OTOH, Lyle brings up some ineresting points, so think about
what he said.
--
Marsh
Bob Darlington wrote:.
>I was toying with adding another 1GB RAM anyway, but do you think that
moving from 2 to 3 would have a significant effect?
Bob D

"Marshall Barton" wrote
>Bob Darlington wrote:
>>>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?

For such a simple query, that is really slow. Maybe part of
the problem is that the memory cache has to be save to disk
before the table data can be loaded. Closing all other
programs/windows might give Access more RAM to work with.
Adding more RAM to your machine almost always helps a lot.

Double check to make sure that the LAN field is indexed in
both tables. Also index the PropNum field.
Aug 3 '08 #7

P: n/a
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?

Aug 4 '08 #8

P: n/a
Thanks again Marsh.
The fields were all indexed.
I wanted to upgrade the RAM anyway, so I'll be putting in 3GB to see what
happens.
Bob D

"Marshall Barton" <ma*********@wowway.comwrote in message
news:79********************************@4ax.com...
>2 GB is usually adequate (depending on what else you have
going on in your machine, e.g. IE), but 3 or 4 GB won't hurt
and it might(?) help. The reason I latched onto the index
and RAM aspects is because you said it is much faster the
second time you run the query.

OTOH, Lyle brings up some ineresting points, so think about
what he said.
--
Marsh
Bob Darlington wrote:.
>>I was toying with adding another 1GB RAM anyway, but do you think that
moving from 2 to 3 would have a significant effect?
Bob D

"Marshall Barton" wrote
>>Bob Darlington wrote:

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?
For such a simple query, that is really slow. Maybe part of
the problem is that the memory cache has to be save to disk
before the table data can be loaded. Closing all other
programs/windows might give Access more RAM to work with.
Adding more RAM to your machine almost always helps a lot.

Double check to make sure that the LAN field is indexed in
both tables. Also index the PropNum field.

Aug 4 '08 #9

P: n/a
Thanks everyone for you responses.
I eventually replaced the motherboard (or Intel did under warranty with no
problems whatsoever), and the problem has disappeared.
--
Bob Darlington
Brisbane
"Bob Darlington" <bo*@dpcnowhereman.com.auwrote in message
news:48**********************@news.optusnet.com.au ...
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?


Aug 11 '08 #10

This discussion thread is closed

Replies have been disabled for this discussion.