Enterprise Java Development@TOPIC@

Chapter 18. Add SQL Tuning

18.1. Summary

In this chapter we are going to add tuning aspects to the schema put in place above. Examples of this include any indexes we believe would enhance the query performance. This example is still quite simple and lacks enough context to determine what would and would not be a helpful index. Simply treat this exercise as a tutorial in putting an index in place when properly identified. Adding the physical files mentioned here could be considered optional if all schema is hand-crafted. You control the contents of each file in a 100% hand-crafted DDL solution. However, for those cases where auto-generated schema files are created, you may want a separate set of files designated for "tuning" the schema that was auto-generated. We will demonstrate using two extra files to create/drop database indexes.

  1. Add a file to add database indexes for your schema 

    # src/main/resources/ddl/emauto_tuningadd.ddl

CREATE INDEX EM_AUTO_MAKEMODEL ON EM_AUTO(MAKE, MODEL);

  2. Wire this new file into your SQL plugin definition for creating schema. Have it run after your table creates. 

    
    <includes>
    
        <include>main/resources/ddl/**/*create*.ddl</include>
    
        <include>main/resources/ddl/**/*tuningadd*.ddl</include>
    
    </includes>

  3. Add a file to augment the drop script and remove indexes from your schema 

    # src/main/resources/ddl/emauto_tuningremove.ddl

DROP INDEX EM_AUTO_MAKEMODEL if exists;

  4. Wire this new file into your SQL plugin definition for dropping schema. Have it run before your table drops. 

    
    <includes>
    
        <include>main/resources/ddl/**/*tuningremove*.ddl</include>
    
        <include>main/resources/ddl/**/*drop*.ddl</include>
    
    </includes>

  5. Build the schema for your module 

    $ mvn clean process-test-classes
...
[INFO] --- sql-maven-plugin:1.4:execute (drop-db-before-test) @ entityMgrEx ---
[INFO] Executing file: .../src/main/resources/ddl/emauto_drop.ddl
[INFO] Executing file: .../src/main/resources/ddl/emauto_tuningremove.ddl
[INFO] 2 of 2 SQL statements executed successfully
[INFO] 
[INFO] --- sql-maven-plugin:1.4:execute (create-db-before-test) @ entityMgrEx ---
[INFO] Executing file: .../src/main/resources/ddl/emauto_create.ddl
[INFO] Executing file: .../entityMgrEx/src/main/resources/ddl/emauto_tuningadd.ddl
[INFO] 2 of 2 SQL statements executed successfully

18.1. Summary

In this chapter you added additional schema files to the overall build process by assuming core schema files may be generated by the persistence provider and additional tweaks may need to be added within the development environment to complete testing.