Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

SybaseVsOracle

7 posts in General Discussion Last posting was on 2012-01-20 18:29:54.0Z
jobless Posted on 2012-01-19 21:08:42.0Z
Sender: 6f20.4f18211b.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: SybaseVsOracle
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f18865a.51d.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 19 Jan 2012 13:08:42 -0800
X-Trace: forums-1-dub 1327007322 172.20.134.41 (19 Jan 2012 13:08:42 -0800)
X-Original-Trace: 19 Jan 2012 13:08:42 -0800, 172.20.134.41
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30847
Article PK: 73737

I installed ASE 15.7 and Oracle 11.2.0.2.0 on my XP PC and
tested a 300K loop inserts: (default config for both rdbms)

- ASE took 214 seconds Avg to insert 300K rows.
- Oracle took 47 seconds Avg to insert 300K rows (tested
both in dedicated and shared server mode);
Oracle is almost 4.5 times faster than ASE !!! <-- just my
test

Is this for real, i repeated test almost 10 times by
truncating table and still same results.

------------
Sybase Code:
------------
1> create table TBL1 ( c1 int, c2 varchar(20), c3 datetime)
2> go
1> create index iTBL1 on TBL1(c1)
2> go

declare @i int
select @i = 1
while (@i <= 300000 )
begin
insert into TBL1 select @i, convert(varchar(40), newid()),
getdate()
select @i=@i+1

if (@i000 = 0 )
select convert(varchar(20),@i) +" rows inserted
"+convert(varchar(30), getdate(), 109)
end

------------
Oracle Code:
------------
SQL> create table TBL1 ( c1 int, c2 varchar2(20), c3 date);
SQL> create index iTBL1 on TBL1(c1);
SQL> commit;

declare
i int;
cnt int;
begin
i:=1;
while i <= 300000
loop
insert into TBL1 select i, to_char(dbms_random.random),
sysdate from dual;
i :=i+1;
if mod(i,10000) = 0 then
dbms_output.put_line(to_char(i)|| ' ' ||
to_char(sysdate, 'YYYY-MON-DD HH:MI:SSPM'));
end if;
end loop;
end ;
/
commit;


:((


"Mark A. Parsons" <iron_horse Posted on 2012-01-20 00:10:36.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: SybaseVsOracle
References: <4f18865a.51d.1681692777@sybase.com>
In-Reply-To: <4f18865a.51d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f18b0fc$1@forums-1-dub>
Date: 19 Jan 2012 16:10:36 -0800
X-Trace: forums-1-dub 1327018236 10.22.241.152 (19 Jan 2012 16:10:36 -0800)
X-Original-Trace: 19 Jan 2012 16:10:36 -0800, vip152.sybase.com
Lines: 76
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30849
Article PK: 73739

Looks like Oracle is running in chained transaction mode (ie, at the end of your inserts you issue a 'commit').

I'm *assuming* Oracle functions similarly to ASE with an open transaction, ie, log writes are deferred (to some extent)
until the end of the transaction.

Since you don't appear to be running the ASE test inside a transaction you'll generally have to wait for each insert to
write the log to disk.

To make the test a bit more fair I'd suggest you run your ASE test a) in chained transaction mode (w/ a single 'commit'
at the end or b) wrap your entire set of SQL in a 'begin/commit tran' pair.

You may also get different results if you run the ASE inserts in tempdb since (I believe) the log writes are deferred by
default for tempdb.

On 01/19/2012 14:08, jobless wrote:
> I installed ASE 15.7 and Oracle 11.2.0.2.0 on my XP PC and
> tested a 300K loop inserts: (default config for both rdbms)
>
> - ASE took 214 seconds Avg to insert 300K rows.
> - Oracle took 47 seconds Avg to insert 300K rows (tested
> both in dedicated and shared server mode);
> Oracle is almost 4.5 times faster than ASE !!!<-- just my
> test
>
> Is this for real, i repeated test almost 10 times by
> truncating table and still same results.
>
> ------------
> Sybase Code:
> ------------
> 1> create table TBL1 ( c1 int, c2 varchar(20), c3 datetime)
> 2> go
> 1> create index iTBL1 on TBL1(c1)
> 2> go
>
> declare @i int
> select @i = 1
> while (@i<= 300000 )
> begin
> insert into TBL1 select @i, convert(varchar(40), newid()),
> getdate()
> select @i=@i+1
>
> if (@i000 = 0 )
> select convert(varchar(20),@i) +" rows inserted
> "+convert(varchar(30), getdate(), 109)
> end
>
> ------------
> Oracle Code:
> ------------
> SQL> create table TBL1 ( c1 int, c2 varchar2(20), c3 date);
> SQL> create index iTBL1 on TBL1(c1);
> SQL> commit;
>
> declare
> i int;
> cnt int;
> begin
> i:=1;
> while i<= 300000
> loop
> insert into TBL1 select i, to_char(dbms_random.random),
> sysdate from dual;
> i :=i+1;
> if mod(i,10000) = 0 then
> dbms_output.put_line(to_char(i)|| ' ' ||
> to_char(sysdate, 'YYYY-MON-DD HH:MI:SSPM'));
> end if;
> end loop;
> end ;
> /
> commit;
>
>
> :((


"Mark A. Parsons" <iron_horse Posted on 2012-01-20 00:53:34.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: SybaseVsOracle
References: <4f18865a.51d.1681692777@sybase.com> <4f18b0fc$1@forums-1-dub>
In-Reply-To: <4f18b0fc$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f18bb0e$1@forums-1-dub>
Date: 19 Jan 2012 16:53:34 -0800
X-Trace: forums-1-dub 1327020814 10.22.241.152 (19 Jan 2012 16:53:34 -0800)
X-Original-Trace: 19 Jan 2012 16:53:34 -0800, vip152.sybase.com
Lines: 85
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30850
Article PK: 73738

I ran the same T-SQL in 15.0.3 ASE on XP:

- code without txn wrapper: 90 secs
- code with txn wrapper: 47 secs
- bcp in w/ 1 big txn: 16 secs
- bcp in w/ 10K batch size: 16 secs

On 01/19/2012 17:10, Mark A. Parsons wrote:
> Looks like Oracle is running in chained transaction mode (ie, at the end of your inserts you issue a 'commit').
>
> I'm *assuming* Oracle functions similarly to ASE with an open transaction, ie, log writes are deferred (to some extent)
> until the end of the transaction.
>
> Since you don't appear to be running the ASE test inside a transaction you'll generally have to wait for each insert to
> write the log to disk.
>
> To make the test a bit more fair I'd suggest you run your ASE test a) in chained transaction mode (w/ a single 'commit'
> at the end or b) wrap your entire set of SQL in a 'begin/commit tran' pair.
>
> You may also get different results if you run the ASE inserts in tempdb since (I believe) the log writes are deferred by
> default for tempdb.
>
> On 01/19/2012 14:08, jobless wrote:
>> I installed ASE 15.7 and Oracle 11.2.0.2.0 on my XP PC and
>> tested a 300K loop inserts: (default config for both rdbms)
>>
>> - ASE took 214 seconds Avg to insert 300K rows.
>> - Oracle took 47 seconds Avg to insert 300K rows (tested
>> both in dedicated and shared server mode);
>> Oracle is almost 4.5 times faster than ASE !!!<-- just my
>> test
>>
>> Is this for real, i repeated test almost 10 times by
>> truncating table and still same results.
>>
>> ------------
>> Sybase Code:
>> ------------
>> 1> create table TBL1 ( c1 int, c2 varchar(20), c3 datetime)
>> 2> go
>> 1> create index iTBL1 on TBL1(c1)
>> 2> go
>>
>> declare @i int
>> select @i = 1
>> while (@i<= 300000 )
>> begin
>> insert into TBL1 select @i, convert(varchar(40), newid()),
>> getdate()
>> select @i=@i+1
>>
>> if (@i000 = 0 )
>> select convert(varchar(20),@i) +" rows inserted
>> "+convert(varchar(30), getdate(), 109)
>> end
>>
>> ------------
>> Oracle Code:
>> ------------
>> SQL> create table TBL1 ( c1 int, c2 varchar2(20), c3 date);
>> SQL> create index iTBL1 on TBL1(c1);
>> SQL> commit;
>>
>> declare
>> i int;
>> cnt int;
>> begin
>> i:=1;
>> while i<= 300000
>> loop
>> insert into TBL1 select i, to_char(dbms_random.random),
>> sysdate from dual;
>> i :=i+1;
>> if mod(i,10000) = 0 then
>> dbms_output.put_line(to_char(i)|| ' ' ||
>> to_char(sysdate, 'YYYY-MON-DD HH:MI:SSPM'));
>> end if;
>> end loop;
>> end ;
>> /
>> commit;
>>
>>
>> :((


jobless Posted on 2012-01-20 17:29:48.0Z
Sender: 3a0c.4f199f3d.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: SybaseVsOracle
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f19a48c.3adb.1681692777@sybase.com>
References: <4f18bb0e$1@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 20 Jan 2012 09:29:48 -0800
X-Trace: forums-1-dub 1327080588 172.20.134.41 (20 Jan 2012 09:29:48 -0800)
X-Original-Trace: 20 Jan 2012 09:29:48 -0800, 172.20.134.41
Lines: 104
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30851
Article PK: 73740

Thanks Mark for testing. It is the unchained mode like you
pointed out earlier, which adds the delay.

- Sybase ASE with chained mode (set chained on) finish the
inserts in 28sec - 33sec (which is better than what Oracle
is giving me).

- ** But my unchined always goes above 200sec for 300k. I
had to bump up my logsegemnt from 25Mb to 100Mb for chained
mode(it used amost 85Mb logsegment for 300k -25M data), but
cant complain as ASE 15.7 has options to shrink
logsegment(hv to test if this lets me remove the added log
device!). Oracle used alomost 200+ mb undo segment.

> I ran the same T-SQL in 15.0.3 ASE on XP:
>
> - code without txn wrapper: 90 secs
> - code with txn wrapper: 47 secs
> - bcp in w/ 1 big txn: 16 secs
> - bcp in w/ 10K batch size: 16 secs
>
>
> On 01/19/2012 17:10, Mark A. Parsons wrote:
> > Looks like Oracle is running in chained transaction mode
> (ie, at the end of your inserts you issue a 'commit'). >
> > I'm *assuming* Oracle functions similarly to ASE with an
> open transaction, ie, log writes are deferred (to some
> > extent) until the end of the transaction.
> >
> > Since you don't appear to be running the ASE test inside
> a transaction you'll generally have to wait for each
> > insert to write the log to disk.
> >
> > To make the test a bit more fair I'd suggest you run
> your ASE test a) in chained transaction mode (w/ a single
> > 'commit' at the end or b) wrap your entire set of SQL in
> a 'begin/commit tran' pair. >
> > You may also get different results if you run the ASE
> inserts in tempdb since (I believe) the log writes are
> > deferred by default for tempdb.
> >
> > On 01/19/2012 14:08, jobless wrote:
> >> I installed ASE 15.7 and Oracle 11.2.0.2.0 on my XP PC
> and >> tested a 300K loop inserts: (default config for
> both rdbms) >>
> >> - ASE took 214 seconds Avg to insert 300K rows.
> >> - Oracle took 47 seconds Avg to insert 300K rows
> (tested >> both in dedicated and shared server mode);
> >> Oracle is almost 4.5 times faster than ASE !!!<-- just
> my >> test
> >>
> >> Is this for real, i repeated test almost 10 times by
> >> truncating table and still same results.
> >>
> >> ------------
> >> Sybase Code:
> >> ------------
> >> 1> create table TBL1 ( c1 int, c2 varchar(20), c3
> datetime) >> 2> go
> >> 1> create index iTBL1 on TBL1(c1)
> >> 2> go
> >>
> >> declare @i int
> >> select @i = 1
> >> while (@i<= 300000 )
> >> begin
> >> insert into TBL1 select @i, convert(varchar(40),
> newid()), >> getdate()
> >> select @i=@i+1
> >>
> >> if (@i000 = 0 )
> >> select convert(varchar(20),@i) +" rows inserted
> >> "+convert(varchar(30), getdate(), 109)
> >> end
> >>
> >> ------------
> >> Oracle Code:
> >> ------------
> >> SQL> create table TBL1 ( c1 int, c2 varchar2(20), c3
> date); >> SQL> create index iTBL1 on TBL1(c1);
> >> SQL> commit;
> >>
> >> declare
> >> i int;
> >> cnt int;
> >> begin
> >> i:=1;
> >> while i<= 300000
> >> loop
> >> insert into TBL1 select i, to_char(dbms_random.random),
> >> sysdate from dual;
> >> i :=i+1;
> >> if mod(i,10000) = 0 then
> >> dbms_output.put_line(to_char(i)|| ' ' ||
> >> to_char(sysdate, 'YYYY-MON-DD HH:MI:SSPM'));
> >> end if;
> >> end loop;
> >> end ;
> >> /
> >> commit;
> >>
> >>
> >> :((


Bret Halford Posted on 2012-01-20 18:20:47.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:9.0) Gecko/20111222 Thunderbird/9.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: SybaseVsOracle
References: <4f18bb0e$1@forums-1-dub> <4f19a48c.3adb.1681692777@sybase.com>
In-Reply-To: <4f19a48c.3adb.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120120-0, 01/20/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f19b07f$1@forums-1-dub>
Date: 20 Jan 2012 10:20:47 -0800
X-Trace: forums-1-dub 1327083647 10.22.241.152 (20 Jan 2012 10:20:47 -0800)
X-Original-Trace: 20 Jan 2012 10:20:47 -0800, vip152.sybase.com
Lines: 21
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30852
Article PK: 73745

I suspect you can squeeze a bit more performance out by adding
"set nocount on" to the top of the script.

-bret

On 1/20/2012 10:29 AM, jobless wrote:
> Thanks Mark for testing. It is the unchained mode like you
> pointed out earlier, which adds the delay.
>
> - Sybase ASE with chained mode (set chained on) finish the
> inserts in 28sec - 33sec (which is better than what Oracle
> is giving me).
>
> - ** But my unchined always goes above 200sec for 300k. I
> had to bump up my logsegemnt from 25Mb to 100Mb for chained
> mode(it used amost 85Mb logsegment for 300k -25M data), but
> cant complain as ASE 15.7 has options to shrink
> logsegment(hv to test if this lets me remove the added log
> device!). Oracle used alomost 200+ mb undo segment.


jobless Posted on 2012-01-19 22:20:40.0Z
Sender: 81c.4f1895d2.1804289383@sybase.com
From: jobless
Newsgroups: sybase.public.ase.general
Subject: Re: SybaseVsOracle
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f189738.847.1681692777@sybase.com>
References: <4f18865a.51d.1681692777@sybase.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: quoted-printable
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 19 Jan 2012 14:20:40 -0800
X-Trace: forums-1-dub 1327011640 172.20.134.41 (19 Jan 2012 14:20:40 -0800)
X-Original-Trace: 19 Jan 2012 14:20:40 -0800, 172.20.134.41
Lines: 111
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30848
Article PK: 73742


> I installed ASE 15.7 and Oracle 11.2.0.2.0 on my XP PC and
> tested a 300K loop inserts: (default config for both
> rdbms)
>
> - ASE took 214 seconds Avg to insert 300K rows.
> - Oracle took 47 seconds Avg to insert 300K rows (tested
> both in dedicated and shared server mode);
> Oracle is almost 4.5 times faster than ASE !!! <-- just my
> test
>
> Is this for real, i repeated test almost 10 times by
> truncating table and still same results.
>
> ------------
> Sybase Code:
> ------------
> 1> create table TBL1 ( c1 int, c2 varchar(20), c3
> datetime) 2> go
> 1> create index iTBL1 on TBL1(c1)
> 2> go
>
> declare @i int
> select @i = 1
> while (@i <= 300000 )
> begin
> insert into TBL1 select @i, convert(varchar(40),
> newid()), getdate()
> select @i=@i+1
>
> if (@i000 = 0 )
> select convert(varchar(20),@i) +" rows inserted
> "+convert(varchar(30), getdate(), 109)
> end
>
> ------------
> Oracle Code:
> ------------
> SQL> create table TBL1 ( c1 int, c2 varchar2(20), c3 date)
> ; SQL> create index iTBL1 on TBL1(c1);
> SQL> commit;
>
> declare
> i int;
> cnt int;
> begin
> i:=1;
> while i <= 300000
> loop
> insert into TBL1 select i, to_char(dbms_random.random)
> , sysdate from dual;
> i :=i+1;
> if mod(i,10000) = 0 then
> dbms_output.put_line(to_char(i)|| ' ' ||
> to_char(sysdate, 'YYYY-MON-DD HH:MI:SSPM'));
> end if;
> end loop;
> end ;
> /
> commit;
>
>
> :((

I looked at the devices, the directio and async was on for
ASE by default. I enabled the same on oracle
'filesystemio_options' still inserts was around 40sec for
300k. Though the ASE pagesize is 4K and Oracle block size is
8K.

SELECT:
Though the select with index access is WAY faster in ASE
then in Oracle. ASE took 14ms-50ms to retrieve 1 row when no
data in cache. Oracle when no data in SGA it is taking
somewhere around 350ms to 800ms (everytime i had rebooted
servers to flush cache/sga).

ASE Code:
---------
declare @st datetime, @ed datetime
declare @dummy int

select @st = getdate()
select @dummy=c1 from TBL1 where c1 = 55587
select @ed = getdate()

select 'Started: ' + convert(varchar(30),@st, 109)
select 'Completed: ' + convert(varchar(30),@ed, 109)


Oracle Code:
------------
set serveroutput on
declare
st timestamp;
ed timestamp;
dummy int;
begin
select localtimestamp(3) into st from dual;
select c1 into dummy from TBL1 where c1 = 55587;
select localtimestamp(3) into ed from dual;

dbms_output.put_line('Started: ' || to_char(st,
'YYYY-MON-DD HH:MI:SS.FF'));
dbms_output.put_line('Completed: ' || to_char(ed,
'YYYY-MON-DD HH:MI:SS.FF'));
end ;

Not sure if this could be the way data is stored in
ASE/Oracle or the optimizer is better (since select was
faster);