01 November 2010

TROUBLESHOOTING: Performance degradation due to calculated attributes based on select count(*)

It is a known practice that when we want to query something in the db before performing an action, eg the data on the db,  to have a calculated  transient attr based on an Expression in the VO.



SELECT COUNT(*)    
       FROM EMPLOYEES   
       WHERE DEPARTMENT_ID = DEPARTMENTs.DEPARTMENT_ID   AND rownum < 2


Although the advantages are tha twe do not have more VO for the calculation, but
the problem is that this select is performed for every row and so if it has a count(*) like above it will break performance.

So if performance is a must try to refactor (depending on the rest requirements) your query like

SELECT 1    
       FROM EMPLOYEES   
       WHERE DEPARTMENT_ID = DEPARTMENTs.DEPARTMENT_ID   AND rownum = 1

and even create a separate Read-only SQL based VO with it.

No comments:

Post a Comment

You might also like:

Related Posts Plugin for WordPress, Blogger...
There was an error in this gadget