Enterprise Java Development@TOPIC@

Chapter 17. Add SQL Tuning

17.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. Add an "orderFile" element to the configuration to specify to the plugin that you wish the files be executed in a specific order. Otherwise the order will be non-deterministic and the tuning may get executed before the schema is created. 

    
 <execution>
    
    <id>create-db-before-test</id>
    
    <phase>process-test-classes</phase>
    
    <goals>
    
        <goal>execute</goal>
    
    </goals>
    
    <configuration>
    
        <autocommit>true</autocommit>
    
        <orderFile>ascending</orderFile>
    
        <fileset>
    
            <basedir>${basedir}/src</basedir>
    
            <includes>
    
                <include>main/resources/ddl/**/*create*.ddl</include>
    
                <include>main/resources/ddl/**/*tuningadd*.ddl</include>
    
            </includes>
    
        </fileset>
    
        <print>true</print>
    
    </configuration>
    
</execution>

  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. Add an "orderFile" element to the configuration to specify to the plugin that you wish the files be executed in a specific order. 

    
<configuration>
    
    <autocommit>true</autocommit>
    
    <orderFile>decending</orderFile>
    
    <fileset>
    
        <basedir>${basedir}/src</basedir>
    
        <includes>
    
            <include>main/resources/ddl/**/*tuningremove*.ddl</include>
    
            <include>main/resources/ddl/**/*drop*.ddl</include>
    
        </includes>
    
    </fileset>
    
    <onError>continue</onError>
    
</configuration>

  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

17.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.