SQL Performance Basics

by Toan T
Creating a database using any application is one easy task, but how efficient the database is is a whole different story. Assuring optimal performance of database applications reply on properly coded SQL statements. Poor written application code is the cause of most performance problems. And in most cases, as much as 75% of performance is caused from bad SQL and application code. It is impossible to address all the performance issues in the database environment and writting SQL properly is also a challenge for most programmers who are new to relational database. This article will explain and emphasize on some of the SQL basics that one can utilize when working with any databases. The first step is to think relationally. Code join instead of using cursors for every table and reading each of them like they were individual files. The DBMS is optimized with join and will return only the data that is needed. The next step is to always provide exact columns that is needed to be retrieved in the SELECT-list of each SELECT statement. Another way of stating is do not use SELECT *. The statement means retrieve all columns from the tables being accessed. This method is only usable for quick queries but is bad practice for inclusion in actual application programs. The next rule is to use WHERE clause to filter data in the SQL instead of bringing it all into your program for filtering. This mistake most often associated with new programmers. It is actually much better to actually letting the DBMS filter the data before returning it to your program since I/O and CPU resources  are required to obtain each row of data. So the fewer rows get passed, the more your SQL will be.

The reason I found this article to be relevant is because as were are about to learn SQL in class. It is very easy for someone who is new to learning SQL to make mistakes such as this without knowing. Knowing how to write SQL is nothing compared to writting SQL at a enterprise level where there are many different data is being utilized and performance will always remain as the core priority for any database.

Source: Mullins, C. S. (2010). SQL performance basics. Database Trends and Applications, 24(4), 29-29. Retrieved from http://search.proquest.com/docview/816707594?accountid=10357

Image Source: https://encrypted-tbn0.google.com/images?q=tbn:ANd9GcQyKk6n6sTjTLEoUId2pDsF1dMfwvQlk3HtpUdajxbMXh6haMAucA