Working in any technology requires backend database solution. Unlike old days application and database needs – now a days it is very hard to maintain the performance of database as the data has grown in size and complexity. Organizations put many efforts to maintain their databases by employing a fleet of DBAs.
However, many of IT Teams do not get the privilege to have special persons on board for the activity. In such scenarios developer is responsible for managing database. There are several techniques for query optimization. I am quoting some of them that might be handy list and can be implemented to optimize the database.
1 Archive / Purge: This might sound too basic but is a very important and effective way of keeping the performance up. Make a schedule to archive some records and put the same in documents shared with the user. What is the need of having old data all the time? The data will be less in space resulting speedy access to it.
Example: Development Team can arrange for archiving data for every year closed. The same can be put into documents to avoid any conflict with user.
2 Dump / Reload Database: Yes, it works. If you feel that the performance of the application is going down because of database response. Try this and you will be amazed by the change.
3 Rebuild New Indexes: Database structuring is not a one time job. It is a process, which involves creation and maintenance. Go back to the database on intervals to have a look on what can be changed. Rebuilt new indexes which will result in great results.
4 Locks / Dead Locks: Generally, this topic comes to mind when it actually happen. However, the correct approach should be how to avoid it. It is very important to handle Locks / Dead Locks as if they occur there is no way to proceed with the query without touching database.
i. Ensure that other queries are not using the same tables in same time.
ii. Check all the processes are accessing tables in the same order.
iii. While writing the queries / procedures – lock tables exclusively before starting the query.
5 Are Indices used properly: Indexes are applied on columns of tables to get the data fast. However, using too many indexes can choke the table resulting very slow access to the data. The data access with and without appropriate indexes can be in 4:100 ratio. Index will only be used if the first field(s) on the index is matched in the query.
6 Joins: We all know this but somewhere we lack the implementation. Joins should not be used between more than 4 tables. Make sure to match column of same data types in join.
7 Break Up the Join: Can you break up a Join? In case you don’t have any option other than using big / more joins – chose some complex queries with join, put the result in temp tables and you are ready to use the table reference instead of putting several joins.
8 Execution Plan: SQL provide view for the query execution plan. SQL server pre-checks the resource utilization, number of records and sequence of execution. It is actually a very useful tool which can be used to proactively see the query and eliminate the bottlenecks. Rolling over every icon will give another set of detailed description.
9 Break-apart procedures: Instead of using a big procedure, break the procedure into two and call the procedure 2 at the end of the first procedure. This gives good results while accessing the data.
10 Night Jobs: Choose tasks which can be run at night. Those heavy tasks if run at night when there is no or less load on database by transactions can save crucial resources of system in the day time. Also those activities can be completed quickly.
11 Hardware: Last in the list but not the least, hardware plays the biggest role in performance. Keep your hardware updated to make sure SQL gets all the resources it needs to perform efficiently. In case you find the SQL responding slowly, spend some time to monitor the server and see if CPU / RAM performing well.
The list provided seems small for the Optimizing methods available. However, the basics can be implemented and used to do a lot for maintaining performance. The solutions seem small but definitely they are most effective and commonly used by experts. Go ahead and try them out to see drastic changes in the performance resulting user satisfaction.Tags: