Get last order for all customers
-
Can someone help me improve the performance of the following query. I am running this query from Access via ODBC connection to FoxPro data tables.
I am trying to return a list of the all customers and their last order.
SELECT Customers.CustomerID,
(SELECT TOP 1 OrderID FROM Orders WHERE
Orders.CustomerID=Customers.CustomerID
ORDER BY DateOfOrder DESC) AS LastOrderID
FROM Customers
The query returns what I need on a test database but on the production data (much larger data set) the query runs forever. Incidentally, if I import the data into SQL Server the query completes in less than 1 second.
Thanks,
Andrew.
-
Well there's your answer; use SQL, not foxpro!
Just kidding; I'm assuming your constrained to deal with the foxpro tables.
select c.CustomerID, o.OrderID
From
(
Select CustomerID, max(DateOfOrder) maxDateOfOrder
From Orders
group by CustomerID
 
o1JOIN Orders o
on o.CustomerID = o1.CustomerID
and o.DateOfOrder = o1.maxDateOfOrder
JOIN Customers c on o.CustomerID = c.CustomerID
Derived tables can perform much better than correlated subqueries, but it's difficult to know without the schema. By far the best solution is:
select c.CustomerID, max(o.OrderID)
From Orders o
JOIN Customers c on o.CustomerID = c.CustomerID
But this is assuming that OrderID is in the form "OrderID = max(OrderID)+1", like an identity column. While this should be a safe assumption (I always use an identity column instead of a datetime stamp to determine "Last") it probably isn't
cl
Signature is NULL
-
I would love to use your second suggestion but unfortunately the OrderID is not sequential (a kind of GUID).
I am trying your first suggestion but I still seem to be having major performance issues. Access goes off into never-never land, waiting for data.
I would dearly love to use SQL Server but as you guessed, am tied to these FoxPro tables for now.
-
Hey,
Check if this SQL gives you the results you are looking for:
SELECT
c.CustomerID, MAX(o.OrderID) AS OrderID
FROM
Customers c JOIN Orders o ON c.customerID = o.CustomerID
GROUP BY
c.CustomerID
HAVING
o.DateOfOrder = MAX(o.DateOfOrder)
Hope this helps ...
JP
-
Try using a pass-thru query in Access. This will force Access to execute the query in SQL Server's terms, not how Access would interpret the query.
-
Another idea is to put the query in a stored proc and then call the stored proc from a pass-through query from within Access.
-
bellis,
Oh yeah...now THAT'S a good idea! Why didn't I think of that? I guess it's because I haven't had to mess with Access for a while.
Signature is NULL
-
Thanks for your help. I have come up with a method that seems to work efficiently enough. It involves making a join on the customer ID and the Date Of Order fields. That is less than ideal but it will work.
The Order ID field is not an integer and it is not sequential so ordering on that is ruled out. I should have mentioned this in my original post sorry.
Unfortunately I am not able to add any procedures to the FoxPro data store. This is a thrid party application so I cannot make changes to it.
I tried the pass through query option but FoxPro seems to interpret SQL differently. It doesn't like my sql statement even though both SQL Server and Access handle it fine. I have another post on a FoxPro forum looking at that issue.
Thanks again for your help, I think I have a feasible tecnique now.
Andrew
-
This was removed by the editor as SPAM
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply