Denormalize for Performance

By 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


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

My informix Sql query retruns Null always (5h)

Vehicle availability query (1d)

SSDT - Unable to reference 'master' or 'msdb' with new sdk style project (1d)

Ola Hallengren backup jobs (1d)

Compare alpha results to INT after get values from a string (5d)

Query performance Call Center data (6d)

Looking for on Premises tool to read data from SQL logs and populate data warehouse (6d)

Possible SQL 2014 to 2016 Issue - Some Application Functions Slow/Failing (7d)

- Advertisement -