One large daily table versus one monthly table and one daily table, what is better for performance?

[DaxPadawan]

Hello, we currently have a table of transactions on a daily basis (so we're talking 5+ million rows) with 7 columns. 

For our analysis, half the transactions will only be looked at on a monthly basis. The other half is needed on a daily basis. However, there are some measures that we would calculate that would use both halves of the data.

From a dashboard performance perspective, would it make sense to separate the monthtly transactions from the daily transactions and have two separate tables? They would still be linked together through a master date table. 

[v-tangjie-msft]

Hi @DaxPadawan ,

Implement partitioning on your single table based on the date. Partitioning can significantly improve query performance by limiting the number of rows to scan for a given query. For daily analysis, the query would scan only the relevant day's partition, and for monthly analysis, it would scan the partitions for the entire month.
For the monthly analysis part, consider creating an aggregated table that summarizes the daily transactions on a monthly basis. This table would be much smaller and could significantly speed up queries that don't require the granularity of daily data.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Bmejia]

I believe this is personal preference.  I would assume that performace will slighly change when you have two table rather then just one.   Preferebaly I would try to work with one table if possible, particularly if they contain the same information.