5 Tips to Improving Database and Query Performance

Considerable amounts of planning needs to go into building a database application. There are a number of things which can affect performance in a negative way if best practices are not followed. Performance might seem perfectly fine in a test environment with only a few thousand rows inside the database but when the application goes live and begins to grow the database, bad design considerations can become obvious very quickly. When designing the database and building the code to query it, there are simple design considerations and best practices which can help ensure that as the database grows, the system remains responsive. The following five tips are in no particular order of preference. 1/ Design tables with the appropriate indexes The lack of appropriate indexes can lead to massive decreases in performance. As the tables become larger, the optimizer has to scan through the data to retrieve the required rows based on the WHERE clause in the query. Imagine using a phone directory without an index to tell you where the page was for your local plumber. It is going to be slower finding them without the page that tells you that they are on page 302 or your 500 page directory. The query optimizer has the same problem if indexes are not put in. 2/ Don't use SELECT * FROM It is easy to write SELECT * FROM. Writing queries in this way is much quicker than writing out the required fields from the table. Well you reap what you sow. If you take the time to build that SQL statement properly, your return on investment will be improved response times. This is because the sql query is pulling back less data than if all columns were included. If you only need 3 columns and not the 10 that comprise your table then just select those three. 3/ Ensure that stored procedures are used There are many benefits to using stored procedures. One of the benefits is that the optimizer can call upon a cached execution plan. Ad-hoc SQL requires more time and resource to execute because the optimizer has to work out the best execution plan to execute the query. 4/ Don't use cursors There is a time for using cursors however they should generally be avoided unless absolutely necessary. The optimizer works best dealing with sets of data. It is not too great at doing things very quickly using a row by row method. 5/ Ensure that you have a maintenance plans in place In SQL Server, you can build maintenance plans to help rebuild indexes and refresh statistics. In MySQL, this can be done using OPTIMIZE TABLE commands as part of a batch process. Ensuring that maintenance plans are in place helps to control index fragmentation. Up to date statistics helps to ensure that the optimizer can use the best execution plan for the query. For more tips view this post on improving database performance Andy Hayes is a DBA working in SQL Server and MySQL technologies. His skills include database administration, performance tuning and ETL process design. Visit his blog DBA Diaries over at http://dbadiaries.com