Re: Is it me or is Oracle just slow?

Your problem lies in the use of ODBC drivers! Performance is never great, whether using Microsoft-provided drivers or Oracle-provided ones. Trying using native Oracle tools to perform the inserts and selects and see the difference. A pity, I would have love to have used VB5 with my Oracle databases, but with large schemas (many objects), just connecting with ODBC takes forever.

Newbie wrote:

> 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 Sat Jun 03 2000 - 00:00:00 CDT