Re: PreparedStatement performance issues
Posted by: Ledion B
Date: May 06, 2005 11:47PM

Mark Matthews wrote:

> The prepared statement code in the server is our
> first "cut" at implementing prepared statements.
> As such, it doesn't cache execution plans, so as
> you've seen in some cases it's no quicker than
> normal statements (and is sometimes slower).

Well, correct me if I am wrong. For a prepared statement all query parsing and verification is done only once, (is this part of the execution plan ?) given this using prepared statement should be a bit faster than using statement. Is query parsing and verification a step where one could gain performance?

> Where it has performance wins is when you 1) Have
> a _lot_ of static data (read a few KB) compared to
> your parameters, or you have a lot of
> numeric/datetime parameters and/or columns
> returned.
> I'm not sure you're going to see a lot of
> difference over 10000 executions on a single
> thread either. Where the performance differences
> really come into play the most is on highly loaded
> networks or with complex result sets with many
> columns.

I am not planning on using the PreparedStatement on a single thread, I just thought that would be a good way to test its performance. Maybe I should try a multithreaded "attack"

> Also, Server-side prepared statements are not able
> to take advantage of the query cache built into
> the server, so if you have any reads that repeat,
> those will end up being quite a bit quicker in
> some cases if the table is read-only.

I was just about to write about this, than when query caching is enabled the preparedstatement performs much worse than the statement. This brings me int a suggestion as to where is the cache being looked up. Does the cache simply maps a query string to result set before it is being parsed or right before the execution planning starts? I think the latter is more correct and would make cache available to prepared statements too.

> -Mark


Options: ReplyQuote

Written By
Re: PreparedStatement performance issues
May 06, 2005 11:47PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.