none
improving query performance with columnar storage RRS feed

  • Question

  • Hi eb

    Would you attempt to improve classic star schema queries performance
    by simply migrating (or duplicating) underlying tables to columnar storage?
    Would keeping the underlying star structure AS IS with its many joins be counter productive to a columnar storage
    performance boost?
    Would very much appreciate your thoughts or experience with this subject.

    TIA

    Rea 



    Saturday, June 15, 2019 6:11 PM

All replies

  • I have *still* not tried a fully columnar table, but it *should* help most queries … some queries …

    I have tried adding the one columnar nonclustered index allowed on row tables, and that helped quite a bit!

    (except it hit a horrible bug in SQL 2016 and I never got to try it in production after that)

    However I have *still* not seen a full technical description (that I could follow) of what is involved with full columnar tables.  It sure looks like it could be far slower at some queries, as well.

    TANSTAAFL

    Josh


    Saturday, June 15, 2019 11:41 PM
  • Sunday, June 16, 2019 5:01 AM
    Answerer
  • Thanks a bunch!
    Interesting approach - Worth reading although quit unconventional!
    My main problem with this is that I have no control on queries being generated by visualisation tools like PowerBI and Tableau (working with direct query\live connection mode to sql server...).
    I guess some remodelling of underlying tables is in order to fit the columnar storage analytics paradigm - 
    that is Big tables including lookup descriptive values in them (replacing the many traditional star schema joins)...
    Hope this would do the trick!



    Monday, June 17, 2019 10:12 AM
  • Hi reapeleg,

     

    Have you solved this problem ?  In order to close this thread, please kindly mark your replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best regards,

    Dedmon Dai


    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, June 20, 2019 9:54 AM