[jira] [Updated] (OFBIZ-4285) Performance : GenericDao does not always use complete PreparedStatement

["Philippe Mouawad (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/OFBIZ-4285?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Philippe Mouawad updated OFBIZ-4285:
------------------------------------

    Description: 
Hello,
While profiling SQL requests going to DB I noticed a strange behaviour where Ofbiz builds SQL Statement partly with ? and partly with inline values which results in lower Database performances.

Issue existed on updateByCondition and has been fixed but it still exists in deleteByCondition.
It also exists in find where EntityFunction are used


I there some reason for this code ?
Is there another way to use delegator that makes it work better ?

Thank you
Regards
Philippe
http://www.ubik-ingenierie.com

  was:
Hello,
While profiling SQL requests going to DB I noticed a strange behaviour where Ofbiz builds SQL Statement partly with ? and partly with inline values which results in lower Database performances.

Example:
JobManager#poll  List<GenericValue> jobEnt = delegator.findByAnd("JobSandbox", updateFields, order);
results in the following SQL query:
UPDATE ECFR.JOB_SANDBOX SET RUN_BY_INSTANCE_ID = ?, STATUS_ID = ? WHERE ((RUN_TIME <= '2011-05-17 17:02:33.163' AND START_DATE_TIME IS NULL AND CANCEL_DATE_TIME IS NULL AND RUN_BY_INSTANCE_ID IS NULL) AND (POOL_ID IS NULL OR POOL_ID = 'poolfrt025'))

As you can see, RUN_TIME condition is inlined.
Analysing this behaviour, I think issue comes from EntityConditionBase#addValue:
    protected void addValue(StringBuilder buffer, ModelField field, Object value, List<EntityConditionParam> params) {
        SqlJdbcUtil.addValue(buffer, params == null ? null : field, value, params);
    }

and particularly:
params == null ? null : field

this ends up here in SqlJdbcUtil#addValueSingle and you can see that as field is null :
    public static void addValueSingle(StringBuilder buffer, ModelField field, Object value, List<EntityConditionParam> params) {
        if (field != null) {
            buffer.append('?');
        } else {
            buffer.append('\'').append(value).append('\'');
        }
        if (field != null && params != null) params.add(new EntityConditionParam(field, value));
    }


I there some reason for this code ?
Is there another way to use delegator that makes it work better ?

Thank you
Regards
Philippe
http://www.ubik-ingenierie.com


> Performance : GenericDao does not always use complete PreparedStatement
> -----------------------------------------------------------------------
>
>                 Key: OFBIZ-4285
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-4285
>             Project: OFBiz
>          Issue Type: Improvement
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Philippe Mouawad
>              Labels: PERFORMANCE
>
> Hello,
> While profiling SQL requests going to DB I noticed a strange behaviour where Ofbiz builds SQL Statement partly with ? and partly with inline values which results in lower Database performances.
> Issue existed on updateByCondition and has been fixed but it still exists in deleteByCondition.
> It also exists in find where EntityFunction are used
> I there some reason for this code ?
> Is there another way to use delegator that makes it work better ?
> Thank you
> Regards
> Philippe
> http://www.ubik-ingenierie.com

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira