473,289 Members | 1,840 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Query slow to open - sometimes

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
9 2960
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Brian Oster | last post by:
After applying security patch MS03-031 (Sql server ver 8.00.818) a query that used to execute in under 2 seconds, now takes over 8 Minutes to complete. Any ideas on what the heck might be going...
5
by: John Bailo | last post by:
I wrote a webservice to output a report file. The fields of the report are formatted based on information in an in-memory XmlDocument. As each row of a SqlDataReader are looped through, a...
7
by: Thomi Baechler | last post by:
Hello Everybody I run the following query against to identical databases. Execution time on the first DB is 0 seconds, on the other 6 seconds! SELECT dbo.HRMABZ.EMPKEY ,...
7
by: Egor Shipovalov | last post by:
I'm implementing paging through search results using cursors. Is there a better way to know total number of rows under a cursor than running a separate COUNT(*) query? I think PostgreSQL is bound...
1
by: Suzi Carr | last post by:
Hello, We create querydefs in VB programs (i.e. CreateQueryDef). But as illustrated below, Access regenerates the SQL code we specify -- particularly the WHERE clause. While the new code is...
6
by: Bill R via AccessMonster.com | last post by:
I have a query: SELECT tblCalendar.CalendarDay AS LastSunday FROM tblCalendar WHERE (((tblCalendar.CalendarDay)>=(Now()-7) And (tblCalendar.CalendarDay)...
4
by: shawno | last post by:
Hello, We are currently in the process of migrating our databases from a relic of a server to a new 4 processor dual-core box with 4 gigs of RAM. Overall, database performance is obviously...
8
by: SaltyBoat | last post by:
Needing to import and parse data from a large PDF file into an Access 2002 table: I start by converted the PDF file to a html file. Then I read this html text file, line by line, into a table...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.