Query Performance Problems with OracleClient OracleParameters

[Biff Gaut]

Hello,

I am querying an Oracle database using the Microsoft Oracle Data
Provider for .NET and I am seeing significant performance penalties
when I us parameter based queries rather than pure text dynamic SQL.
Below is an actual query that demonstrates the problem - the first
query, which embeds the target value directly in the query executes in
abou1 1/10 sec. When I run the exact same query using a parameter I
get hung up for 25 seconds. Has anyone seen a problem like this or
have any idea what could be causing this delay? Is it endemic to
using the Microsoft provider as I have seen hinted at in other
messages? Being that we are close to deployment, I would really
prefer not to switch providers and have security concerns about
abandoning parameters.

//---------------------------------------
// Doing the query with Dynamic SQL is very quick
//---------------------------------------
string connectionString = "Data
Source=ValidateConnectionStringOmitted";

using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
OracleCommand cmd = new OracleCommand();

cmd.Connection = cn;

cmd.CommandText =
@"SELECT
DISTINCT I.COL1,
I.COL2,
E.COL3,
E.COL1
FROM
TABLE1 I,
TABLE2 E
WHERE
I.COL1 = E.COL1 AND
I.COL2 LIKE 'AVALUE%'";

cmd.CommandType = System.Data.CommandType.Text;

OracleDataAdapter a = new OracleDataAdapter(cmd);

DataSet d = new DataSet();

// Timing on this call shows execution time of around 100 ms
a.Fill(d);

}

//---------------------------------------
// Using a parameter has a performance hit of 2 orders of magnitude!
//---------------------------------------
string connectionString = "Data
Source=ValidateConnectionStringOmitted";

using (OracleConnection cn = new OracleConnection(connectionString))
{
cn.Open();
OracleCommand cmd = new OracleCommand();

cmd.Connection = cn;

cmd.CommandText =
@"SELECT
DISTINCT I.COL1,
I.COL2,
E.COL3,
E.COL1
FROM
TABLE1 I,
TABLE2 E
WHERE
I.COL1 = E.COL1 AND
I.COL2 LIKE :LastName";


cmd.CommandType = System.Data.CommandType.Text;

cmd.Parameters.Add(new OracleParameter("LastName",
OracleType.VarChar,
7,
ParameterDirection.Input,
false,
0,
0,
"",
DataRowVersion.Default,
"AVALUE%"));

OracleDataAdapter a = new OracleDataAdapter(cmd);

DataSet d = new DataSet();

// Timing on this call shows an execution time of over 25 seconds
a.Fill(d);

}

Thanks in advance for any advice.

Biff Gaut
Gaithersburg, MD

 

[Kevin Yu [MSFT]]

Hi Biff,

This is a typical performance issue. Please try to use the following steps
for trouble shooting:

1. Use a trace tool provided by Oracle to see the execution time of the
command.

2. Compare the execution time of the server with the total execution time
to see whether it is a server problem or a network problem.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Kevin Yu [MSFT]]

Hi Biff,

You can also use the trace tool to see what statement is actually executing
on the server.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Biff Gaut]

Some interesting further information. First, the same behavioral
patterns are encountered when using the generic OleDb provider, which
indicates that the issue is not specific to the Microsoft Oracle
provider. Second, we ran the trace that Kevin requested and saw the
following-

-Without the parameter (the fast way), the query used the E.COL1 index

-With the parameter(the slow way), the query did not use the E.COL1
index. The entire duration of the query appears to be spent executing
on the server and the SQl statement seen in the trace is the SQL
statement with the parameter placeholder.

So the question now morphs into, "Why doesn't my query use the index
when I am passing in a parameter?"

Thanks,
Biff Gaut
Gaithersburg, MD

 

[Kevin Yu [MSFT]]

Hi Biff,

It seems that there's something wrong with the Oracle server. However, I'm
not very familiar with that. Let wait to see if any community memeber has a
solution to this. Or you can contact Oracle support for help.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."