Re: Very slow executes with utf8 integer parameters in DBD::Oracle
- From: martin.evans@xxxxxxxxxxxx (Martin Evans)
- Date: Mon, 17 Sep 2007 12:27:58 +0100
Peter J. Holzer wrote:
On 2007-09-14 18:03:15 +0100, Martin Evans wrote:I have NLS_LANG=AMERICAN_AMERICA.AL32UTF8 as recommended in DBD::Oracle when using utf8 and I need to as my data is utf8 in Perl.
Grossly simplified my code does:
o select integer_primary_key_field from table
o prepare(select from another_table where field = ?)
o execute($inter_primary_key_value_retrieved_from_select)
This query is vastly more complex than this really
Even though the field retrieved from the first table is an integer when I look at it, Perl has utf8 flag set. When these utf8 encoded integers are then passed into the execute for a select on another table the execute takes 0.7s. Now that may not sound a lot to you but this query gets runs a lot. If I downgrade the integer parameter with utf8::downgrade before passing it to execute the execute takes 0.01s.
When I look at the QEP the filter contains a TO_NUMBER(:p1) and I think Oracle has decided it cannot use an index on the column.
I tried binding the parameter as ora_number but that does not help. The only thing which seems to work is to downgrade the parameter from utf8.
Any ideas?
This may be the same problem I ran into a few weeks ago. See
http://www.mail-archive.com/dbi-users@xxxxxxxx/msg30138.html
I have a patch for this but I still haven't gotten around to testing it,
so even though it's only a few lines I don't want to post it yet. Feel
free to contact me off-list if you want to try it.
The workaround which I actually use in production code is to set
NLS_NCHAR=US7ASCII. Of course this isn't a good idea if you have
nvarchar2 or nclob columns. Explicitely binding with the correct cs_form
also works:
$sth->bind_param(1, $name, { ora_csform => SQLCS_IMPLICIT });
hp
Thank you Peter. Rather stupidly, I had marked the post your refer to as particularly noteworthy but forgot I'd seen it.
My time for the query has come down from at best .7s (some were a lot worse) to 0.035s - a huge difference. All I've done to achieve this is:
1) bound the select columns which are integers as ORA_NUMBER (which I don't think is having an real affect as the numbers I get back are still marked utf-8).
2) added SQLCS_IMPLICIT to the bound parameters which are numbers (keys in my case).
Like you found, when I look at the QEP, I find Oracle is doing a lot of different things now including the creation of a view and use of a index it was not using before.
All my data is utf8 so this problem probably exists elsewhere as well. Does anyone know what the disadvantage of changing DBD::Oracle to default to SQLCS_IMPLICIT instead of SQLCS_NCHAR is?
Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
.
- References:
- Very slow executes with utf8 integer parameters in DBD::Oracle
- From: Martin Evans
- Re: Very slow executes with utf8 integer parameters in DBD::Oracle
- From: Peter J. Holzer
- Very slow executes with utf8 integer parameters in DBD::Oracle
- Prev by Date: Re: Very slow executes with utf8 integer parameters in DBD::Oracle
- Next by Date: 2nd Patch for SQL::Statement
- Previous by thread: Re: Very slow executes with utf8 integer parameters in DBD::Oracle
- Next by thread: odd install problem with DBD::Pg
- Index(es):
Relevant Pages
|
|
