Re: Is it me or is Oracle just slow?
From: Kevin A Lewis <Kevin_A_Lewis_at_Hotmail.com>
Date: 2000/06/01
Message-ID: <99qZ4.11$YJ1.1970_at_newreader.ukcore.bt.net>
Date: 2000/06/01
Message-ID: <99qZ4.11$YJ1.1970_at_newreader.ukcore.bt.net>
Oracle main advantages are good scalability and control.
Downside control equals effort to manage and tune - cannot sensibly be avoided
Regards
--
Kevin A Lewis (BOCM PAULS LTD) - Animal Feed Manufacturer - Ipswich United
Kingdom)
<Kevin_A_Lewis_at_Hotmail.com>
The views expressed herein by the author of this document
are not necessarily those of BOCM PAULS Ltd.
Newbie <nospam_at_please.com> wrote in message
news:8h4f6n$1s1$1_at_slb0.atl.mindspring.net...
> 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
> _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
>
pleObjectInsert(
> 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 CEST