If the query has where clause where division_id = :1 and division_order_id = :2
execution tooks way too long taking into account that division_id (VARCHAR2) + division_order_id (VARCHAR2) is the primary key in this table. A simple addition forcing division_order_id to be VARCHAR, dramatically improves performance where division_id = :1 and division_order_id = '' || :2 So, the code obviously binds the second parameter incorrectly. Thank you, Igor -----Original Message----- From: Tim Bunce [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 21, 2005 10:23 AM To: Igor Korolev Cc: dbi-users@perl.org; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: Bug in Oracle driver ? On Wed, Dec 21, 2005 at 09:29:18AM -0600, Igor Korolev wrote: > Wayne, Tim, > > Any ideas why DBD::Oracle incorrectly binds even non-numeric value > '1130979589-6919-090637' from the > below example ? I don't understand the question. Tim. > Thank you, > > Igor > > _____________________________________________ > From: Igor Korolev > Sent: Thursday, December 15, 2005 4:28 PM > To: 'dbi-users@perl.org' > Subject: RE: Bug in Oracle driver ? > > Hello, > > It looks like the Oracle driver incorrectly binds VARCHAR2 variables in > case a number is passed from > perl. > > Here is an example. This table has a primary key on division_id + > division_order_id, so such selects > should run very quickly (and they do run very quickly from sqlplus or > toad). > > However, if I execute this using selectrow_array method of DBI.pm > > my $sql = 'select ' . join(',',@$sum_fields) . ' from ' . $table . > qq[ where division_id = :1 and division_order_id = :2]; > > It takes up to 1 second per select: > > Order [1], select took: [0.730479001998901] > Order [2], select took: [0.441529989242554] > Order [1130979589-6919-090637], select took: [0.462771892547607] > Order [3], select took: [0.424475193023682] > Order [947647153], select took: [0.513856172561646] > Order [4], select took: [0.49094295501709] > Order [6], select took: [0.523995161056519] > Order [7], select took: [0.558786153793335] > Order [8], select took: [0.452331066131592] > Order [131635406], select took: [0.451704978942871] > > However, a simple addition forcing VARCHAR2, dramatically improves > performance > > my $sql = 'select ' . join(',',@$sum_fields) . ' from ' . $table . > qq[ ]; > > Order [1], select took: [0.00937604904174805] > Order [2], select took: [0.00504183769226074] > Order [1130979589-6919-090637], select took: [0.0115630626678467] > Order [3], select took: [0.00729489326477051] > Order [947647153], select took: [0.00545406341552734] > Order [4], select took: [0.00602984428405762] > Order [6], select took: [0.00556111335754395] > Order [7], select took: [0.00576210021972656] > Order [8], select took: [0.00520086288452148] > Order [131635406], select took: [0.00546598434448242] > > Thank you, > > Igor
