March 6, 2012 at 7:50 am
Dear All,
I have two tables in my OLTP.
Table1 with C1, C2 columns. and
Table2 with D1, D2 columns.
Mostly, when i SELECT Table2, I will join with Table1 for columns C2.
My Question:
Is it a good practice to have the columns C2 ALSO in Table2
so as to avoid the JOIN, as Table1 is very huge.
(obviously, i will take care of updating New C2 column in Table2)
something like:
Table2 with D1, D2, C2.
Always Appreciating your help
March 6, 2012 at 8:10 am
DBA Rafi (3/6/2012)
Dear All,I have two tables in my OLTP.
Table1 with C1, C2 columns. and
Table2 with D1, D2 columns.
Mostly, when i SELECT Table2, I will join with Table1 for columns C2.
My Question:
Is it a good practice to have the columns C2 ALSO in Table2
so as to avoid the JOIN, as Table1 is very huge.
(obviously, i will take care of updating New C2 column in Table2)
something like:
Table2 with D1, D2, C2.
Always Appreciating your help
What you are referring to is denormalization. It is not "good practice," but sometimes necessary based on the business requirements. A properly designed database should have no problem with the current join. Of course, we all know that some times we inherit designs that are not easy to change. Before denormalizing, ask yourself what you are gaining and what you are losing by doing this? What happens if the data gets out of sync? How do you prevent that? Is this only for 1 query, or are many queries doing this? How often? Can you put the data in table1 and NOT store it in table2?
Set up a test database with the same data in different scenarios and see if changing this actually changes performance. Maybe it will, maybe it won't. What if the problem is a missing index or a non-SARGable filter that can be fixed. I would evaluate all options before denormalizing.
Jared
CE - Microsoft
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply