This discussion is archived
1 2 Previous Next 27 Replies Latest reply: May 16, 2011 9:45 AM by user13131199 RSS

Native SQL Performance Difference Between Hard-Coded Value and Parameter

user13131199 Newbie
Currently Being Moderated
Hi,

I have a native SQL (Oracle) query (fairly long & complex with a few sub-queries) that returns in under a second in both ODSI and using an external SQL tool. This query has a hard-coded value for a particular column, namely, a date column.
When I modify the ODSI function signature so that I pass in a parameter and then replace the hard-coded value in the native SQL with the appropriate parameter binding notation (i.e. '?'), the query takes much longer (2-30 seconds). The duration of the query depends on how many records are actually returned, so it must be running a separate query for each of the results (i.e. the more results returned, the longer the query takes to return).
What can I do to keep the duration of my ODSI query low while allowing for the parameter?
  • 1. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    mikereiche Pro
    Currently Being Moderated
    1) avoid native SQL queries like the plague, try to figure out (ask for help, if necessary) how to write the xquery to get the result you want. It may be difficult up front, but will save you all kinds of headaches in the future (mostly performance - since there is little ODSI can do to optimize your query).

    2) See (1). Then - read the 'Best Practices' document posted to this forum.

    3) See (1). I'm confused by the information you have provided. You state that (a) simply replacing a hard-coded value with a parameter (the same value, I assume) makes the query take much longer; and (b) the duration of the query depends on how many records are actually returned. Hmmm... are these two related? If you used the same value, then why would more records be returned? If it is the same value, and the binding notation is slower - perhaps this is due to the database taking longer for the binding notation - try it outside of ODSI. (if it is used in 'LIKE' it will be slower).

    4) See (1). Then look at the 'audit' to see what is actually being executed.

    5) See (1). Then create a dataspace with both versions of the function and carefully compare the query plans to see what is different. Also post the query plans here.

    6) open a case with customer support.
  • 2. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    661228 Newbie
    Currently Being Moderated
    If you really can't find a way to write this correctly in ODSI and xQuery, you could do it with a stored procedure. I have done this in situations where I have many optional parameters and the query itself depends on which of the parameters the user provides (that is, I will join certain tables only if necessary to satisfy the where clause). ODSI does a good job of calling the stored procedure and mapping its results. I believe the stored procedure needs to return a cursor for this to work correctly.

    Good luck,

    Jeff

    Edited by: jhoffmanme on May 11, 2011 2:23 PM
  • 3. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    user13131199 Newbie
    Currently Being Moderated
    Thanks guys. I'm going to clarify #3 for a moment because that's the weirdness I'd like to sort out first.

    Here a snip from my ds file:
    =================================================================================================================================================================

    (::pragma function <f:function visibility="protected" kind="library" isPrimary="false" style="sqlQuery" xmlns:f="urn:annotations.ld.bea.com">
    <sql isSubquery="false">


    select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (

    select distinct

    sd.codeid, sd.description,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )

    ISOVR,


    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )

    ISINT



    ,trow.answerstr FEE_CODE_DOC_TYPE

    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd

    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup

    where




    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and






    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and
    ? between safp.effectivedate and safp.enddate and
    o.customerno = ? || ?

    order by sd.codeid

    )
    where ISINT = 1





    </sql>


    </f:function>::)

    declare function f1:getFeeCodeByCaregiverEffectiveDate1($effectiveDate as xs:dateTime, $caregiverType as xs:string, $caregiverNumber as xs:string) as schema-element(t2:FeeCode3) external;


    =================================================================================================================================================================

    when I run this, I pass in 3 parameters and, depending on the parameters I pass in, the result can take 2-30 seconds. Now, when I make a change to the ds file...see below:

    =================================================================================================================================================================

    (::pragma function <f:function visibility="protected" kind="library" isPrimary="false" style="sqlQuery" xmlns:f="urn:annotations.ld.bea.com">
    <sql isSubquery="false">


    select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (

    select distinct

    sd.codeid, sd.description,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )

    ISOVR,


    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )

    ISINT



    ,trow.answerstr FEE_CODE_DOC_TYPE

    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd

    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup

    where




    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and






    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and
    '01-MAY-11' between safp.effectivedate and safp.enddate and
    o.customerno = ? || ?

    order by sd.codeid

    )
    where ISINT = 1





    </sql>


    </f:function>::)

    declare function f1:getFeeCodeByCaregiverEffectiveDate1($caregiverType as xs:string, $caregiverNumber as xs:string) as schema-element(t2:FeeCode3) external;


    =================================================================================================================================================================

    Notice how I've:
    1) changed the signature of the function by removing the first parameter
    2) replaced the first ? in the query with a hard-coded value

    When I do this, and run the function, the results never take longer than 1 second...are are often under 0.5 seconds. So, I hope that clarifies what I meant from my original post.

    So, it seems that having a constant as opposed to a parameter makes a difference...so I pulled out SQL Developer and tried to run the same query:

    =================================================================================================================================================================
    select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (

    select distinct

    sd.codeid, sd.description,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )

    ISOVR,


    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )

    ISINT



    ,trow.answerstr FEE_CODE_DOC_TYPE

    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd

    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup

    where




    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and






    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and

    :date1 between safp.effectivedate and safp.enddate and

    o.customerno = :cgvtyp || :cgvnum
    order by sd.codeid
    )
    where ISINT = 1;
    =================================================================================================================================================================

    Notice how I have 3 parameters: :date1, :cgvtyp, and :cgvnum
    When I run this, SQL Developer pops up a prompt for me to put in the parameters, which I do. The results always return in less than 1 second and are often under 0.5 seconds. So, why does using parameters in SQL Developer not take the same performance hit as ODSI? And how can I overcome this?
  • 4. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    661228 Newbie
    Currently Being Moderated
    I don't know if SQL Developer creates a prepared statement and uses runtime variable substitution. A better test would be to try this with your own test app using JDBC. I have seen cases where Oracle will choose to use an index when it has a hard-coded value, but ignore the index when it doesn't have a hard-coded value in the where clause...so it could be a database thing rather than ODSI.

    Unsolicited opinion: I would create a view and implement your ODSI function against that, instead of embedding the whole SQL in ODSI.

    Good luck,

    Jeff
  • 5. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    user13131199 Newbie
    Currently Being Moderated
    Thanks, Jeff. The view or stored procedure (you suggested earlier) are the acceptable alternatives I was fishing for. If I can find the time, I will try the JDBC test app to see happens when I put in some variables there. I will get our DBA to check if an index is being used/ignored during the queries.
  • 6. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    mikereiche Pro
    Currently Being Moderated
    Agree with Jeff about comparing your own Java/JDBC with binding parameters instead of using SQL Developer.

    Google on : Oracle Explain Plan

    Then use Explain Plan to capture the oracle query plans of the query with the parameter binding and the query with the constant. Compare them. If they are not identical, you'll need to talk to your dba. If they are identical, come back here with the ODSI query plans and the ODSI audit for the two queries. For the purpose of this exercise, always use the same parameter values to reduce the number of variables in the problem.
  • 7. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    user13131199 Newbie
    Currently Being Moderated
    Thanks, Mike.
    I ran the JDBC test and found the performance to be good when binding parameters. So, it seems that however ODSI is parsing the native SQL is different than how JDBC parses the SQL and binds the parameters.
    I will report back here the results of the Explain Plan.
  • 8. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    mikereiche Pro
    Currently Being Moderated
    Forget the explain plan for now.

    Provide the two ODSI query plans and also the ODSI Audit from both versions of the query.
  • 9. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    user13131199 Newbie
    Currently Being Moderated
    OSDI plan with date parameter:

    <?xml version="1.0"?>
    <source ns="fn-bea" name="jdbc.wcb.fineos" kind="relational" tip="jdbc.wcb.fineos">
    <![CDATA[select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (

    select distinct

    sd.codeid, sd.description,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )

    ISOVR,


    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )

    ISINT



    ,trow.answerstr FEE_CODE_DOC_TYPE

    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd

    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup

    where




    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and






    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and
    ? between safp.effectivedate and safp.enddate and
    o.customerno = ? || ?

    order by sd.codeid

    )
    where ISINT = 1]]>
    <variable name="__fparam0" kind="EXTERNAL">
    </variable>
    <variable name="__fparam1" kind="EXTERNAL">
    </variable>
    <variable name="__fparam2" kind="EXTERNAL">
    </variable>
    </source>

    OSDI plan with date constant:

    <?xml version="1.0"?>
    <source ns="fn-bea" name="jdbc.wcb.fineos" kind="relational" tip="jdbc.wcb.fineos">
    <![CDATA[select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (

    select distinct

    sd.codeid, sd.description,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,

    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )

    ISOVR,


    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )

    ISINT



    ,trow.answerstr FEE_CODE_DOC_TYPE

    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd

    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup

    where




    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and






    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and
    '01-MAY-11' between safp.effectivedate and safp.enddate and
    o.customerno = ? || ?

    order by sd.codeid

    )
    where ISINT = 1]]>
    <variable name="__fparam0" kind="EXTERNAL">
    </variable>
    <variable name="__fparam1" kind="EXTERNAL">
    </variable>
    </source>

    ODSI Audit with date parameter:

    [Thu May 12 13:02:23 GMT-06:00 2011] Starting...

    Query compilation time: 0 ms
    Query evaluation time: 16142 ms
    Operation duration: 16189 ms

    Audit Event:

    common/application
    user: weblogic
    name: ClaimsDataspace
    server: AdminServer
    eventkind: evaluation

    query/cache/queryplan
    found: false
    type: XQUERY_PLAN_CACHE

    query/cache/queryplan
    type: XQUERY_PLAN_CACHE
    inserted: true

    query/performance
    compiletime: 0

    common/session/query/invocation
    time: Thu May 12 13:02:07 GMT-06:00 2011
    blocksize: 65536
    duration: 16001

    common/session/query/invocation
    time: Thu May 12 13:02:23 GMT-06:00 2011
    blocksize: 65536
    duration: 47

    common/session/query/invocation
    time: Thu May 12 13:02:23 GMT-06:00 2011
    blocksize: 65536
    duration: 46

    common/session/query/invocation
    time: Thu May 12 13:02:23 GMT-06:00 2011
    blocksize: 35779
    duration: 16

    query/wrappers/relational
    source: jdbc.wcb.fineos
    sql:
    select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (
    select distinct
    sd.codeid, sd.description,
    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,
    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )
    ISOVR,
    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )
    ISINT
    ,trow.answerstr FEE_CODE_DOC_TYPE
    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd
    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup
    where
    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and
    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and
    ? between safp.effectivedate and safp.enddate and
    o.customerno = ? || ?
    order by sd.codeid
    )
    where ISINT = 1
    parameters:
    2011-05-01T00:00:00
    DOC
    007492
    time: 16048
    rows: 1967

    query/performance
    evaltime: 16142

    query/service
    result:

    *** removed due to length ***

    query/service
    function: getFeeCodeByCaregiverEffectiveDate1
    arity: 3
    dataservice: ld:org/wcb/claims/payment/FINEOS/physical/SQL.ds
    query:
    import schema namespace t1 = "http://www.test.com/claims/payment" at "ld:org/wcb/claims/payment/FINEOS/physical/schemas/SQL.xsd";
    declare namespace ns0="ld:org/wcb/claims/payment/FINEOS/physical/SQL";
    declare namespace ns1="http://www.w3.org/2001/XMLSchema";
    declare variable $__fparam0 as ns1:dateTime external;
    declare variable $__fparam1 as ns1:string external;
    declare variable $__fparam2 as ns1:string external;
    fn:subsequence(
    for $FeeCode3 in ns0:getFeeCodeByCaregiverEffectiveDate1($__fparam0,$__fparam1,$__fparam2)
         return
                   $FeeCode3
    ,1,5000)
    parameters:
    2011-05-01T00:00:00
    DOC
    007492

    common/time
    duration: 16189
    timestamp: Thu May 12 13:02:07 GMT-06:00 2011

    [Thu May 12 13:02:23 GMT-06:00 2011] End
    ------------------------------------------------------------------------



    ODSI Audit with date constant:

    [Thu May 12 13:10:00 GMT-06:00 2011] Starting...

    Query compilation time: 0 ms
    Query evaluation time: 359 ms
    Operation duration: 375 ms

    Audit Event:

    common/application
    user: weblogic
    name: ClaimsDataspace
    server: AdminServer
    eventkind: evaluation

    query/cache/queryplan
    found: true
    type: XQUERY_PLAN_CACHE

    query/performance
    compiletime: 0

    common/session/query/invocation
    time: Thu May 12 13:10:00 GMT-06:00 2011
    blocksize: 59256
    duration: 359

    query/wrappers/relational
    source: jdbc.wcb.fineos
    sql:
    select codeid, description, FEE_CODE_DOC_TYPE, ismax, isovr
    from
    (
    select distinct
    sd.codeid, sd.description,
    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-MAX (MAXIMUM AMOUNT)'
    ) ISMAX,
    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-OVR (Overrideable)'
    )
    ISOVR,
    (
    select count(*) from wcbapp.tolserviceset ss, wcbapp.RSERVICESRVCSETSERVICESETS sss, wcbapp.tolservicedefinition sd1 where
    sss.i_from = sd.i and
    sss.c_from = sd.c and
    sss.i_to = ss.i and
    sss.c_to = ss.c and
    sd1.codeid = sd.codeid and
    ss.name = 'FEEGROUP-INT (Internet Enterable)'
    )
    ISINT
    ,trow.answerstr FEE_CODE_DOC_TYPE
    from wcbapp.tocorganisation o, wcbapp.tolpartydetails pd, wcbapp.tolserviceagrmtforprovider safp,
    wcbapp.tolserviceagreement sa, wcbapp.tolserviceagreementversion sav, wcbapp.rsrvchrgrsrvagrvrserviceagreem scg_sav,
    wcbapp.tolservicechargegroup scg, wcbapp.tolservicechargegroupversion scgv, wcbapp.tolserviceprovisionagreement spa,
    wcbapp.tolservicedefinition sd
    ,wcbapp.trow trow, wcbapp.tlookupversion tlookupversion, wcbapp.tlookup tlookup
    where
    trow.i_lkpver_rows = tlookupversion.i
    and trow.c_lkpver_rows = tlookupversion.c
    and tlookupversion.i_lookup_versions = tlookup.i
    and tlookupversion.c_lookup_versions = tlookup.c
    and sd.codeid = trow.minstr_1
    and sd.codeid = trow.maxstr_1
    and tlookup.name = 'FeeCodeToDocumentLookup' and
    spa.i_service_serviceratede = sd.i and
    spa.c_service_serviceratede = sd.c and
    spa.i_srchrgrv_serviceratede = scgv.i and
    spa.c_srchrgrv_serviceratede = scgv.c and
    scgv.i_srvchrgr_servicecharge = scg.i and
    scgv.c_srvchrgr_servicecharge = scg.c and
    scg.i = scg_sav.i_from and
    scg.c = scg_sav.c_from and
    scg_sav.i_to = sav.i and
    scg_sav.c_to = sav.c and
    sav.i_srvcagrm_serviceagreem = sa.i and
    sav.c_srvcagrm_serviceagreem = sa.c and
    sa.i = safp.i_srvcagrm_provider and
    sa.c = safp.c_srvcagrm_provider and
    safp.i_prtdtls_serviceagreem = pd.i and
    safp.c_prtdtls_serviceagreem = pd.c and
    pd.i_ocprty_party = o.i and
    pd.c_ocprty_party = o.c and
    '01-MAY-11' between safp.effectivedate and safp.enddate and
    o.customerno = ? || ?
    order by sd.codeid
    )
    where ISINT = 1
    parameters:
    DOC
    007492
    time: 344
    rows: 500

    query/performance
    evaltime: 359

    query/service
    result:

    *** removed due to length ***

    query/service
    function: getFeeCodeByCaregiverEffectiveDate1
    arity: 2
    dataservice: ld:org/wcb/claims/payment/FINEOS/physical/SQL.ds
    query:
    import schema namespace t1 = "http://www.test.com/claims/payment" at "ld:org/wcb/claims/payment/FINEOS/physical/schemas/SQL.xsd";
    declare namespace ns0="ld:org/wcb/claims/payment/FINEOS/physical/SQL";
    declare namespace ns1="http://www.w3.org/2001/XMLSchema";
    declare variable $__fparam0 as ns1:string external;
    declare variable $__fparam1 as ns1:string external;
    fn:subsequence(
    for $FeeCode3 in ns0:getFeeCodeByCaregiverEffectiveDate1($__fparam0,$__fparam1)
         return
                   $FeeCode3
    ,1,500)
    parameters:
    DOC
    007492

    common/time
    duration: 375
    timestamp: Thu May 12 13:10:00 GMT-06:00 2011

    [Thu May 12 13:10:00 GMT-06:00 2011] End
    ------------------------------------------------------------------------
  • 10. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    mikereiche Pro
    Currently Being Moderated
    Can you show the function signature? Are you using an xs:date? or an xs:dateTime? or an xs:string?

    p.s. :

    Your first query (parameterized) retrieves 1967 rows, and the second query retrieves only 500 rows. This is because in the first query specifies max rows of 5000 and the second one specifies 500. This isn't the reason for the discrepancy, but it does make my job harder. Be careful.
  • 11. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    user13131199 Newbie
    Currently Being Moderated
    the 4th post on this thread has the signatures of the functions...it's an xs:dateTime.
    I have tried using an xs:string as a parameter and passing the string '01-MAY-11' in and that didn't seem to make a difference.

    Sorry, Mike...I didn't mean to slip that row restriction in there...I tweaked the ODSI test tool setting for the first query but forgot to for the second query.
  • 12. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    mikereiche Pro
    Currently Being Moderated
    Ok, you're using a dateTime for the bind parameter. But you're using a string (which, I assume gets converted to a date) for the constant. Use a date type for both or a dateTime (with hours etc) for both.

    For your constant, you could use something like this.

    to_date('2011/05/01:11:11:11AM', 'yyyy/mm/dd:hh:mi:ssam')

    Under some conditions, ODSI uses a different type for date-time for Oracle parameters - but I thought it was when the date-time contained milli-seconds.
  • 13. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    user13131199 Newbie
    Currently Being Moderated
    Ok, I changed the constant from *'01-MAY-11'* to to_date('20110501000000', 'yyyymmddhh24miss')

    There was no change in the performance of the constant query. I left my date parameter query using a dateTime.
  • 14. Re: Native SQL Performance Difference Between Hard-Coded Value and Parameter
    mikereiche Pro
    Currently Being Moderated
    Your constant date still does not have any hours/minutes/seconds (they are all zero). Try it with non-zero hours/minutes/seconds like I showed.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points