Subquery with MAX too slow

[masterslave]

I've got a query for search function where I need to select the latest
range of records for product codes and I'm using a subquery
CurrentItem.DateActive = (SELECT MAX(DateActive) FROM SupplierItem si
Where si.SupplierItemCode = CurrentItem.SupplierItemCode) as part of
WHERE condition.

The problem is, it's very slow, takes about eight seconds!! And for
customers, that response time isn't very good... Is there any
alternatives to this method? Any advice much appreciated!

SELECT
SupplierProduct.SupplierProductId,
SupplierProduct.SupplierProduct,
CurrentItem.SupplierItemId,
CurrentItem.SupplierItemCode,
CurrentItem.Description2,
CurrentItem.BuyUnit,
CurrentItem.PricingUnit,
CurrentItem.OCCost,
CurrentItem.OCCost1,
CurrentItem.OCCost2,
CurrentItem.OCCost3,
CurrentItem.ConnCost,
CurrentItem.ConnCost1,
CurrentItem.ItemStatusId,
ItemStatus.ItemStatusName,
ItemStatus.ItemStatusImage,
ItemStatus.ItemStatusColour,
Supplier.SourceCode,
CurrentItem.UnitId
FROM SupplierItem CurrentItem
INNER JOIN SupplierProduct ON (CurrentItem.SupplierProductId =
SupplierProduct.SupplierProductId)
INNER JOIN Supplier ON (Supplier.SupplierId =
SupplierProduct.SupplierId)
INNER JOIN ItemStatus ON (CurrentItem.ItemStatusId =
ItemStatus.ItemStatusId)
WHERE (IsApproved = 1 -- item must be approved
AND ( CurrentItem.SupplierItemCode LIKE '%' + @SearchTerm + '%' )
AND ( CurrentItem.DateActive <= GetDate() AND CurrentItem.DateActive
= (SELECT MAX(DateActive) FROM SupplierItem si Where
si.SupplierItemCode = CurrentItem.SupplierItemCode) )
AND ( (@IncludeInactive = 0 AND ((DeletedItem IS NULL OR DeletedItem
= 0) AND OCCost > 0) AND CurrentItem.ItemStatusId <> 5 )
OR @IncludeInactive = 1)
)
ORDER BY
Supplier.SourceCode,
CurrentItem.SupplierItemCode

[Ed Murphy]

masterslave wrote:

> I've got a query for search function where I need to select the latest
> range of records for product codes and I'm using a subquery
> CurrentItem.DateActive = (SELECT MAX(DateActive) FROM SupplierItem si
> Where si.SupplierItemCode = CurrentItem.SupplierItemCode) as part of
> WHERE condition.
>
> The problem is, it's very slow, takes about eight seconds!! And for
> customers, that response time isn't very good... Is there any
> alternatives to this method? Any advice much appreciated!

Does SupplierItem have an index on SupplierItemCode?

Side note: The WHERE clause is badly in need of formatting for
readability, especially this bit:

[Jack Vamvas]

What's your indexing structure? Have you looked at your Execution Plan ? Do
you have any items on that Plan
which are doing table scans or index scans? Focus on those first.

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com

"masterslave" <touchdo...@gmail.com> wrote in message
news:1194995618.3...@v23g2000prn.googlegroups.com...