One of the important considerations when creating a data-driven application is database performance. Often, when adequate load testing is not performed, problems are camouflaged because even the poorest database designs are still very fast for small amounts of data. For this reason, it is important to validate your database under simulated real-world conditions before deploying it.
So here are some of the tips we have for resolving database speed issues that we encounter:
- Cache it. The number one speed issue I see in data driven websites is that some piece of information, like a list of topics or menu items, is queried for every page hit. This is a pointless hit on your database server and an unnecessary slowdown for your site. For WordPress sites, there are a number of caching plugins – a good one that I have used is WP Super Cache. For a homegrown .NET website, consider generating caching lists that change infrequently to a server variable or an XML file.
- Use the performance tools your database server provides, but don’t blindly follow them. SQL Server provides two outstanding performance tools – the SQL Profiler and the Tuning Adviser. The profiler generates a log of all commands sent to the server (it can be filtered by database, application, or other meaningful criteria). This log can be examined for duplicate queries or its output can be submitted to the Database Engine Tuning Adviser. The Tuning Adviser makes recommendations for missing indexes, among other things. But as useful as this tool is, you should not blindly create anything that it suggests. The tool cannot distinguish, for instance, between a missing index and a poorly conceived query.
- Examine your data types. Don’t use varchars for text-based columns that are going to have fixed widths. For example, there is no need for a varchar on an ID field that will always or nearly always be, say, eight characters. Fixed length tables are accessed more quickly than variable-length tables.
- Check for missing indexes. In general, if you sort by it, join by it, or use it in a where clause, you want an index on it. Every table should include a clustered index.
- Check your application for memory leaks. If you are writing a .NET application, use the “using” construct for your database objects, as this construct automatically frees the resource when it goes out of scope. Failing to free a database resource can cause numerous problems.
Is a slow database frustrating you? For more help with your database design issues, contact our technology team.
Photo credit: Mikko Koponen