How to query some data from T_Order table which is a big table with 50 million rows? RRS feed

  • Question

  • User-252718598 posted

    there is a situation I think over as below:

    1. there is a big table(T_Order: used to store the order infos of customers) with more than 50 million rows.

    2. now, it's very slow for me to search something from the single big table. Therefore, I have to divide this table into 10 parts depends on the field(OrderData).

    just like below:

    T_Order(2016) T_Order(2015) T_Order(2014) T_Order(2013) T_Order(2012) ... ...

    3. it's a lot faster than before while only searching the order infos of 2016.

    Now, question:

    1. If I want to search the order infos between 2012 and 2015, how to do that? Search the results from the tables(2012-2015) and merge together in memory? It's not good of perfmance.Frown

    2. If I want to search the order infos by using the other field(OrderPrice > 100), how to do that? Search the results from each table and merge together in memory? It's also not good of perfmance.Frown

    3. If I divide a big table to small parts, how to design  the SQLHelper.cs (CURD operations)Frown

    thanks for you in advanced, who can give me some helpful answers or useful links?Smile

    Friday, May 20, 2016 8:25 AM


  • User753101303 posted


    AFAIK if you really have to split those tables you do have partition features in SQL Server that allows basically your app to still see a single logical table. Try https://msdn.microsoft.com/en-us/library/ms188730.aspx

    If not done I would really try to find first why it is slow. For example it could be just that an index was not used because of a bad year selection (for example using the YEAR function). If you change the design without knowing exactly which performance problem you have, you may end up in doing more work than needed or doing a change that doesn't enhance the situation or even make it worse...

    In some case you prefer to preprocess at regular live data into a separate database which is designed to answer to particular questions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, May 20, 2016 12:13 PM