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"?> 
  <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 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 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 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 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"/> 

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

private static daoChecker daoCheck=null;
private static void initDaoChecker() throws DaoException
              SqlMapDaoTransaction transaction = (SqlMapDaoTransaction)DaoManager.getLocalTransaction();
              SqlMap sqlMap=transaction.getSqlMap();
              daoCheck=new daoChecker(sqlMap);
TestSuite suite = new TestSuite();
suite.addTest(new TestUserPrivilegeDaoImpl("testFindUserRequest"));

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: