|
Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
|
|
Denormalize for PerformanceBy Bill Graziano on 10 January 2001 | Tags: Table Design Doug writes "Glad to see a cool SQL site (Big YEA for the design\model section, this is often over looked!) . . . What I need to do is faily simple for the most part, but have yet to come up with a solution that would work for web delivery (somewhat fast). I need to select _the_ lowest price for 25 different products at once. The only solution I can come up with is to use 25 different sub queries and just pound on the db in a stored procedure. I guess I am looking for creative solutions to this. I have thought of demoralization and a couple of other client side array tricks, but nothing solid yet . . ." Interesting question Doug. Yummmmm . . denormalization :)
The full text of the question is "Glad to see a cool SQL site (Big YEA for the design\model section, this is often over looked!).
Anyways- Usually when I am surfing around and come to a new site, I generally have a pretty good idea of how it works (or at least think I do). Well that was the case up until I came across pricewatch.com's default page. Pondered how it worked for months, but never had a call for such a thing until now (can I bill for all them hours of past thinking?). What I need to do is faily simple for the most part, but have yet to come up with a solution that would work for web delivery(somewhat fast). I need to select _the_ lowest price for 25 different products at once. The only solution I can come up with is to use 25 different sub queries and just pound on the db in a stored procedure. I guess I am looking for creative solutions to this. I have thought of demoralization and a couple of other client side array tricks, but nothing solid yet. Results should be something list... Scuba Tank - $110 Regulator - $220 Fins - $19 Any help would be great. Thanks, Doug D." Thanks for the great comments. I hope you don't mind if I use your question to add to my table design section. First let's cover the easy part. Suppose your database looks like this: Products ProductPrices
ProductID ProductID
ProductName VendorID
Price
As you indicated the easiest way to write this query is using subqueries. It looks like this: SELECT ProductID,
ProductName,
MinPrice = (SELECT MIN(Price)
FROM ProductPrices
WHERE ProductID = P.ProductID)
FROM Products P
That query will get you the lowest price for each item. It actually wouldn't be all that slow. However, if you update that table once per day and query it 100,000 times per day it could make a big difference.
Denormalizing can take a number of different forms. In the past I have stored subtotals in databases to save me from computing them on the fly. We'll do something like that here. Let's assume we have a table that looks like this: WebPrices ProductID ProductName MinPrice A slight modification of our query above might give us this: DELETE FROM WebPrices
INSERT WebPrices
SELECT ProductID,
ProductName,
MinPrice = (SELECT MIN(Price)
FROM ProductPrices
WHERE ProductID = P.ProductID)
FROM Products P
Let's assume we run this query every time a price or description changes which as I said earlier is once per day. Now our query to return the data looks like this: SELECT ProductID, ProductName, MinPrice FROM Web Prices Much better. I think that's what I'd do if I were you. -graz
|
- Advertisement - |