Query is too slow

[Zybron]

The following query gives me the exact information I need but it takes about 10 minutes for it to run. Is there anyway to speed this query up?

SELECT Auctions.CurrentPrice, Items.PartNum, Items.ShortDescription, Max(DateValue([Auctions.EndDate])) AS [Closing Date]

FROM (Sales INNER JOIN Items ON Sales.ItemID = Items.ItemID) INNER JOIN Auctions ON (Sales.SaleID = Auctions.SaleID) AND (Items.ItemID = Auctions.ItemID)

WHERE (((Sales.DatePaymentReceived) Is Null) AND ((Sales.DatePaymentCleared) Is Null) AND ((Sales.StatusID)<200) AND ((Date()-[Auctions.EndDate])>17) AND ((Items.PartNum) Not In

(SELECT Items.PartNum

FROM (Items INNER JOIN Auctions ON Items.ItemID = Auctions.ItemID) INNER JOIN Sales ON (Items.ItemID = Sales.ItemID) AND (Auctions.SaleID = Sales.SaleID)

WHERE (((Auctions.StatusID)=80) AND ((Sales.DatePaymentReceived) Is Not Null) AND ((Sales.DatePaymentCleared) Is Not Null)))))

GROUP BY Auctions.CurrentPrice, Items.PartNum, Items.ShortDescription

ORDER BY Max(DateValue([Auctions.EndDate])) DESC

Please note that the extra spacing is for ease of readability (I hope

 

[smedvid]

My guess is that the sub-select &quot;((Items.PartNum) Not In
(SELECT Items.PartNum &quot; is killing the performance.

Consider either linking differently to that table (Outer Join) where value is not null.

Or create a static table with part numbers. Have (2) Queries execute. First would create temp table, then reference the temp table in a second query.

Another suggestion, which u already have done I assume... is to insure proper indexes exist on all linking and where fields.

Without a full view of the data set and volume, it is difficult to offer any other opions.


Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[Zybron]

Steve,

Thanks for the reply and I think the Outer Join may be the answer I'm looking for but, despite the prolific use of Joins in that query, I've rarely used them for anything more than to associate data in two tables. I know that the various types of outer joins include data that does not have it's key in the other table(s) so I'm not quite getting how including extra data in my query helps me to find data that isn't in a sub-query. Perhaps I'm not grasping some key point?

 

[lespaul]

Here's a great link to understanding joins:

http://www.devshed.com/Server_Side/MySQL/SQLJoins/page1.html

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!