Wednesday, March 2, 2011

Ibatis SQL Code performance check tool for oracle and mysql

when write the SQL,we want to check the SQL performance,
for example:
1)the SQL can't include select *.
2)the SQL must has certain column.
3)the SQL explain plan must not use full table scan or index scan.

we can define the rule as  following:

<?xml version="1.0" encoding="UTF-8"?> 
<rule-execution-set> 
  <name>daoCheckRules</name> 
  <description>DAO check Rule Execution Set</description> 
  <synonymn name="daoRuleParser" class="com.database.util.daoRuleParser" /> 
 
  <rule name="selectStar" description="Check SQL if has select * " > 
  <if leftTerm="daoRuleParser.getSql"  op="contains" rightTerm="*" /> 
  <then method="daoRuleParser.setCheckSelectStar" arg1="true" /> 
  </rule>
  
  <rule name="siteid" description="Check SQL if has no siteid column" > 
  <if leftTerm="daoRuleParser.getWhereClauseSql" op="notcontains" rightTerm="siteid" />
  <then method="daoRuleParser.setCheckSiteid" arg1="true"/> 
  </rule> 
 
  <rule name="checkOracleFullScan" description="Check SQL if oracle index or table full scan" > 
  <if leftTerm="daoRuleParser.getSqlExplan" op="contains" rightTerm="full scan" />
  <then method="daoRuleParser.setCheckOracleFullScan" arg1="true"/> 
  </rule>
 
  <rule name="checkMysqlFullScan" description="Check SQL if mysql index or table full scan" > 
  <if leftTerm="daoRuleParser.getSqlExplan" op="containsatleastone" rightTerm="[index,all]" />
  <then method="daoRuleParser.setCheckMysqlFullScan" arg1="true"/> 
  </rule>
 
  <rule name="checkReglevel" description="Check SQL if has wbxuser and coruser then must add REGLEVEL column" > 
  <if leftTerm="daoRuleParser.getAllTables" op="containsatleastone" rightTerm="[wbxuser,coruser]" />
  <if leftTerm="daoRuleParser.getWhereClauseSql" op="notcontains" rightTerm="reglevel"/>
  <then method="daoRuleParser.setCheckReglevel"  arg1="true"/> 
  </rule> 
</rule-execution-set>


then you can use the tool in your unit test case as following:

private static daoChecker daoCheck=null;
private static void initDaoChecker() throws DaoException
  {
              if(null==daoCheck){
              DaoManager.startTransaction();
              SqlMapDaoTransaction transaction = (SqlMapDaoTransaction)DaoManager.getLocalTransaction();
              SqlMap sqlMap=transaction.getSqlMap();
              daoCheck=new daoChecker(sqlMap);
              wbxDaoManager.commitTransaction();
              }      
  }
...
TestSuite suite = new TestSuite();
initDaoChecker();
suite.addTest(new TestUserPrivilegeDaoImpl("testFindUserRequest"));
daoCheck.daoCheckStart("findUserRequest",sc);

the unit test result as following:
 ERROR:         com.database.daoChecker     - Has table/index full scan in sql (findUserRequest)
 ERROR:         com.database.daoChecker     - Has no Reglevel field in  sql (findUserRequest)


now the tool is free, if you want to use the tool , you can contact with me, the blog can't input the jar package.

1 comment:

  1. give the source code download url:
    http://www.box.com/s/i2btk106mx9reolq8mt7

    ReplyDelete