Database Performance Tips You Won’t Want To Miss

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.

1. 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.
2. 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.
3. 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.

Read more: JASE Digital Media blog

Posted via email from Jasper-net