Is it me or is Oracle just slow?

Hi,
[Quoted]  I have Oracle 8i and SQL Server 7.0 installed on identical hardware (PIII [Quoted] 733 with 256M memory and a single disk). I have not tuned either database [Quoted] (both are just simple installs.)

I am accessing the SQL Server 7.0 database via Microsofts ODBC for SQL [Quoted] Server and Oracle 8i via Oracles ODBC driver.

The time for an insert into SQL Server is approximately 1/20th the time for [Quoted] an insert into Oracle 8i.

A sample Object, called Example would be set up in SQL Server via the command:

CREATE TABLE dbo.ExampleObject (

                   ID int IDENTITY (1, 1) NOT NULL ,
                   Name varchar(50) NOT NULL ,
                   Status bit NOT NULL,
                   Reason varchar(255) NOT NULL ,
                   ConcurrencyID int NOT NULL
                   )

With an index on the ID column created by the command:

Create Unique Clustered Index IX_ExampleObject ON dbo.ExampleObject(ID)

The audit table for this object is created by the command:

CREATE TABLE dbo.ExampleObjectAudit (

                   ID int IDENTITY (1, 1) NOT NULL ,
                   Name varchar(50) NOT NULL ,
                   Status bit NOT NULL,
                   Reason varchar(255) NOT NULL ,
                   StampID int NOT NULL ,
                   StampAction char (1) NOT NULL ,
                   StampDateTime datetime NOT NULL ,
                   StampUser char (15) NOT NULL)

With an index on its ID column created by the command:

Create Unique Clustered Index IX_ExampleObjectAudit ON dbo.ExampleObjectAudit(ID)

The table in Oracle 8i is created by the command:

CREATE TABLE ExampleObject (
 ID integer NOT NULL,
 Name varchar2(50),
 Status integer NOT NULL,
 Reason varchar2(255),
 PRIMARY KEY(ID)) The index for the ID column should automatically be created because of the Primary Key constraint.

The table has an associated sequence for assigning ID numbers created by the command:

Create Sequence SEQExampleObject start with 1

The ID's are assigned by a trigger created by the command:

Create Trigger TRGExampleObject
 before insert on ExampleObject
 for each row
 begin
  if (:new.ID = 0) then
   select SEQExampleObject.NEXTVAL into :new.ID from DUAL;   end if;
 end;

Similarly, the Audit table in Oracle is created by the commands:

CREATE TABLE ExampleObjectAudit (

                    ID integer NOT NULL ,
                    Name varchar2(50) ,
                    Status integer NOT NULL ,
                    Reason varchar2(255) ,
                    StampID integer NOT NULL ,
                    StampAction char(1) NOT NULL ,
                    StampDateTime date NOT NULL ,
                    StampUser char(15) NOT NULL ,
                    PRIMARY KEY(ID))

Create Sequence SEQExampleObjectAudit start with 1

Create Trigger TRGExampleObjectAudit
 before insert on ExampleObjectAudit
 for each row
 begin
  if (:new.ID = 0) then
   select SEQExampleObjectAudit.NEXTVAL into :new.ID from DUAL;   end if;
 end;

The stored procedure called in SQL Server reads as:

Create Procedure spExampleObjectInsert

_at_StampUser varchar(10),
_at_Name varchar (50) ,
_at_Status bit ,
_at_Reason varchar (255)

    As

        Declare _at_trncnt int,
                _at_ErrorNumber int,
                _at_ID int

        Select _at_ErrorNumber = -1

        Select _at_trncnt = @@trancount

        If _at_trncnt = 0
           Begin Transaction t1
        Else
           Save Transaction t1

        Insert ExampleObject
               (
               Name,
               Status,
               Reason,
               ConcurrencyID)
        Values
               (
               _at_Name,
               _at_Status,
               _at_Reason,
               1)

        If _at__at_Error <> 0 GoTo ErrorHandler

        Select _at_ID = @@Identity

        Insert ExampleObjectAudit
              (StampUser,
               StampDateTime,
               StampAction,
               StampID,
               Name,
               Status,
               Reason)
        Select _at_StampUser,
               GetDate() ,
               'I',
               ID,
               Name,
               Status,
               Reason
        From ExampleObject
        Where   ID = _at_ID

        If _at__at_Error <> 0 GoTo ErrorHandler

        If _at_trncnt = 0
          Commit Transaction t1

        Select
               ID,
               Name,
               Status,
               Reason,
               ConcurrencyID As ConcurrencyID1
        From ExampleObject
        Where   ID = _at_ID

        Return (0)

        ErrorHandler:
           Rollback Transaction t1
           Select   ErrorCondition
           From     Common..ErrorMessage
           Where    ErrorNumber = _at_ErrorNumber
           Return  (100)

The Package and procedure in Oracle read:

Create Package PKGExampleObject AS

        Type RT1 is RECORD

             ID             ExampleObject.ID%type,
             Name           ExampleObject.Name%type,
             Status         ExampleObject.Status%type,
             Reason         ExampleObject.Reason%type,"
             ConcurrencyID1 ExampleObject.ConcurrencyID%type );
        Type RCT1 is REF CURSOR RETURN RT1;
        End; "

Create Procedure
xampleObjectInsert(

             varStampUser IN ExampleObjectAudit.StampUser%type, 
             varName IN ExampleObject.Name%type, 
             varStatus IN ExampleObject.Status%type, 
             varReason IN ExampleObject.Reason%type, 
             RC1 IN OUT PKGExampleObject.RCT1) 
        As 
            varErrorNumber ErrorMessage.ErrorNumber%type; 
            varErrorCondition ErrorMessage.ErrorCondition%type; 
            varID ExampleObject.ID%type; 
        Begin  
            varErrorNumber := -1; 

            Insert into ExampleObject 
               (ID, 
               Name, 
               Status, 
               Reason,
               ConcurrencyID) 
           V
alues
               (0,
               varName,
               varStatus,
               varReason,
               1);

           Select SEQExampleObject.CURRVAL into varID From DUAL;

           Insert into ExampleObjectAudit
              (ID,
               StampUser,
               StampDateTime,
               StampAction,
               StampID,
               Name,
               Status,
               Reason)
           Select 0,
               varStampUser,
               SYSDATE ,
               'I',
               ExampleObject.ID,
               ExampleObject.Name,
               ExampleObject.Status,
               ExampleObject.Reason
           From ExampleObject
           Where   ExampleObject.ID = varID ;

           Commit;

           Open RC1 For

           Select
               ID,
               Name,
               Status,
               Reason,
               ConcurrencyID ""ConcurrencyID1""
           From ExampleObject
           Where   ID = varID;

        Exception
            When Others then
                 ROLLBACK;
                 Select ErrorCondition into varErrorCondition From
ErrorMessage
                 Where ErrorNumber = varErrorNumber;
                 RAISE_APPLICATION_ERROR(-20000,varErrorCondition);
        End;

[Quoted] Any ideas as to the reason for the performance gap... Is the Oracle [Quoted] Procedure badly written? Is the problem the Trigger? The ODBC driver? Or [Quoted] do I need to "tune" the Oracle database? Any input would be appreciated... Received on Wed May 31 2000 - 00:00:00 CEST