query taking forever to execute!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

query taking forever to execute!

Author

Topic

iori
Starting Member

20 Posts

Posted - 2006-10-09 : 11:14:06

[code]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);[/code]

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

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-09 : 11:42:57

FCST.REF_DT = (SELECT DISTINCT
That looks odd - how many distinct values do you expect from the subquery?

I suspect it is the subqueries that are causing the problem.
Try creating derived tables rather than a correalated subquery.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

iori
Starting Member

20 Posts

Posted - 2006-10-09 : 11:49:28

I am expecting only one value from the subquery. i will see how i can conver this query to use derived tables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 13:52:03

Also make the main query a INNER JOIN, rather than a CROSS JOIN.

Peter Larsson
Helsingborg, Sweden

iori
Starting Member

20 Posts

Posted - 2006-10-09 : 14:53:23

can anyone point me to the right direction in converting this to use derived tables, i havent used derived tables before

iori
Starting Member

20 Posts

Posted - 2006-10-09 : 15:49:40

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 );

this works. Credit for this goes to ByteMyzer and thanks to you all

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources