Spock+DbUnit自动化测试用例执行缓慢问题排查咨询

Hey there, that 2-4 second lag between your setup finishing and the feature test kicking off adds up fast with 2000 cases—let’s break down the key areas to investigate and fix this:

1. DbUnit Dataset Operations Overhead #

Your @DbUnit configuration uses InsertIdentityOperation.REFRESH for setup and DELETE for teardown. REFRESH works by first deleting matching records then inserting new ones, which can be slow if you’re working with large datasets or frequent database round-trips:

• Simplify Dataset Scope: Check if every test needs the full dataset you’ve defined. Trim down the rows to only what’s required for the specific feature.
• Optimize DbUnit Operations: Try swapping REFRESH with CLEAN_INSERT (which deletes all rows in the table first then inserts) or even INSERT if you can guarantee test isolation via teardown. For teardown, TRUNCATE_TABLE is faster than DELETE for clearing tables (just be cautious with identity columns).
• Reuse Database Connections: Verify if DbUnit is reusing connections from your shared dataSource or creating new ones per test. Some DbUnit extensions require explicit configuration to leverage connection pooling—check the be.janbols.spock.extension.dbunit implementation to ensure it’s using your pooled dataSource efficiently.

2. MasterSpec’s Per-Test Setup Overhead #

Your MasterSpec runs its setup() method every single test case (2000 times!), which involves reading XML files from disk, processing them with a template engine, and populating xmlDataSource:

• Cache XML Resources: Convert xmlDataSource to a @Shared variable and move the XML loading logic to setupSpec() (which runs once per test class, not per method). This will eliminate repeated disk I/O and template processing for every test.
• Skip Unnecessary Logic: Add a check to skip the XML loading if the current feature method doesn’t have the FileResource annotation—no need to run that code when it’s not required.

3. Spock Test Isolation & Context Initialization #

Spock creates a new instance of your spec class for every feature method by default, which can add overhead if you’re initializing heavy objects in the spec:

• Share Reusable Objects: Mark your dao instance in AllergyFormDAOSpec with @Shared (as long as the DAO is thread-safe and doesn’t hold state between tests). This avoids recreating the DAO 2000 times.
• Enable Parallel Testing: Spock 1.3+ supports parallel test execution. If your tests are properly isolated (each test cleans up after itself), enabling parallel runs can drastically cut total time. Add this to your spock-config.groovy:

  runner {
      parallel {
          enabled = true
          fixedPoolSize = 4 // Adjust based on your CPU cores
      }
  }
  

4. Database-Specific Bottlenecks #

You’re using MSSQL with the JTDS driver—database-level issues could be contributing to the delay:

• Tune Connection Pooling: Your MasterSpec creates a raw data source without pooling configurations. Add connection pool settings (like maxActive, minIdle) to reuse connections instead of creating new ones for each test. For example, if using JTDS:

  ((JtdsDataSource)dataSource).setMaxActive(10)
  ((JtdsDataSource)dataSource).setMinIdle(2)
  

• Monitor Database Performance: During test runs, check your database for slow queries, lock contention, or high IO usage. Tools like SQL Server Profiler can help identify if DbUnit’s setup/teardown queries are taking longer than expected.
• Use Batch Operations: Configure DbUnit to use batch inserts/updates instead of single-row operations. This reduces the number of round-trips to the database.

5. Logging Overhead #

Your test code uses println statements for timing—these synchronous IO operations can add up across 2000 tests:

• Remove or Optimize Logging: Temporarily comment out all println calls and re-run the tests to see if total time drops. If it does, switch to a logging framework like SLF4J with async logging instead of direct println.

Your Code References #

AllergyFormDAOSpec

package allergy.dao
import java.util.Date
import org.dbunit.IDatabaseTester;
import org.dbunit.ext.mssql.InsertIdentityOperation;
import allergy.AllergyForm;
import be.janbols.spock.extension.dbunit.DbUnit;
import spock.lang.Shared
import util.MasterSpec

class AllergyFormDAOSpec extends MasterSpec {
    def dao = new AllergyFormDAO();
    @Shared Date timeStart1
    @Shared Date timeEnd1

    @DbUnit(configure={ IDatabaseTester it -> it.setUpOperation = InsertIdentityOperation.REFRESH
        it.tearDownOperation = InsertIdentityOperation.DELETE })
    def content = {
        allergy_form(formId:99999,formName:'DummySpockForm',displayIndex:1,deleteFlag:0,is_biological:1)
        allergy_form_facilities(id:99999,formId:99999,facilityid:2)
        form_concentration(id:99999,formId:99999,name:'1:100',deleteflag:0,displayindex:1)
    }

    def setup(){
        timeStart1 = new Date()
        println "setup method execution started at : " + timeStart1
    }

    def "API to test delete Form facility"(){
        def startTime = new Date()
        println "Feature method execution started at : " + startTime
        given:"form Id is given"
        def formId = 99999
        when:"delete form facilities"
        def result =dao.deleteFormFacilities(null, formId)
        then:"validate result"
        (result>0)==true
        def endTime = new Date()
        println "Feature method execution ended at: " + endTime
        println 'Total time taken to run the one test case: '+ (endTime.getTime() - startTime.getTime())
    }

    def cleanup() {
        timeEnd1 = new Date()
        println "cleanup method execution started at : " + timeEnd1
        def difference = timeEnd1.time - timeStart1.time
        println "Total time taken to run a fixture method : " + difference
    }
}

MasterSpec

package util
import com.ecw.dao.SqlTranslator
import catalog.Root
import spock.lang.Shared
import spock.lang.Specification
import javax.sql.DataSource

/** */
class MasterSpec extends Specification {
    @Shared Properties properties = new Properties()
    @Shared public DataSource dataSource
    @Shared protected xmlDataSource = [:]
    static int timeCntr = 0;

    //setup is to read xml file's content in xmlDataSource Hashmap
    def setup(){
        //Get Running Class name without its package
        def className = this.class.name.substring(this.class.name.lastIndexOf('.') + 1)
        def resourceAnno = specificationContext.currentFeature.featureMethod.getAnnotation(FileResource)
        if(resourceAnno != null){
            def files = resourceAnno.xmlFiles()
            def packageName = (this.class.package.name).replaceAll('\\.','/')
            for(int i=0;i< files.length;i++){
                def f = new File("src/test/resources/"+packageName+"/"+className+"/"+files[i])
                def engine = new groovy.text.GStringTemplateEngine()
                def template = engine.createTemplate(f).make(null)
                def xmlString = template.toString()
                //load the hashmap with file name as Key and its content in form of string as Value
                xmlDataSource.put(files[i].split("\\.")[0],xmlString)
            }
        }
    }

    def setupSpec() {
        Date timeStart = new Date()
        File propertiesFile = new File('src/test/webapps/myApp/conf/connection.properties').withInputStream { properties.load it }
        String strDBName = getPropertyValue("myApp.DBName")
        if(strDBName.indexOf('?') > -1){
            strDBName = strDBName.substring(0, strDBName.indexOf('?'))
        }
        String strServerName = getPropertyValue("myApp.DBHost");
        if(strServerName.indexOf(':') > -1){
            strServerName = strServerName.substring(0, strServerName.indexOf(':'))
        }
        String strUrl = getPropertyValue("myApp.DBUrl")
        String strPort = strUrl.substring(strUrl.lastIndexOf(':') + 1)
        //FOR MSSQL
        System.setProperty("myApp.SkipJndi", "yes")
        //dataSource = new JtdsDataSource()
        Object newObject = null;
        if(SqlTranslator.isDbSqlServer()){
            newObject = Class.forName("net.sourceforge.jtds.jdbcx.JtdsDataSource").newInstance()
        } else if(SqlTranslator.isDbMySql()){
            newObject = Class.forName("com.mysql.jdbc.jdbc2.optional.MysqlDataSource").newInstance()
        }
        dataSource = (DataSource)newObject
        dataSource.setDatabaseName(strDBName)
        dataSource.setUser(getPropertyValue("myApp.DBUser"))
        dataSource.setPassword(getPropertyValue("myApp.DBPassword"))
        dataSource.setServerName(strServerName)
        dataSource.setPortNumber(Integer.parseInt(strPort))
    }
}

内容的提问来源于stack exchange，提问作者Hardik