Tricky update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Tricky update

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-03 : 12:31:38

Fellas; In order to get some quicker reads from my database I'm doing a denormalization of a table. What I basically gotta do is to aggregate some data and update that into another table with a predefined number of rows. The aggregation might return any number of rows but the denormalized table has room for only the 3 best prices from each market (and the best would actually be the highest prices so prices should be ordered descending). Any clue to how I should make this update? It needs to be efficient as it will be run very frequently. DDL:

DECLARE @Market_1 table 
  (ID int IDENTITY(1, 1), MarketID int, Price int, Volume int)
DECLARE @Market_2 table 
  (ID int IDENTITY(1, 1), MarketID int, Price int, Volume int)
DECLARE @Final_result table 
  (ID int IDENTITY(1, 1), MarketID int, Price int, Volume int)

--> Table to be denormalized
INSERT INTO @Market_1
SELECT 1, 3, 200 UNION ALL SELECT 1, 3, 150 UNION ALL SELECT 1, 2, 100 UNION ALL
SELECT 1, 1, 100 UNION ALL SELECT 1, 2, 100 UNION ALL SELECT 1, 2, 100 UNION ALL
SELECT 1, 4, 100 UNION ALL SELECT 1, 5, 100 UNION ALL SELECT 2, 3, 200 UNION ALL
SELECT 2, 3, 150 UNION ALL SELECT 2, 2, 100 UNION ALL SELECT 2, 1, 100 UNION ALL
SELECT 2, 2, 100 UNION ALL SELECT 2, 2, 100 UNION ALL SELECT 2, 4, 100 UNION ALL
SELECT 2, 5, 100

--> Denormalized table
INSERT INTO @Market_2
SELECT 1, NULL, NULL UNION ALL SELECT 1, NULL, NULL UNION ALL SELECT 1, NULL, NULL
INSERT INTO @Market_2
SELECT 2, NULL, NULL UNION ALL SELECT 2, NULL, NULL UNION ALL SELECT 2, NULL, NULL

--> For illustration purposes
INSERT INTO @Final_result
SELECT 1, 5, 100 UNION ALL SELECT 1, 4, 100 UNION ALL SELECT 1, 3, 350
INSERT INTO @Final_result
SELECT 2, 5, 100 UNION ALL SELECT 2, 4, 100 UNION ALL SELECT 2, 3, 350

SELECT * FROM @Market_1
SELECT * FROM @Market_2
SELECT * FROM @Final_result

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-03 : 12:40:11

Ya lost me...how is table 2 denormalized?

I would have expected 3 columns in the denorm'ed table...

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-03 : 12:47:21

Well, should maybe have used another word. Aggregated and filtered would probably be better. I need the three best prices and the sum of the volume for each of those into the second table with the matching MarketID. Did that make any more sense?

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-03 : 13:23:43

Well you said effecient...so I don't think this counts....



  SELECT a.MarketId, b.MAX1_Price, SUM(Volume) 
    FROM Market_1 a
    JOIN (
	   SELECT MarketId, MAX(Price) AS MAX1_Price 
	     FROM Market_1 GROUP BY MarketId
	  ) AS b
      ON a.MarketId = b.MarketId AND a.Price = b.MAX1_Price
GROUP BY a.MarketId, b.MAX1_Price
UNION ALL
  SELECT a.MarketId, MAX(a.Price), SUM(Volume) 
    FROM Market_1 a
    JOIN ( SELECT MarketId, MAX(Price) AS MAX2_Price 
	     FROM Market_1 c 
	    WHERE EXISTS (
		SELECT MarketId, MAX(Price) AS MAX2_Price 
	     	  FROM Market_1 d 
		 WHERE c.Price < d.Price AND c.MarketId = d.MarketId
	      GROUP BY MarketId)
	 GROUP BY MarketID
	  ) AS b
      ON a.MarketId = b.MarketId AND a.Price = b.MAX2_Price
GROUP BY a.MarketId

Brett

8-)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-03 : 14:06:57

Umh, I see what you mean

And I'm afraid it's not really what I was looking for either...the data you have created a select for here should be placed in @Market_2 so that @Market_2 in the end will look exactly like @Final_result and you have also only selected the 2 best prices not 3 (but I could have managed to do that myself). Crossing my fingers that it will be possible...

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-04 : 05:22:55

Nobody?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 09:07:28

Does this work for you??

DECLARE @Market_1 table
(ID int IDENTITY(1, 1), MarketID int, Price int, Volume int)
DECLARE @Market_2 table
(ID int IDENTITY(1, 1), MarketID int, Price int, Volume int)
DECLARE @Final_result table
(ID int IDENTITY(1, 1), MarketID int, Price int, Volume int)

--> Table to be denormalized
INSERT INTO @Market_1
SELECT 1, 3, 200 UNION ALL SELECT 1, 3, 150 UNION ALL SELECT 1, 2, 100 UNION ALL
SELECT 1, 1, 100 UNION ALL SELECT 1, 2, 100 UNION ALL SELECT 1, 2, 100 UNION ALL
SELECT 1, 4, 100 UNION ALL SELECT 1, 5, 100 UNION ALL SELECT 2, 3, 200 UNION ALL
SELECT 2, 3, 150 UNION ALL SELECT 2, 2, 100 UNION ALL SELECT 2, 1, 100 UNION ALL
SELECT 2, 2, 100 UNION ALL SELECT 2, 2, 100 UNION ALL SELECT 2, 4, 100 UNION ALL
SELECT 2, 5, 100

--> Denormalized table
INSERT INTO @Market_2
Select
MarketId, Price, Volume
From
(
SELECT
MarketId,
Price,
Volume = sum(Volume),
rank = (Select count(distinct price) From @Market_1 Where MarketId=A.marketId and price >= A.price)
FROM @Market_1 as A Group By MarketId, Price
) as Z
Where rank <=3
Order By MarketId, Price Desc

--> For illustration purposes
INSERT INTO @Final_result
SELECT 1, 5, 100 UNION ALL SELECT 1, 4, 100 UNION ALL SELECT 1, 3, 350
INSERT INTO @Final_result
SELECT 2, 5, 100 UNION ALL SELECT 2, 4, 100 UNION ALL SELECT 2, 3, 350

SELECT * FROM @Market_1
SELECT * FROM @Market_2
SELECT * FROM @Final_result

Corey

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-04 : 09:33:41

I must say I have a hard time understanding how this rank-thing of yours is working but it seems to be doing the job. However, the next time I run this it will insert an additional 3 rows for each market (given that there are 3 prices available) and this is why I was basically asking for an *update* instead of an insert. It might be possible/easier to do it in another way but my initial thought was to just update the 3 fields in @Market_2 (all tables will be normal tables in production, table-variables are just used for testing) each time instead of inserting new rows all the time. I only need the 3 best prices (and ALWAYS 3 prices, NULL if none are present)and I need the select in the end to be as light as possible, i.e. SELECT * FROM @Market_2 WHERE MarketID = 1. This select will eventually be run several hundred times a second...do you understand what I mean?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 09:46:04

well why don't you put it all on one row per market... then it could be an update...

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 09:49:08

Select
MarketId,
Price1 = max(case when rank = 1 then Price else null end),
Volume1 = max(case when rank = 1 then Volume else null end),
Price2 = max(case when rank = 2 then Price else null end),
Volume2 = max(case when rank = 2 then Volume else null end),
Price3 = max(case when rank = 3 then Price else null end),
Volume3 = max(case when rank = 3 then Volume else null end)
From
(
SELECT
MarketId,
Price,
Volume = sum(Volume),
rank = (Select count(distinct price) From @Market_1 Where MarketId=A.marketId and price >= A.price)
FROM @Market_1 as A Group By MarketId, Price
) as Z
Where rank <=3
Group By MarketId
Order By MarketId--, Price Desc

Corey

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-04 : 10:00:44

Now we're talking! I'll have to try this out but if this select is working like it seems I think we have a problem solved!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-04 : 10:16:06

Good!

Good luck with it...

Corey

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-08 : 10:40:25

I'm in the process of implementing the select you have here into my database as a trigger but as it seems aggregates (max) are not allowed directly in update-statements. What exactly does the MAX around each case-statement do here? I just don't get it...

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 10:22:32

Well from the inner select aliased as 'Z', you will get three rows per MarketId. One row will have a rank of 1, one will have rank 2 and the last will have rank 3. So with the case statements it looks something like:


Rank    Price1   Volume1   Price2   Volume2   Price3   Volume3
-----   ------   -------   ------   -------   ------   -------
  1       5        100      null      null     null      null
  2      null      null      4        100      null      null
  3      null      null     null      null      3        350

By using max(), I combined the 3 rows showing only the non-null values.

Corey

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-09 : 10:23:33

Oh... and a possible suggestions. Save the new values in a temp-table and then update.

Corey

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-09 : 10:31:10

Yup, I figured it out after a while and I must say that it was a very nice little trick! I did end up putting the select into a table-variable and inserted from there and it worked brilliantly. Had to do an outer join to fill up empty markets with NULL aswell and didn't manage to add it in the select so I just did the join on the table-variable instead. This part now seems to be working great, just have to figure out how to get a cascade of 2 triggers to work properly...

Thanx for the help man, I could never have done it without you

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources