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

[Nicolas Malin (Jira)]

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


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-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

[Nicolas Malin (Jira)]

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

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

    Attachment: patch-OFBIZ-4285-genericDAO.patch

Patch that fixes deleteByCondition
Philippe Mouawad
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
>         Attachments: patch-OFBIZ-4285-genericDAO.patch
>
>
> 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

[Nicolas Malin (Jira)]

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

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

    Attachment: patch-OFBIZ-4285-2.patch

Patch that fixes issue on SELECT queries.
I am less sur of this one, your critics are welcome.

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
>         Attachments: patch-OFBIZ-4285-2.patch, patch-OFBIZ-4285-genericDAO.patch
>
>
> 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

[Nicolas Malin (Jira)]

     [ 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 for Select queries


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.

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

> 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
>         Attachments: patch-OFBIZ-4285-2.patch, patch-OFBIZ-4285-genericDAO.patch
>
>
> 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 for Select queries
> 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