It’s common for developers without any database experience to throw together a design without understanding the critical role it plays in application performance. Unless you have a DBA to review stored procedures and table design, it can become a major bottleneck for your application. Relational databases are workhorses, but if you feed them junk, you get junk back. Here are some ways to feed your workhorse to get the best performance in return.
1) Limit Functions in the WHERE Clause
Functions in the development world make code much more efficient, but it’s not always the case with database programming. With database programming, functions run on every record, and they can be a huge bottleneck on performance.
Take the following query as an example:
SELECT * FROM Orders WHERE FirstOrder(CreateDate) = ‘1/1/2016’ AND LastOrder(CreateDate) = ‘2/1/2016’
In the above query, the two functions return a date, and the record is returned if the function returns the correct date. What if you have a million records? This query would run very slowly. A better way to work with this data set would be to just use the BETWEEN clause.
SELECT * FROM Orders WHERE CreateDate BETWEEN ‘1/1/2016’ and ‘2/1/2016’
2) Avoid Using Cursors at All Costs
Web developers can’t help but add loops to their code. There are ways to optimize loops, but with databases the standard practice is to avoid them as much as possible. Cursors are the database equivalent of a loop. You grab some records and loop through them. The only time a DBA will possibly allow a cursor is if it runs on a non-critical database that runs complex reports and a cursor can’t be avoid.
Most cursors can be replaced with a better query. Evaluate any cursor and find a way to run it with a better UPDATE or SELECT query.
3) Drop Indexes on Large INSERT Procedures
Most developers know that indexes are important for speed, but what they don’t know is that these indexes slow down INSERT procedures. If you need to import thousands of records, it’s better to drop indexes, perform the import, and then replace the indexes.
Of course, you only drop indexes on large INSERT procedures. Only when you import data from another table or a flat file would you drop indexes. You should also perform the import during off-peak hours, because once you drop the indexes you affect queries running on the application.
4) Do Your Constraints in Application Code Rather Than the Database
Foreign key constraints are nothing new in relational databases, and the stop orphaned records or duplicate data. Constraints are one of the main features of a relational database, but they also take a toll on performance. You can leave your foreign keys active, but it’s better to perform the logic for data storage in the application.
When a foreign constraint rule is violated, the database needs to roll back the transaction and send the error back to your application. It’s better to stay a step ahead and do any logic needed in the application, send the data for storage, and then get confirmation from the database. It eliminates the overhead of rollbacks, and you still stick to your foreign constraint rules.
5) Use NOCOUNT on Application Queries
When you run a query, the database takes a count of the records affected and returns the count to the application. If you’re running a query that’s later used on the frontend to view data, you can take a count of the records using the local application language. You don’t need the count returned from the database.
The NOCOUNT directive tells the database not to bother with the count and just collect the data for the record set. It’s a small tweak to your queries that will add a performance boost to the frontend application.
It often takes some analysis and sometimes table redesign before you get your database to optimal performance. Depending on your platform, you can use query analyzers to identify any queries that could be the crux of your bottleneck. Just remember that your database should always be maintained and reviewed. Any queries should be reviewed before you promote them to production or you could find yourself performing emergency maintenance.