Re: Is it me or is Oracle just slow?

        Unless properly tuned Oracle will run - oops make that crawl - especially on a single disk system. Oracle is intended for use with LARGE databases with heavy transaction volumes. If you have a small, low volume database SQL Server will probably always outperform Oracle.  Although it isn't a fair comparison ;-) I like to equate it to a comparison between a pick up truck and a semi-trailer. The pick up is faster and more economical, but it can't carry the load. On second thought, maybe it isn't an unfair comparison.

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 5/31/00, 8:43:59 PM, "Newbie" <nospam_at_please.com> wrote regarding Is

it me or is Oracle just slow?:

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

> I am accessing the SQL Server 7.0 database via Microsofts ODBC for SQL
 

> Server and Oracle 8i via Oracles ODBC driver.
 

> The time for an insert into SQL Server is approximately 1/20th the
 time for
> 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
> @StampUser varchar(10),
> @Name varchar (50) ,
> @Status bit ,
> @Reason varchar (255)
> As
> Declare @trncnt int,
> @ErrorNumber int,
> @ID int
 

> Select @ErrorNumber = -1
 

> Select @trncnt = @@trancount
 

> If @trncnt = 0
> Begin Transaction t1
> Else
> Save Transaction t1
 

> Insert ExampleObject
> (
> Name,
> Status,
> Reason,
> ConcurrencyID)
> Values
> (
> @Name,
> @Status,
> @Reason,
> 1)
 

> If @@Error <> 0 GoTo ErrorHandler
 

> Select @ID = @@Identity
 

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

> If @@Error <> 0 GoTo ErrorHandler
 

> If @trncnt = 0
> Commit Transaction t1
 

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

> Return (0)
 

> ErrorHandler:
> Rollback Transaction t1
> Select ErrorCondition
> From Common..ErrorMessage
> Where ErrorNumber = @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;

> Any ideas as to the reason for the performance gap... Is the Oracle
> Procedure badly written? Is the problem the Trigger? The ODBC
 driver? Or
> do I need to "tune" the Oracle database? Any input would be
appreciated... Received on Thu Jun 01 2000 - 00:00:00 CDT