In a data-driven web application, poor database performance can needlessly burden your server or, worse, make your website slow enough that your user gives up and goes somewhere else.
So we have put together some of the common pitfalls we have seen with database performance. We’re mainly gearing this towards SQL Server, though many database (Oracle, Sybase, MySQL, etc.) concepts are applicable across platforms.
- Think before you index. An index is a construct that enables faster lookup of data from a table. Rather than doing a table scan of all of the rows of the table, an index allows SQL Server to more directly seek the desired data. Adding indexes to your database is important, but adding indexes you don’t need can do more harm than good. When you insert or update rows in your table, SQL Server has to update your indexes as well. Though SQL Server 2008 will actually allow you to have 1000 indexes in a table, if you find yourself desiring anything remotely close to that number, you should reconsider your design.
- Do you really need to use functions? In general, user-defined functions are much slower than incorporating your operation in a join or a view, as appropriate. Test out your function using the SQL Profiler to see if it is taking a long time to execute.
- Use bound parameters. In nearly all cases, it’s faster to use bound parameters than to not use them. When you bind your parameters, you allow the server to only have to optimize your query the first time it sees it. In Oracle, this is extremely important as the server reserves a set amount of memory for queries it has “learned” and if you don’t use bound parameters, you will constantly fill this space up with the same query. As a side note, should you ever find yourself using the ODBC API, you can use SQLExecDirect to execute a statement in a single command. In this case, it is marginally faster because it does not require the extra overhead for preparing the ODBC statement with SQLPrepare.
- Don’t repetitive requery the same data. Consider the web page that has four components. Each of these components may need to display a piece of data about your user. Often, you will see a design where the user ID is stored in session data, then each component will query the database for information about the user. You have queried the database four times when you really only needed it once. Or you may be calling the database repeatedly, for example, one query per category to retrieve all of that category’s items rather than a single query sorted by category. Not only do excessive queries slow down your application just for the time spent executing them, they potentially hammer your server.
- Normalize your database, but use common sense. Suppose that you have a supplier sending you a data file containing information about the products you are selling. It is tempting to simply load that data file into your database, slap on a few indexes, and use it as is. But that may not be the best way to do things. For example, categories or manufacturers’ information should be split out into separate tables. We call this “normalization”. But don’t go crazy – if you are creating a city table, you might be going too far.
Do you have a data-driven application that needs to be improved? Let JASE help!
image credit: Arthur John Picton
|Enjoy Your 30-day Free Trial!|
|Learn how Inbound Marketing Software can help your business grow!|