ASE 11.9.3 performance compared to 11.0.3.3

At our site, we just upgraded from 11.0.3.3 to 11.9.3 - in the process
we changed machines, so:

old configuration:

ASE 11.0.3.3 on a Sparc 10 running Solaris 2.5.1, 1 processor, 100 Mb
allocated to sybase

new configuration:

ASE 11.9.3 on a Sun E250 running Solaris 7, 2 processors, 200 Mb
allocated to sybase.

I'm running in to two performance related problems:

1) High throughput inserts/updates of tables.  We have a data feed that
pumps data into
   the database at a high rate of speed.  We have more memory and faster
disks on the
   new system, but the rate at which the inserts/updates occur is the
same as the
   old configuration.  I've setup a 16k buffer pool, and the set
logiosize on the two
   databases that have the high number of inserts/updates.  I've also
set row level
   locking on the two databases.  After these changes, I still see no
improvement over
   the old configuration.  I've check sp_sysmon and nothing seems to be
wrong, but
   granted I'm not at expert at reading the output of sp_sysmon. 
Anything I should
   specifically be looking for?

2) Slow querying of tables involving sql with variables.  I have the two
following
   queries:

	query 1:

	select min(trade_time), max(trade_time)
	from history..underlying_ticks_fp
	where symbol = "SP" and instrument = "F"
	and exp_year = 100 and exp_month = 12
	and price_type = "T" and price > 0
	and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"

	query 2:

	declare @symbol symbol
	declare @exp_year int
	declare @exp_month int
	declare @start_time datetime
	declare @end_time datetime
	select @symbol = "SP"
	select @exp_year = 100
	select @exp_month = 12
	select @start_time = "7/31/2000 00:00:00"
	select @end_time = "8/1/2000 00:00:00"
	select min(trade_time), max(trade_time)
	from history..underlying_ticks_fp
	where symbol = @symbol and instrument = "F"
	and price_type = "T" and exp_month = @exp_month
	and exp_year = @exp_year and trade_time
	between @start_time and @end_time

   Now, in the new configuration, using set statistics time on, query 1
takes 30ms
   and query 2 takes 33420 ms.  In the old configuration, both query 1
and query 2
   took 6 ms.  The table schemas between the new/old configurations are
the same as
   well as the data in the tables.  I've run update all statistics on
all affected
   tables in the new configuration.  I realize that the optimizer
doesn't know what
   the variables are at compile time in query 2, however, in the old
configuration of
   ASE it didn't seem to have the same sort of performance problem.

Any ideas on what's going on for both of these problems would be greatly
appreciated.
-- 
____________________         __           ____________________
\_____________      \       /_ \         /      _____________/ 
 \_____________      \_______/  \_______/      _____________/
  \_________________________      _________________________/ 
                           /______\
   Han Tunca
   Tradelink L.L.C.
   han@trdlnk.com
0
Han
8/2/2000 3:19:01 PM
sybase.sqlserver.performance 1181 articles. 0 followers. Follow

8 Replies
1279 Views

Similar Articles

[PageSpeed] 59
Get it on Google Play
Get it on Apple App Store

If your transactions are very small, your insert/update speed will be limited
regardless of how large the buffers into the transaction log are.  Is it
possible for you to block multiple inserts and updates into larger
transactions?

When faced with a similar limitation, I found that solid state disk for the
transaction log is the best solution.

Han Tunca wrote:

> At our site, we just upgraded from 11.0.3.3 to 11.9.3 - in the process
> we changed machines, so:
>
> old configuration:
>
> ASE 11.0.3.3 on a Sparc 10 running Solaris 2.5.1, 1 processor, 100 Mb
> allocated to sybase
>
> new configuration:
>
> ASE 11.9.3 on a Sun E250 running Solaris 7, 2 processors, 200 Mb
> allocated to sybase.
>
> I'm running in to two performance related problems:
>
> 1) High throughput inserts/updates of tables.  We have a data feed that
> pumps data into
>    the database at a high rate of speed.  We have more memory and faster
> disks on the
>    new system, but the rate at which the inserts/updates occur is the
> same as the
>    old configuration.  I've setup a 16k buffer pool, and the set
> logiosize on the two
>    databases that have the high number of inserts/updates.  I've also
> set row level
>    locking on the two databases.  After these changes, I still see no
> improvement over
>    the old configuration.  I've check sp_sysmon and nothing seems to be
> wrong, but
>    granted I'm not at expert at reading the output of sp_sysmon.
> Anything I should
>    specifically be looking for?
>
> 2) Slow querying of tables involving sql with variables.  I have the two
> following
>    queries:
>
>         query 1:
>
>         select min(trade_time), max(trade_time)
>         from history..underlying_ticks_fp
>         where symbol = "SP" and instrument = "F"
>         and exp_year = 100 and exp_month = 12
>         and price_type = "T" and price > 0
>         and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
>
>         query 2:
>
>         declare @symbol symbol
>         declare @exp_year int
>         declare @exp_month int
>         declare @start_time datetime
>         declare @end_time datetime
>         select @symbol = "SP"
>         select @exp_year = 100
>         select @exp_month = 12
>         select @start_time = "7/31/2000 00:00:00"
>         select @end_time = "8/1/2000 00:00:00"
>         select min(trade_time), max(trade_time)
>         from history..underlying_ticks_fp
>         where symbol = @symbol and instrument = "F"
>         and price_type = "T" and exp_month = @exp_month
>         and exp_year = @exp_year and trade_time
>         between @start_time and @end_time
>
>    Now, in the new configuration, using set statistics time on, query 1
> takes 30ms
>    and query 2 takes 33420 ms.  In the old configuration, both query 1
> and query 2
>    took 6 ms.  The table schemas between the new/old configurations are
> the same as
>    well as the data in the tables.  I've run update all statistics on
> all affected
>    tables in the new configuration.  I realize that the optimizer
> doesn't know what
>    the variables are at compile time in query 2, however, in the old
> configuration of
>    ASE it didn't seem to have the same sort of performance problem.
>
> Any ideas on what's going on for both of these problems would be greatly
> appreciated.
> --
> ____________________         __           ____________________
> \_____________      \       /_ \         /      _____________/
>  \_____________      \_______/  \_______/      _____________/
>   \_________________________      _________________________/
>                            /______\
>    Han Tunca
>    Tradelink L.L.C.
>    han@trdlnk.com

0
Brian
8/4/2000 4:31:29 PM
Han Tunca wrote:
> 
> At our site, we just upgraded from 11.0.3.3 to 11.9.3 - in the process
> we changed machines, so:
> 
> old configuration:
> 
> ASE 11.0.3.3 on a Sparc 10 running Solaris 2.5.1, 1 processor, 100 Mb
> allocated to sybase
> 
> new configuration:
> 
> ASE 11.9.3 on a Sun E250 running Solaris 7, 2 processors, 200 Mb
> allocated to sybase.

	Are you sure its 11.9.3? Seems a waste to run a 64bit app for
	memory that small.

> I'm running in to two performance related problems:

	What was performance like on the Sparc10? Hardware-wise it's
	dog slow compared to the E250.

> 1) High throughput inserts/updates of tables.  We have a data feed that
> pumps data into the database at a high rate of speed.  We have more memory
> and faster disks on the new system,

	And faster network - full duplex hme compared to half duplex le.

> but the rate at which the inserts/updates occur is the same as the
> old configuration.  I've setup a 16k buffer pool, and the set
> logiosize on the two databases that have the high number of inserts/updates.
> I've also set row level locking on the two databases.  After these changes,
> I still see no improvement over the old configuration.  I've check sp_sysmon
> and nothing seems to be wrong, but granted I'm not at expert at reading the
> output of sp_sysmon.

	I get the impression of some head scratching going on. The options
	you changed had no effect, so they weren't needed to be changed.
	Remember to se them back later.

> Anything I should specifically be looking for?

	Check your version first. Make sure you're on the latest EBF.
	Compare show plans between the two servers.

> 2) Slow querying of tables involving sql with variables.  I have the two
> following queries:
> 
>         query 1:
> 
>         select min(trade_time), max(trade_time)
>         from history..underlying_ticks_fp
>         where symbol = "SP" and instrument = "F"
>         and exp_year = 100 and exp_month = 12
>         and price_type = "T" and price > 0
>         and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"

	Er ... this is a select statement and not a DML statement. Look at
	your schema design. What and how many indexes do you have on this table?

>         query 2:
> 
>         declare @symbol symbol
>         declare @exp_year int
>         declare @exp_month int
>         declare @start_time datetime
>         declare @end_time datetime
>         select @symbol = "SP"
>         select @exp_year = 100
>         select @exp_month = 12
>         select @start_time = "7/31/2000 00:00:00"
>         select @end_time = "8/1/2000 00:00:00"
>         select min(trade_time), max(trade_time)
>         from history..underlying_ticks_fp
>         where symbol = @symbol and instrument = "F"
>         and price_type = "T" and exp_month = @exp_month
>         and exp_year = @exp_year and trade_time
>         between @start_time and @end_time

	Is this done within a stored procedure?

> Now, in the new configuration, using set statistics time on, query 1
> takes 30ms and query 2 takes 33420 ms.  In the old configuration, both
> query 1 and query 2 took 6 ms.  The table schemas between the new/old
> configurations are the same as well as the data in the tables.

	Try writing an sproc and pass the variables in as parameters.

> I've run update all statistics on all affected tables in the new
> configuration.  I realize that the optimizer doesn't know what
> the variables are at compile time in query 2, however, in the old
> configuration of ASE it didn't seem to have the same sort of performance
> problem.
> 
> Any ideas on what's going on for both of these problems would be greatly
> appreciated.

	Eric Miner will probably jump in here once he's back from TechWave.
	Expect to read some comments from him about running "update all stats".

-am
0
Anthony
8/6/2000 2:59:44 AM
Hello Han,

My first suspicion here is mismatched datatypes between the columns and the
variables' declared types. Check this, make sure the declared types are the
same as the columns'. There was a change in 11.5 in how type hierarchies are
processed before going to the optimizer. Let us know the result of this.

There is no change in how unknown values at runtime are handled in the
optimizer. The only thing that changed in this area is that you can now change
the value of the old 'magic numbers' from their default.

 >I've run update all statistics on all affected tables

Anthony knows me too well ;-) There is RARELY any need to run update all stats.
In fact I suggest you don't even consider it until you've done full testing.
The time it takes to run and the maintenance it adds can be overwhelming.

Hope this helps

Eric Miner
ASE Engineering
Optimizer Group

Han Tunca wrote:

> 2) Slow querying of tables involving sql with variables.  I have the two
> following
>    queries:
>
>         query 1:
>
>         select min(trade_time), max(trade_time)
>         from history..underlying_ticks_fp
>         where symbol = "SP" and instrument = "F"
>         and exp_year = 100 and exp_month = 12
>         and price_type = "T" and price > 0
>         and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
>
>         query 2:
>
>         declare @symbol symbol
>         declare @exp_year int
>         declare @exp_month int
>         declare @start_time datetime
>         declare @end_time datetime
>         select @symbol = "SP"
>         select @exp_year = 100
>         select @exp_month = 12
>         select @start_time = "7/31/2000 00:00:00"
>         select @end_time = "8/1/2000 00:00:00"
>         select min(trade_time), max(trade_time)
>         from history..underlying_ticks_fp
>         where symbol = @symbol and instrument = "F"
>         and price_type = "T" and exp_month = @exp_month
>         and exp_year = @exp_year and trade_time
>         between @start_time and @end_time
>
>    Now, in the new configuration, using set statistics time on, query 1
> takes 30ms
>    and query 2 takes 33420 ms.  In the old configuration, both query 1
> and query 2
>    took 6 ms.  The table schemas between the new/old configurations are
> the same as
>    well as the data in the tables.  I've run update all statistics on
> all affected
>    tables in the new configuration.  I realize that the optimizer
> doesn't know what
>    the variables are at compile time in query 2, however, in the old
> configuration of
>    ASE it didn't seem to have the same sort of performance problem.
>
> Any ideas on what's going on for both of these problems would be greatly
> appreciated.
> --
> ____________________         __           ____________________
> \_____________      \       /_ \         /      _____________/
>  \_____________      \_______/  \_______/      _____________/
>   \_________________________      _________________________/
>                            /______\
>    Han Tunca
>    Tradelink L.L.C.
>    han@trdlnk.com

0
Eric
8/7/2000 4:32:28 PM
>         declare @exp_year int
>         declare @exp_month int
Eric is right, check exp_year and exp_month data types.
I had the similar case last week, I bet exp_year and exp_month
are smallint or tinyint.
But I wonder why optimizer is getting dumb on this case....
0
SrSybDBA
8/8/2000 1:27:08 PM
Han,

Let's look at your queries.

> query 1:
>
> select min(trade_time), max(trade_time)
> from history..underlying_ticks_fp
> where symbol = "SP" and instrument = "F"
> and exp_year = 100 and exp_month = 12
> and price_type = "T" and price > 0
> and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"

Your first query is performing a cardinal sin. NEVER perform a min and max
in the same SQL statement because it will perform an index leaf scan on
every page. If you separate the min and max into two queries, two point
queries will be performed for those functions and you will get infinitely
better performance. This is known as min and max optimisation.

> query 2:
>
> declare @symbol symbol
> declare @exp_year int
> declare @exp_month int
> declare @start_time datetime
> declare @end_time datetime
> select @symbol = "SP"
> select @exp_year = 100
> select @exp_month = 12
> select @start_time = "7/31/2000 00:00:00"
> select @end_time = "8/1/2000 00:00:00"
> select min(trade_time), max(trade_time)
> from history..underlying_ticks_fp
> where symbol = @symbol and instrument = "F"
> and price_type = "T" and exp_month = @exp_month
> and exp_year = @exp_year and trade_time
> between @start_time and @end_time

Is this in a stored procedure? If so, the optimiser cannot resolve @symbol,
@exp_month, @exp_year, @start_time and @end_tme until the proc is run as the
value is unknown. It will not use statistics and therefore it will end up
using defaults. If you declare the variables in another proc and pass the
values by parameter, the called proc can be optimised with known values.
Note that if the values vary such that different query plans can result,
look at creating the procs with the 'with recompile' option.

Andy

--

Andy Price
Sybase Certified Professional - Adaptive Server DBA v12.0
Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5
Sybase Certified Associate - SQL Developer v11.5



"Han Tunca" <han@trdlnk.com> wrote in message
news:39883BE5.61C5A12@trdlnk.com...
> At our site, we just upgraded from 11.0.3.3 to 11.9.3 - in the process
> we changed machines, so:
>
> old configuration:
>
> ASE 11.0.3.3 on a Sparc 10 running Solaris 2.5.1, 1 processor, 100 Mb
> allocated to sybase
>
> new configuration:
>
> ASE 11.9.3 on a Sun E250 running Solaris 7, 2 processors, 200 Mb
> allocated to sybase.
>
> I'm running in to two performance related problems:
>
> 1) High throughput inserts/updates of tables.  We have a data feed that
> pumps data into
>    the database at a high rate of speed.  We have more memory and faster
> disks on the
>    new system, but the rate at which the inserts/updates occur is the
> same as the
>    old configuration.  I've setup a 16k buffer pool, and the set
> logiosize on the two
>    databases that have the high number of inserts/updates.  I've also
> set row level
>    locking on the two databases.  After these changes, I still see no
> improvement over
>    the old configuration.  I've check sp_sysmon and nothing seems to be
> wrong, but
>    granted I'm not at expert at reading the output of sp_sysmon.
> Anything I should
>    specifically be looking for?
>
> 2) Slow querying of tables involving sql with variables.  I have the two
> following
>    queries:
>
> query 1:
>
> select min(trade_time), max(trade_time)
> from history..underlying_ticks_fp
> where symbol = "SP" and instrument = "F"
> and exp_year = 100 and exp_month = 12
> and price_type = "T" and price > 0
> and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
>
> query 2:
>
> declare @symbol symbol
> declare @exp_year int
> declare @exp_month int
> declare @start_time datetime
> declare @end_time datetime
> select @symbol = "SP"
> select @exp_year = 100
> select @exp_month = 12
> select @start_time = "7/31/2000 00:00:00"
> select @end_time = "8/1/2000 00:00:00"
> select min(trade_time), max(trade_time)
> from history..underlying_ticks_fp
> where symbol = @symbol and instrument = "F"
> and price_type = "T" and exp_month = @exp_month
> and exp_year = @exp_year and trade_time
> between @start_time and @end_time
>
>    Now, in the new configuration, using set statistics time on, query 1
> takes 30ms
>    and query 2 takes 33420 ms.  In the old configuration, both query 1
> and query 2
>    took 6 ms.  The table schemas between the new/old configurations are
> the same as
>    well as the data in the tables.  I've run update all statistics on
> all affected
>    tables in the new configuration.  I realize that the optimizer
> doesn't know what
>    the variables are at compile time in query 2, however, in the old
> configuration of
>    ASE it didn't seem to have the same sort of performance problem.
>
> Any ideas on what's going on for both of these problems would be greatly
> appreciated.
> --
> ____________________         __           ____________________
> \_____________      \       /_ \         /      _____________/
>  \_____________      \_______/  \_______/      _____________/
>   \_________________________      _________________________/
>                            /______\
>    Han Tunca
>    Tradelink L.L.C.
>    han@trdlnk.com


0
Andy
8/8/2000 2:31:03 PM
Andy Price wrote:
> 
> Han,
> 
> Let's look at your queries.
> 
> > query 1:
> >
> > select min(trade_time), max(trade_time)
> > from history..underlying_ticks_fp
> > where symbol = "SP" and instrument = "F"
> > and exp_year = 100 and exp_month = 12
> > and price_type = "T" and price > 0
> > and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
> 
> Your first query is performing a cardinal sin. NEVER perform a min and max
> in the same SQL statement because it will perform an index leaf scan on
> every page. If you separate the min and max into two queries, two point
> queries will be performed for those functions and you will get infinitely
> better performance. This is known as min and max optimisation.

This wasn't my concern - my concern was more with query 2 being
infinitely
slower on a 11.9.3 database as compared to the 11.0.3.3 database.  After
taking
Eric's advice, I double checked the variable types and indeed found they
did
not match up with the column types - I changed them and now get the
performance
I saw in 11.0.3.3.  As another user asked, why the change and is it
documented? - this
almost seems like a bug.

The other problem I'm still having is slower inserts/updates on tables -
I have
two small tables that recieve a stream of data throughout the day.  On
the new server,
which has a faster network connection, faster disks, more memory and
more processors,
I get a slower insert/update throughput than I do for the old system
running 11.0.3.3.
Anybody have any pointers?  I've looked at sp_sysmon, changed some
configuration
params, etc, and it still is nowhere near the speed of the old system.

> 
> > query 2:
> >
> > declare @symbol symbol
> > declare @exp_year int
> > declare @exp_month int
> > declare @start_time datetime
> > declare @end_time datetime
> > select @symbol = "SP"
> > select @exp_year = 100
> > select @exp_month = 12
> > select @start_time = "7/31/2000 00:00:00"
> > select @end_time = "8/1/2000 00:00:00"
> > select min(trade_time), max(trade_time)
> > from history..underlying_ticks_fp
> > where symbol = @symbol and instrument = "F"
> > and price_type = "T" and exp_month = @exp_month
> > and exp_year = @exp_year and trade_time
> > between @start_time and @end_time
> 
> Is this in a stored procedure? If so, the optimiser cannot resolve @symbol,
> @exp_month, @exp_year, @start_time and @end_tme until the proc is run as the
> value is unknown. It will not use statistics and therefore it will end up
> using defaults. If you declare the variables in another proc and pass the
> values by parameter, the called proc can be optimised with known values.
> Note that if the values vary such that different query plans can result,
> look at creating the procs with the 'with recompile' option.
> 
> Andy
> 
> --
> 
> Andy Price
> Sybase Certified Professional - Adaptive Server DBA v12.0
> Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5
> Sybase Certified Associate - SQL Developer v11.5
> 
> "Han Tunca" <han@trdlnk.com> wrote in message
> news:39883BE5.61C5A12@trdlnk.com...
> > At our site, we just upgraded from 11.0.3.3 to 11.9.3 - in the process
> > we changed machines, so:
> >
> > old configuration:
> >
> > ASE 11.0.3.3 on a Sparc 10 running Solaris 2.5.1, 1 processor, 100 Mb
> > allocated to sybase
> >
> > new configuration:
> >
> > ASE 11.9.3 on a Sun E250 running Solaris 7, 2 processors, 200 Mb
> > allocated to sybase.
> >
> > I'm running in to two performance related problems:
> >
> > 1) High throughput inserts/updates of tables.  We have a data feed that
> > pumps data into
> >    the database at a high rate of speed.  We have more memory and faster
> > disks on the
> >    new system, but the rate at which the inserts/updates occur is the
> > same as the
> >    old configuration.  I've setup a 16k buffer pool, and the set
> > logiosize on the two
> >    databases that have the high number of inserts/updates.  I've also
> > set row level
> >    locking on the two databases.  After these changes, I still see no
> > improvement over
> >    the old configuration.  I've check sp_sysmon and nothing seems to be
> > wrong, but
> >    granted I'm not at expert at reading the output of sp_sysmon.
> > Anything I should
> >    specifically be looking for?
> >
> > 2) Slow querying of tables involving sql with variables.  I have the two
> > following
> >    queries:
> >
> > query 1:
> >
> > select min(trade_time), max(trade_time)
> > from history..underlying_ticks_fp
> > where symbol = "SP" and instrument = "F"
> > and exp_year = 100 and exp_month = 12
> > and price_type = "T" and price > 0
> > and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
> >
> > query 2:
> >
> > declare @symbol symbol
> > declare @exp_year int
> > declare @exp_month int
> > declare @start_time datetime
> > declare @end_time datetime
> > select @symbol = "SP"
> > select @exp_year = 100
> > select @exp_month = 12
> > select @start_time = "7/31/2000 00:00:00"
> > select @end_time = "8/1/2000 00:00:00"
> > select min(trade_time), max(trade_time)
> > from history..underlying_ticks_fp
> > where symbol = @symbol and instrument = "F"
> > and price_type = "T" and exp_month = @exp_month
> > and exp_year = @exp_year and trade_time
> > between @start_time and @end_time
> >
> >    Now, in the new configuration, using set statistics time on, query 1
> > takes 30ms
> >    and query 2 takes 33420 ms.  In the old configuration, both query 1
> > and query 2
> >    took 6 ms.  The table schemas between the new/old configurations are
> > the same as
> >    well as the data in the tables.  I've run update all statistics on
> > all affected
> >    tables in the new configuration.  I realize that the optimizer
> > doesn't know what
> >    the variables are at compile time in query 2, however, in the old
> > configuration of
> >    ASE it didn't seem to have the same sort of performance problem.
> >
> > Any ideas on what's going on for both of these problems would be greatly
> > appreciated.
> > --
> > ____________________         __           ____________________
> > \_____________      \       /_ \         /      _____________/
> >  \_____________      \_______/  \_______/      _____________/
> >   \_________________________      _________________________/
> >                            /______\
> >    Han Tunca
> >    Tradelink L.L.C.
> >    han@trdlnk.com

-- 
____________________         __           ____________________
\_____________      \       /_ \         /      _____________/ 
 \_____________      \_______/  \_______/      _____________/
  \_________________________      _________________________/ 
                           /______\
   Han Tunca
   Tradelink L.L.C.
   han@trdlnk.com
0
Han
8/8/2000 5:42:38 PM
Taking Erics point a bit further regarding datatypes matching, the issue
with the variable types is to do with the datatype hierarchy and determines
how indexes will be used with sargs or joins. For sargs, they don't
necessarily have to match, but the datatype needs to come before it in the
hierarchy if they don't. There are some exceptions, such as char and varchar
being treated the same, but you can read up on this in the P&T guide if you
want more information.

If you are interested, you can create the datatype consolidation hierarchy
from the systypes table by doing something like this:

select name, length, hierarchy from systypes order by 3

I know this is basic stuff and many of you will already know this, but it
may help as a refresher.

Andy



"Han Tunca" <han@trdlnk.com> wrote in message
news:3990468E.DCCF704C@trdlnk.com...
> Andy Price wrote:
> >
> > Han,
> >
> > Let's look at your queries.
> >
> > > query 1:
> > >
> > > select min(trade_time), max(trade_time)
> > > from history..underlying_ticks_fp
> > > where symbol = "SP" and instrument = "F"
> > > and exp_year = 100 and exp_month = 12
> > > and price_type = "T" and price > 0
> > > and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
> >
> > Your first query is performing a cardinal sin. NEVER perform a min and
max
> > in the same SQL statement because it will perform an index leaf scan on
> > every page. If you separate the min and max into two queries, two point
> > queries will be performed for those functions and you will get
infinitely
> > better performance. This is known as min and max optimisation.
>
> This wasn't my concern - my concern was more with query 2 being
> infinitely
> slower on a 11.9.3 database as compared to the 11.0.3.3 database.  After
> taking
> Eric's advice, I double checked the variable types and indeed found they
> did
> not match up with the column types - I changed them and now get the
> performance
> I saw in 11.0.3.3.  As another user asked, why the change and is it
> documented? - this
> almost seems like a bug.
>
> The other problem I'm still having is slower inserts/updates on tables -
> I have
> two small tables that recieve a stream of data throughout the day.  On
> the new server,
> which has a faster network connection, faster disks, more memory and
> more processors,
> I get a slower insert/update throughput than I do for the old system
> running 11.0.3.3.
> Anybody have any pointers?  I've looked at sp_sysmon, changed some
> configuration
> params, etc, and it still is nowhere near the speed of the old system.
>
> >
> > > query 2:
> > >
> > > declare @symbol symbol
> > > declare @exp_year int
> > > declare @exp_month int
> > > declare @start_time datetime
> > > declare @end_time datetime
> > > select @symbol = "SP"
> > > select @exp_year = 100
> > > select @exp_month = 12
> > > select @start_time = "7/31/2000 00:00:00"
> > > select @end_time = "8/1/2000 00:00:00"
> > > select min(trade_time), max(trade_time)
> > > from history..underlying_ticks_fp
> > > where symbol = @symbol and instrument = "F"
> > > and price_type = "T" and exp_month = @exp_month
> > > and exp_year = @exp_year and trade_time
> > > between @start_time and @end_time
> >
> > Is this in a stored procedure? If so, the optimiser cannot resolve
@symbol,
> > @exp_month, @exp_year, @start_time and @end_tme until the proc is run as
the
> > value is unknown. It will not use statistics and therefore it will end
up
> > using defaults. If you declare the variables in another proc and pass
the
> > values by parameter, the called proc can be optimised with known values.
> > Note that if the values vary such that different query plans can result,
> > look at creating the procs with the 'with recompile' option.
> >
> > Andy
> >
> > --
> >
> > Andy Price
> > Sybase Certified Professional - Adaptive Server DBA v12.0
> > Sybase Certified Associate - Adaptive Server DBA v12.0 & v11.5
> > Sybase Certified Associate - SQL Developer v11.5
> >
> > "Han Tunca" <han@trdlnk.com> wrote in message
> > news:39883BE5.61C5A12@trdlnk.com...
> > > At our site, we just upgraded from 11.0.3.3 to 11.9.3 - in the process
> > > we changed machines, so:
> > >
> > > old configuration:
> > >
> > > ASE 11.0.3.3 on a Sparc 10 running Solaris 2.5.1, 1 processor, 100 Mb
> > > allocated to sybase
> > >
> > > new configuration:
> > >
> > > ASE 11.9.3 on a Sun E250 running Solaris 7, 2 processors, 200 Mb
> > > allocated to sybase.
> > >
> > > I'm running in to two performance related problems:
> > >
> > > 1) High throughput inserts/updates of tables.  We have a data feed
that
> > > pumps data into
> > >    the database at a high rate of speed.  We have more memory and
faster
> > > disks on the
> > >    new system, but the rate at which the inserts/updates occur is the
> > > same as the
> > >    old configuration.  I've setup a 16k buffer pool, and the set
> > > logiosize on the two
> > >    databases that have the high number of inserts/updates.  I've also
> > > set row level
> > >    locking on the two databases.  After these changes, I still see no
> > > improvement over
> > >    the old configuration.  I've check sp_sysmon and nothing seems to
be
> > > wrong, but
> > >    granted I'm not at expert at reading the output of sp_sysmon.
> > > Anything I should
> > >    specifically be looking for?
> > >
> > > 2) Slow querying of tables involving sql with variables.  I have the
two
> > > following
> > >    queries:
> > >
> > > query 1:
> > >
> > > select min(trade_time), max(trade_time)
> > > from history..underlying_ticks_fp
> > > where symbol = "SP" and instrument = "F"
> > > and exp_year = 100 and exp_month = 12
> > > and price_type = "T" and price > 0
> > > and trade_time between "7/31/2000 00:00:00" and "8/1/2000 00:00:00"
> > >
> > > query 2:
> > >
> > > declare @symbol symbol
> > > declare @exp_year int
> > > declare @exp_month int
> > > declare @start_time datetime
> > > declare @end_time datetime
> > > select @symbol = "SP"
> > > select @exp_year = 100
> > > select @exp_month = 12
> > > select @start_time = "7/31/2000 00:00:00"
> > > select @end_time = "8/1/2000 00:00:00"
> > > select min(trade_time), max(trade_time)
> > > from history..underlying_ticks_fp
> > > where symbol = @symbol and instrument = "F"
> > > and price_type = "T" and exp_month = @exp_month
> > > and exp_year = @exp_year and trade_time
> > > between @start_time and @end_time
> > >
> > >    Now, in the new configuration, using set statistics time on, query
1
> > > takes 30ms
> > >    and query 2 takes 33420 ms.  In the old configuration, both query 1
> > > and query 2
> > >    took 6 ms.  The table schemas between the new/old configurations
are
> > > the same as
> > >    well as the data in the tables.  I've run update all statistics on
> > > all affected
> > >    tables in the new configuration.  I realize that the optimizer
> > > doesn't know what
> > >    the variables are at compile time in query 2, however, in the old
> > > configuration of
> > >    ASE it didn't seem to have the same sort of performance problem.
> > >
> > > Any ideas on what's going on for both of these problems would be
greatly
> > > appreciated.
> > > --
> > > ____________________         __           ____________________
> > > \_____________      \       /_ \         /      _____________/
> > >  \_____________      \_______/  \_______/      _____________/
> > >   \_________________________      _________________________/
> > >                            /______\
> > >    Han Tunca
> > >    Tradelink L.L.C.
> > >    han@trdlnk.com
>
> --
> ____________________         __           ____________________
> \_____________      \       /_ \         /      _____________/
>  \_____________      \_______/  \_______/      _____________/
>   \_________________________      _________________________/
>                            /______\
>    Han Tunca
>    Tradelink L.L.C.
>    han@trdlnk.com


0
Andy
8/8/2000 6:55:13 PM
Han Tunca wrote:

> The other problem I'm still having is slower inserts/updates on tables -
> I have
> two small tables that recieve a stream of data throughout the day.  On
> the new server,
> which has a faster network connection, faster disks, more memory and
> more processors,
> I get a slower insert/update throughput than I do for the old system
> running 11.0.3.3.
> Anybody have any pointers?  I've looked at sp_sysmon, changed some
> configuration
> params, etc, and it still is nowhere near the speed of the old system.

	I'd suggest looking at the showplan on both servers. Check that
	both are running the same way. If not, find out why. I suspect that
	it could be an index issue. Also check the datatypes just to be
	sure.

-am
0
Anthony
8/9/2000 1:42:27 AM
Reply:

Similar Artilces:

Performance degrades: 11.0.3.3 upgraded to 11.9.2
We recently upgraded a test server from ASE 11.0.3 on HP-UX 10.20 to ASE 11.9.2 on HP-UX 11.0. We have a query that runs in 15 min in production (11.0.3) an 60 minutes in test (11.9.2). Another query runs for 20 min in production and has yet to finish in test (11.9.2). We did update all statistics on the tables and have recompiled the stored procedures. Any help, advice or comments would be greatly appreciated. Georgette M. Geotsi DaimlerChrysler Capital Services GGeotsi@DCXCapital.com GGeotsi@dcxcapital.com wrote: > > We recently upgraded a test server from ASE ...

Slow performance after 11.0.3.3 ->11.9.2.6
Hi, It is on AIX 4.3.3. Only tempdb is on jfs, all others are raw devices. Did update index statistics for every table in the database. Now a query via a PowerBuilder application take 25-35 seconds which before the upgrade took only 0-2 seconds. What can I do to improve performance ? I already increased the number of open objects number of open indexes because I found related messages in the errorlog. The database has about 550 tables. The size of it about 20GB. The largest table about 1.41 GB. Many tables have clustered and other indexes. Tempdb is 302MB /...

Performance loss after a 11.0.3.3 -> 11.9.2.6 ugrade
Hi, It is on AIX 4.3.3. Only tempdb is on jfs, all others are raw devices. Did update index statistics for every table in the database. Now a query via a PowerBuilder application takes 25-35 seconds which before the upgrade took only 0-2 seconds. What can I do to improve performance ? I already increased the number of open objects number of open indexes because I found related messages in the errorlog. The database has about 550 tables. The size of it about 20GB. The largest table about 1.41 GB. Many tables have clustered and other indexes. Tempdb is 302MB...

ASE 11.0.3.3 and ASE 11.9.2 on same box
Hi evereyone, I would like to install 2 ASE servers with different versions on same linux box. Is this possible? Here is what I tried to do: I have a working ASE 11.9.2 on linux box tried to install ASE 11.0.3.3 on same box in different directory and it gave me an error. Wondering if this ispossible at all. Appreciate any help. TIA ar The 11.0.3.3 rpm tries to uninstall the 11.9.2 package. I shutdown the 11.9.2 server before install 11.0.3.3 and I do not keep the both versions running. The variables SYBASE, LD_LIBRARY_PATH ,PATH must be correctly set to point the...

Replication Server 11.5
Simply, can I replicate from 11.9.3 to 11.0.3.3 using Replication Server 11.5, warm standby solution with replication definitions? We are in the process of upgrading our production systems to 11.9.3 from 11.0.3.3. Our current scenerio looks like this: Server A : 11.0.3.3 Production ASE/11.5 RepServer Server B: 11.0.3.3 ASE Server C: 11.0.3.3 ASE 1. Currently replicating Server A --> Server C 2. Upgrade Server B to 11.9.3. 3. Replicate Server A --> Server B ** This is where my question fits in--if a "switch" is needed, can 11.9.3 --> 11.0.3.3? 4. Sto...

Performance on 11.9.2 as compared to 11.0.3
We have just migrated from 11.0.3 to 11.9.2.2 However we are facing serious performance issues . contrary to our expectations I have found that all the queries are taking 3 to 4 times more "Parse and Compile Time" on 11.9.2 as compared to 11.0.3 Has any one got ideas of what could be causing the problem . Any help in this regard will be greatly appreciated Thanks Raghu did you do update statistics on your tables after the upgrade? I've found this is the problem many times after upgrades. Be sure to read up on update stats before you go crazy upd...

Upgrade 11.0.3.3 to ASE 11.9.2 with RepServer 11.5 and LTMs
I am preparing to upgrade our production servers (11.0.3.3) using RepServer 11.5 with LTMs. My understanding is that the ASE 11.9.2 has RepAgents build in as Open Server applications. Are there any issues with upgrading that I need to be aware of? Where would I look? I would assume that I don't need to run LTMs anymore, but I need to ensure no loss of replication to the warm standby. Any advice? Take care. JCS ASE 11.5+ has RepAgent built in as internal server threads (which has nothing to do with OpenServer). When upgrading, keep the LTM's and upgrade to RS 11.5 / ...

Performance problems in ASE 11.0.3.3
Hi, We are using Sybase 11.0.3.3 (EBF 9121), and we're in the process of upgrading but is too far. We are experimenting a degradation in the performance of the Sybase. The 2 processors are working at 60%, I have a lot of 1608 error messages in the errorlog. We have tried with users caches for tempdb and large pool (8k) for default and user caches. But still we have the messages and the users are saying that the response time is very, very slow. What can we do? Thanks. Luis. To what version did you upgrade? Can you post the server config file. Also, post the output of ...

Error after upgrade from 11.0.3.3 to 11.9.2.3
We have upgraded our production Sybase servers to 11.9.2 from 11.0.3.3 last weekend and we applied ebf9160 All went well except for an error in the errorlog as follows NT operating system error 2 in module 'e:\rel1192\porttree\svr\sql\generic\ksource\strmio\n_winsock.c' at line 3481: The system cannot find the file specified. kernel nl__connect: Could not connect to site 'VUA_S2002_XP', socket 1. This error did not occur when we tested the upgrade on our test servers. There appears to be no problems with the overall install, everything works fine - except for t...

11.0.3.2 vs 11.0.3.3
Dear All, I understand that SQL server 11.0.3.2 is Y2K compliance. However, I have been suggested to upgrade to 11.0.3.3 for much safety (in Y2K). Is it true? What is the main difference between these 2 versions? Best Regards, Colin Colin wrote: > > Dear All, > > I understand that SQL server 11.0.3.2 is Y2K compliance. However, I > have been suggested to upgrade to 11.0.3.3 for much safety (in Y2K). Is > it true? What is the main difference between these 2 versions? > Just a number of bugfixes. I don't believe any of them are Y2K rel...

upgrade ASE from 11.0.3.3 to 11.9.2 on AIX
Hi, We are planning to upgrade our SQL Server 11.0.3.3 servers to ASE 11.9.2. We have 6 production servers on one UNIX box and (almost) corresponding test servers on another UNIX box. We have little new development, so we should have enough time to test things out first on the TEST platform. I am trying to decide between upgrading the servers in place or doing new installs and migrating the data over to the new servers. The upgrades in place would be a lot easier on me and the UNIX support people, but I am not sure how much more difficult fallback would be. If we needed to fall...

ASE 12.5 vs 11.0.3.3 performance.
Hi all, At a customer site they have installed new 12.5.0.1 servers (ESD#1 rollup) wherein dumps from 11.0.3.3 were loaded - and yes it can be done and is 'legal'. Dataservers are configured alike (developement environment) with 120 mb per dataserver and both 11.0.3.3 and 12.5 running on the same physical machine. (Compaq Alphaserver DS20E, 2CPUs, 2.5 gb RAM, Tru64 Unix 5.1A). On 11.0.3.3 40% was allocated to procedure cache, and the rest to data cache. On 12.5 around 40mb was allocated to procedure cache and 60mb to data cache. So far so good. Every night all stored...

dump performance. 11.0.3.3
Dear group, months ago, we were satisfied with our dump database performance on DLT tapes: our main database, 16.5 GB in size, was dumped on 1h 32min, which gives 3 MB per second. Not bad for a DLT3000 drive, whose nominal uncompressed speed is 1.5 MB/sec, IIRC. Since our compression ratio is estimated as 2.5, I thought that our dump speed was merely the tape speed. But now we are urged to increase speed, at any cost. The perfect answer would be to migrate our ASE 11.0.3.3 (on HP-UX) to ASE 12.0, but this will take some time. We began experiencing faster hardware; a DLT7000 drive...

3.0.11 keeps updating 3.5 beta 4 to 3.0.11
Name: thomas sturges Email: tsturgesatearthlinkdotnet Product: Firefox Summary: 3.0.11 keeps updating 3.5 beta 4 to 3.0.11 Comments: see above Browser Details: Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.1b4) Gecko/20090423 Firefox/3.5b4 (.NET CLR 3.5.30729) From URL: Note to readers: Hendrix gives no expectation of a response to this feedback but if you wish to provide one you must BCC (not CC) the sender for them to see it. ...

Web resources about - ASE 11.9.3 performance compared to 11.0.3.3 - sybase.sqlserver.performance

Performance - Wikipedia, the free encyclopedia
A performance , in performing arts , generally comprises an event in which a performer or group of performers behave in a particular way for ...

Australia v West Indies: Tourists earn support from SCG crowd with battling performance in third Test ...
The SCG crowd shows desire for the contest, and for much of the first day of the third Test they got it, writes Geoff Lemon.

BMW recaps the glory days of M3 performance
Filed under: Videos , BMW , Convertible , Coupe , Sedan , Classics , Luxury , Performance BMW continues it documentary chronicle of the M3 lineage ...

Old Spice Ups Innovation, Performance Ante with New Hardest Working Collection Line of Anti-Perspirant/Deodorants ...
Old Spice is taking performance to legendary extremes with the introduction of the new Hardest Working Collection product line, offering the ...

Keep two Macs running at peak performance with this award-winning app
Like all of us, our computers can develop habits over time, not all of them great. Many computers basically become hoarders, getting distracted, ...

‘The Revenant’: Cast & Director On The “Intensity” Of Performances – Featurette
EXCLUSIVE: Making it through Alejandro G. Iñárritu ’s brutal period film The Revenant is as much an endurance test for the audience as it is ...

Weak iPhone sales affect December performance at Taiwan panel makers
Several Taiwan touch panel makers have reported more than 30% on-month revenue declines for December 2015 as a result of weak iPhone 6s and 6s ...

AccuWeather Gets Big Material Design Update, Improved Performance
... that it is available globally to all. The update introduces Google’s Material Design language to weather fans, but also includes performance ...

Jimmy Fallon, J.K. Simmons and Billy Joel Pull Off an Epic Impromptu Performance During a Commercial ...
The 'Tonight Show' host and actor joined the musician in a rendition of his doo-wop hit 'The Longest Time.'

Billy Joel: Doo-wop performance on Jimmy Fallon - EW.com
... Jimmy Fallon didn’t even occur on the show. While shooting promos for the broadcast, guest Billy Joel started an unrehearsed doo-wop performance ...

Resources last updated: 1/7/2016 11:23:36 PM