We're updating the issue view to help you get more done. 

Cache getLookupInfo method to avoid multiple queries execution

Description

1 MLookupFactory.getLookupInfo(Properties ctx, int WindowNo, int TabNo, int Column_ID, int AD_Reference_ID)

executes

1 2 "SELECT c.ColumnName, c.AD_Reference_Value_ID, c.IsParent, vr.Code " + "FROM AD_Column c" + " LEFT OUTER JOIN AD_Val_Rule vr ON (c.AD_Val_Rule_ID=vr.AD_Val_Rule_ID) " + "WHERE c.AD_Column_ID=?"

everytime. I believe for each column_id retrieved values would remain same. Can we cache them?

I am using older version of idempiere, so could not create patch, however is the code sample for caching and attached is the DTO class.

MLookupFactory.java

1 2 private static CCache<Integer, MLookupDto> s_cacheRefLookup = new CCache<Integer, MLookupDto>("MLookupDto", 30, 60); // 1h
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 public static MLookupInfo getLookupInfo(Properties ctx, int WindowNo, int TabNo, int Column_ID, int AD_Reference_ID) { String ColumnName = ""; int AD_Reference_Value_ID = 0; boolean IsParent = false; String ValidationCode = ""; // MLookupDto mLookupDto = s_cacheRefLookup.get(Column_ID); if(mLookupDto!=null){ return getLookupInfo(ctx, WindowNo, TabNo, Column_ID, AD_Reference_ID, Env.getLanguage(ctx), mLookupDto.getColumnName(), mLookupDto.getAD_Reference_Value_ID(), mLookupDto.isParent(), mLookupDto.getCode()); } String sql = "SELECT c.ColumnName, c.AD_Reference_Value_ID, c.IsParent, vr.Code " + "FROM AD_Column c" + " LEFT OUTER JOIN AD_Val_Rule vr ON (c.AD_Val_Rule_ID=vr.AD_Val_Rule_ID) " + "WHERE c.AD_Column_ID=?"; PreparedStatement pstmt = null; ResultSet rs = null; try { pstmt = DB.prepareStatement(sql, null); pstmt.setInt(1, Column_ID); // rs = pstmt.executeQuery(); if (rs.next()) { ColumnName = rs.getString(1); AD_Reference_Value_ID = rs.getInt(2); IsParent = "Y".equals(rs.getString(3)); ValidationCode = rs.getString(4); } else { s_log.log(Level.SEVERE, "Column Not Found - AD_Column_ID=" + Column_ID); } } catch (SQLException ex) { s_log.log(Level.SEVERE, "create", ex); } finally { DB.close(rs, pstmt); rs = null; pstmt = null; } mLookupDto = new MLookupDto(ColumnName, AD_Reference_Value_ID, IsParent, ValidationCode); s_cacheRefLookup.put(Column_ID, mLookupDto); MLookupInfo info = getLookupInfo(ctx, WindowNo, TabNo, Column_ID, AD_Reference_ID, Env.getLanguage(ctx), ColumnName, AD_Reference_Value_ID, IsParent, ValidationCode); return info; }

Environment

None

Status

Assignee

Carlos Ruiz

Reporter

Pritesh Shah

Labels

None

Tested By

None

Priority

Major