Key steps for increasing database performance

by Giselle N
In this specific article, the author stresses the key rules for a high performance database using SQL. One of the first steps the author talks about is to move away from applying cursors for every table and reading from each of them like files, and instead use code join. Joins will return only the data is necessary, so performance wont decrease with accessing more data than needed as would happen with implementing cursors. Next, the author says it is best to provide only the precise columns that are needed to retrieve in the SELECT-list of each SQL SELECT statement. Basically only asking for the data that is needed for the program. Otherwise, the database will be over taken by unnecessary things for every column that is requested. Another rule to follow is to make sure the DBMS filters the data before returning it to the program by using the WHERE clause. A program will run much more smoothly if fewer rows are passed through it. Next, it is important to remember not to duplicate your information. For example,

“SELECT EMPNO, LASTNAME, SALARY

FROM EMP

WHERE EMPNO = ‘000010’; “

Because EMPNO is already included in the WHERE clause, it does not have to be included in the SELECT-list as well, otherwise, it will be retrieved an additional time without being necessary. The author concludes that these simple rules are not hard to follow and will help avoid decreasing the program’s performance.

I found this article to be highly informative and helpful. It is important to know the basics of SQL before trying to master it. And like the author said, they are simple rules that will help develop a good habit when creating a database. I really appreciated the author showing actual examples in the article to help the readers get a better picture of how the rules can be used. As we come to work more and more with SQL, these rules will come in handy.

 

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