Problem
We have a table where we are dumping data on every 5 mins
Every 5 mins we are dumping approx 0.1 to 0.2 million rows
The tables has 5 indexes and we have a complaint that dump is getting slower over the time
It was taking 40-50 seconds , which needs to come down as we had to do other process after the dump
Solution
I checked the tables and could not find anything special
Only way to optimize is to work on Index. As I removed all the indexes the dump completed in 2 seconds
And once again I created all the indexes it took again 40-50 seconds.
AS its live table and being queried I can not remove indexes. So we decided to change the fillfactor to avoid splitting.
So we changed the fillfactor to 50 and effect is as per our expectation. The dump completes in 15 seconds.
As the index pages are created then they have 50% more space, so that there are now less chances of splitting compare to previous.
So it also attributes that page splitting is creating too much resource and time for large tables with heavy insert operations.
We have a table where we are dumping data on every 5 mins
Every 5 mins we are dumping approx 0.1 to 0.2 million rows
The tables has 5 indexes and we have a complaint that dump is getting slower over the time
It was taking 40-50 seconds , which needs to come down as we had to do other process after the dump
Solution
I checked the tables and could not find anything special
Only way to optimize is to work on Index. As I removed all the indexes the dump completed in 2 seconds
And once again I created all the indexes it took again 40-50 seconds.
AS its live table and being queried I can not remove indexes. So we decided to change the fillfactor to avoid splitting.
So we changed the fillfactor to 50 and effect is as per our expectation. The dump completes in 15 seconds.
As the index pages are created then they have 50% more space, so that there are now less chances of splitting compare to previous.
So it also attributes that page splitting is creating too much resource and time for large tables with heavy insert operations.
No comments:
Post a Comment