Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

  1. Will this table splitting be a better or an optimized way to get the table update or search faster?
  2. Or Will row locking feature in microsoft sql sever solve this case?
  3. Do we have any best optimized way to handle this case?
  4. 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.

share|improve this question

migrated from stackoverflow.com Jul 15 '12 at 13:20

This question came from our site for professional and enthusiast programmers.

    
"we have 2 indexes" No, you have 3. There is also an index "under" the PK (Job id). Have you specified NONCLUSTERED for the PK? If no, then both secondary indexes are "fatter" and probably less efficient. Also, show us your UPDATE statement. –  Branko Dimitrijevic Jul 15 '12 at 9:21

2 Answers 2

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.

share|improve this answer
    
We have more number of concurrent jobs >5000 jobs starting at the same time for all different users. We are setting the index based on user and job start time but even it is slower now and this is causing our jobs to run slower than before. –  user1360733 Jul 14 '12 at 13:03
    
Please post your queries to see what indexes they need. –  usr Jul 14 '12 at 13:08
    
@user1360733 why are you starting 5000 jobs for individual users at the same time? Other domains have learned not to do this. For example, races of all kinds have staggered starts or stages so that everyone isn't jumping out of the gate and killing each other at the start. –  Aaron Bertrand Jul 15 '12 at 19:37

1.Will this table splitting be a better or an optimized way to get the table update or search faster?

How will you do the update?Will you update based on user or some other criteria?Having multuiple tables is bad idea better have the tablke partitioned based on user as you have some 25 users so you will have 25 partitions.You can optimized updates using other techniques but as you mentioned data is growing I would suggest that you use partitioning so that droping and recreating indexes is easier.

2.Or Will row locking feature in microsoft sql sever solve this case?

SQL server by default uses this feature but it can escalate based on the number of rows to be updated and again it would be better to know how updates happen and how many rows at a time.Also,check how your indexes are created?is allow_rowlocks is true or false?

3.Do we have any best optimized way to handle this case?

It depends what kind of updates you are doing.It could be optimized.

4.Will the index be the reason for slowness?

This could be a possible reason but you might need indexes for the select queries.Again this depends on how you update your data and how many rows at a time.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.