Page 1 of 1
SQL, again
Posted: 10 Dec 2009, 09:45
by GrimStoner
Hi
I have two tables in my database... one called Ingredient, and one IngredientPriceHistory. They are fk'd on Ingredient.IngredientID = IngredientPriceHistory.IngredientID.
Everytime an ingredient's price is updated, a row gets added to the IngredientPriceHistory table, with the new price and the date.
I would like to make a view, that contains all the Ingredients with their most recent prices.
Some sample data :
Ingredients
Code: Select all
IngredientID Name
----------- ---------
1 Fish Paste
2 Bread
IngredientPriceHistory
Code: Select all
IngredientPriceHistoryID IngredientID Price Date
----------------------- ------------ ----- -----
1 1 15 2009-10-01
2 2 5 2009-10-01
3 1 16 2009-10-05
4 2 4.30 2009-10-07
The view should then return :
Code: Select all
Ingredient Price
---------- ----
Fish Paste 16
Bread 4.30
When I use
Code: Select all
SELECT dbo.Ingredients.IngredientID, MAX(dbo.IngredientPriceHistory.EffectiveDate) AS LatestPrice
FROM dbo.IngredientPriceHistory INNER JOIN
dbo.Ingredients ON dbo.IngredientPriceHistory.Ingredient = dbo.Ingredients.IngredientID
GROUP BY dbo.Ingredients.IngredientID
I get the Ingredient's ID with the date of its latest price... How can I combine this with the price at that date into one query so a view can display it?
Re: SQL, again
Posted: 10 Dec 2009, 10:34
by rustypup
you can treat result sets as tables... taking a quick stab...
Code: Select all
SELECT
Ingredients.Name,
MAX(HIST.Price) AS 'PRICE'
FROM
((SELECT DISTINCT
IngredientID AS 'ID',
MAX(Date) AS 'LATEST'
FROM
IngredientPriceHistory
GROUP BY
IngredientID) AS DATES LEFT JOIN
IngredientPriceHistory AS HIST ON
(HIST.IngredientID=DATES.ID) AND
(HIST.Date) = DATES.LATEST)) LEFT JOIN
Ingredients ON
Ingredients.IngredientID=DATES.IngredientID
GROUP BY
Ingredients.Name
Re: SQL, again
Posted: 10 Dec 2009, 10:52
by GrimStoner
Thanks... Working great.
EDIT : Had to tune it a bit to use as a view... was moaning about the 'ID' and 'Latest' text :
Code: Select all
SELECT dbo.Ingredients.Name, MAX(HIST.PricePerUnit) AS PRICE
FROM (SELECT DISTINCT Ingredient AS IDHere, MAX(EffectiveDate) AS LatestDate
FROM dbo.IngredientPriceHistory
GROUP BY Ingredient) AS DATES LEFT OUTER JOIN
dbo.IngredientPriceHistory AS HIST ON HIST.Ingredient = DATES.IDHere AND HIST.EffectiveDate = DATES.LatestDate LEFT OUTER JOIN
dbo.Ingredients ON dbo.Ingredients.IngredientID = DATES.IDHere
GROUP BY dbo.Ingredients.Name
Re: SQL, again
Posted: 10 Dec 2009, 11:12
by RuadRauFlessa
ROFL. Will work but it will be inefficient.
Try this instead
Code: Select all
SELECT
dbo.Ingredients.Name,
(
SELECT TOP1
HIST.PricePerUnit
FROM
IngredientPriceHistory WITH (NOLOCK)
WHERE
IngredientPriceHistory.IngredientPriceHistoryID = IngredientPrice
ORDER BY IngredientPriceHistory.EffectiveDate
) AS PRICE
FROM
IngredientPrice WITH (NOLOCK)
Then you don't need any fancy from clauses and I bet you wit will run faster
Re: SQL, again
Posted: 10 Dec 2009, 11:47
by doo_much
RRF - why the "WITH (NOLOCK)"? Just for speed?
Don't you open youself up to the possibility of data corruption or reading non-current data?
Or are my Google skills letting me donw?
Re: SQL, again
Posted: 10 Dec 2009, 12:36
by RuadRauFlessa
LoL. An uncommitted transaction will not be process yes you are correct. Also it will not wait for any read/write locks. Instead of waiting for the transaction which is locking the read it will read the data which was last committed. Personally I have never found any discrepancy regarding the data you get back and I worked on DB servers which process up to and over a couple hundred thousand writes per second.
Oh and it does make a difference in terms of speed depending on the data you are working on.
Re: SQL, again
Posted: 10 Dec 2009, 12:50
by doo_much
Ah thanks.
Difficult question to answer I know, but how big an improvement in the query speed should one see?
Re: SQL, again
Posted: 10 Dec 2009, 13:42
by RuadRauFlessa
doo_much wrote:Ah thanks.
Difficult question to answer I know, but how big an improvement in the query speed should one see?
Depends on how active the database is.
I used to work at C-Track SA and their tracking db has a table with about 1.5bil records in it. On that table a 2sec query using WITH (NOLOCK) means a 30-40min query without it. On smaller databases the difference won't be so drastic but it will also be more random depending on the type of services connecting to the database.
Say you have a website processing updates to the db then it will truly be random or actually negligible, depending on the amount of traffic. If you have a couple of applications constantly making changes to the database on on ongoing basis like an SLA clock which needs to update every record's times tamp in the table then it could potentially have a very big impact. Where I currently sit I have the latter situation where you have clock values which needs to be constantly updated on records in the DB. Yes some would say that calculating it on the fly when the data needs to be presented could be a better option but it needs to be calculated constantly in any case for escalation purposes. On the client side all of the queries needs to use WITH (NOLOCK) as we don't want it to block the write locks needed for the updating of the time stamps. And as for the performance bonus, we get about 30-45% performance bonus depending on the complexity of the query.
The long and short is rather that you have to look at your own situation. I as a rule would actually say use it and abuse it as much as possible. Even with our clock running I have not come to a situation where you actually get inconsistent data.
Re: SQL, again
Posted: 10 Dec 2009, 13:48
by doo_much
Thanks. Wish I'd garnered this little jewel three years ago when I was actively doing reports off SQL!
Re: SQL, again
Posted: 10 Dec 2009, 14:16
by RuadRauFlessa
Well drawing reports is primarily what I do and it does help a lot. Keeping queries from timing out
