Need some advice optimizing some queries....

[kalto]

Well, i'm upsizing from an Access Database to an Access Project.

I was having trouble with last(), i found a way around it, just to find it was causing me another trouble.

My solution was to use an UDF to get my last row, here's an example:

SELECT 
  ProductID, 
  dbo.SupplierProductionCard_LastOfClosingDate(ProductID) AS LastOfClosingDate
FROM         dbo.dbProductionCard
GROUP BY ProductID, Completed, TransfertToInventory
HAVING      (Completed = 1) AND (TransfertToInventory = 1)

And SupplierProductionCard_LastOfClosingDate() looks like that:

CREATE FUNCTION dbo.SupplierProductionCard_LastOfClosingDate
( @ProductID int )
RETURNS datetime
AS
BEGIN
  DECLARE @LastOfClosingDate datetime
  SET @LastOfClosingDate = ( 
    SELECT TOP 1
      dbProductionCard.ClosingDate AS LastOfClosingDate
    FROM dbProductionCard
    WHERE dbProductionCard.ProductID=@ProductID AND 
          dbProductionCard.ClosingDate IS NOT NULL
    ORDER BY dbProductionCard.CardNumberID DESC
    )
  RETURN ( @LastOfClosingDate )
END

It works fairly well on table with ±1000 rows, but when you have a table with something like 20 000 rows... or worse, if you need a last value for 3 or 4 columns... i tried a view like that... 4 different last function getting data from 2 different tables with ~20 000 rows each, i evaluated that it would take around 8 hours to complete on a P4 2.4Ghz (!!!)...

Any1 got an idea how i could either optimize or maybe find another way those "last()" ? And please, don't tell me MIN() or MAX(), the fact that a value is in the last row of a column doesn't mean it has the highest (or lowest) value.

 

[cmmrfrds]

MAX() will give the highest VALUE according to the level asked for in the Group By clause. Can you explain what the last date is relative to and maybe it can be done in a query.