This discussion is archived
12 Replies Latest reply: Jan 31, 2013 9:26 AM by Joe Weinstein RSS

query is very slow if using PreparedStatement

987717 Newbie
Currently Being Moderated
Oracle database 10.2.0.1.0.
Java version: 1.6.0_23

I have a complex query. I uses PreparedStatement to execute it because I want to add some parameters when running.
It runs more than 25 minutes. However, when I use Statement it takes only 5 seconds (I set fixed values for the query instead of using parameters).


PreparedStatement  + This query = 25 minutes
SELECT ...
FROM ...
WHERE ...
AND
(
cp.cp_contractState_val = :1
AND
'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = :2
)

Statement+ This query = 5 seconds
SELECT ...
FROM ...
WHERE ...
AND
(
cp.cp_contractState_val = 'value 1'
AND
'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = 'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates'
)

_'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = 'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates'_ is the reason that cause slow performance. This condition is not necessary. However, i cannot remove it because the query is generated automatically.

Edited by: 984714 on Jan 29, 2013 11:26 PM
  • 1. Re: query is very slow if using PreparedStatement
    987717 Newbie
    Currently Being Moderated
    One more information, the issue doesn't happen on Oracle 11g

    Edited by: 984714 on Jan 29, 2013 11:27 PM
  • 2. Re: query is very slow if using PreparedStatement
    rp0428 Guru
    Currently Being Moderated
    >
    'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = 'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' is the reason that cause slow performance. This condition is not necessary. However, i cannot remove it because the query is generated automatically.
    >
    Well there must be something you aren't telling us.

    You say you can't remove the clause because the query is generated automatically yet you can modify the query to add bind variables. How can you do the one but not the other?

    The condition uses a literal string (appears to be similar to Oracle's SOA web services strings) so what is the datatype of the bind variable that you are using?

    Using bind variables is only needed if the same statement is going to executed multiple (many) times but with different values. It saves having to hard parse the query each time. If you are only running the statement once or a few times you won't really save much.

    You could also just use a condition that always evaluates such as 'WHERE myColumn IN ANY...'
  • 3. Re: query is very slow if using PreparedStatement
    jschellSomeoneStoleMyAlias Expert
    Currently Being Moderated
    984714 wrote:
    One more information, the issue doesn't happen on Oracle 11g
    Which indicates that it is likely that something is wrong with your tests or your assumptions.

    For example you ran the one test when the one database server (or box) was very busy doing something. Or the database server was busy with other requests which caused lock contentions. Or you didn't measure only the query. Or the results of the query are different (one returns 1 row and the other returns 100 million). Or something else like that.
  • 4. Re: query is very slow if using PreparedStatement
    dsurber Explorer
    Currently Being Moderated
    Look at the query plan. It could be that the optimizers are coming up with different plans. When you use PreparedStatements the optimizer considers the types of the bound values. This can give a different plan than you get with a plain Statement and all literal values. We have seen similar problems where in one case the optimizer uses an index but in another it uses a full table scan. That can account for the performance difference.

    Douglas
  • 5. Re: query is very slow if using PreparedStatement
    987717 Newbie
    Currently Being Moderated
    Hi jschell
    I ensure that the server is not busy. There are no other requests to the server because nobody can access to the server except me.
    I know how to measure the query on Oracle 10g and 11g to make a comparison between them correctly.
  • 6. Re: query is very slow if using PreparedStatement
    987717 Newbie
    Currently Being Moderated
    Thanks dsurber,

    I also doubt there is a problem related to execution plan.
    Oracle database confuses whether indexes will be used or not although they are created already on expected columns.
  • 7. Re: query is very slow if using PreparedStatement
    rp0428 Guru
    Currently Being Moderated
    >
    I also doubt there is a problem related to execution plan.
    Oracle database confuses whether indexes will be used or not although they are created already on expected columns.
    >
    Those two statement contradict each other.

    When you are troubleshooting you have to set aside any preconceived ideas about what the problem might be and actually gather evidence about what Oracle is actually doing. Often when people do that they skip over the most useful piece of evidence about what is taking place.

    So check each plan and see what Oracle is actually doing.

    It doesn't make sense that Oracle is using the same plan but the execution time is radically different because in Java (which Oracle doesn't even know about) you are using a different object class.
  • 8. Re: query is very slow if using PreparedStatement
    Tolls Journeyer
    Currently Being Moderated
    984714 wrote:
    _'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = 'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates'_ is the reason that cause slow performance. This condition is not necessary. However, i cannot remove it because the query is generated automatically.

    Edited by: 984714 on Jan 29, 2013 11:26 PM
    Well, the difference between the Prepared and normal Statement is simply that Oracle has determined that that line there is always true so the plan will simply ignore it for the non-bound query.
    You'd have to check the plans, but that would be my initial guess.
    Surprised it would result in such a large difference, but it's possible the plan (and only you can actually see what the plan is) for the bound query has decided it needs to do a full table scan somewhere and then check that 'soa' clause?

    11g may well have changed how the plans are drawn up, but again only you can actually see that.
    As others have said, compare the plans between 10g and 11g for the bound queries. It's not exactly a difficult task.

    ETA: Also, while I think about it, have you ensured the cache is clear when you time these things?

    Edited by: Tolls on 31-Jan-2013 01:03
  • 9. Re: query is very slow if using PreparedStatement
    987717 Newbie
    Currently Being Moderated
    I ensure the cache is clear.
    I concern that why the query spends only 5 seconds if I remove the condition that it's always true
    _'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = 'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates'_
  • 10. Re: query is very slow if using PreparedStatement
    Tolls Journeyer
    Currently Being Moderated
    I have no idea, but have you looked at the plan being used?
    You seem to be extraordinarily reluctant to look at them.
  • 11. Re: query is very slow if using PreparedStatement
    rp0428 Guru
    Currently Being Moderated
    >
    I ensure the cache is clear.
    I concern that why the query spends only 5 seconds if I remove the condition that it's always true
    'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates' = 'uddi:systinet.com:soa:model:taxonomies:contractAgreementStates'
    >
    And yet you totally ignore the expert advice dsurber, Tolls and I provided to check the execution plans to see what the difference is.
    >
    I also doubt there is a problem related to execution plan.
    >
    Your doubts are both irrevelant and wrong; Oracle does not use them in deciding what plan to use.

    As I already said above, and two others have also recommended:
    >
    When you are troubleshooting you have to set aside any preconceived ideas about what the problem might be and actually gather evidence about what Oracle is actually doing. Often when people do that they skip over the most useful piece of evidence about what is taking place.

    So check each plan and see what Oracle is actually doing.

    It doesn't make sense that Oracle is using the same plan but the execution time is radically different because in Java (which Oracle doesn't even know about) you are using a different object class.
  • 12. Re: query is very slow if using PreparedStatement
    Joe Weinstein Expert
    Currently Being Moderated
    Hi. I'll chip in. Please tell me how the column cp.cp_contractState_val is defined in the table.
    I'm going to guess it is a CHAR(XX), rather than a VARCHAR2(XX). If so, then the difference
    is that if the query is compiled in the DBMS with the hard-coded compare value, the DBMS
    is able to interpret and pad the hard-coded value out to the length of the column, and therefore
    allow any query to use any index on that column. If instead, you use a PreparedStatement,
    the compare value is only sent after the query plan has been chosen, and the value sent to
    it is a variable-length string, not a fixed one, so the DBMS may not feel safe/able to pad to
    match a fixed-length field, and therefore will not use the index.

Legend

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