January 1, 2014 at 12:45 am
My project involves a business requirement to capture the activities that occur daily which might average to over 500 entries per day. Although SqlServer can handles billions of rows, is it not a better design to capture the logs of each day in a separate table? i.e. create a new table every day, because the older logs are not used at all and it would degrade query performance to have these old logs in the same table.
Which is the better design? One new table each day or all the logs in a single large table?
IF one new table each day is the efficient one, is there an easy mechanism in SqlServer Management Studio 2012 to schedule these creation of new tables easily??
January 1, 2014 at 1:10 am
For such small amount of records per day, I'd do it as simple as possible – One table for all days. By the way I don't think that having a table for each day is a good idea even if there were lots of records.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 1, 2014 at 6:56 am
One table. 500 rows is nothing, 200 000 rows per year is nothing. 365 tables per year is horrible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 2, 2014 at 8:36 am
rnithish (1/1/2014)
My project involves a business requirement to capture the activities that occur daily which might average to over 500 entries per day. Although SqlServer can handles billions of rows, is it not a better design to capture the logs of each day in a separate table? i.e. create a new table every day, because the older logs are not used at all and it would degrade query performance to have these old logs in the same table.Which is the better design? One new table each day or all the logs in a single large table?
IF one new table each day is the efficient one, is there an easy mechanism in SqlServer Management Studio 2012 to schedule these creation of new tables easily??
Suggest you to create 2 tables :
CurrentLogs
ArchiveLogs
And have a process to first put rows from CurrentLogs into ArchiveLogs and then load data into CurrentLogs.
This way you have a small CurrentLogs table and you keep your data intact into ArchiveLogs for historical analysis.
Make sure to have a purging job based on your business requirements to purge data that is no longer used/required.
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 2, 2014 at 12:08 pm
If in fact 'the older logs are not used at all' as you state, you simple need to create a table to track whether or not a purge has taken place for the current day, or schedule a task to do this in the wee hours. Daily tables is a bad idea...it's much harder to aggregate data from multiple tables, something you may not need now, but keep your options open for future needs. Also, keep in mind that your table structure might change...applying the change across daily tables would be cumbersome. Just choose your indexes carefully (for sure on the date column) and your queries responses should not suffer.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply