OS:       RedHat 6.0 with kernel 2.2.5-smp
 Hardware: IBM Netfinity 7000 M10 with 2 Xenon Processors and 256M memory
 SCSI sub system: 4 IBM 9.1G scsi disk on a 7895 scsi card

On Sybase 11.0.3.3
1>t001 1760
2>go
-- ----------- ------- -----
13 seconds for 4224000 bytes


After my update, on Sybase 11.9.2
1>t001 1760
2>go
--- ----------- ------- -----
454 seconds for 4224000 bytes

Why 11.0.3.3 is much faster than 11.9.2?
( Two Sbyase has same config, max engine = 2, total memory = 65536, all of
other are default)


                                Xuzheng 2000-07-20


procedure t001 code:
------------------------------------------------------------------
create procedure t001
  (
  @ReportNum int
  )
as
begin
declare
   @reportNum int,
   @reportID int,
   @beginTime datetime,
   @endTime datetime,
   @reportTime datetime

select
  @reportNum = 0,
  @beginTime = getdate(),
  @reportTime = getdate()
while( @reportNum < @ReportNum )
  begin
  select @reportID = 1
  select @reportTime = dateadd( ss, 2, @reportTime )
  while( @reportID < 31 )
    begin
    insert into report..testReport
    select
      @reportTime,
      'AA',
      'AA',
      @reportTime,

'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAA'
    select @reportID = @reportID + 1
    end
  select @reportNum = @reportNum + 1
  end

select
  @endTime = getdate()

select
  datediff( ss, @beginTime, @endTime ), ' seconds for ', ReportNum*2400, '
bytes'
end
go
-----------------------------------------------------------

xuzheng wrote:
> 
>  OS:       RedHat 6.0 with kernel 2.2.5-smp
>  Hardware: IBM Netfinity 7000 M10 with 2 Xenon Processors and 256M memory
>  SCSI sub system: 4 IBM 9.1G scsi disk on a 7895 scsi card
> 
> On Sybase 11.0.3.3
> 1>t001 1760
> 2>go
> -- ----------- ------- -----
> 13 seconds for 4224000 bytes
> 
> After my update, on Sybase 11.9.2
> 1>t001 1760
> 2>go
> --- ----------- ------- -----
> 454 seconds for 4224000 bytes
> 
> Why 11.0.3.3 is much faster than 11.9.2?
> ( Two Sbyase has same config, max engine = 2, total memory = 65536, all of
> other are default)

Because 11.0.3.3 will by default use buffered filesystem access, which avoids a 
lot of writes during your inserts in the proc. However, if the server
goes down while the proc is running you may see database corruption.

If you use a recent version of 11.0.3.3 (ESD#6) and a recent kernel
(2.2.14, with patches) you can use raw IO and async IO and get throughput
that is as good as buffered file access, without the risk of loosing data.

Unfortunately the raw IO/async IO access has not been added to 11.9.2 yet.

Michael
-- 
Michael Peppler         -||-  Data Migrations Inc.
mpeppler@peppler.org    -||-  http://www.mbay.net/~mpeppler
Int. Sybase User Group  -||-  http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com

You need to look deeper than the machine and the configuration.

Settle on a query that is running poorly, ust this as your test.

Check for the presence of mismatched datatypes. That is, are there any
differences between the declared datatype of a parameter and the column it's
going against? This is just one example. There were changes in how mismatches
are handled.

BTW - think of performance analysis as a set of levels you need to examine. My
bias (based on experience) say to check the query (optimization) level first,
then move up from there.

Hope this helps

Eric Miner
Sybase
ASE Engineering
Optimizer Group

xuzheng wrote:

>  OS:       RedHat 6.0 with kernel 2.2.5-smp
>  Hardware: IBM Netfinity 7000 M10 with 2 Xenon Processors and 256M memory
>  SCSI sub system: 4 IBM 9.1G scsi disk on a 7895 scsi card
>
> On Sybase 11.0.3.3
> 1>t001 1760
> 2>go
> -- ----------- ------- -----
> 13 seconds for 4224000 bytes
>
> After my update, on Sybase 11.9.2
> 1>t001 1760
> 2>go
> --- ----------- ------- -----
> 454 seconds for 4224000 bytes
>
> Why 11.0.3.3 is much faster than 11.9.2?
> ( Two Sbyase has same config, max engine = 2, total memory = 65536, all of
> other are default)
>
>                                 Xuzheng 2000-07-20
>
> procedure t001 code:
> ------------------------------------------------------------------
> create procedure t001
>   (
>   @ReportNum int
>   )
> as
> begin
> declare
>    @reportNum int,
>    @reportID int,
>    @beginTime datetime,
>    @endTime datetime,
>    @reportTime datetime
>
> select
>   @reportNum = 0,
>   @beginTime = getdate(),
>   @reportTime = getdate()
> while( @reportNum < @ReportNum )
>   begin
>   select @reportID = 1
>   select @reportTime = dateadd( ss, 2, @reportTime )
>   while( @reportID < 31 )
>     begin
>     insert into report..testReport
>     select
>       @reportTime,
>       'AA',
>       'AA',
>       @reportTime,
>
> 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> AAAAA'
>     select @reportID = @reportID + 1
>     end
>   select @reportNum = @reportNum + 1
>   end
>
> select
>   @endTime = getdate()
>
> select
>   datediff( ss, @beginTime, @endTime ), ' seconds for ', ReportNum*2400, '
> bytes'
> end
> go
> -----------------------------------------------------------

This is yes/no answer depends on the system configuration.

11.0.3.3 is faster if you do not use parallel query and row level locking on
1192.
And just 1 cpu assigns to sybase.
Upgrade 11033 to 1192 or 12 and using 1 cpu for Sybase, most SPs (90%) will
run slower even add more system memory.

1 big plus for 1192 and 12 is high availability and parallel feature for
24x7 shops which is worth the upgrade.

Rey Wang
www.dbmethods.com

"Eric Miner" <eminer@sybase.com> wrote in message
news:399C0C87.AA597982@sybase.com...
> You need to look deeper than the machine and the configuration.
>
> Settle on a query that is running poorly, ust this as your test.
>
> Check for the presence of mismatched datatypes. That is, are there any
> differences between the declared datatype of a parameter and the column
it's
> going against? This is just one example. There were changes in how
mismatches
> are handled.
>
> BTW - think of performance analysis as a set of levels you need to
examine. My
> bias (based on experience) say to check the query (optimization) level
first,
> then move up from there.
>
> Hope this helps
>
> Eric Miner
> Sybase
> ASE Engineering
> Optimizer Group
>
> xuzheng wrote:
>
> >  OS:       RedHat 6.0 with kernel 2.2.5-smp
> >  Hardware: IBM Netfinity 7000 M10 with 2 Xenon Processors and 256M
memory
> >  SCSI sub system: 4 IBM 9.1G scsi disk on a 7895 scsi card
> >
> > On Sybase 11.0.3.3
> > 1>t001 1760
> > 2>go
> > -- ----------- ------- -----
> > 13 seconds for 4224000 bytes
> >
> > After my update, on Sybase 11.9.2
> > 1>t001 1760
> > 2>go
> > --- ----------- ------- -----
> > 454 seconds for 4224000 bytes
> >
> > Why 11.0.3.3 is much faster than 11.9.2?
> > ( Two Sbyase has same config, max engine = 2, total memory = 65536, all
of
> > other are default)
> >
> >                                 Xuzheng 2000-07-20
> >
> > procedure t001 code:
> > ------------------------------------------------------------------
> > create procedure t001
> >   (
> >   @ReportNum int
> >   )
> > as
> > begin
> > declare
> >    @reportNum int,
> >    @reportID int,
> >    @beginTime datetime,
> >    @endTime datetime,
> >    @reportTime datetime
> >
> > select
> >   @reportNum = 0,
> >   @beginTime = getdate(),
> >   @reportTime = getdate()
> > while( @reportNum < @ReportNum )
> >   begin
> >   select @reportID = 1
> >   select @reportTime = dateadd( ss, 2, @reportTime )
> >   while( @reportID < 31 )
> >     begin
> >     insert into report..testReport
> >     select
> >       @reportTime,
> >       'AA',
> >       'AA',
> >       @reportTime,
> >
> >
'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> > AAAAA'
> >     select @reportID = @reportID + 1
> >     end
> >   select @reportNum = @reportNum + 1
> >   end
> >
> > select
> >   @endTime = getdate()
> >
> > select
> >   datediff( ss, @beginTime, @endTime ), ' seconds for ', ReportNum*2400,
'
> > bytes'
> > end
> > go
> > -----------------------------------------------------------
>

In article <1LimeTIVAHA.202@forums.sybase.com>, "Philip Chan"
<philipc@i-cable.com> wrote:

> Hi Michael,
> 
> Do you know if Sybase will add raw device and async. I/O feature to
> their
> 11.9.2.x ASE?

No, I don't know what their plans are. My guess is that most of their
efforts right now are geared towards getting 12.5 to GA status, and I
also suspect that they may be waiting forthe 2.4.x kernels to make it to
GA status too.

But all of that is complete speculation - I have no inside knowledge :-)

Michael
-- 
Michael Peppler - Data Migrations Inc. - mpeppler@peppler.org
http://www.mbay.net/~mpeppler - mpeppler@mbay.net - AIM MPpplr
International Sybase User Group - http://www.isug.com
Sybase on Linux mailing list: ase-linux-list@isug.com

Hi Michael,

Do you know if Sybase will add raw device and async. I/O feature to their
11.9.2.x ASE?

Regards,
Philip



"Michael Peppler" <mpeppler@peppler.org> ?????
news:3999E601.B45C444@peppler.org...
> xuzheng wrote:
> >
> >  OS:       RedHat 6.0 with kernel 2.2.5-smp
> >  Hardware: IBM Netfinity 7000 M10 with 2 Xenon Processors and 256M
memory
> >  SCSI sub system: 4 IBM 9.1G scsi disk on a 7895 scsi card
> >
> > On Sybase 11.0.3.3
> > 1>t001 1760
> > 2>go
> > -- ----------- ------- -----
> > 13 seconds for 4224000 bytes
> >
> > After my update, on Sybase 11.9.2
> > 1>t001 1760
> > 2>go
> > --- ----------- ------- -----
> > 454 seconds for 4224000 bytes
> >
> > Why 11.0.3.3 is much faster than 11.9.2?
> > ( Two Sbyase has same config, max engine = 2, total memory = 65536, all
of
> > other are default)
>
> Because 11.0.3.3 will by default use buffered filesystem access, which
avoids a
> lot of writes during your inserts in the proc. However, if the server
> goes down while the proc is running you may see database corruption.
>
> If you use a recent version of 11.0.3.3 (ESD#6) and a recent kernel
> (2.2.14, with patches) you can use raw IO and async IO and get throughput
> that is as good as buffered file access, without the risk of loosing data.
>
> Unfortunately the raw IO/async IO access has not been added to 11.9.2 yet.
>
> Michael
> --
> Michael Peppler         -||-  Data Migrations Inc.
> mpeppler@peppler.org    -||-  http://www.mbay.net/~mpeppler
> Int. Sybase User Group  -||-  http://www.isug.com
> Sybase on Linux mailing list: ase-linux-list@isug.com