Query stops responding Access97

[Tim Dolloff]

I'm using Access97 on Windows NT(although I've run into
this same issue on Windows 98 and 2000). I have a query
that is pulling 70 columns of data from 17 tables and
queries. All of the tables are leftjoined to a single
query. When I run the query, Access stops responding for
about 10 minutes. During that 10 minutes, If I look at
task manager, Microsoft Access has a status of (Not
Responding) and The MSAccess.exe process is utilizing 99
percent of the CPU. Then after the 10 minutes, Access
will finish the query and resume responding like normal.
The time that it is not responding seems to go up quite a
bit for each additional table or query that I join. It
was not responding for about 5 minutes and I just added
the 17th table (3 columns) and the time has almost
doubled. Below is the SQL of the query that I'm running.
Is there a fix for this issue or is there something wrong
with the way that I've written the query?

SELECT [Forms]![F_GetData]![PMonth] AS PMonth, [Forms]!
[F_GetData]![PYear] AS PYear, QF_dslist.SN, QF_dslist.DN,
QF_dslist.Town, QF_dslist.ST, QF_dslist.OwnerName,
QF_dslist.OpenDate, QF_dslist.Status, T_SP_compm.F3 AS
compm_salesty, T_SP_compm.F4 AS compm_salesly,
T_SP_compm.F5 AS compm_growth, T_SP_compy.F3 AS
compy_salesty, T_SP_compy.F4 AS compy_salesly,
T_SP_compy.F5 AS compy_growth, T_SP_ham.F3 AS ham_salesty,
T_SP_ham.F4 AS ham_salesly, T_SP_ham.F5 AS ham_growth,
T_SP_hay.F3 AS hay_salesty, T_SP_hay.F4 AS hay_salesly,
T_SP_hay.F5 AS hay_growth, T_SP_margm.F3 AS margm_dollty,
T_SP_margm.F4 AS margm_perty, T_SP_margm.F5 AS
margm_dollly, T_SP_margm.F6 AS margm_perly, T_SP_margm.F7
AS margm_change, T_SP_margy.F3 AS margy_dollty,
T_SP_margy.F4 AS margy_perty, T_SP_margy.F5 AS
margy_dollly, T_SP_margy.F6 AS margy_perly, T_SP_margy.F7
AS margy_change, T_VD_uw21_2.TOPERC, T_VD_uw21_2.TOPAY,
Q_Calc_tx93_2.TY, Q_Calc_tx93_2.LY, Q_Calc_vc89_2.SATM,
Q_Calc_vc89_2.LOYM, Q_Calc_vc89_2.OPNM,
Q_Calc_vc89_2.SATLM, Q_Calc_vc89_2.LOYLM,
Q_Calc_vc89_2.OPNLM, Q_Calc_vc89_2.SATQ,
Q_Calc_vc89_2.LOYQ, Q_Calc_vc89_2.OPNQ,
Q_Calc_vc89_2.SATY, Q_Calc_vc89_2.LOYY,
Q_Calc_vc89_2.OPNY, Q_Calc_snc.LAST, Q_Calc_wg65y.PA AS
PAy, Q_Calc_wg65y.PA2PARITY AS PA2y, Q_Calc_wg65y.MRA AS
MRAy, Q_Calc_wg65y.MRA2PARITY AS MRA2y, Q_Calc_wg65m.PA AS
PAm, Q_Calc_wg65m.PA2PARITY AS PA2m, Q_Calc_wg65m.MRA AS
MRAm, Q_Calc_wg65m.MRA2PARITY AS MRA2m, Q_Calc_tk90_2.MTY,
Q_Calc_tk90_2.MLY, Q_Calc_tk90_2.YTY, Q_Calc_tk90_2.YLY,
Q_Calc_BosUnit.WashAvg, Q_Calc_BosUnit.DryAvg,
Q_Calc_BosUnit.HEPer, T_XL_credit.F8 AS appsM,
T_XL_credit.F12 AS dollM, T_XL_credit.F13 AS appsY,
T_XL_credit.F17 AS dollY, T_VD_vj40_3.MONTH,
T_VD_vj40_3.EIGHT, T_VD_vj40_3.YTD
FROM (((((((((((((((QF_dslist LEFT JOIN T_SP_compm ON
QF_dslist.SN = T_SP_compm.F1) LEFT JOIN T_SP_compy ON
QF_dslist.SN = T_SP_compy.F1) LEFT JOIN T_SP_ham ON
QF_dslist.SN = T_SP_ham.F1) LEFT JOIN T_SP_hay ON
QF_dslist.SN = T_SP_hay.F1) LEFT JOIN T_SP_margm ON
QF_dslist.SN = T_SP_margm.F1) LEFT JOIN T_SP_margy ON
QF_dslist.SN = T_SP_margy.F1) LEFT JOIN T_VD_uw21_2 ON
QF_dslist.SN = T_VD_uw21_2.UNIT) LEFT JOIN Q_Calc_tx93_2
ON QF_dslist.SN = Q_Calc_tx93_2.StoreNum) LEFT JOIN
Q_Calc_vc89_2 ON QF_dslist.SN = Q_Calc_vc89_2.StoreNum)
LEFT JOIN Q_Calc_snc ON QF_dslist.SN =
Q_Calc_snc.StoreNum) LEFT JOIN Q_Calc_wg65m ON
QF_dslist.SN = Q_Calc_wg65m.UNIT) LEFT JOIN Q_Calc_wg65y
ON QF_dslist.SN = Q_Calc_wg65y.UNIT) LEFT JOIN
Q_Calc_tk90_2 ON QF_dslist.SN = Q_Calc_tk90_2.StoreNum)
LEFT JOIN Q_Calc_BosUnit ON QF_dslist.SN =
Q_Calc_BosUnit.SN) LEFT JOIN T_XL_credit ON QF_dslist.SN =
T_XL_credit.F3) LEFT JOIN T_VD_vj40_3 ON QF_dslist.SN =
T_VD_vj40_3.UNIT;

 

[Tom Ellison]

I'm using Access97 on Windows NT(although I've run into
this same issue on Windows 98 and 2000). I have a query
that is pulling 70 columns of data from 17 tables and
queries. All of the tables are leftjoined to a single
query. When I run the query, Access stops responding for
about 10 minutes. During that 10 minutes, If I look at
task manager, Microsoft Access has a status of (Not
Responding) and The MSAccess.exe process is utilizing 99
percent of the CPU. Then after the 10 minutes, Access
will finish the query and resume responding like normal.
The time that it is not responding seems to go up quite a
bit for each additional table or query that I join. It
was not responding for about 5 minutes and I just added
the 17th table (3 columns) and the time has almost
doubled. Below is the SQL of the query that I'm running.
Is there a fix for this issue or is there something wrong
with the way that I've written the query?

Dear Tim:

This is a lot of work for the Jet engine. While there may be some
opportunity to address your performance issues with indexing, I would
really expect something like what you are experiencing for
performance.

There is another engine besides Jet that comes with Access 2000 and
later - MSDE. I would expect it to solve your performance problems.
A query of the complexity you describe is not uncommon here, but we
wouldn't use Jet to do it.

Switching your system to MSDE is not a trivial project. MSDE is a
limited version of SQL Server, but the limits it does have may be
somewhat more generous for your purposes than Jet.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts