| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Is it me or is Oracle just slow?
Try upgrading to VB6 and get all the new access options
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.
Richard J Woodland <richwoodland_at_interfacefamily.com> wrote in message
news:393922b9$0$73584_at_news.erinet.com...
> 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 Mon Jun 05 2000 - 00:00:00 CDT
 |
 |