by Dean H
SQL is a strong tool that can perform amazing calculations. However in order for it work properly the first requirement would be neat, clean, formulated SQL codes. As much as 75% of poor relational performance is caused by “bad” SQL and application code.
Here are some suggested tips: 1. Think relationally in stead of file level processing. When joins are optimized, the database will automatically search the necessary data to return the requested criteria. 2. DO NOT USE SELECT. using select* is a bad practice because it adds columns to the tables and it will case the programs to fail unless changed. 3. Use “where” to filter data in the SQL instead of bringing it all into your program. 4. keep your database statistics up-todate. It will be hard for the optimizer to optimizing anything if it is not up-todate. 5. Building appropriate indexes for the queries is critically important. It will be the next thing to check if rule 1~4 fails.
Since we will be using SQL soon for our 305 class, I think it is a good idea to research some tips when using SQL to optimize the tool. I have heard amazing things about SQL, but I think it all comes to the knowledge of the user. For example, excel is widely used in school and companies, but most of the users barely used 30 ~ 40% of its function/feature. Excel is truly useful when the user has a good knowledge on the available options. I would say the same thing for SQL as well, so review these tips will definitely help out the future use of SQL.
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