Million thanks for all your efforts.
I'm at home and don't have Access here. I'll check it on Monday.
I really appreciate your assist.
This SQL problem is the MAX() amount of frustration I can handle right now. (2 Viewers)
- Thread starter KitaYama
- Start date
I'm at home and don't have Access here. I'll check it on Monday.
I really appreciate your assist.
With this information that you are using SQL Server and you are looking to scale this (you note the table is expected to grow to half a million records), you have several good ways of tackling this:
1. As @DHookom notes, I also would highly suggest having this query be processed on SQL Server either as a pass-through query or using VBA to construct the query dynamically and using ADO to execute it on SQL Server. SQL Server will much better optimize performance particularly as your data size grows.
2. Use indexes on SQL Server to improve performance. If you are going to be frequently querying for particular PricesProductFK values, an index on that field could go a long way and you could also incorporate other fields into the index (as additional key fields) to optimize.
3. As @isladogs suggests, absolutely test with larger datasets. Query performance can vary significantly with larger datasets and what performs best on a small dataset can perform very differently as the dataset gets larger.
4. With SQL Server, you have many more options for how to write the query. For example, you could take a common SQL Server approach that uses the ROW_NUMBER window function to get the result you are looking for. See below for your query that uses that approach (this would need to run as a pass-through or direct query on SQL Server as this is not supported by the Access DB Engine).
1. As @DHookom notes, I also would highly suggest having this query be processed on SQL Server either as a pass-through query or using VBA to construct the query dynamically and using ADO to execute it on SQL Server. SQL Server will much better optimize performance particularly as your data size grows.
2. Use indexes on SQL Server to improve performance. If you are going to be frequently querying for particular PricesProductFK values, an index on that field could go a long way and you could also incorporate other fields into the index (as additional key fields) to optimize.
3. As @isladogs suggests, absolutely test with larger datasets. Query performance can vary significantly with larger datasets and what performs best on a small dataset can perform very differently as the dataset gets larger.
4. With SQL Server, you have many more options for how to write the query. For example, you could take a common SQL Server approach that uses the ROW_NUMBER window function to get the result you are looking for. See below for your query that uses that approach (this would need to run as a pass-through or direct query on SQL Server as this is not supported by the Access DB Engine).
SQL:
WITH CTE AS (
SELECT p.*,
ROW_NUMBER() OVER (
PARTITION BY p.PricesProductFK, p.PricesOrderedFromFK,
p.PricesQuantity, p.PricesRegisteredBy
ORDER BY p.PricesRegisteredOn DESC, p.PricesUnitPrice DESC
) AS rownum
FROM tblProductPrices AS p
)
SELECT
PricesProductFK,
PricesOrderedFromFK,
PricesQuantity,
PricesRegisteredOn AS RegisteredOn,
PricesRegisteredBy,
PricesUnitPrice
FROM CTE
WHERE rownum = 1 AND PricesProductFK = 34086;
Minty
AWF VIP
- Local time
- Today, 07:17
- Joined
- Jul 26, 2013
- Messages
- 10,658
I agree with @DaveMD that a windowing function in SQL server and suitable indexes would probably speed this up further.
The windowing functions are an absolute winner for this type of query.
If you run the current query directly in SQL server, you would be able see the execution plan which will suggest suitable indexes if it sees performance improvements as a result.
The windowing functions are an absolute winner for this type of query.
If you run the current query directly in SQL server, you would be able see the execution plan which will suggest suitable indexes if it sees performance improvements as a result.
I just wanted to come back and tell you how much better your solution is than mine. Now after a while using it, it's Indeed super fast!
I used your suggestion as a pass-through query and set it as the record source of a subform. Even though I can't use a parent-child link for a subform with a pass-through record source and have to rebuild the SQL in VBA and reassign it to the query, it's still super, super fast.
I'm really not sure how to thank you. All I can say is that you helped so much.
A million thanks!
If you're doing this in SQL Server, then why not use APPLY?
Getting your head around the differences between Access SQL and Transact SQL is fundamental to getting your code to work fast. That and make friends with Indexing. (Learned that one the hard way)
SQL:
SELECT
p.PricesProductFK,
p.PricesOrderedFromFK,
p.PricesQuantity,
p.PricesRegisteredOn AS RegisteredOn,
p.PricesRegisteredBy,
p.PricesUnitPrice,
lp.LastPrice
FROM
tblProductPrices AS p
/* OUTER APPLY is analogous to LEFT JOIN to a table-valued function... CROSS APPLY is analogous to INNER JOIN to a table-valued function */
OUTER APPLY (
SELECT TOP 1 p2.PricePK AS LastPrice
FROM tblProductPrices AS p2
WHERE
p2.PricesProductFK = p.PricesProductFK
AND p2.PricesOrderedFromFK = p.PricesOrderedFromFK
AND p2.PricesQuantity = p.PricesQuantity
AND p2.PricesRegisteredBy = p.PricesRegisteredBy
ORDER BY p2.PricesRegisteredOn DESC, p2.PricesUnitPrice DESC
) lpGetting your head around the differences between Access SQL and Transact SQL is fundamental to getting your code to work fast. That and make friends with Indexing. (Learned that one the hard way)
The simple answer is because it's the first time I heard it and I don't know anything about it.
But thanks for offering something new to learn.
I'll study a bit and try to understand how it works and the differences between my current solution and APPLY>
Thanks again.
Before I start learning, I wanted to be sure this shows the correct result.
Running your suggested code (with a slightly corrections), it doesn't show the expected result.
It shows all the prices.
But I'm still reading about APPLY. For now I stick with CTE until I learn more about APPLY. Maybe I can find a way around it on my own.
Here's my final query:
SQL:
SELECT
p.PricesProductFK,
p.PricesOrderedFromFK,
p.PricesQuantity,
p.PricesRegisteredOn AS RegisteredOn,
p.PricesRegisteredBy,
p.PricesUnitPrice,
lp.LastPrice
FROM
tblProductPrices AS p
OUTER APPLY (
SELECT TOP 1 p2.PricePK AS LastPrice
FROM tblProductPrices AS p2
WHERE
p2.PricesProductFK = p.PricesProductFK
AND p2.PricesOrderedFromFK = p.PricesOrderedFromFK
AND p2.PricesQuantity = p.PricesQuantity
AND p2.PricesRegisteredBy = p.PricesRegisteredBy
ORDER BY p2.PricesRegisteredOn DESC, p2.PricesUnitPrice DESC
) lp
WHERE p.PricesProductFK =36065Can you post some sample data? (It can be fake... as long as it's representative)
CREATE TABLE script(s), INSERT script(s)... well, if you want a tested result.
Is what you posted something like a price history or purchase history table? The way I solved the "get the most recent price prior to [some date]" is to use APPLY.... something like this:
the OUTER APPLY is retrieving the most recent price paid for a related ingredient prior to the BakeDate. If that's similar to what you're trying to do, then that pattern will do it.
APPLY basically lets you join a table (like the RecipeIngredients information) to a "derived table" or table-valued function. (like a TOP
result). The weird part is that you don't use a typical JOIN statement, you include the join in the WHERE clause in the APPLY query. Note that CROSS APPLY is analogous to an INNER JOIN, and OUTER APPLY is analogous to an OUTER JOIN (think LEFT/RIGHT).
CREATE TABLE script(s), INSERT script(s)... well, if you want a tested result.
Is what you posted something like a price history or purchase history table? The way I solved the "get the most recent price prior to [some date]" is to use APPLY.... something like this:
SQL:
/* for each date a loaf is made, what's the most recent price for each ingredient? */
SELECT mfg.LoafKey,
mfg.BakeDate,
ri.IngredientID,
ri.IngredientName,
ri.IngredientWeightGrams,
ri.RecipeWeight,
ri.RecipeWeight/ri.IngredientWeightGrams AS PctOfPkg,
lp.UnitPrice,
lp.UnitPrice * ri.RecipeWeight/ri.IngredientWeightGrams AS UnitCost
FROM BakeEvents mfg
INNER JOIN vwBaseIngredientRecipe ri
ON mfg.LoafKey = ri.RecipeID
OUTER APPLY
/* retrieve the most recent unit price prior to the bake date */
(SELECT TOP 1 UnitPrice
FROM Purchases p
WHERE p.IngredientID = ri.IngredientID
AND p.PurchaseDate <= mfg.BakeDate
ORDER BY p.PurchaseDate DESC) lp
WHERE ri.RecipeID IN (2,8);the OUTER APPLY is retrieving the most recent price paid for a related ingredient prior to the BakeDate. If that's similar to what you're trying to do, then that pattern will do it.
APPLY basically lets you join a table (like the RecipeIngredients information) to a "derived table" or table-valued function. (like a TOP
result). The weird part is that you don't use a typical JOIN statement, you include the join in the WHERE clause in the APPLY query. Note that CROSS APPLY is analogous to an INNER JOIN, and OUTER APPLY is analogous to an OUTER JOIN (think LEFT/RIGHT).My post #1 contains the sample data and how I need the result.
If you need a script, I can send it. But I just left my desk. So I won't be able until I return back to my desk. Maybe this evening.
yes. It's a list of price history of our products. When a new order comes in, we need to show the latest prices in each lot.
So in my #1 sample data, there are two prices of 51 quantity. I need to show the latest registered date. Or there are 3 records with 21 quantities. It shows that the price for 21 piece to 50 piece has been changed 3 times. But the query should show ONLY the one with latest (largest) registered date.
Okay, so you have a price history (ItemID, PurchaseDate, UnitPrice) and then you have a new order (OrderID, OrderDate, ... ItemID, Quantity, etc)
So you'd do something like
SELECT
rderID, rderDate, o.ItemID, o.Quantity, lp.PurchaseDate, lp.UnitPriceFROM Orders o
CROSS APPLY (SELECT TOP 1 p.PurchaseDate, p.UnitPrice
FROM Purchases p
WHERE p.PurchaseDate <
rderDateAND p.ProductID = o.ItemID
ORDER BY p.PurchaseDate DESC) lp
The easiest way to understand the query is sort of from the bottom up. The Orders part is obvious (I hope!). What makes the CROSS APPLY work like a correlated subquery is the WHERE clause that (1) joins to the Orders table (p.ProductID = o.ItemID), and then limits it to the most recent purchase prior to the Order Date (WHERE p.PurchaseDate <
rderDate). Ordering the CROSS APPLY result by OrderDate DESC means the latest related record is returned, and then you just grab the PurchaseDate and UnitPrice. If there might not be related Purchases, then you'd use OUTER APPLY instead of CROSS APPLY (as OUTER APPLY is like an outer join to another query).Hope that clarifies things. If not, tell me where I went off the rails.
Last edited:
Will read carefully and follow as soon as I'm back to my desk.Okay, so you have a price history (ItemID, PurchaseDate, UnitPrice) and then you have a new order (OrderID, OrderDate, ... ItemID, Quantity, etc)
So you'd do something like
SELECT
FROM Orders o
CROSS APPLY (SELECT TOP 1 p.PurchaseDate, p.UnitPrice
FROM Purchases p
WHERE p.PurchaseDate <
AND p.ProductID = o.ItemID
ORDER BY p.PurchaseDate DESC) lp
The easiest way to understand the query is sort of from the bottom up. The Orders part is obvious (I hope!). What makes the CROSS APPLY work like a correlated subquery is the WHERE clause that (1) joins to the Orders table (p.ProductID = o.ItemID), and then limits it to the most recent purchase prior to the Order Date (WHERE p.PurchaseDate <
Hope that clarifies things. If not, tell me where I went off the rails.
Thanks for your time and help.
cheekybuddha
AWF VIP
- Local time
- Today, 07:17
- Joined
- Jul 21, 2014
- Messages
- 3,286
Don't be lazy, Piet!Okay, so you have a price history (ItemID, PurchaseDate, UnitPrice) and then you have a new order (OrderID, OrderDate, ... ItemID, Quantity, etc)
So you'd do something like
SELECT
FROM Orders o
CROSS APPLY (SELECT TOP 1 p.PurchaseDate, p.UnitPrice
FROM Purchases p
WHERE p.PurchaseDate <
AND p.ProductID = o.ItemID
ORDER BY p.PurchaseDate DESC) lp
The easiest way to understand the query is sort of from the bottom up. The Orders part is obvious (I hope!). What makes the CROSS APPLY work like a correlated subquery is the WHERE clause that (1) joins to the Orders table (p.ProductID = o.ItemID), and then limits it to the most recent purchase prior to the Order Date (WHERE p.PurchaseDate <
Hope that clarifies things. If not, tell me where I went off the rails.
Use code tags!
But the smiley faces in the code made me laugh!Don't be lazy, Piet!
Use code tags!
cheekybuddha
AWF VIP
- Local time
- Today, 07:17
- Joined
- Jul 21, 2014
- Messages
- 3,286
Me too!
dave,
isn't the rownum = 1 part the logical equivalent of a correlated-ish TOP 1? (Except I'm not sure how to set the ProductKey here...).
Okay, after relearning how to read... here's my answer:
The row_number stuff numbers each row in the group (grouped by ProductID and Quantity)... then once that's calculated, you retrieve only the first instance (where row_number()... = 1) ... and there ya go. The row_number stuff is just numbering the records where the ProductID and Quantity match... then just takes the last one (top one after you order in descending order).
Looks like Dave was actually understanding the question... unlike the person writing this response... but the query returns the result set that you want. As written, it should work if you have other ProductIDs in your real dataset.
Pieter
SQL:
SELECT *
FROM (
SELECT PricePK, ProductID, OrderedFromID, RegisteredDate, Quantity, UnitPrice, RegisteredBy, ROW_NUMBER() OVER (PARTITION BY ProductID, Quantity ORDER BY RegisteredDate DESC) as rn,
MAX(RegisteredDate) OVER (PARTITION BY ProductID, Quantity) AS MostRecentDate
FROM Purchases
) x
WHERE x.rn = 1;The row_number stuff numbers each row in the group (grouped by ProductID and Quantity)... then once that's calculated, you retrieve only the first instance (where row_number()... = 1) ... and there ya go. The row_number stuff is just numbering the records where the ProductID and Quantity match... then just takes the last one (top one after you order in descending order).
Looks like Dave was actually understanding the question... unlike the person writing this response... but the query returns the result set that you want. As written, it should work if you have other ProductIDs in your real dataset.
Pieter
Last edited:
Thanks for all your efforts. But unfortunately I'm still away from my PC. Will test and report back as soon as I'm back.
Million thanks.
It's not your reading problem. It's my poor English.
