Best Practice 18 – Fine tuning SQLs
1. Always use explicit joins. (If you mean INNER JOIN, then use INNER JOIN). Don’t use just a plain "JOIN".
Never use a comma join. Also, keep join conditions in an ON or USING clause; they should not go in the WHERE clause.
2. Always define field names. Don’t use SELECT * or INSERT INTO table VALUES. It can be difficult, and even more so given that mysqldump does not specify INSERT fields. However, if it's important enough to save in a text file (i.e., its seed data or a migration script), then use explicit field names.
3. Always use the database server's timestamp (Web servers may have disparate times). Reports may come from different servers than the inserted data.
4. Store IPs as integers with INET_ATON and retrieve them with INET_NTOA.
5. When doing reports, the network traffic is usually the biggest bottleneck. If you're going to receive information, it's better to receive it in chunks, which will likely be larger than a logical piece. For example, if your report involves all 50 US states, get them all at once instead of making 50 separate connections for each state. If the dataset is very large and you do not want to stare at a blank page while the report is loading, use paging with LIMIT to grab a specific number of entries (for example 1000 or so). Then display them on the screen so that you can view data while the rest is being retrieved.
6. Running a query in a loop is normally not a good practice. If you are executing the same query with different data, consider building a query string using UNION. You can then execute it at the end of the loop, allowing you to execute multiple queries with only one trip across the network to the database.
7. Consider using JOINs. They need not be resource intensive if proper indexing is used. A denormalized schema without a join is often worse than a normalized schema using a join. When there is redundant data, use less cycles and assure data integrity up front by providing a framework.
8. Limit the use of correlated sub queries; often they can be replaced with a JOIN.
Best Practice 19 – Miscellaneous
• The lines of code in a Java class file should not be excessive. As a general guideline, anything past 1200 lines may
be excessive.
• The lines of code in a Java method should not be excessive. As a general guideline, anything past 100 lines may be
excessive.
• The number of characters in a line of code should not be excessive. As a general guideline, anything past 120
characters may be excessive.
• Declare each variable on a new line.
• Declare class variable as private and use getter and setter methods to access it outside of the class.
• Do not use System.out.println – refer to “Logging Framework”.
• Do not make fields public without good reason.
• Import individual classes specifically - do not use * imports.
• Use System.gc for explicit garbage collection in case of some memory intensive task, though java run time
performs the garbage collection by itself.
• After try and catch block use finally block to clean up or free the resources.
• Use constants in place of hard coded values.
• Avoid the use of properties (.properties extension) files. If properties are defined with a .properties file, they can become inconsistent, poorly documented and easily broken when the size and the complexity of the content increases. XML configuration files are easier to organize, read and maintain. XML configuration files can also handle and maintain complex content in an easier way.
• Watch out for use of “==” vs. “equals()” when comparing objects.
• Use StringBuffer instead of String concatenation.
• Assign null to object references that are no longer used.
• Avoid use of deprecated methods. http://java.sun.com/j2se/1.5.0/docs/api/deprecated-list.html
• Avoid the use of Hashtable and use Hashmap instead. The key difference between the two is that access to Hashtable is synchronized on the table, while access to the HashMap isn't. Another advantage is that iterator in the HashMap is fail-safe while the enumerator for the Hashtable isn't. The third advantage is that HashMap permits null values in it, while Hashtable doesn't.
• Avoid the use of Vector and use ArrayList instead. Both Vector and ArrayList are considered very similar in that
both of them represent an array that can be increased and where the elements can be accessed through an index.
The big difference is that Vectors are synchronized and any method that touches the Vector's contents is thread safe. However, using synchronization will incur a performance hit. So, unless there is a need for a thread-safe collection,
it is recommended to use Arraylist.
• Retrieve language specific details from the database (instead of using properties files)
• It is recommended to avoid code formatting tools (such as those available in RAD and WebSphere Integration
Developer (WID) ). Following the standards described in this document should result in code that is readable,
functional and well formatted.
1. Always use explicit joins. (If you mean INNER JOIN, then use INNER JOIN). Don’t use just a plain "JOIN".
Never use a comma join. Also, keep join conditions in an ON or USING clause; they should not go in the WHERE clause.
2. Always define field names. Don’t use SELECT * or INSERT INTO table VALUES. It can be difficult, and even more so given that mysqldump does not specify INSERT fields. However, if it's important enough to save in a text file (i.e., its seed data or a migration script), then use explicit field names.
3. Always use the database server's timestamp (Web servers may have disparate times). Reports may come from different servers than the inserted data.
4. Store IPs as integers with INET_ATON and retrieve them with INET_NTOA.
5. When doing reports, the network traffic is usually the biggest bottleneck. If you're going to receive information, it's better to receive it in chunks, which will likely be larger than a logical piece. For example, if your report involves all 50 US states, get them all at once instead of making 50 separate connections for each state. If the dataset is very large and you do not want to stare at a blank page while the report is loading, use paging with LIMIT to grab a specific number of entries (for example 1000 or so). Then display them on the screen so that you can view data while the rest is being retrieved.
6. Running a query in a loop is normally not a good practice. If you are executing the same query with different data, consider building a query string using UNION. You can then execute it at the end of the loop, allowing you to execute multiple queries with only one trip across the network to the database.
7. Consider using JOINs. They need not be resource intensive if proper indexing is used. A denormalized schema without a join is often worse than a normalized schema using a join. When there is redundant data, use less cycles and assure data integrity up front by providing a framework.
8. Limit the use of correlated sub queries; often they can be replaced with a JOIN.
Best Practice 19 – Miscellaneous
• The lines of code in a Java class file should not be excessive. As a general guideline, anything past 1200 lines may
be excessive.
• The lines of code in a Java method should not be excessive. As a general guideline, anything past 100 lines may be
excessive.
• The number of characters in a line of code should not be excessive. As a general guideline, anything past 120
characters may be excessive.
• Declare each variable on a new line.
• Declare class variable as private and use getter and setter methods to access it outside of the class.
• Do not use System.out.println – refer to “Logging Framework”.
• Do not make fields public without good reason.
• Import individual classes specifically - do not use * imports.
• Use System.gc for explicit garbage collection in case of some memory intensive task, though java run time
performs the garbage collection by itself.
• After try and catch block use finally block to clean up or free the resources.
• Use constants in place of hard coded values.
• Avoid the use of properties (.properties extension) files. If properties are defined with a .properties file, they can become inconsistent, poorly documented and easily broken when the size and the complexity of the content increases. XML configuration files are easier to organize, read and maintain. XML configuration files can also handle and maintain complex content in an easier way.
• Watch out for use of “==” vs. “equals()” when comparing objects.
• Use StringBuffer instead of String concatenation.
• Assign null to object references that are no longer used.
• Avoid use of deprecated methods. http://java.sun.com/j2se/1.5.0/docs/api/deprecated-list.html
• Avoid the use of Hashtable and use Hashmap instead. The key difference between the two is that access to Hashtable is synchronized on the table, while access to the HashMap isn't. Another advantage is that iterator in the HashMap is fail-safe while the enumerator for the Hashtable isn't. The third advantage is that HashMap permits null values in it, while Hashtable doesn't.
• Avoid the use of Vector and use ArrayList instead. Both Vector and ArrayList are considered very similar in that
both of them represent an array that can be increased and where the elements can be accessed through an index.
The big difference is that Vectors are synchronized and any method that touches the Vector's contents is thread safe. However, using synchronization will incur a performance hit. So, unless there is a need for a thread-safe collection,
it is recommended to use Arraylist.
• Retrieve language specific details from the database (instead of using properties files)
• It is recommended to avoid code formatting tools (such as those available in RAD and WebSphere Integration
Developer (WID) ). Following the standards described in this document should result in code that is readable,
functional and well formatted.
