We are currently facing performance issues in our application. While analyzing we found the issues are not at the application layer but on Database Layer. The query timing goes on increasing as the load on database goes on increasing.

We have approx 7-8 million records in our table, which seems to be causing this issue.

To counter this issue - we have already introduced around 5 indexes and the cost has been brought to minimum. The query when executed in isolation takes 1-2 secs. But as we start increasing the load (viz 100 users concurrently) - the query timing increases to 2-3 mins.

We are currently thinking if adding Partitioning would resolve the performance of the system?

Please share your experiences if you have seen similar situations on your applications.