query taking forever to execute!

I

iori

Registered User.
Local time
Today, 15:47
Joined
Aug 19, 2006
Messages
29
SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

FROM TBL_FCST_WKLY AS FCST, TBL_ACTUAL_WKLY AS ACTUAL

WHERE (FCST.TYPE=ACTUAL.TYPE) AND (FCST.REGION=ACTUAL.REGION) AND (FCST.PART_NR=ACTUAL.PART_NR) AND

FCST.REF_DT = (SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION) AND

FCST.MONTH_DT = (SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION);


i am querying two tables TBL_FCST_WKLY which has 29500 records and TBL_ACTUAL_WKLY which has 798222 records.
When i run this query it sites there forever. Is it because i dint optimize my query or is it because the number of records is too much for access? can someone help plz.

thanks
 
iori,

JET is normally pretty good performance-wise, but ...

Code: 
Select Field1
From   Table1
Where  Field1 In (Select Field1
                  From   Table2)

The above construct will execute SLOWLY with even a few records
in each table.

If you can change your query to a series of queries, you'll get
better performance, probably even acceptable.

Even if you replaced your nested Select with a DLookup (Oh no!)
or a user-defined function you'd get better performance.

Wayne
 
thanks for the reply Wayne, how can i change this query to series of queries? i am new to this topic
 
Inner joins to inline queries may be the solution for you. Try the following and see if it works for you:

SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

FROM TBL_ACTUAL_WKLY AS ACTUAL, (TBL_FCST_WKLY AS FCST INNER JOIN (SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION) TBL1 ON FCST.REF_DT = TBL1.FORECASTED)
INNER JOIN (SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED FROM TBL_FCST_WKLY FCST2 INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST2.PART_NR AND LT.REGION=FCST2.REGION WHERE FCST2.PART_NR=FCST.PART_NR AND FCST2.REGION=FCST.REGION) TBL2 ON FCST.MONTH_DT = TBL2.FORECASTED

WHERE (FCST.TYPE=ACTUAL.TYPE) AND (FCST.REGION=ACTUAL.REGION) AND (FCST.PART_NR=ACTUAL.PART_NR)
 
bytemyzer,
i get a prompt for ACTUAL.REF_DT,FCST.PART_NR,FCST.REGION,FCST.REF_DT when i execute this query. hmmm...
 
I reconstructed your query (ALWAYS use joins where possible) to this:

SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

FROM ((TBL_ACTUAL_WKLY AS ACTUAL INNER JOIN TBL_FCST_WKLY AS FCST ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR)

INNER JOIN (SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED, FCST.PART_NR, FCST.REGION
FROM (TBL_ACTUAL_WKLY AS ACTUAL INNER JOIN TBL_FCST_WKLY AS FCST ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR)
INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST.PART_NR AND LT.REGION=FCST.REGION) FCST2 ON FCST.REF_DT = FCST2.FORECASTED AND FCST.PART_NR = FCST2.PART_NR AND FCST.REGION = FCST2.REGION)

INNER JOIN (SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED, FCST.PART_NR, FCST.REGION
FROM (TBL_ACTUAL_WKLY AS ACTUAL INNER JOIN TBL_FCST_WKLY AS FCST ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR)
INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST.PART_NR AND LT.REGION=FCST.REGION) FCST3 ON FCST.MONTH_DT = FCST3.FORECASTED AND FCST.PART_NR = FCST3.PART_NR AND FCST.REGION = FCST3.REGION


See if this works for you.
 
your query does execute now but giving me differnt results, let me show u my test data:

TBL_ACTUAL_WKLY
REF_DT,REGION,TYPE,PART_NR,QT
9/18/2006,A,MAG,123,5
9/11/2006,A,MAG,123,20

TBL_FCST_WKLY
REF_DT,REGION,TYPE,PART_NR,MONTH_DT,QT
9/4/2006,A,MAG,123,9/18/2006,10
8/28/2006,A,MAG,123,9/11/2006,20
9/4/2006,A,MAG,123,9/25/2006,50


LEADTIME
COMPONENT,REGION,LEADTIME
123,A,2

my original query gives me this result
Date Forecasted,Date Shipped,Qt Forecasted,Qt Shipped, PART_NR, REGION,TYPE,MPE
9/4/2006,9/18/2006,10,5,123,A,MAG, -50.00%
8/28/2006,9/11/2006,20,20,123,A,MAG,0.00%

9/4/2006,A,MAG,123,9/25/2006,50 from TBL_FCST_WKLY is not included in my resultset as 9/25/2006 is not in TBL_ACTUAL_WKLY


while your query gives this resultset
Date Forecasted,Date Shipped,Qt Forecasted,Qt Shipped, PART_NR, REGION,TYPE,MPE
8/28/2006,9/18/2006,20,5,123,A,MAG,-75.00%
9/4/2006,9/18/2006,10,5,123,A,MAG, -50.00%
8/28/2006,9/11/2006,20,20,123,A,MAG,0.00%
9/4/2006,9/11/2006,10,20,123,A,MAG,100.00%

the ones in red shouldnt be there. I am looking into your query to see why its doing it. thanks alot
 
SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

FROM
((TBL_ACTUAL_WKLY_TEST AS ACTUAL INNER JOIN TBL_FCST_WKLY_TEST AS FCST ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR)

INNER JOIN
(SELECT DISTINCT DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT) AS FORECASTED, FCST.PART_NR, FCST.REGION,LT.LEADTIME AS LEADTIME
FROM (TBL_ACTUAL_WKLY_TEST AS ACTUAL INNER JOIN TBL_FCST_WKLY_TEST AS FCST ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR) INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST.PART_NR AND LT.REGION=FCST.REGION) FCST2
ON FCST.REF_DT = FCST2.FORECASTED AND FCST.PART_NR = FCST2.PART_NR AND FCST.REGION = FCST2.REGION)

INNER JOIN
(SELECT DISTINCT DATEADD("ww",LT.LEADTIME,FCST.REF_DT) AS FORECASTED, FCST.PART_NR, FCST.REGION
FROM (TBL_ACTUAL_WKLY_TEST AS ACTUAL INNER JOIN TBL_FCST_WKLY_TEST AS FCST ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR) INNER JOIN LEADTIME LT ON LT.COMPONENT=FCST.PART_NR AND LT.REGION=FCST.REGION) FCST3 ON
FCST.MONTH_DT = FCST3.FORECASTED AND FCST.PART_NR = FCST3.PART_NR AND FCST.REGION = FCST3.REGION

WHERE DATEDIFF("ww",FCST.REF_DT,ACTUAL.REF_DT)=FCST2.LEADTIME


putting the where condition gives me the same result, lets c how it runs on the actual data. thanks bytemyzer
 
How about:

SELECT FCST.REF_DT AS [Date Forecasted], ACTUAL.REF_DT AS [Date Shipped], FCST.QT AS [Qt Forecasted], ACTUAL.QT AS [Qt Shipped], FCST.PART_NR, FCST.REGION, FCST.TYPE, IIf(FCST.QT=0,'',formatpercent((ACTUAL.QT-FCST.QT)/FCST.QT,2)) AS MPE

FROM (TBL_FCST_WKLY AS FCST INNER JOIN TBL_ACTUAL_WKLY AS ACTUAL ON FCST.TYPE=ACTUAL.TYPE AND FCST.REGION=ACTUAL.REGION AND FCST.PART_NR=ACTUAL.PART_NR)
INNER JOIN LEADTIME LT ON FCST.PART_NR=LT.COMPONENT AND FCST.REGION=LT.REGION

WHERE FCST.REF_DT=DATEADD("ww",-LT.LEADTIME,ACTUAL.REF_DT)
AND FCST.MONTH_DT=DATEADD("ww",LT.LEADTIME,FCST.REF_DT);
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom