Re: Poor Oracle performance? getting rid of bind variables
Re: Poor Oracle performance? getting rid of bind variables
- Subject: Re: Poor Oracle performance? getting rid of bind variables
- From: Dov Rosenberg <email@hidden>
- Date: Tue, 21 Aug 2007 17:02:54 -0400
- Thread-topic: Poor Oracle performance? getting rid of bind variables
Title: Re: Poor Oracle performance? getting rid of bind variables
Is there a similar performance hit/solution for SQL Server 2005?
Dov Rosenberg
On 8/21/07 5:00 PM, "Ken Anderson" <email@hidden> wrote:
In the java command line parameters:
-Doracle.jdbc.V8Compatible=true
Ken
On Aug 21, 2007, at 4:48 PM, Dov Rosenberg wrote:
Where did you set that property? In the connection dictionary? We use prototypes if that makes any difference
What happens if you are using 10g?
Thanks in advance
On 8/21/07 4:12 PM, "Ken Anderson" <email@hidden> wrote:
David, you win a dinner if you're ever in New York City! Yes, the query absolutely flies now - Thank you!!
Ken
On Aug 21, 2007, at 4:03 PM, David den Boer wrote:
Hi Ken,
What type of column is your date column? Is it TIMESTAMP or DATE, and what version of Oracle is it?
The reason I ask is that when you bind an NSTimestamp into a DATE column, Oracle has to do conversion on it from TIMESTAMP to DATE. What we had to do to solve that problem is set the property :
oracle.jdbc.V8Compatible=true
And it solved our problems completely. We went from seconds down to milliseconds in all cases where it was slow.
What that does is has Oracle jdbc driver do the conversion (like it used to in drivers up to 9.2.0.3) instead of at the database level.
Hope this helps.
On Aug 21, 2007, at 11:11 AM, Ken Anderson wrote:
Art,
Seems like other people have similar problems with bind variables:
http://www.thescripts.com/forum/thread65559.html
According to this guy, the optimizer punts when bind variables are used and uses a table scan! Now, more than anything, I really need to figure out how to stop EOF from using bind variables.
Any thoughts?
Thanks,
Ken
On May 16, 2007, at 4:04 PM, Art Isbell wrote:
On May 16, 2007, at 9:33 AM, Gavin Eadie wrote:
I concur, with Oracle and OpenBase - that setting has no effect (and its default is false anyway) .. Gav
I see that JDBCExpression overrides EOSQLExpression's useBindVariables() to always return true. And OraclePlugIn.OracleExpression, a JDBCExpression subclass, doesn't override useBindVariables(), so the Oracle plugin must be set to use bind variables regardless of the value of EOAdaptorUseBindVariables.
That said, I'd be surprised that merely using bind variables would cause the poor fetch performance. In fact, I thought that bind variable usage was supposed to help the query optimizer improve performance, but my memory might be faulty.
Aloha,
Art
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden