0

So, I am working on Angular + .NET Core Web Appliction with SQL Server 2017 as the database. So, the new feature requires that we have to store daily data of users belonging to all countries.

Example:

enter image description here

and so on...

Now, as the application will grow, the nummber of users will also increase, thus, making millions of records. I have to fetch monthly, yearly quarterly data from this table, which will take too much time to fetch whatever query I will use. So, I want to ask what can I do to speed up the process of fetching the data fastly? Also, can anyone please tell me what better design can be given to this table?

NOTE: I am a beginner in Database work and has very little and limited knowledge.

Improve this question
2
  • 3
    You should consider adding details to your question to show where the perceived problem will be. Is the problem anticipated to be with querying the table you show in your question? Or is the problem around gathering the login data required to construct your new table? Without the details, it's difficult to answer your question accurately.
    – Hannah Vernon ♦
    Commented Jan 10, 2020 at 17:18
  • 2
    With proper indexing, millions of records is no problem at all. As sketched out, records would be very small: eight bytes for a DATETIMEOFFSET and eight bytes for a pair of INTs. You could have 50 million records and still store it in memory. I say give it a shot and come back later if you can't get the performance you need.
    – Jon of All Trades
    Commented Jan 10, 2020 at 23:01

2 Answers 2

Reset to default
1

Your table is going to be very narrow with two INTs and a DATE, so even with hundreds of millions of rows, storage will be relatively light.

You can also create summary tables off of this main table, like if you need monthly or yearly logins, you can create these as separate tables.

Improve this answer
0

You can try with monthly partition table design using date column. It will keep each month's data in appropriate data files and easy to manage. Index B-Tree structure would faster in this method with big volume of data.

  • Index searching page count will be less and faster as per B-Tree structure

  • Easy to make read-only, remove specific files from database

  • Can take backup for individual files/file groups in huge database like partial backup.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.