by Hieu H
We have all learned throughout the course that the database can affect our applications’ overall performances. As we progress in our knowledge of databases and SQL, we will undoubtedly be exposed to more advanced queries and techniques. However, there are some “basic” techniques that we can use to improve relational database performance now. Cullins points out that “up to 75% of poor relational performance” is caused by inefficient SQL queries and application code. By following some simple rules, we can eliminate some of these issues. The first step is to use JOIN queries instead of cursors for every table. JOINs will return only what we need from the database. The next step is to stop using SELECT * in our queries. Instead of selecting every column in a table, we should select only the required columns. Another trick to improve performance is to use the WHERE clause. This helps to filter out and return only the desired information.
These are some of the tricks that we can use now to improve our databases’ performance. They are simple enough for us to incorporate into our everyday processes, yet they are powerful enough to make big differences. In smaller projects where databases aren’t as big, we may not realize the differences, but as the database grows and more and more information is present, performance drain is more noticeable.
When I first started writing database queries, I often used the SELECT * statement. It was a lazy-man’s way of just getting a bunch of information fast. Once I received all of the information, I would use the application code to do the filtering and apply business logic. According to this article, we should let the DBMS take care of such calculations, as they are more than capable of doing so. Passing it to the application code causes extra processing power and is not necessary. I think that the tips given in this article will somehow help all of us in the next coming chapters as we learn more about SQL queries.
Mullins, C. (2010). Sql performance basics. Database Trends and Applications, 24(4), 29.