473,372 Members | 871 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,372 software developers and data experts.

Query Design View is Slow to Open

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 to open in datasheet view. As an
experiment, I deleted all rows in all tables; after that, the query
took only seconds to open in both design view and datasheet view. From
these facts, I conclude that Access is evaluating the query when I go
to design view. Why would it do that?

The problem occurs each time I do a Compact and Repair. After I
compact the database, the first time I open my benchmark query in
design view takes 20 minutes. Each subsequent time, the query opens
instantly, until the next time I compact.

In the same database, there are many queries that open normally in
design view. I can't find any obvious differences between the slow
ones and the normal ones, except that the slow ones are generally
downstream of the normal ones.

This issue is killing my productivity. It can easily take hours to
make a trivial design change. Can anyone offer insight into what is
going on?
Thanks
-TC
Here are some additional notes:
- This is not a multi-user problem. It occurs even when I have the
database opened in exclusive mode.
- While I'm waiting for the query to open in design view, I can't
interrupt Access.
- The database is local (i.e. on my C drive).
- The troublesome queries are based entirely on local tables (i.e. no
linked tables).
- I cleaned the database by creating a new mdb file and importing all
objects into it.
- Subdatasheet Name is set to [None] for all tables.
- Name AutoCorrect is turned off.
- There are no crosstab queries or union queries anywhere in the
database.
- The problem doesn't seem to be related to the anti-virus software;
it is just as slow when anti-virus is disabled.
- My version of Jet is 4.0.9511.0, which supposedly does not suffer
from the bug described at <http://support.microsoft.com/?kbid=302496>.
- I'm running Access 2003 SP3 and Windows XP with a 2GHz dual-core
processor and 2GB of RAM.
- The computer doesn't have any resource-hogging processes slowing
down Access.
- While the query is opening, the Task Manager shows that Access is
using about 50% of the CPU cycles and progressively more memory, to
about 80MB.
- I've seen the same behavior on three different computers, each
configured very differently, so I doubt the problem is related to some
esoteric configuration thing.
- I've been doing research online, and I've followed-up on all the
performance tips I could find, including those at <http://
www.granite.ab.ca/access/performancefaq.htm>.
Oct 23 '08 #1
2 9790
On Oct 23, 7:15*pm, existential.philoso...@gmail.com wrote:
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 to open in datasheet view. As an
experiment, I deleted all rows in all tables; after that, the query
took only seconds to open in both design view and datasheet view. From
these facts, I conclude that Access is evaluating the query when I go
to design view. Why would it do that?

The problem occurs each time I do a Compact and Repair. After I
compact the database, the first time I open my benchmark query in
design view takes 20 minutes. Each subsequent time, the query opens
instantly, until the next time I compact.

In the same database, there are many queries that open normally in
design view. I can't find any obvious differences between the slow
ones and the normal ones, except that the slow ones are generally
downstream of the normal ones.

This issue is killing my productivity. It can easily take hours to
make a trivial design change. Can anyone offer insight into what is
going on?

Thanks
-TC

Here are some additional notes:
- This is not a multi-user problem. It occurs even when I have the
database opened in exclusive mode.
- While I'm waiting for the query to open in design view, I can't
interrupt Access.
- The database is local (i.e. on my C drive).
- The troublesome queries are based entirely on local tables (i.e. no
linked tables).
- I cleaned the database by creating a new mdb file and importing all
objects into it.
- Subdatasheet Name is set to [None] for all tables.
- Name AutoCorrect is turned off.
- There are no crosstab queries or union queries anywhere in the
database.
- The problem doesn't seem to be related to the anti-virus software;
it is just as slow when anti-virus is disabled.
- My version of Jet is 4.0.9511.0, which supposedly does not suffer
from the bug described at <http://support.microsoft.com/?kbid=302496>.
- I'm running Access 2003 SP3 and Windows XP with a 2GHz dual-core
processor and 2GB of RAM.
- The computer doesn't have any resource-hogging processes slowing
down Access.
- While the query is opening, the Task Manager shows that Access is
using about 50% of the CPU cycles and progressively more memory, to
about 80MB.
- I've seen the same behavior on three different computers, each
configured very differently, so I doubt the problem is related to some
esoteric configuration thing.
- I've been doing research online, and I've followed-up on all the
performance tips I could find, including those at <http://www.granite.ab.ca/access/performancefaq.htm>.
Try the following experiment. Turn off compacting the database on
close (or just don't do it manually) then close Access. Now open the
database again. If the problem recurrs close Access and open it a 3rd
time.
What we're looking for here is any evidence that the compact & repair
is anything to do with the problem as I don't believe it is. If I'm
right the slow query will continue to happen every time you run it
fresh from loading the DB.

What I think might be happening is that the query is poorly written -
either too much nesting or something similar. Once the tables are in
memory everything will happen much faster. 50% CPU time would be
appropriate in this case as would the enormous amount of memory being
used.

I suggest you post the troublesome query(ies) and some bright spark
will perhaps be able to spot something.
Oct 23 '08 #2
On Oct 23, 2:55*pm, daved <daved1...@googlemail.comwrote:
On Oct 23, 7:15*pm, existential.philoso...@gmail.com wrote:
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 to open in datasheet view. As an
experiment, I deleted all rows in all tables; after that, the query
took only seconds to open in both design view and datasheet view. From
these facts, I conclude that Access is evaluating the query when I go
to design view. Why would it do that?
The problem occurs each time I do a Compact and Repair. After I
compact the database, the first time I open my benchmark query in
design view takes 20 minutes. Each subsequent time, the query opens
instantly, until the next time I compact.
In the same database, there are many queries that open normally in
design view. I can't find any obvious differences between the slow
ones and the normal ones, except that the slow ones are generally
downstream of the normal ones.
This issue is killing my productivity. It can easily take hours to
make a trivial design change. Can anyone offer insight into what is
going on?
Thanks
-TC
Here are some additional notes:
- This is not a multi-user problem. It occurs even when I have the
database opened in exclusive mode.
- While I'm waiting for the query to open in design view, I can't
interrupt Access.
- The database is local (i.e. on my C drive).
- The troublesome queries are based entirely on local tables (i.e. no
linked tables).
- I cleaned the database by creating a new mdb file and importing all
objects into it.
- Subdatasheet Name is set to [None] for all tables.
- Name AutoCorrect is turned off.
- There are no crosstab queries or union queries anywhere in the
database.
- The problem doesn't seem to be related to the anti-virus software;
it is just as slow when anti-virus is disabled.
- My version of Jet is 4.0.9511.0, which supposedly does not suffer
from the bug described at <http://support.microsoft.com/?kbid=302496>..
- I'm running Access 2003 SP3 and Windows XP with a 2GHz dual-core
processor and 2GB of RAM.
- The computer doesn't have any resource-hogging processes slowing
down Access.
- While the query is opening, the Task Manager shows that Access is
using about 50% of the CPU cycles and progressively more memory, to
about 80MB.
- I've seen the same behavior on three different computers, each
configured very differently, so I doubt the problem is related to some
esoteric configuration thing.
- I've been doing research online, and I've followed-up on all the
performance tips I could find, including those at <http://www.granite.ab.ca/access/performancefaq.htm>.

Try the following experiment. Turn off compacting the database on
close (or just don't do it manually) then close Access. Now open the
database again. If the problem recurrs close Access and open it a 3rd
time.
What we're looking for here is any evidence that the compact & repair
is anything to do with the problem as I don't believe it is. If I'm
right the slow query will continue to happen every time you run it
fresh from loading the DB.

What I think might be happening is that the query is poorly written -
either too much nesting or something similar. Once the tables are in
memory everything will happen much faster. 50% CPU time would be
appropriate in this case as would the enormous amount of memory being
used.

I suggest you post the troublesome query(ies) and some bright spark
will perhaps be able to spot something.
Daved,

Thank you for your advice.

I've determined that, after opening the query once, I can close and
reopen the database any number of times without resetting the queries
and making them slow to open in design view again. The queries get
reset 1) when I do a Compact & Repair; or 2) when I close and reopen
Access. I agree with you that Compact & Repair probably doesn't have
anything to do with the problem, except to the extent that it flushes
query meta-data out of memory.

I'd like to know more about why you think too much nesting might be
the problem. There is a lot of nesting in this case, and I'm willing
to accept that that may be the culprit, but I don't understand why. In
the past, I've seen nesting make queries fail to evaluate, but I've
never seen it affect design view.

Your suggestion that I post the queries is a good one. I've posted SQL
for two queries below. HOUSEHOLD_PersonalInfo opens slowly in design
view; PERSON_RO opens quickly. Note that PERSON_RO is created from 21
other queries, each in a 1:1 relationship. I haven't posted SQL for
those 21 queries, but all of them are fairly shallow. I'd estimate
that HOUSEHOLD_PersonalInfo is created from a total of 30 nested
queries and 15 tables. Does that cross the threshold of "too much
nesting"?

-TC
HOUSEHOLD_PersonalInfo
----------------------
SELECT PERSON_RO.HOUSEHOLD_ID, PERSON_RO.LAST_NAME,
PERSON_RO.FIRST_NAME, PERSON_RO.MIDDLE_NAME, PERSON_RO.PREFIX,
PERSON_RO.PREFERRED_ADDRESS, PERSON_RO.JOINT_PERSON, PERSON_RO.SSN,
PERSON_RO.PERSON_STATUS, PERSON_RO.GENDER, PERSON_RO.MARITAL_STATUS,
PERSON_RO.BIRTH_DATE, PERSON_RO.PREFERRED_NAME,
PERSON_RO.PERSON_ADD_OPERATOR, PERSON_RO.SPOUSE,
PERSON_RO.SPOUSE_LAST_NAME, PERSON_RO.SPOUSE_FIRST_NAME,
PERSON_RO.NICKNAME, PERSON_RO.ETHNIC,
PERSON_RO.PERSON_CHANGE_OPERATOR, PERSON_RO.PERSON_CHANGE_DATE,
PERSON_RO.PERSON_ADD_DATE, PERSON_RO.SUFFIX,
PERSON_RO.PERSON_NATIVE_LANGUAGE, PERSON_RO.ANONYMOUS,
PERSON_RO.DECEASED_DATE, PERSON_RO.BIRTH_NAME_LAST,
PERSON_RO.BIRTH_NAME_FIRST, PERSON_RO.BIRTH_NAME_MIDDLE,
PERSON_RO.PREFERRED_RESIDENCE, PERSON_RO.PERSON_BEN_ID,
PERSON_RO.INCOME_LEVEL, PERSON_RO.PREF_BUS_ADDRESS,
PERSON_RO.PERSON_COUNTRY_ENTRY_DATE, PERSON_RO.PREMIUM_PREFERENCE,
PERSON_RO.PERSON_CORP_INDICATOR, PERSON_RO.RESIDENCE_COUNTRY,
PERSON_RO.BOX, PERSON_RO.PERSON_DONOR_TYPE, PERSON_RO.PERSON_GROUP_ID,
PERSON_RO.PREFERRED_LISTING, PERSON_RO.OBITUARY,
PERSON_RO.MEMORIAL_TYPE, PERSON_RO.PERSON_ORIGIN_DATE,
PERSON_RO.PERSON_ORIGIN_CODE, PERSON_RO.PERSON_USER1,
PERSON_RO.PERSON_USER2, PERSON_RO.PERSON_USER3,
PERSON_RO.PERSON_USER4, PERSON_RO.PERSON_USER5,
PERSON_RO.PERSON_USER_CHANGED_BY, PERSON_RO.PERSON_USER_CHANGED_DATE,
PERSON_RO.PERSON_USER6, PERSON_RO.PERSON_USER7,
PERSON_RO.PERSON_USER8, PERSON_RO.PERSON_USER9,
PERSON_RO.PERSON_USER10, PERSON_RO.PERSON_TP_CODE,
PERSON_RO.PERSON_TP_DATE, PERSON_RO.PERSON_TP_TIME,
PERSON_RO.PERSON_TP_CAMPAIGN, PERSON_RO.PERSON_MERGED_TO_ID,
PERSON_RO.ANNUITY_ADDRESS, PERSON_RO.PERSON_BIRTH_PLACE,
PERSON_RO.DENOMINATION, PERSON_RO.RFA_SEGMENT, PERSON_RO.PERSON_VIP,
PERSON_RO.POLITICAL_PARTY, PERSON_RO.VISA_ISSUED_DATE,
PERSON_RO.OCCUPATION, PERSON_RO.PERSON_OVERRIDE_SALUTATION,
PERSON_RO.PERSON_FAMILY_SIZE, PERSON_RO.GUARDIANS,
PERSON_RO.PERSON_OVRL_EMP_STAT, PERSON_RO.PARTICIPANT_TYPE,
PERSON_RO.RESIDENCE_COUNTY, PERSON_RO.RESIDENCE_STATE,
PERSON_RO.VISA_TYPE, PERSON_RO.VISA_EXP_DATE, PERSON_RO.ALIEN_ID,
PERSON_RO.ALIEN_FLAG, PERSON_RO.SELECTIVE_SERVICE_FLAG,
PERSON_RO.SELECTIVE_SERVICE_NUMBER, PERSON_RO.CITIZENSHIP,
PERSON_RO.EMER_CONTACT_NAME, PERSON_RO.EMER_CONTACT_PHONE,
PERSON_RO.DIRECTORY_FLAG, PERSON_RO.PRIVACY_FLAG,
PERSON_RO.IMMIGRATION_STATUS, PERSON_RO.PREF_EMPLOYMENT,
PERSON_RO.PERSON_PRIMARY_LANGUAGE, PERSON_RO.AARS,
PERSON_RO.DRIVER_LICENSE_NO, PERSON_RO.DRIVER_LICENSE_STATE,
PERSON_RO.PERSON_HOME_LANG_SCH_NO_YRS,
PERSON_RO.PERSON_HOME_LANG_SCH_COUNTRY, PERSON_RO.PERSON_ACHIEVEMENTS,
PERSON_RO.PERSON_WEBSITE_ADDRESS, PERSON_RO.PERSON_CAMPUS_ORGS_ID,
PERSON_RO.SPOUSE_NICKNAME, PERSON_RO.ADDRESS_LINE1,
PERSON_RO.ADDRESS_LINE2, PERSON_RO.ADDRESS_LINE3, PERSON_RO.CITY,
PERSON_RO.STATE, PERSON_RO.ZIP, PERSON_RO.COUNTY, PERSON_RO.COUNTRY,
PERSON_RO.CTRY_DESC_UC, PERSON_RO.FYTD_RECOG_AMT,
PERSON_RO.CAMPAIGN_GIFT_IN_RANGE, PERSON_RO.REUNION_CLASS,
PERSON_RO.SOURCE_CODE_1, PERSON_RO.SOURCE_CODE_2,
PERSON_RO.SOURCE_CODE_3, PERSON_RO.SPOUSE_REUNION_CLASS,
PERSON_RO.RE_MAIL_RULE, PERSON_RO.LatestDonorDate,
PERSON_RO.LATEST_HARD_CREDIT_AMT, PERSON_RO.LATEST_SOFT_CREDIT_AMT,
PERSON_RO.LATEST_DESIGNATION_LIST, PERSON_RO.Salutation,
PERSON_RO.CA_MailLabel1, PERSON_RO.CA_MailLabel2,
PERSON_RO.SPOUSE_SOURCE_CODE_1, PERSON_RO.SPOUSE_SOURCE_CODE_2,
PERSON_RO.SPOUSE_SOURCE_CODE_3, PERSON_RO.SORT_NAME, PERSON_RO.TPA,
PERSON_RO.YATPA
FROM PERSON_RO
WHERE (((PERSON_RO.HOUSEHOLD_ID)=[ID]));

PERSON_RO
---------
SELECT PERSON_RW.*, PERSON_FYTD.FYTD_RECOG_AMT,
PERSON_FYTDRangeStart.FYTD_RANGE_START,
PERSON_FYTDRangeEnd.FYTD_RANGE_END, Not
IsNull(PERSON_ISTRUSTEE.PERSON_ID) AS IS_TRUSTEE, Not
IsNull(PERSON_CampaignGiftInRange.PERSON_ID) AS
CAMPAIGN_GIFT_IN_RANGE, PERSON_ReunionClass.REUNION_CLASS,
PERSON_Source1.SOURCE_CODE_1, PERSON_Source2.SOURCE_CODE_2,
PERSON_Source3.SOURCE_CODE_3,
PERSON_SpouseReunionClass.SPOUSE_REUNION_CLASS,
PERSON_ReMailRule.RE_MAIL_RULE,
PERSON_LatestDonorDate.LatestDonorDate,
PERSON_LatestHardCreditAmt.LATEST_HARD_CREDIT_AMT,
PERSON_LatestHardCreditAmt.LATEST_SOFT_CREDIT_AMT,
PERSON_LatestHardCreditAmt.LATEST_DESIGNATION_LIST ,
PERSON_Salutation.Salutation, tblFinalMailLabels.CA_MailLabel1,
tblFinalMailLabels.CA_MailLabel2, PERSON_HouseholdID.HOUSEHOLD_ID,
PERSON_SpouseSources.SPOUSE_SOURCE_CODE_1,
PERSON_SpouseSources.SPOUSE_SOURCE_CODE_2,
PERSON_SpouseSources.SPOUSE_SOURCE_CODE_3, Not IsNull([PERSON_TPA].
[ID]) AS TPA, Not IsNull([PERSON_YATPA].[ID]) AS YATPA, Not
IsNull([PERSON_IsFoundation].[PERSON_ID]) AS IS_FOUNDATION
FROM (((((((((((((((((((PERSON_RW LEFT JOIN PERSON_FYTD ON
PERSON_RW.ID = PERSON_FYTD.COND_DONOR) LEFT JOIN PERSON_ISTRUSTEE ON
PERSON_RW.ID = PERSON_ISTRUSTEE.PERSON_ID) LEFT JOIN
PERSON_CampaignGiftInRange ON PERSON_RW.ID =
PERSON_CampaignGiftInRange.PERSON_ID) LEFT JOIN PERSON_ReunionClass ON
PERSON_RW.ID = PERSON_ReunionClass.PERSON_ID) LEFT JOIN PERSON_Source1
ON PERSON_RW.ID = PERSON_Source1.PERSON_ID) LEFT JOIN PERSON_Source2
ON PERSON_RW.ID = PERSON_Source2.PERSON_ID) LEFT JOIN PERSON_Source3
ON PERSON_RW.ID = PERSON_Source3.PERSON_ID) LEFT JOIN
PERSON_SpouseReunionClass ON PERSON_RW.ID =
PERSON_SpouseReunionClass.PERSON_ID) LEFT JOIN PERSON_ReMailRule ON
PERSON_RW.ID = PERSON_ReMailRule.PERSON_ID) LEFT JOIN
PERSON_LatestHardCreditAmt ON PERSON_RW.ID =
PERSON_LatestHardCreditAmt.PERSON_ID) LEFT JOIN PERSON_Salutation ON
PERSON_RW.ID = PERSON_Salutation.ID) LEFT JOIN tblFinalMailLabels ON
PERSON_RW.ID = tblFinalMailLabels.ID) LEFT JOIN PERSON_LatestDonorDate
ON PERSON_RW.ID = PERSON_LatestDonorDate.PERSON_ID) LEFT JOIN
PERSON_HouseholdID ON PERSON_RW.ID = PERSON_HouseholdID.PERSON_ID)
LEFT JOIN PERSON_SpouseSources ON PERSON_RW.ID =
PERSON_SpouseSources.ID) LEFT JOIN PERSON_FYTDRangeStart ON
PERSON_RW.ID = PERSON_FYTDRangeStart.PERSON_ID) LEFT JOIN
PERSON_FYTDRangeEnd ON PERSON_RW.ID = PERSON_FYTDRangeEnd.PERSON_ID)
LEFT JOIN PERSON_TPA ON PERSON_RW.ID = PERSON_TPA.ID) LEFT JOIN
PERSON_YATPA ON PERSON_RW.ID = PERSON_YATPA.ID) LEFT JOIN
PERSON_IsFoundation ON PERSON_RW.ID = PERSON_IsFoundation.PERSON_ID;
Oct 24 '08 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Peter Royle | last post by:
I am working with VB.NET. It has just started losing my design view of a form when I open a project. If the design view was open when I last closed it, the view is there, but the Solution Explorer...
6
by: NB | last post by:
Hi Is there any way to call up the query design view from code? In my compiled-as-MDE app everything is hidden from end users. However, I want advanced user to have access to the query design...
0
by: Martin Lacoste | last post by:
Seems like a trifling thing, but it gets annoying after a while... Whenever I create a new query, I'm always needing to resize the window - the top half is always too small. Is there any way to...
2
by: Ray | last post by:
Stop me if you've heard this, but I am running Access 2002 and all of a sudden, if I design a particular form (it's been working fine for ages), Access crashes rather than open it in design view. ...
2
by: Michael Donahoe | last post by:
I have a database that I inherited that has query in it that has hidden fields when it is in design view. Datasheet view shows the fields and when I switch back to design view it appears as though...
1
by: Luis Ferrao | last post by:
Happy new year everyone. I'm building a templated website. For each individual content page I have a UserControl that holds the content. The UserControl inherits from a custom PlaceHolder...
2
by: NowItsWhatever | last post by:
In query DESIGN view, how do I automatically "fit" the columns in the table/field grid to the lengths of the table and field names (including any functions applied to the fields). I am not talking...
3
by: mnjkahn via AccessMonster.com | last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I right click on the field name in Query Design View, and then click Build, Access crashes before the Build window...
2
by: buddyr | last post by:
example: field1 bblandscape, wallmart, kmart field2 4,6,9 field3 5,7,6 field4 12,0,5 field5 if field2 > field3 then field5 = "Yes" if field4 = 0 then field5 = "Yes"
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.