Denormalize for Performance

By Bill Graziano on 10 January 2001 | 0 Comments | 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

Discuss this article: 0 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

Implementing Table Interfaces (19 May 2008)

Implementing Table Inheritance in SQL Server (20 February 2008)

Custom Auto-Generated Sequences with SQL Server (24 April 2007)

The Case for the Surrogate Key (9 August 2002)

Using TABLE Variables (7 June 2002)

More Trees & Hierarchies in SQL (1 May 2002)

Default Constraint Names (24 January 2001)

Temporary Tables (17 January 2001)

Other Recent Forum Posts

Finding last day date code (0 Replies)

Msg 4104 (54 Replies)

Query Help (1 Reply)

script running longer (3 Replies)

Passing Column as Parameter (1 Reply)

Duplicates in query (3 Replies)

Convert UTC to local time (1 Reply)

view to be indexed but has a subquery (9 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -