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

[Nicolas Malin (Jira)]

Performance : GenericDao does not always use complete PreparedStatement
-----------------------------------------------------------------------

                 Key: OFBIZ-4286
                 URL: https://issues.apache.org/jira/browse/OFBIZ-4286
             Project: OFBiz
          Issue Type: Improvement
          Components: framework
    Affects Versions: SVN trunk
            Reporter: Philippe Mouawad


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

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

[Nicolas Malin (Jira)]

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

Philippe Mouawad closed OFBIZ-4286.
-----------------------------------

> Performance : GenericDao does not always use complete PreparedStatement
> -----------------------------------------------------------------------
>
>                 Key: OFBIZ-4286
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-4286
>             Project: OFBiz
>          Issue Type: Improvement
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Philippe Mouawad
>
> 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

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

[Nicolas Malin (Jira)]

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

Philippe Mouawad resolved OFBIZ-4286.
-------------------------------------

    Resolution: Duplicate

Duplicate OFBIZ-4285

> Performance : GenericDao does not always use complete PreparedStatement
> -----------------------------------------------------------------------
>
>                 Key: OFBIZ-4286
>                 URL: https://issues.apache.org/jira/browse/OFBIZ-4286
>             Project: OFBiz
>          Issue Type: Improvement
>          Components: framework
>    Affects Versions: SVN trunk
>            Reporter: Philippe Mouawad
>
> 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

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