Skip to main content

Performance

Improve DB Performance​

Indexing is too often overlooked during the development process, but a good indexing strategy can be your best tool for tuning your database. Indexes are data structures that allow you to quickly select and sort the rows in a database table. They facilitate faster data retrieval by providing random lookups and easy access to orderly records. Setting up indexes usually doesn’t require much coding, but it does take a bit of thought.

  1. Check your SQL have joins? If yes then check those table whether it contains any indexes on Primary key and foreign key - else check your where clause.

  2. Does the query returns lots of data? create an index on filtered columns.

  3. Create an index on aggregate functions

  4. Create an index on ordered columns

When you need to perform a single, large batch insert, temporarily dropping your indexes can accelerate the process; however, keep in mind that doing so will affect all queries running in the affected tables, so you should only do this when you have more than a million rows of data to insert. Don’t forget to recreate your indexes when you’re done.

Correlated subqueries depend on values from a parent query. Newer developers love using them because subqueries make coding more convenient. Unfortunately, structuring queries in this fashion can drag down performance.

Upgrade Your Hardware​

Like most tech-related issues, you can always boost your database performance by throwing money at it. Since every query must run through memory, adding capacity to your server should speed things up; however, if you wish to take full advantage of extra memory, you need to properly configure your server.

You should particularly keep an eye on your disk latency. It will increase along with the hard drive load, which will cause a decrease in your overall database performance. Take advantage of any available caching mechanisms to alleviate this issue.

Check Your Connection Capacity​

If a large chunk of your database’s response time is consumed by connection acquisition, then you may need to re-configure your connection pool. You must know the exact number of connections your database can handle; don’t set the default to maximum because this can cause problems for other clients connected to the database.

A connection pool’s size should remain constant, so always set the minimum and maximum to the same value. To determine your connection capacity, gradually increase the load and the number of connections while monitoring your database server’s metrics. When your CPU or memory is maxed out, you’ll know your limits.

Quick Tips for Boosting Database Performance​

  1. Create a primary key as a clustered index for every table. Fortunately, Enterprise Manager clusters primary keys by default. You should also set up an index for any column that is a foreign key.
  2. When you reference objects in T-SQL, make it a habit to always owner qualify them. For example, you would use dbo.sysdatabases as opposed to just sysdatabases.
  3. Use SET NOCOUNT ON at the top of every procedure and SET NOCOUNT OFF at the bottom of every procedure.
  4. Unless you’re writing banking software, then don’t bother with locking. Using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top and reverting to READ COMMITTED at the bottom of your stored procedures is more effective than using the NOLOCK hint.
  5. Feel free to use transactions when needed, but don’t allow any user interaction while they are in progress. It’s best to keep all of your transactions inside a stored procedure.
  6. Don’t use temp tables unless you absolutely need them.
  7. Scan your code for uses of the NOT IN command and replace them with a left outer join.
  8. Speaking of which, make it a habit to review your code before and after each change you make.
  9. Find ways to lower the number of required round trips to the server. For example, try returning multiple ResultSets.
  10. Don’t bother using index hints or join hints.
  11. Instead of using SELECT * statements, you should just individually specify each column you require even if you need every column in a table.
  12. When checking to see if a record exists, use EXISTS() instead of COUNT(). They are both effective, but EXISTS() stops running automatically once it finds the requested record, which will result in better performance and cleaner code.