Intentional Normalization Violation

Question

Intentional Normalization Violation

BSavoie

SSCertifiable

Points: 6309
More actions
September 29, 2016 at 5:52 pm

#330965

I'll use an example. Consider an Orders table and an OrderStatus table. There can be 0 or more OrderStatus records for each Order record. Of course OrderStatus has an OrderId key reference to Orders. We're talking tens of millions of OrderStatuses and millions of Orders.
Many of our queries could care less about the many statuses for a given Order, all they care about is the current status. For these queries, having to join Orders & OrderStatuses has become much, much too slow.
I'm considering adding an OrderStatusCode and OrderStatusDate column right into the Orders table. Then use insert/update triggers on the OrderStatus table to make sure that these OrderStatusCode & OrderStatusDate columns are always kept current. This would allow queries that only care about the current status to query the Orders much more easily, and, MUCH faster I'm betting.
This is probably not an ideal solution, but it has the benefit of letting me speed up many of our queries without having to re-architect the entire system. I would have the luxury of only updating the slow queries to take advantage of this, and leave everything else untouched.
I can't be the first person that ever considered this alternative. Any thoughts?
Thanks as always.
.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

My take on this would be to separate the current status and the status history as those are two different things and should not be combined in a model entity.

😎

Current schema with the combination of order status and order history, requires a lookup for the latest entry in the history table every time in order to retrieve the current value.

,-----------, ,----------------, ,----------------,

| Order_ID |-+--, | OrderStatus | ,--+-| OrderStatus_ID |

| (Details) | '-o<| Order_ID | | | (Details) |

'-----------' | OrderStatus_ID |>+--' '----------------'

| TimeStamp |

'----------------'

Separation of status and history, requires a lookup in the history table only when querying for historic values.

,----------------, ,---------------------------, ,-----------------,

| Order_ID |-+-' ,----------------, | | OrderHistory_ID |

| OrderStatus_ID |>+---+-| OrderStatus_ID |-+-, '----o<| Order_ID |

| (Details) | | (Details) | '-------o<| OrderStatus_ID |

'----------------' '----------------' | TimeStamp |

'-----------------'

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

BSavoie (9/29/2016)
...
Many of our queries could care less about the many statuses for a given Order, all they care about is the current status. For these queries, having to join Orders & OrderStatuses has become much, much too slow.
...

Have you investigated the cause? Is there an index on the OrderStatus table to support these queries?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Maybe a filtered index on the order status table that only keeps the current statuses?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

ScottPletcher SSC Guru Points: 101098 More actions · Answer 4

In this case, I think it's ok to add the current status to the Orders table, using an efficient, well-written trigger to maintain the status.

However, the lookup of the current status should not present that much overhead either, except for a all orders or at least a whole lot of orders. Just be sure you cluster the OrderStatus table correctly. It would be clustered on either ( OrderId, StatusDate ) or perhaps (Status, OrderId, StatusDate ) if you care only about Active orders and they represent a small % of the total orders.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".