Skip to content

MS SQL

Top 11 Tips for Database Performance Optimization

DB-PerformanceWhen it comes to optimize the performance of a Database you must do certain things in a standard way to get the desired results. Like any other field, the more you experience it, the more you learn. Here are my top 11 tips for Database developers at beginners and intermediate level. With minor or no alterations, these tips are applicable to any RDBMS.

Database Modeling Tips:

11. Normalization:
For beginners, an under normalized database sounds easier to work with but going forward it becomes a maintenance headache. So, avoid any unnecessary redundancy in your database as storing same data or derived data in multiple tables is bound to fetch ambiguous data in future in your reports. As a thumb rule, start database modeling with a strict normalization rules in mind and later review it and introduce some redundancy only if required.

Always remember, a highly normalized database will have more number of joins and this will definitely lead to slower ‘read’ operations i.e. select queries. On the other hand an under normalized database will have slower ‘write’ operations i.e. insert/update because it has to write same data to multiple tables.

10. Default Constraints values – In most of the applications every table would require a few audit columns like CreatedDate, ModifiedDate etc. To save network bandwidth and optimize write operations performance to your database, use Getdate() as default in CreatedDate and ModifiedDate columns.

9. Data types: This is the most ignored one. Use it wisely as there’s no point in wasting database storage size of 4 bytes in every record by defining the type as Int if Tinyint  i.e 1byte (or Smallint, 2 bytes) is what you actually required. A coulmn showing status is better with bit or tinyint datatypes (as per your requirement).

Similarly most of the applications of general nature can work perfectly fine with a SmallDateTime (4 bytes of storage space with a precision of 1 minute) or simply Date in a few case (just 3 byte of storage space). I fail to understand why Datetime (storage space 8 byets) is the most popular date data type! For zip code: Char(5) is always a better choice than Varchar.

8. If you notice more NULL values in your database then there might be some issue with your database design. It simply means you’ve not broken down your tables wisely and there might be a few unrelated columns forced into tables. Fix it.

T-SQL Tips

7. Many UI developers has this habit of getting a result set by using SELECT * from table(s) and caching this data on a web server and then processing it at client side before rendering it. If the volume of data is high it will unnecessarily increase the network load and memory issue on the web server.

6. A stored procedure with all null parameters should be used carefully and only when it’s really required. These type of stored procedure may have a single query plan (the condition which was executed for the first time) and that might lead to performance degradation for other conditions. If you are facing performance issue with this kind of stored procedures then use WITH RECOMPILE option inside this procedures and check performance.

5. Indexes improves read operations but slows down write performance, don’t rush for them. Columns used in Where, Join, Order by, Group By clause are good candidates for Indexes.

4. Tables with Lists and Look up values that are not frequently changing are good candidates to be used with SQL Server Query notification, i.e. minimize database hits. It’s a SQL Server caching technique implemented at UI layer by ADO.Net.

3. Performance may take a beating when Transactions are unnecessarily large. The tables used inside transaction may be locked and won’t be accessible to another request coming from the application layer. Short transactions are recommended.

2. Stored Procedure variable should be of same type and length as the underlying table column’s data type and length.

1. If you’ve a large Stored Procedure and if it has some repeating block of code you can easily move it to a user defined function. Functions promote re-usability but it might hit a little as far as performance is concerned. Then what’s the advantage of this? It’s easier to read and maintain a large stored procedure with the help of functions. This is trade-off between performance and maintainability. Choose wisely.

The above tips should help a SQL developer to get a better performance. Do you know of any other SQL Server secret to help with performance tuning? Please share in the comment section.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)