Query taking long time onbly for certain data

[Sreedevi]

Hi All,

I have 1 query which is taking very long time (in hours) only for certain data. I have 4 data bases, for 3 data bases it is working fine but only for 1 data base it is giving an issue.

When I compared the size of the data base it is lesser than one of the data base for which it is working fine. I decresed the tables data also but still no use

Following is the query:

SELECT DISTINCT qrySubFlowForecast.Business_Unit, qrySubFlowForecast.Division_Name, qrySubFlowForecast.Dept_Name, qrySubFlowForecast.Class_Name, qrySubFlowForecast.[9 Digit Style #], qrySubFlowForecast.[Style Description], qrySubFlowForecast.Hierarchy, qrySubFlowForecast.ServiceStart, qrySubFlowForecast.ServiceEnd, qrySubFlowForecast.[In-Store], qrySubFlowForecast.Distro, qrySubFlowForecast.Quantity1, qrySubFlowForecast.[Store Count], qrySubFlowForecast.DCMapping, qrySubFlowForecast.MS_AA, qrySubFlowForecast.MS_A, qrySubFlowForecast.MS_B, qrySubFlowForecast.MS_C, qrySubFlowForecast.MS_D, qrySubFlowForecast.MS_E, qrySubFlowForecast.MS_F, qrySubFlowForecast.SSN, qrySubFlowForecast.StoreCountA, qrySubFlowForecast.StoreCountAA, qrySubFlowForecast.StoreCountB, qrySubFlowForecast.StoreCountC, qrySubFlowForecast.StoreCountD, qrySubFlowForecast.StoreCountE, qrySubFlowForecast.StoreCountF, qrySubFlowForecast.Attribute1, qrySubFlowForecast.Attribute2, qrySubFlowForecast.Attribute3, qrySubFlowForecast.Attribute4, qrySubFlowForecast.Attribute5, qrySubFlowForecast.[Total Committed], tblScanData.[FORECAST INDC DATE] AS [Fcst In-DC Date], tblScanData.[FORECAST STOCK DATE] AS [Fcst Stock Date], qrySubPickFlowForecast.[Bleed Off Date] AS [Bleed-Off Date], qrySubPickFlowForecast.[New Flow Date], qry_LatestReplenDate.[On-Time Replen] AS [On-Time Replen], IIf(tblScanData.[FORECAST INDC DATE] Is Null And tblScanData.[FORECAST STOCK DATE] Is Null,"No SCAN",IIf(qrySubPickFlowForecast.[Bleed Off Date] Is Not Null And tblScanData.[FORECAST STOCK DATE] Is Not Null And tblScanData.[FORECAST STOCK DATE]<=qrySubPickFlowForecast.[Bleed Off Date],"BleedOff",IIf(qrySubPickFlowForecast.[New Flow Date] Is Not Null And tblScanData.[FORECAST STOCK DATE] Is Not Null And tblScanData.[FORECAST STOCK DATE]<=qrySubPickFlowForecast.[New Flow Date],"Final",IIf(tblScanData.[FORECAST STOCK DATE] Is Not Null And qry_LatestReplenDate.[On-Time Replen] Is Not Null And tblScanData.[FORECAST STOCK DATE]<=qry_LatestReplenDate.[On-Time Replen],"On Time Replen",IIf(qrySubPickFlowForecast.[Bleed Off Date] Is Null And qrySubPickFlowForecast.[New Flow Date] Is Null And qry_LatestReplenDate.[On-Time Replen] Is Null,"No Match","Late"))))) AS [Pick Type], qrySubFlowForecast.Confirm
FROM ((qrySubFlowForecast LEFT JOIN tblScanData ON (qrySubFlowForecast.[9 Digit Style #] = tblScanData.[STYLE#]) AND (qrySubFlowForecast.DCMapping = tblScanData.[DC Name])) LEFT JOIN qrySubPickFlowForecast ON (qrySubFlowForecast.Division_Name = qrySubPickFlowForecast.Division_Name) AND (qrySubFlowForecast.Business_Unit = qrySubPickFlowForecast.Business_Unit) AND (qrySubFlowForecast.[In-Store] = qrySubPickFlowForecast.[In-Store])) LEFT JOIN qry_LatestReplenDate ON qrySubFlowForecast.[In-Store] = qry_LatestReplenDate.Delivery



Sub queries are running pretty fast but this query is creating aproblem.

Any help on this would be great...

Thanks in advance!!!!!!!!!!

 

[KenHigg]

It would help if you formatted your sql statement in a code window so it would be easier to read.