SQL, again

Get help on programming - C++, Java, Delphi, etc.
Post Reply
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

SQL, again

Post 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?
Image
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL, again

Post 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
Most people would sooner die than think; in fact, they do so - Bertrand Russel
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

Re: SQL, again

Post 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
Image
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL, again

Post 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
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL, again

Post 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? :)
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL, again

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL, again

Post by doo_much »

Ah thanks.

Difficult question to answer I know, but how big an improvement in the query speed should one see?
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL, again

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
doo_much
Registered User
Posts: 26022
Joined: 13 May 2004, 02:00
Location: Getting there...
Contact:

Re: SQL, again

Post by doo_much »

Thanks. Wish I'd garnered this little jewel three years ago when I was actively doing reports off SQL! :wink:
MOOD - Thirsty

A surprising amount of modern pseudoscience is coming out of the environmental sector. Perhaps it should not be so surprising given that environmentalism is political rather than scientific.
Timothy Casey
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL, again

Post by RuadRauFlessa »

Well drawing reports is primarily what I do and it does help a lot. Keeping queries from timing out :P
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Post Reply