0

One vendor need to store in a database multiple records. There are 5 tables (a table per element - elements are: pressure, flow, volume, temperature and distance). Each table have the following columns: ID, Timestamp, Value; Each day, a table will have approximately 50.000 records.

Database used is SQL Server 2017, on a Windows Server 2016 machine. There will be a lot of analytics to make (charts, reports, etc.) (daily, weekly, monthly, yearly)

So, the vendor wants to create these 5 tables every day for each day of one year (example: "Table_yyyyMMdd_Pressure") and store into it values from that day only; this approach will get the database to have something like 1825 tables per year.

Our request was to create only 5 tables and store all records there - which will get to have approximately 18 000 000 records in one year per table.

From my point of view, our approach is better taking into account:

  1. queries will be run only on those 5 tables once (per table) instead of running queries multiple times per each table per each day;
  2. easier to create reports on a single table than on multiple tables
  3. easier to change the structure of the tables if will be necessary
  4. less complex to write code/procedure

Can you please give me more pro's and con's regarding these 2 approaches?

Thank you!

  • 4
    Do NOT create daily tables. The whole point of having an RDBMS is to normalise data. Properly indexed, reporting will not be a problem. The amount of data you are dealing with is tiny by today's standards - You could run the reporting using SQLite on your mobile phone & it'd be fast. – Philᵀᴹ Jun 27 '18 at 11:26
1

Avoiding separate tables by date would be best. Besides the benefits you mentioned, performance will likely be better too.

If each table has a row for the same ID and Timestamp (e.g. a device that reports all 5 readings at the same time), you could have a single table with ID, Timestamp, pressure, flow, volume, temperature, and distance.

You might also consider using columnstore and partitioning by date for maximum performance and manageability (respectively).

0

50k rows is a small amount, sql will handle many years of data in a single table without a problem, especially with so little columns.

Having the data split across hundreds or thousands of tables will make you want to commit suicide no later than the second time someone asks you to write a query to retrieve data. This would be anti-querable. You would have to create and maintain views or dynamic queries to alleviate this. I cannot think of any pros this architecture would give.

As for retrieving data for analytics, I agree with Dan Guzman a columnstore could improve performance even further. But even with this amount of data row store indexes would do fine. If you decide to go with columnstore, You might want to consider to do a regular maintenance on a clustered columnstore index as it gets auto maintained every 1M rows inserted (and you might find it more useful to have it done more frequently at this pace of inserts).

With single type table per single type of data you could then also easily create a multidimetional analytics using Analysis Services as these would be actually your Fact Tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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