We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state of all jobs executed in last 6months in a database table. Database server used in Microsoft sql server 2008. With the growing number of jobs or records in table, time taken for updating the records in a table is also going slower than before.
We wanted to redesign our table and schema to update jobs state in real time scenario without causing any delay to job execution. We are thinking of splitting this single table to multiple tables for each user so that updating one user job state should not cause any delay in updating other user job state. To add a note here: the number of users is less than 25 but the number of jobs for each user is more than 50,000. Also, there is no dependency between each user jobs.
- Will this table splitting be a better or an optimized way to get the table update or search faster?
- Or Will row locking feature in microsoft sql sever solve this case?
- Do we have any best optimized way to handle this case?
- Will the index be the reason for slowness?
Table:
Job id [primary key] | Job name | started time | End time | status
We have 2 indexes: one based on job start and end time. Second index is only based on job name.
Thanks.
Best Answer
The best strategy for your case is to properly index the table, update statistics and defrag it. That should solve the problem.
There is no inherent limitation on row count in SQL Server. With the right indexes it should not start to become slower.
You need to look at your select and update queries and build useful indexes for them.