Part 3 – Database Queries
This is part three of the blog discussing how bad code does often lead to bad performance of web pages, and what bad coding habits can contribute to the slow performance. In the previous parts, I’ve discussed how a lack of parallelism in code and over-reliance on server-side code can slow performance down.
In this part of the blog, I shall be looking at how poorly structured and unnecessarily expensive database queries can contribute to bad performance.
Poorly structured and expensive database queries
If many database queries are being made by the server-side code and executed on the database server, there are many connections being made between server-side and database-side. So, it is important to make sure that database queries are written in a manner that optimises performance as much as possible.
If queries are returning more columns and rows than what are actually needed, performance is being wasted on fetching back data that actually isn’t required. So, querying just the columns and rows of interest will aid with speeding up the performance of a web page.
A query may be doing too many joins or lookups to other database tables, or more than what is required. There may even be too many SqlConnection instances active and open, which all use up resource and performance to execute database queries from C#. Managing the queries and connections can help limit the impact database queries will have on performance.
The Microsoft Developer site lists these and several more performance issues caused by database queries. Check out the link here. A lot of the points raised by both myself and Microsoft may seem like common sense, but these are points which are often overlooked when developing database queries for web pages.
Stay tuned for the final part of this blog, where I shall discuss how use of variables can have a big impact on web page performance.
