locked
Converting the normalized table into Flat table RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    Currently we are designing a database in which tables are normalized. Few of the tables have more than 20 to 30 columns as foreign keys. So while querying these tables, we have join 20 to 30 tables every time. Some of the developers feel it is difficult to do this while creating API's. So i suggested to to create completely de-normalized view.

    My question is if do that, will it affect the performance, because once they starts to use this view for everything, instead of querying the table directly even for a query with 3 to 4 joins and table size grows.

    Is there any other approach to do this, instead of creating view?

    One more option comes to my mind is, i can create a de-normalized table and push data from main table using ETL.

    Thanks in advance..!



    • Edited by Rathesh Monday, May 25, 2020 8:22 AM
    Monday, May 25, 2020 8:19 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    One question seems to be if there is a performance penalty to use a view that jons 30 tables instead of only join the tables you actually need. Yes, there is. SQL Server *can* eliminate tables when you query a view and don't refer to a table in any way in your query, but there are a number of requirements for that and I doubt you will see that elimination very often. So you should expect the whole lot of tables to be joined when the wire if queries, regardless of how many are *actually needed. To be more specific, you can try yourself and check the execution plan to see what tables are referred to.

    You can create a number of views, with different "aspects" of the data, perhaps?

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Monday, May 25, 2020 9:31 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rathesh,

    >Is there any other approach to do this, instead of creating view?

    Yes.You can create views.

    And if you want to make data ananlysis, you can try SSAS.

    And this maybe helpful: database-denormalization-with-examples

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 26, 2020 2:09 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rathesh,

    Is the reply helpful?

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 27, 2020 1:14 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rathesh,

    Is the reply helpful?

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 28, 2020 1:16 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Rathesh,

    Is the reply helpful?

    Best Regards.

    yuxi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, May 29, 2020 12:56 AM