Oracle Procedure runs slow in ASP but fast in TOAD

Question

I am having this issue with the procedure below. It's pretty fast when I run it in TOAD for Oracle. But when it's being called from my asp application, it's really taking a long time to return the data.

Any help is appreciated.

PROCEDURE Getdruminfo --get list of drums details
                     (
  Pvessel_id               Ccmsdba.Barcode.Vessel_id%TYPE,
  Pbarcode_status_id       CCMSDBA.BARCODE.BARCODE_STATUS_ID%TYPE,
  Pagingfrom               INTEGER,
  Pagingto                 INTEGER,
  Pdrum_type               VARCHAR2,
  Plocation_id             CCMSDBA.LOCATION.LOCATION_ID%TYPE,
  Pgroup_by                VARCHAR2,
  Sa_return            OUT Ref_cv_type
)
IS
BEGIN
  IF Pgroup_by IS NOT NULL
  THEN
     IF Pgroup_by = 'BARCODE_STATUS_ID'
     THEN
        BEGIN
           OPEN Sa_return FOR
                SELECT COUNT (*) AS COUNT,
                       Bdd.Aging_cat_min_days AS Aging,
                       Bdd.Status AS Category,
                       Bdd.Barcode_status_id AS Id
                  FROM Ccmsdba.Vw_barcode_drilldown_drum Bdd
                 WHERE Bdd.Location_id =
                          NVL (Plocation_id, Bdd.Location_id)
                       AND Bdd.Aging <= NVL (Pagingto, Bdd.Aging)
                       AND Bdd.Aging >= NVL (Pagingfrom, Bdd.Aging)
                       AND Bdd.Drum_type = NVL (Pdrum_type, Bdd.Drum_type)
                       AND Bdd.Barcode_status_id =
                              NVL (Pbarcode_status_id,
                                   Bdd.Barcode_status_id)
                       AND Bdd.Vessel_id = NVL (Pvessel_id, Bdd.Vessel_id)
              GROUP BY Bdd.Status,
                       Bdd.Aging_cat_min_days,
                       Bdd.Barcode_status_id
              ORDER BY Bdd.Aging_cat_min_days;
        END;

Answer 1

I know this sounds like an oversimplification, but you need to switch to a non-depricated Oracle driver. System.Data.OracleClient was last packaged with .NET 2.0, and even Microsoft says to use ODP.net. There is also managed ODP.net and devArt dotConnect with don't rely on a local Oracle client to work.

You're probably thinking this won't account for the delay, and I confess I haven't set up and tested your procedure, but I can share this experience with you.

Circa 2008 or so, we had a simple query, it ran fine with both System.Data.OracleClient and ODP.net. However, when we added a parameter (bind variable) to the query, the ODP.net query ran normally -- in the blink of an eye, but the Microsoft version had an inexplicable 17-second delay on the "execute" (ExecuteDataReader) phase. When switched to a literal, everything ran fine.

Bind variables are encouraged and should actually help performance, but in this case it hurt. It made no sense. We found this to be true in other situations. It wasn't always 17 seconds, but it was always inexplicable.

TRY using ODP.net instead, and if it doesn't work, I'll eat my hat.

You mentioned that you have to use the Microsoft driver... can you explain?

-- EDIT --

This is using a literal:

string sql = "insert into foo values ({0})";
OracleCommand cmd = new OracleCommand(String.Empty, connection);
foreach (string value in values)
{
    cmd.CommandText = string.Format(sql, value);
    cmd.ExecuteNonQuery();
}

You are passing the literal value in a query, which in turn becomes a new query to Oracle every time -- it has to parse and compile it before it executes.

This is using a bind variable:

OracleCommand cmd = new OracleCommand("insert into foo values (:BAR)",
    connection);
cmd.Parameters.Add(new OracleParameter("BAR", DbType.String));

foreach (string value in values)
{
    cmd.Parameters[0].Value = value;
    cmd.ExecuteNonQuery();
}

:BAR is the bind variable.

The statement is parsed once, compiled once and executed multiple times with different values. It's much more efficient and more friendly to the shared pool (and therefore all other users of this database).