Tag Archives: SQL

Optimizing SQL

Posted on by
Reply

night-job-img1
While you are sleeping, there are lots of programs are running based on it’s scheduling. They are often called “Batch Jobs“. And one of batch jobs is to get rid of old transactions.

In Biz apps, we often need to do performance tuning. According to my experience, the SQL is usually the biggest reason to cause a performance issue.

I have a memory that I have a performance issue for this SQL.
This is to get the target records for Data Purge (Oracle):

SELECT
*
FROM TABLE_TO_BE_PURGED A
WHERE
EXISTS ( 
    SELECT '*' 
    FROM  
          PURGE_TARGET_TABLE TGT
    WHERE 1=1
    AND  TGT.INV_NUM  = A.REF_TXT_01
) OR EXISTS ( 
    SELECT '*' 
    FROM 
          INTEREFACE_TABLE ITFC, 
    PURGE_TARGET_TABLE     TGT 
    WHERE 
         ITFC.REF_NUM     = B.REF_NUM
    AND  ITFC.REF_SUB_NUM = B.REF_SUB_NUM
    AND  ITFC.INV_NUM     = TGT.INV_NUM
)

And came up with this after struggling hours…

SELECT
*
FROM TABLE_TO_BE_PURGED A1
WHERE EXISTS (
    SELECT '*'
    FROM (
        SELECT 
            B1.PURGED_PK
        FROM 
            TABLE_TO_BE_PURGED  B1
        ,   PURGE_TARGET_TABLE  TGT
        WHERE 1=1
        AND TGT.INV_NUM = B1.REF_TXT_01
        UNION
        SELECT 
            B2.PURGED_PK
        FROM 
            TABLE_TO_BE_PURGED  B2
        ,   INTEREFACE_TABLE    ITFC
        ,   PURGE_TARGET_TABLE  TGT
        WHERE 
            ITFC.REF_NUM     = B2.REF_NUM
        AND ITFC.REF_SUB_NUM = B2.REF_SUB_NUM
        AND ITFC.INV_NUM     = TGT.INV_NUM
    ) T1
    WHERE T1.PURGED_PK = A1.PURGED_PK
)

The performance improved dramatically, but the interesting thing was that the execution plan (cost) was pretty much the same. The one above get slower when having more records. And the one below was fast enough although having many records.

HQL and Native SQL in Hibernate

Posted on by
Reply

hiv-mvc-img
In Hibernate, there are number of ways to accessing a data: HQL, native SQL and others.
This section shows the implementation of HQL and native SQL.
These method are all defined in the DAO implementation classes.
hql-vs-sql-header-img1
The syntax of HQL is very much like SQL, but HQL is Hibernate object oriented.

SELECT * 
FROM RTRN_TP_RSN_CMBN  
WHERE rtrn_tp_cd ='01'
AND rtrn_rsn_cd ='001'

The above SQL is implemented as HQL as follow: (Source Code)

public RTRN_TP_RSN_CMBN getTpRsnCmbnByHql(String rtrnTpCd, String rtrnRsnCd) {

	String hqlStr = "from RTRN_TP_RSN_CMBN " 
			+ "where rtrn_tp_cd = :rtrnTpCd "
			+ "and   rtrn_rsn_cd = :rtrnRsnCd ";
	
	Query query = super.getCurrSession().createQuery(hqlStr);
	query.setParameter("rtrnTpCd", rtrnTpCd);
	query.setParameter("rtrnRsnCd", rtrnRsnCd);

	List<RTRN_TP_RSN_CMBN> list = query.list();
	if (list != null && list.size() > 0) {
		return (RTRN_TP_RSN_CMBN) list.get(0);
	}
	return null;
}

Native SQl: The result will be a list of Object Array.

SELECT 
 a1.rtrn_rsn_cd 
,a1.rtrn_rsn_nm 
,a1.sort_num 
,'' as rtrn_tp_cd 
FROM rtrn_rsn a1 
WHERE NOT EXISTS ( 
   SELECT '*' 
   FROM rtrn_rsn a2 
   JOIN rtrn_tp_rsn_cmbn cmb 
   ON a2.rtrn_rsn_cd = cmb.rtrn_rsn_cd 
   WHERE cmb.rtrn_tp_cd  = '02'
   AND a2.rtrn_rsn_cd = a1.rtrn_rsn_cd 
) 
ORDER BY a1.sort_num

The above SQL is implemented as follow: (Source Code)

public List<Object[]> getListRsnsUnBoundTpBySql(String tpCd) {
	
	StringBuilder sb = new StringBuilder();
	sb.append("\n");
	sb.append("SELECT \n");
	sb.append(" a1.rtrn_rsn_cd \n");
	sb.append(",a1.rtrn_rsn_nm \n");
	sb.append(",a1.sort_num \n");
	sb.append(",'' as rtrn_tp_cd \n");
	sb.append("FROM rtrn_rsn a1 \n");
	sb.append("WHERE NOT EXISTS ( \n");
	sb.append("   SELECT '*' \n");
	sb.append("   FROM rtrn_rsn a2 \n");
	sb.append("   JOIN rtrn_tp_rsn_cmbn cmb \n");
	sb.append("   ON a2.rtrn_rsn_cd = cmb.rtrn_rsn_cd \n");
	sb.append("   WHERE cmb.rtrn_tp_cd  = :tpCd \n");
	sb.append("   AND a2.rtrn_rsn_cd = a1.rtrn_rsn_cd \n");
	sb.append(") \n");
	sb.append("ORDER BY a1.sort_num");

	Query query = super.getCurrSession().createSQLQuery(sb.toString());
	query.setParameter("tpCd", tpCd);

	List<Object[]> list = query.list();
	return list;
}