Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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 Wed May 31 2000 - 00:00:00 CDT