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;
