Query Design View is Slow to Open

**daved** · Oct 23 '08, 09:55 PM

Re: Query Design View is Slow to Open

On Oct 23, 7:15 pm, existential.phi loso...@gmail.c om 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.microso ft.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.

**existential.philosophy@gmail.com** · Oct 24 '08, 03:35 AM

Re: Query Design View is Slow to Open

On Oct 23, 2:55 pm, daved <daved1...@goog lemail.comwrote :

On Oct 23, 7:15 pm, existential.phi loso...@gmail.c om 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.microso ft.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_Perso nalInfo 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_Perso nalInfo is created from a total of 30 nested
queries and 15 tables. Does that cross the threshold of "too much
nesting"?

-TC

HOUSEHOLD_Perso nalInfo
----------------------
SELECT PERSON_RO.HOUSE HOLD_ID, PERSON_RO.LAST_ NAME,
PERSON_RO.FIRST _NAME, PERSON_RO.MIDDL E_NAME, PERSON_RO.PREFI X,
PERSON_RO.PREFE RRED_ADDRESS, PERSON_RO.JOINT _PERSON, PERSON_RO.SSN,
PERSON_RO.PERSO N_STATUS, PERSON_RO.GENDE R, PERSON_RO.MARIT AL_STATUS,
PERSON_RO.BIRTH _DATE, PERSON_RO.PREFE RRED_NAME,
PERSON_RO.PERSO N_ADD_OPERATOR, PERSON_RO.SPOUS E,
PERSON_RO.SPOUS E_LAST_NAME, PERSON_RO.SPOUS E_FIRST_NAME,
PERSON_RO.NICKN AME, PERSON_RO.ETHNI C,
PERSON_RO.PERSO N_CHANGE_OPERAT OR, PERSON_RO.PERSO N_CHANGE_DATE,
PERSON_RO.PERSO N_ADD_DATE, PERSON_RO.SUFFI X,
PERSON_RO.PERSO N_NATIVE_LANGUA GE, PERSON_RO.ANONY MOUS,
PERSON_RO.DECEA SED_DATE, PERSON_RO.BIRTH _NAME_LAST,
PERSON_RO.BIRTH _NAME_FIRST, PERSON_RO.BIRTH _NAME_MIDDLE,
PERSON_RO.PREFE RRED_RESIDENCE, PERSON_RO.PERSO N_BEN_ID,
PERSON_RO.INCOM E_LEVEL, PERSON_RO.PREF_ BUS_ADDRESS,
PERSON_RO.PERSO N_COUNTRY_ENTRY _DATE, PERSON_RO.PREMI UM_PREFERENCE,
PERSON_RO.PERSO N_CORP_INDICATO R, PERSON_RO.RESID ENCE_COUNTRY,
PERSON_RO.BOX, PERSON_RO.PERSO N_DONOR_TYPE, PERSON_RO.PERSO N_GROUP_ID,
PERSON_RO.PREFE RRED_LISTING, PERSON_RO.OBITU ARY,
PERSON_RO.MEMOR IAL_TYPE, PERSON_RO.PERSO N_ORIGIN_DATE,
PERSON_RO.PERSO N_ORIGIN_CODE, PERSON_RO.PERSO N_USER1,
PERSON_RO.PERSO N_USER2, PERSON_RO.PERSO N_USER3,
PERSON_RO.PERSO N_USER4, PERSON_RO.PERSO N_USER5,
PERSON_RO.PERSO N_USER_CHANGED_ BY, PERSON_RO.PERSO N_USER_CHANGED_ DATE,
PERSON_RO.PERSO N_USER6, PERSON_RO.PERSO N_USER7,
PERSON_RO.PERSO N_USER8, PERSON_RO.PERSO N_USER9,
PERSON_RO.PERSO N_USER10, PERSON_RO.PERSO N_TP_CODE,
PERSON_RO.PERSO N_TP_DATE, PERSON_RO.PERSO N_TP_TIME,
PERSON_RO.PERSO N_TP_CAMPAIGN, PERSON_RO.PERSO N_MERGED_TO_ID,
PERSON_RO.ANNUI TY_ADDRESS, PERSON_RO.PERSO N_BIRTH_PLACE,
PERSON_RO.DENOM INATION, PERSON_RO.RFA_S EGMENT, PERSON_RO.PERSO N_VIP,
PERSON_RO.POLIT ICAL_PARTY, PERSON_RO.VISA_ ISSUED_DATE,
PERSON_RO.OCCUP ATION, PERSON_RO.PERSO N_OVERRIDE_SALU TATION,
PERSON_RO.PERSO N_FAMILY_SIZE, PERSON_RO.GUARD IANS,
PERSON_RO.PERSO N_OVRL_EMP_STAT , PERSON_RO.PARTI CIPANT_TYPE,
PERSON_RO.RESID ENCE_COUNTY, PERSON_RO.RESID ENCE_STATE,
PERSON_RO.VISA_ TYPE, PERSON_RO.VISA_ EXP_DATE, PERSON_RO.ALIEN _ID,
PERSON_RO.ALIEN _FLAG, PERSON_RO.SELEC TIVE_SERVICE_FL AG,
PERSON_RO.SELEC TIVE_SERVICE_NU MBER, PERSON_RO.CITIZ ENSHIP,
PERSON_RO.EMER_ CONTACT_NAME, PERSON_RO.EMER_ CONTACT_PHONE,
PERSON_RO.DIREC TORY_FLAG, PERSON_RO.PRIVA CY_FLAG,
PERSON_RO.IMMIG RATION_STATUS, PERSON_RO.PREF_ EMPLOYMENT,
PERSON_RO.PERSO N_PRIMARY_LANGU AGE, PERSON_RO.AARS,
PERSON_RO.DRIVE R_LICENSE_NO, PERSON_RO.DRIVE R_LICENSE_STATE ,
PERSON_RO.PERSO N_HOME_LANG_SCH _NO_YRS,
PERSON_RO.PERSO N_HOME_LANG_SCH _COUNTRY, PERSON_RO.PERSO N_ACHIEVEMENTS,
PERSON_RO.PERSO N_WEBSITE_ADDRE SS, PERSON_RO.PERSO N_CAMPUS_ORGS_I D,
PERSON_RO.SPOUS E_NICKNAME, PERSON_RO.ADDRE SS_LINE1,
PERSON_RO.ADDRE SS_LINE2, PERSON_RO.ADDRE SS_LINE3, PERSON_RO.CITY,
PERSON_RO.STATE , PERSON_RO.ZIP, PERSON_RO.COUNT Y, PERSON_RO.COUNT RY,
PERSON_RO.CTRY_ DESC_UC, PERSON_RO.FYTD_ RECOG_AMT,
PERSON_RO.CAMPA IGN_GIFT_IN_RAN GE, PERSON_RO.REUNI ON_CLASS,
PERSON_RO.SOURC E_CODE_1, PERSON_RO.SOURC E_CODE_2,
PERSON_RO.SOURC E_CODE_3, PERSON_RO.SPOUS E_REUNION_CLASS ,
PERSON_RO.RE_MA IL_RULE, PERSON_RO.Lates tDonorDate,
PERSON_RO.LATES T_HARD_CREDIT_A MT, PERSON_RO.LATES T_SOFT_CREDIT_A MT,
PERSON_RO.LATES T_DESIGNATION_L IST, PERSON_RO.Salut ation,
PERSON_RO.CA_Ma ilLabel1, PERSON_RO.CA_Ma ilLabel2,
PERSON_RO.SPOUS E_SOURCE_CODE_1 , PERSON_RO.SPOUS E_SOURCE_CODE_2 ,
PERSON_RO.SPOUS E_SOURCE_CODE_3 , PERSON_RO.SORT_ NAME, PERSON_RO.TPA,
PERSON_RO.YATPA
FROM PERSON_RO
WHERE (((PERSON_RO.HO USEHOLD_ID)=[ID]));

PERSON_RO
---------
SELECT PERSON_RW.*, PERSON_FYTD.FYT D_RECOG_AMT,
PERSON_FYTDRang eStart.FYTD_RAN GE_START,
PERSON_FYTDRang eEnd.FYTD_RANGE _END, Not
IsNull(PERSON_I STRUSTEE.PERSON _ID) AS IS_TRUSTEE, Not
IsNull(PERSON_C ampaignGiftInRa nge.PERSON_ID) AS
CAMPAIGN_GIFT_I N_RANGE, PERSON_ReunionC lass.REUNION_CL ASS,
PERSON_Source1. SOURCE_CODE_1, PERSON_Source2. SOURCE_CODE_2,
PERSON_Source3. SOURCE_CODE_3,
PERSON_SpouseRe unionClass.SPOU SE_REUNION_CLAS S,
PERSON_ReMailRu le.RE_MAIL_RULE ,
PERSON_LatestDo norDate.LatestD onorDate,
PERSON_LatestHa rdCreditAmt.LAT EST_HARD_CREDIT _AMT,
PERSON_LatestHa rdCreditAmt.LAT EST_SOFT_CREDIT _AMT,
PERSON_LatestHa rdCreditAmt.LAT EST_DESIGNATION _LIST,
PERSON_Salutati on.Salutation, tblFinalMailLab els.CA_MailLabe l1,
tblFinalMailLab els.CA_MailLabe l2, PERSON_Househol dID.HOUSEHOLD_I D,
PERSON_SpouseSo urces.SPOUSE_SO URCE_CODE_1,
PERSON_SpouseSo urces.SPOUSE_SO URCE_CODE_2,
PERSON_SpouseSo urces.SPOUSE_SO URCE_CODE_3, Not IsNull([PERSON_TPA].
[ID]) AS TPA, Not IsNull([PERSON_YATPA].[ID]) AS YATPA, Not
IsNull([PERSON_IsFounda tion].[PERSON_ID]) AS IS_FOUNDATION
FROM ((((((((((((((( ((((PERSON_RW LEFT JOIN PERSON_FYTD ON
PERSON_RW.ID = PERSON_FYTD.CON D_DONOR) LEFT JOIN PERSON_ISTRUSTE E ON
PERSON_RW.ID = PERSON_ISTRUSTE E.PERSON_ID) LEFT JOIN
PERSON_Campaign GiftInRange ON PERSON_RW.ID =
PERSON_Campaign GiftInRange.PER SON_ID) LEFT JOIN PERSON_ReunionC lass ON
PERSON_RW.ID = PERSON_ReunionC lass.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_SpouseRe unionClass ON PERSON_RW.ID =
PERSON_SpouseRe unionClass.PERS ON_ID) LEFT JOIN PERSON_ReMailRu le ON
PERSON_RW.ID = PERSON_ReMailRu le.PERSON_ID) LEFT JOIN
PERSON_LatestHa rdCreditAmt ON PERSON_RW.ID =
PERSON_LatestHa rdCreditAmt.PER SON_ID) LEFT JOIN PERSON_Salutati on ON
PERSON_RW.ID = PERSON_Salutati on.ID) LEFT JOIN tblFinalMailLab els ON
PERSON_RW.ID = tblFinalMailLab els.ID) LEFT JOIN PERSON_LatestDo norDate
ON PERSON_RW.ID = PERSON_LatestDo norDate.PERSON_ ID) LEFT JOIN
PERSON_Househol dID ON PERSON_RW.ID = PERSON_Househol dID.PERSON_ID)
LEFT JOIN PERSON_SpouseSo urces ON PERSON_RW.ID =
PERSON_SpouseSo urces.ID) LEFT JOIN PERSON_FYTDRang eStart ON
PERSON_RW.ID = PERSON_FYTDRang eStart.PERSON_I D) LEFT JOIN
PERSON_FYTDRang eEnd ON PERSON_RW.ID = PERSON_FYTDRang eEnd.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_IsFounda tion ON PERSON_RW.ID = PERSON_IsFounda tion.PERSON_ID;

Query Design View is Slow to Open

Query Design View is Slow to Open

Comment

Comment