Help with slow query's!

[NightZEN]

I have a slow loading form that seems to be caused by a slow query... well the last of a series of queries, one building on the other. The first two query's open up nice and fast, the last takes ~30 sec. I am hoping to get some help speeding these up somehow. Any suggestions appreciated.
Here they are: and thanks!

QryReviewXX1:
SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [PressTon], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC], [BlankAcrossFlow], [BlankWithFlow]
FROM [TblSubFCR]

UNION SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [PressTon], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC], [BlankAcrossFlow], [BlankWithFlow]
FROM [TblSubFIR]
ORDER BY [Job#];

UNION SELECT [Job#], [ReviewDate], [NumberStations], [BlankLoadTableHeight], [ExitConveyorHeight], [MatlThickness], [ClampStroke], [LiftStroke], [Pitch], [PressTon], [Press], [RailDistance], [#SetsTooling], [PanelN], [PanelS], [PanelE], [PanelW], [PanelC], [BlankAcrossFlow], [BlankWithFlow]
FROM [TblSubHTR]
ORDER BY [Job#];


QryReviewXX2:
SELECT [Job#], ReviewDate, NumberStations, BlankLoadTableHeight, ExitConveyorHeight, MatlThickness, ClampStroke, LiftStroke, Pitch, PressTon, Press, RailDistance, [#SetsTooling], PanelN, PanelS, PanelE, PanelW, PanelC, ReviewDate, [BlankAcrossFlow], [BlankWithFlow]
FROM QryReviewXX1 AS X
WHERE (ReviewDate=(SELECT MAX(ReviewDate)
From QryReviewXX1
WHERE [Job#]=X.[Job#]));


QryReviewXX3:
SELECT TblMasterSalesBol2.Archive, TblMasterSalesBol2.[Job#], QryReviewXX2.NumberStations, QryReviewXX2.BlankLoadTableHeight, QryReviewXX2.ExitConveyorHeight, QryReviewXX2.MatlThickness, QryReviewXX2.ClampStroke, QryReviewXX2.LiftStroke, QryReviewXX2.Pitch, QryReviewXX2.Press, QryReviewXX2.RailDistance, QryReviewXX2.[#SetsTooling], QryReviewXX2.PanelN, QryReviewXX2.PanelS, QryReviewXX2.PanelE, QryReviewXX2.PanelW, QryReviewXX2.PanelC, QryReviewXX2.ReviewDate, TblMasterSalesBol2.CustName, TblMasterSalesBol2.CustPlant, TblMasterSalesBol2.[CustPart#], QryReviewXX2.PressTon, QryReviewXX2.BlankAcrossFlow, QryReviewXX2.BlankWithFlow
FROM QryReviewXX2 RIGHT JOIN TblMasterSalesBol2 ON QryReviewXX2.[Job#] = TblMasterSalesBol2.[Job#]
WHERE (((TblMasterSalesBol2.Archive)<>1))
ORDER BY TblMasterSalesBol2.[Job#];

 

[OnTheFly]

Whenever you have nested queries you are going to slow. Without knowing what you are trying to accomplish with this massive query, it will be difficult to suggest a different option.

Hope this helps.

OnTheFly

 

[NightZEN]

I would love to have just one query, but the requirement is complex as you can see.

This is what I am doing:

I have jobs that each get reviews of three types FCR, FIR, and HTR. I need to return the most recent review for each job (could be from any one of the 3 review types). I then need to add some colomns from my main table (TblMasterSalesBol2) to display on the form.

So the 1st query gathers all three reviews with Union statments. The second returns the most recent record. The 3rd adds the columns needed for my form.

Thanks

 

[lespaul]

couldn't you take your second query and make it the UNION query:

SELECT [Job#], ReviewDate, NumberStations, BlankLoadTableHeight, ExitConveyorHeight, MatlThickness, ClampStroke, LiftStroke, Pitch, PressTon, Press, RailDistance, [#SetsTooling], PanelN, PanelS, PanelE, PanelW, PanelC, ReviewDate, [BlankAcrossFlow], [BlankWithFlow]
FROM [TblSubFCR] AS X
WHERE (ReviewDate=(SELECT MAX(ReviewDate)
          From [TblSubFCR]
          WHERE [Job#]=X.[Job#]))
UNION
SELECT [Job#], ReviewDate, NumberStations, BlankLoadTableHeight, ExitConveyorHeight, MatlThickness, ClampStroke, LiftStroke, Pitch, PressTon, Press, RailDistance, [#SetsTooling], PanelN, PanelS, PanelE, PanelW, PanelC, ReviewDate, [BlankAcrossFlow], [BlankWithFlow]
FROM [TblSubFIR] AS X
WHERE (ReviewDate=(SELECT MAX(ReviewDate)
          From [TblSubFIR]
          WHERE [Job#]=X.[Job#]))
UNION
SELECT [Job#], ReviewDate, NumberStations, BlankLoadTableHeight, ExitConveyorHeight, MatlThickness, ClampStroke, LiftStroke, Pitch, PressTon, Press, RailDistance, [#SetsTooling], PanelN, PanelS, PanelE, PanelW, PanelC, ReviewDate, [BlankAcrossFlow], [BlankWithFlow]
FROM [TblSubHTR] AS X
WHERE (ReviewDate=(SELECT MAX(ReviewDate)
          From [TblSubHTR]
          WHERE [Job#]=X.[Job#]))

And just for future reference, if you had kept all that information in the same table (since it appears that ALL three tables have EXACTLY the same information) and added a field to determine what kind of review it was, this query would be A LOT easier!!!

Leslie

 

[BNPMike]

Maybe Access can optimise the 2nd query which looks the big one, but when you add the third in, it loses it and starts bringing across large amounts of data.

I would put the data from the 2nd query into a temporary table and base your 3rd query on that.

 

[OnTheFly]

I agree with Leslie about the fact that these 3 Sub tables should all be in one table but that is beside the point at this time.

I would get the Max date through code, keep the first union query but add a WHERE statement that uses the newly discovered MaxDate. Then you can skip right to the the third query.

Hope this helps.

OnTheFly

 

[NightZEN]

Thanks everyone! By the way, i would have loved to share this data in one table, but my client wanted to have the ability to keep all old reviews, even if the common data got updated as new reviews were added. Perhaps I missed something when I designed this, but the only way i could think of was to duplicate data (though it could have been normalized better perhaps) in the three review tables.

Thanks again!

 

[JonFer]

You'll should get better performance if you write queries that select the max(reviewdate) for each JobID and then join those queries (based on JobID) in the appropriate Selects instead of using the "Select Max()..." subquery in the WHERE clause.

I also vote for combining the 3 tables as mentioned earlier.