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.

Why 12.0 slower than 11.5 ?

25 posts in ,  General Discussion Windows NT Last posting was on 2000-07-14 18:09:58.0Z
Eugene Valentinov Posted on 2000-06-28 09:56:40.0Z
Message-ID: <3959CBD7.9A9EEAFF@citkami.ru>
Date: Wed, 28 Jun 2000 13:56:40 +0400
From: Eugene Valentinov <valentinov@citkami.ru>
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Why 12.0 slower than 11.5 ?
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 14
NNTP-Posting-Host: dialup-000015.magelan.ru 212.11.193.15
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3723 sybase.public.sqlserver.nt:581
Article PK: 1074681

Hi !
We have two ASE installations on a NT box: 11.5 and 12.0. [To make it
possible we run our servers from the DOS prompt ( run_***.bat ).]
Configuation parameters are the same.
Also we have PowerBuilder client working with them via the native 11
driver.

Question is: why 12 server can be 1.5-2.5 times slower in SELECT tasks
then 11.5 ? [ both in data/allpages lock schemes ]
UPDATES are not tested...

So, how can I do this experiment in clearest way?
Any comments/tips are welcome!

Best regards, Eugene


Andy Price Posted on 2000-06-28 10:12:10.0Z
From: "Andy Price" <andy.price@canada.com>
References: <3959CBD7.9A9EEAFF@citkami.ru>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Wed, 28 Jun 2000 11:12:10 +0100
Lines: 36
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <$L58EqO4$GA.204@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: dyn240-ras13.screaming.net 212.49.236.240
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3722 sybase.public.sqlserver.nt:580
Article PK: 1074678

Eugene,

As part of your upgrade, did you update all the statistics for all tables in
the v12 server? V11.9.2 and v12 stores stats for all columns in the tables,
provided you update statistics, rather than just for the index columns. This
enables v11.9.2+ optimiser to make better choices about its query execution
plans. To make it an even test, ensure that you are using the allpages
locking scheme in v12 rather than datapages or datarows, or your tests will
not be comparable.

--
Andy Price
Sybase Certified Adaptive Server Administrator - Associate v11.5

"Eugene Valentinov" <valentinov@citkami.ru> wrote in message
news:3959CBD7.9A9EEAFF@citkami.ru...
> Hi !
> We have two ASE installations on a NT box: 11.5 and 12.0. [To make it
> possible we run our servers from the DOS prompt ( run_***.bat ).]
> Configuation parameters are the same.
> Also we have PowerBuilder client working with them via the native 11
> driver.
>
> Question is: why 12 server can be 1.5-2.5 times slower in SELECT tasks
> then 11.5 ? [ both in data/allpages lock schemes ]
> UPDATES are not tested...
>
> So, how can I do this experiment in clearest way?
> Any comments/tips are welcome!
>
> Best regards, Eugene
>


Eugene Valentinov Posted on 2000-06-28 14:48:16.0Z
Message-ID: <395A1030.537B2E71@citkami.ru>
Date: Wed, 28 Jun 2000 18:48:16 +0400
From: Eugene Valentinov <valentinov@citkami.ru>
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com>
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 39
NNTP-Posting-Host: dialup-000019.magelan.ru 212.11.193.19
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3721 sybase.public.sqlserver.nt:579
Article PK: 1074679

Andy, of course we've run update all statistics on every table. Also all views
and procedures were recompiled, tempdb was expanded, all dbcc checks are done (
except of dbcc checkstorage ).
Any more ideas ?

Andy Price wrote:

> Eugene,
>
> As part of your upgrade, did you update all the statistics for all tables in
> the v12 server? V11.9.2 and v12 stores stats for all columns in the tables,
> provided you update statistics, rather than just for the index columns. This
> enables v11.9.2+ optimiser to make better choices about its query execution
> plans. To make it an even test, ensure that you are using the allpages
> locking scheme in v12 rather than datapages or datarows, or your tests will
> not be comparable.
>
> --
> Andy Price
> Sybase Certified Adaptive Server Administrator - Associate v11.5
>
> "Eugene Valentinov" <valentinov@citkami.ru> wrote in message
> news:3959CBD7.9A9EEAFF@citkami.ru...
> > Hi !
> > We have two ASE installations on a NT box: 11.5 and 12.0. [To make it
> > possible we run our servers from the DOS prompt ( run_***.bat ).]
> > Configuation parameters are the same.
> > Also we have PowerBuilder client working with them via the native 11
> > driver.
> >
> > Question is: why 12 server can be 1.5-2.5 times slower in SELECT tasks
> > then 11.5 ? [ both in data/allpages lock schemes ]
> > UPDATES are not tested...
> >
> > So, how can I do this experiment in clearest way?
> > Any comments/tips are welcome!
> >
> > Best regards, Eugene
> >


Jim Egan Posted on 2000-06-29 04:14:27.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Wed, 28 Jun 2000 23:14:27 -0500
Message-ID: <MPG.13c48920551784498aa56@199.93.177.77>
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 15
NNTP-Posting-Host: ts4-00.she.cyberhighway.net 209.161.50.60
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3719 sybase.public.sqlserver.nt:577
Article PK: 1074676

We have seen better performance in 12.0 but it was not without some work.
I made extensive use of OPTDIAG.EXE to find situations where the
statistics for indexed columns were either not correct or not optimal
when compared to 11.5.

In most instances I had to manually update the statistics and increase
the number of steps. I also had to use the system procedure
sp_modifystats to adjust the skew in one of my indexes.
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Eugene Valentinov Posted on 2000-06-29 10:01:25.0Z
Message-ID: <395B1E75.8F78F9BA@citkami.ru>
Date: Thu, 29 Jun 2000 14:01:25 +0400
From: Eugene Valentinov <valentinov@citkami.ru>
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <MPG.13c48920551784498aa56@199.93.177.77>
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 28
NNTP-Posting-Host: dialup-000055.magelan.ru 212.11.193.55
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3717 sybase.public.sqlserver.nt:575
Article PK: 1074672

Thank you, Jim!

Your message is most realistic then other ones I got. It seems to me the
problem is in incorrect statistics. 12th server made in one case 3 steps
instead of 2 in 11.5 ( in showplan ) and have 2.5 times more logical reads.

What can I do? Does it means, that it's no reason to install new server? I
think, I could'nt set manually all statistic in correct way...
The next question is: perhaps there is no such problem tin 11.9.2 server ?

Eugene

Jim Egan wrote:

> We have seen better performance in 12.0 but it was not without some work.
> I made extensive use of OPTDIAG.EXE to find situations where the
> statistics for indexed columns were either not correct or not optimal
> when compared to 11.5.
>
> In most instances I had to manually update the statistics and increase
> the number of steps. I also had to use the system procedure
> sp_modifystats to adjust the skew in one of my indexes.
> --
> Jim Egan [TeamSybase]
> Houston, TX
> http://www.eganomics.com
>
> Sybase Developers Network
> http://sdn.sybase.com/sdn/mec/mec_home.stm


Jim Egan Posted on 2000-07-02 20:16:18.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Sun, 2 Jul 2000 15:16:18 -0500
Message-ID: <MPG.13c81b4fdab1f53498aa59@199.93.177.77>
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <MPG.13c48920551784498aa56@199.93.177.77> <395B1E75.8F78F9BA@citkami.ru>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 35
NNTP-Posting-Host: user-33qs1js.dialup.mindspring.com 199.174.6.124
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3716 sybase.public.sqlserver.nt:574
Article PK: 1074674

I can't give you much advice regarding the difference between 11.9.2 and
12.0 since I skipped from 11.5 to 12.0. However, my understanding is
that the optimization in 12.0 is very similar to 11.9.2.

How did you upgrade the server? Was it an in-place upgrade? That is the
recommended method.

You may want to go back and run UPDATE INDEX STATISTICS <tablename>
instead of UPDATE STATISTICS <tablename> if you have multi-column
indexes. The new syntax will generate statistics for columns beyond the
first one in the index.

Did you change the locking method on any of your tables? In our case
this had a very positive effect when we changed from AllPages to DataPage
locking. On only two tables did we find it necessary to move to DataRow
locking. Most of our tables are still using AllPages locking. We
defined our tables with the most contention and moved them to DataPage
locking. This was only 6 tables out of 75.

You should also run the monitor to gather information on what the system
is doing. You may find that there is a lot of contention for pages.
Normally you would have also seen this on 11.5. A comparison of the
output from 11.5 and 12.0 may reveal something that needs attention.
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Henrik Bruun Posted on 2000-07-06 03:17:24.0Z
From: "Henrik Bruun" <henrik@splitcycle.com>
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <MPG.13c48920551784498aa56@199.93.177.77> <395B1E75.8F78F9BA@citkami.ru> <MPG.13c81b4fdab1f53498aa59@199.93.177.77>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Wed, 5 Jul 2000 23:17:24 -0400
Lines: 150
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <OvSK7nv5$GA.86@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: 216.76.57.225
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3715 sybase.public.sqlserver.nt:573
Article PK: 1074673

I have another example where 11.9.2 is much slower than 11.0 (see below)
The call is made two different ways but the values used in the qualification
are the same (the second call/good one takes 14 logical reads, the first
call
takes 5875 reads....., in 11.0 both calls take around 14 reads)... the
table
being read has a unique clustered index on (site_id, move_number) and the
stats have been updated.

set statistics io on
go
set showplan on
go
declare @first_loco int
declare @last_loco int
declare @site_id int, @move_number int

select @site_id = 126, @move_number = 4851

select @first_loco = min(sequence_number),
@last_loco = max(sequence_number)
from vvs_car_image_tbl
where site_id = @site_id
and move_number = @move_number
and car_type_number = 5
go

declare @first_loco int
declare @last_loco int
select @first_loco = min(sequence_number),
@last_loco = max(sequence_number)
from vvs_car_image_tbl
where site_id = 126
and move_number = 4851
and car_type_number = 5
go

set statistics io off
go
set showplan off
go


OUTPUT BELOW

Total writes for this command: 0
Total writes for this command: 0

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 5).


STEP 1
The type of query is SELECT.


QUERY PLAN FOR STATEMENT 3 (at line 7).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped MAXIMUM AGGREGATE.
Evaluate Ungrouped MINIMUM AGGREGATE.

FROM TABLE
vvs_car_image_tbl
Nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.
Using I/O Size 2 Kbytes for data pages.
With MRU Buffer Replacement Strategy for data pages.

STEP 2
The type of query is SELECT.
Evaluate Ungrouped ASSIGNMENT OPERATOR.
Evaluate Ungrouped ASSIGNMENT OPERATOR.

Total writes for this command: 0

(1 row(s) affected)

Table: vvs_car_image_tbl scan count 1, logical reads: (regular=5875 apf=0
total=5875), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

(1 row(s) affected)


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is DECLARE.


QUERY PLAN FOR STATEMENT 2 (at line 3).


STEP 1
The type of query is SELECT.
Evaluate Ungrouped MAXIMUM AGGREGATE.
Evaluate Ungrouped MINIMUM AGGREGATE.

FROM TABLE
vvs_car_image_tbl
Nested iteration.
Using Clustered Index.
Index : xvcit_site_move_seq
Forward scan.
Positioning by key.
Keys are:
site_id ASC
move_number ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

STEP 2
The type of query is SELECT.
Evaluate Ungrouped ASSIGNMENT OPERATOR.
Evaluate Ungrouped ASSIGNMENT OPERATOR.

Table: vvs_car_image_tbl scan count 1, logical reads: (regular=14 apf=0
total=14), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
Total writes for this command: 0

(1 row(s) affected)


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET STATUS OFF.


QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
The type of query is SET OPTION OFF.


Anthony Mandic Posted on 2000-07-06 05:46:37.0Z
Message-ID: <39641D3D.8E96DC70@lumina.com.au>
Date: Thu, 06 Jul 2000 15:46:37 +1000
From: Anthony Mandic <am@lumina.com.au>
Organization: ... luminous thru all these years ...
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <MPG.13c48920551784498aa56@199.93.177.77> <395B1E75.8F78F9BA@citkami.ru> <MPG.13c81b4fdab1f53498aa59@199.93.177.77> <OvSK7nv5$GA.86@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 31
NNTP-Posting-Host: 210.9.51.46
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3714 sybase.public.sqlserver.nt:572
Article PK: 1074669


Henrik Bruun wrote:
>
> I have another example where 11.9.2 is much slower than 11.0 (see below)
> The call is made two different ways but the values used in the qualification
> are the same (the second call/good one takes 14 logical reads, the first
> call takes 5875 reads....., in 11.0 both calls take around 14 reads)...

Well, its doing a table scan which explains that.

> the table being read has a unique clustered index on (site_id, move_number)
> and the stats have been updated.

> select @first_loco = min(sequence_number),
> @last_loco = max(sequence_number)
> from vvs_car_image_tbl
> where site_id = @site_id
> and move_number = @move_number
> and car_type_number = 5

A couple of points with your test query. If site_id and move_number
are unique, then only one row will ever match it exactly. So, the
sequence number is both max and min, in which case your test isn't
very optimal. The optimiser in 11 seems to have caught this while
the one in 11.9.2 didn't. Also the test for car_type_number is
redundant. Try leaving out one of the fields s othat you get a true
min and max range and then note the differences.

-am


TLS Posted on 2000-07-06 06:15:25.0Z
From: TLS <>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Thu, 06 Jul 2000 02:15:25 -0400
Message-ID: <2t88msgtlkeoqf1ddh0s7b7qq0p0ts2iqq@4ax.com>
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <MPG.13c48920551784498aa56@199.93.177.77>
X-Newsreader: Forte Agent 1.7/32.534
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 20
NNTP-Posting-Host: ariosoca-106.pangeatech.primenet.com 206.57.17.106
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7291
Article PK: 1078272

Jim,

I am interested in the sp_modiystats sproc you mention but I cannot
find it anywhere on my ASE12 server...

Is there anyplace special I need to look other than sybstystemprocs,
master, etc....???


Thanks much,
Tim


On Wed, 28 Jun 2000 23:14:27 -0500, Jim Egan <dbaguru@eganomics.com>

wrote:

>We have seen better performance in 12.0 but it was not without some work.
>I made extensive use of OPTDIAG.EXE to find situations where the
>statistics for indexed columns were either not correct or not optimal
>when compared to 11.5.
>
>In most instances I had to manually update the statistics and increase
>the number of steps. I also had to use the system procedure
>sp_modifystats to adjust the skew in one of my indexes.


Jim Egan Posted on 2000-07-06 11:26:03.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Thu, 6 Jul 2000 06:26:03 -0500
Message-ID: <MPG.13ce28c44e81065298aa76@199.93.177.77>
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <MPG.13c48920551784498aa56@199.93.177.77> <2t88msgtlkeoqf1ddh0s7b7qq0p0ts2iqq@4ax.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.general
Lines: 166
NNTP-Posting-Host: user-33qs1iu.dialup.mindspring.com 199.174.6.94
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7287
Article PK: 1078273

It wasn't included in the 12.0 CD and is supposed to be included in the
next release. I have included it below. Hopefully the formatting won't
be lost due to putting it on the newsgroup. Also, you must run this
after you run UPDATE STATS.


use master
go

/* Need to create proc with allow updates enabled */
exec sp_configure "allow updates to system tables", 1
go

/*Drop sp_instmsg proc if it already exists */
if exists (select *
from sysobjects
where sysstat & 7 = 4
and name = 'sp_instmsg')
begin
print "Dropping sp_instmsg"
drop procedure sp_instmsg
end
go

/*
** Need to install sp_instmsg to install sp_modifystats messages
*/
create procedure sp_instmsg
@msg_num int,
@msg_text varchar(255)
as
declare @description varchar(255)

select @description = description from sysmessages where
error = @msg_num and langid is null

if @description = @msg_text
return 1

if @description is not null
delete sysmessages where error = @msg_num

if @msg_text is null
return 2

/* Note: this version of sp_instmsg works on 4.x and 10.0 */
insert sysmessages (error, severity, dlevel, description, langid)
values (@msg_num, 0, 0, @msg_text, null)

return 0
go

exec sp_instmsg 18800,
"'%1!' is an invalid option for sp_modifystats. Valid options are:
'REMOVE_SKEW_FROM_DENSITY'."
go
exec sp_instmsg 18803,
"No statistics exist for column '%1!'."
go

drop procedure sp_instmsg
go

use sybsystemprocs
go

/* Drop sp_modifystats proc if it already exists*/
if exists (select *
from sysobjects
where sysstat & 7 = 4
and name = 'sp_modifystats')
begin
print "Dropping sp_modifystats"
drop procedure sp_modifystats
end
go


print "Installing sp_modifystats"
go

/* create proc sp_modifystats*/
create procedure sp_modifystats
@tabname varchar(62), /* user table name */
@colname varchar(30), /* column name */
@option varchar(60) /* statistic to be modified */
as
/*
**
** Description: Allow sa to modify statistics stored
** in sysstatistics.
**
** Options: "REMOVE_SKEW_FROM_DENSITY" -
** This option allows sa to change the
** total density of a column to be equal
** to the range density which is useful
** when data skew is present.
** This will also update total density
** of any index(es) that have the specified
** column as the leading column of the index.
**
** Future Info: Other options can be added in the future
** using the @option parameter.
**
** Dependencies:This proc relies on the object_id built-in.
**
** Errors: This proc relies on the following errors:
** 17461, 18162
** Errors 18800 and 18803 are installed for
this proc.
**
** Version: This proc is for ASE 11.9.x and beyond
**
** Usage: sp_modifystats tabname, colname, opt
**
** History: 9/16/99 (mciccone)
*/

declare @rows int /* Variable to check @@rowcount */
declare @err int /* Variable to check @@error */
declare @colid int /* Variable to hold colid from syscolumns */

/* Check Usage */

if @tabname is null
or @colname is null
or @option is null
begin
print "Usage: sp_modifystats tabname, colname, option"
print "Valid Options: REMOVE_SKEW_FROM_DENSITY"
return(1)
end

if @@trancount = 0
begin
set chained off
end

set transaction isolation level 1

/* Check if user has SA role, proc_role will also do auditing
** if required. proc_role will also print error message if required.
*/
if (proc_role("sa_role") = 0)
return (1)

/*
** Check to see if the @tabname is in sysobjects.
*/
if not exists (select *
from sysobjects
where id = object_id(@tabname))
begin
/* 17461, "Object does not exist in this database." */
raiserror 17461
return (1)
end

/* Make sure the @tabname is NOT a system table */
if @tabname LIKE "sys%"
begin
/*
** 18162: '%1!' is a not a user table.
** '%2!' can be used only on user tables.
*/
raiserror 18162, @tabname, "sp_modifystats"
return (1)
end

/* Check to see if the @colname is in syscolumns */
if not exists (select * from syscolumns
where id = object_id(@tabname) and name = @colname)
begin
/* 17461: "Object does not exist in this database." */
raiserror 17461
return(1)
end

if @option = "REMOVE_SKEW_FROM_DENSITY"
/**Update total density for specified column**/
begin
select @colid = (select colid from syscolumns where
id = object_id(@tabname) and name = @colname)
begin tran
/* Change the total density & update STATISTICS_EDITED flag */
update sysstatistics set c3 = c2,
c1 = convert (varbinary,
(convert(int, c1) | 2)) where
convert(tinyint, substring(colidarray, 1, 1)) = @colid and
id = object_id(@tabname) and
formatid=100
select @rows = @@rowcount, @err=@@error
if @err != 0
begin
select @err
rollback tran
return(1)
end
if @rows >= 1
begin
/* Successful update */
commit tran
return(0)
end
else
begin
raiserror 18803, @colname
rollback tran
return(1)
end
end
else
begin
raiserror 18800, @option
return(1)
end
go
/* Granting permission on sp_modifystats */
grant execute on sp_modifystats to public
go


/* Disabling allow updates to system tables */
sp_configure "allow updates to system tables", 0
go

--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Andy Price Posted on 2000-06-28 15:21:58.0Z
From: "Andy Price" <andy.price@canada.com>
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Wed, 28 Jun 2000 16:21:58 +0100
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <4mi$LXR4$GA.288@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
NNTP-Posting-Host: dyn157-ras36.screaming.net 212.188.131.157
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3720 sybase.public.sqlserver.nt:578
Article PK: 1074677

Just check the access paths using showplan and see what is going on. That's
all I can suggest. I've noticed a 15% improvement on average with the
production server I work on, which also uses the HA option for cluster
support. I haven't experienced the problem you are having with the SQL.

Andy

"Eugene Valentinov" <valentinov@citkami.ru> wrote in message
news:395A1030.537B2E71@citkami.ru...
> Andy, of course we've run update all statistics on every table. Also all
views
> and procedures were recompiled, tempdb was expanded, all dbcc checks are
done (
> except of dbcc checkstorage ).
> Any more ideas ?
>
> Andy Price wrote:
>
> > Eugene,
> >
> > As part of your upgrade, did you update all the statistics for all
tables in
> > the v12 server? V11.9.2 and v12 stores stats for all columns in the
tables,
> > provided you update statistics, rather than just for the index columns.
This
> > enables v11.9.2+ optimiser to make better choices about its query
execution
> > plans. To make it an even test, ensure that you are using the allpages
> > locking scheme in v12 rather than datapages or datarows, or your tests
will
> > not be comparable.
> >
> > --
> > Andy Price
> > Sybase Certified Adaptive Server Administrator - Associate v11.5
> >
> > "Eugene Valentinov" <valentinov@citkami.ru> wrote in message
> > news:3959CBD7.9A9EEAFF@citkami.ru...
> > > Hi !
> > > We have two ASE installations on a NT box: 11.5 and 12.0. [To make it
> > > possible we run our servers from the DOS prompt ( run_***.bat ).]
> > > Configuation parameters are the same.
> > > Also we have PowerBuilder client working with them via the native 11
> > > driver.
> > >
> > > Question is: why 12 server can be 1.5-2.5 times slower in SELECT tasks
> > > then 11.5 ? [ both in data/allpages lock schemes ]
> > > UPDATES are not tested...
> > >
> > > So, how can I do this experiment in clearest way?
> > > Any comments/tips are welcome!
> > >
> > > Best regards, Eugene
> > >
>


Eugene Valentinov Posted on 2000-06-29 09:54:05.0Z
Message-ID: <395B1CBD.25500D89@citkami.ru>
Date: Thu, 29 Jun 2000 13:54:05 +0400
From: Eugene Valentinov <valentinov@citkami.ru>
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <$L58EqO4$GA.204@forums.sybase.com> <395A1030.537B2E71@citkami.ru> <4mi$LXR4$GA.288@forums.sybase.com>
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general,sybase.public.sqlserver.nt
Lines: 61
NNTP-Posting-Host: dialup-000055.magelan.ru 212.11.193.55
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:3718 sybase.public.sqlserver.nt:576
Article PK: 1074675

Andy!

The plan in 12th is more complex and less eficient than the 11.5th.
Moreover the simple query like 'select * from table' takes 30% more time on 12
server.
At the same time, your installaion takes us the reason for optimism.
Thank you, but we still have the problem.

Andy Price wrote:

> Just check the access paths using showplan and see what is going on. That's
> all I can suggest. I've noticed a 15% improvement on average with the
> production server I work on, which also uses the HA option for cluster
> support. I haven't experienced the problem you are having with the SQL.
>
> Andy
>
> "Eugene Valentinov" <valentinov@citkami.ru> wrote in message
> news:395A1030.537B2E71@citkami.ru...
> > Andy, of course we've run update all statistics on every table. Also all
> views
> > and procedures were recompiled, tempdb was expanded, all dbcc checks are
> done (
> > except of dbcc checkstorage ).
> > Any more ideas ?
> >
> > Andy Price wrote:
> >
> > > Eugene,
> > >
> > > As part of your upgrade, did you update all the statistics for all
> tables in
> > > the v12 server? V11.9.2 and v12 stores stats for all columns in the
> tables,
> > > provided you update statistics, rather than just for the index columns.
> This
> > > enables v11.9.2+ optimiser to make better choices about its query
> execution
> > > plans. To make it an even test, ensure that you are using the allpages
> > > locking scheme in v12 rather than datapages or datarows, or your tests
> will
> > > not be comparable.
> > >
> > > --
> > > Andy Price
> > > Sybase Certified Adaptive Server Administrator - Associate v11.5
> > >
> > > "Eugene Valentinov" <valentinov@citkami.ru> wrote in message
> > > news:3959CBD7.9A9EEAFF@citkami.ru...
> > > > Hi !
> > > > We have two ASE installations on a NT box: 11.5 and 12.0. [To make it
> > > > possible we run our servers from the DOS prompt ( run_***.bat ).]
> > > > Configuation parameters are the same.
> > > > Also we have PowerBuilder client working with them via the native 11
> > > > driver.
> > > >
> > > > Question is: why 12 server can be 1.5-2.5 times slower in SELECT tasks
> > > > then 11.5 ? [ both in data/allpages lock schemes ]
> > > > UPDATES are not tested...
> > > >
> > > > So, how can I do this experiment in clearest way?
> > > > Any comments/tips are welcome!
> > > >
> > > > Best regards, Eugene
> > > >
> >


Eric Miner Posted on 2000-07-10 19:35:23.0Z
Message-ID: <396A257B.89D180BD@sybase.com>
Date: Mon, 10 Jul 2000 12:35:23 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Eugene Valentinov <valentinov@citkami.ru>
CC: eminer@sybase.com, sybase.public.sqlserver.performance+tuning@sybase.com
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 115
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7252
Article PK: 1078237

Let's go over a few points.

Since you're going from 11.5 to 12.0 the datatype mismatch issues is mute.
But, if anyone reading this is going from 11.0.x to 11.9.2 or above check
for mismatched dtattypes.

1. It's not necessary, or advisable, to run update all statistics. The
maintenance costs are massive and the pay off is low. It is advisable to
run update index statistics on your composite indexes. There will be
maintenance costs, but the pay off in efficient plans will make it
worthwhile.

2. How was the dataset moved to 12.0? Are page and row counts identical in
both 11.5 and 12.0? Can you provide 302 output from the same query run on
both server? That would be very useful in analyzing this for you.

3.>I made extensive use of OPTDIAG.EXE to find situations where the

>statistics for indexed columns were either not correct or not optimal

>when compared to 11.5.
Statistics in 11.9.2 and above are far more accurate than in previous
versions. There are also new statistics used in optimization (the cluster
ratios, deleted rows, forwarded rows, empty pages). I have not seen a case
where stats are 'not correct'. More accurate stats result in the optimizer
having a clearer picture of the index(es) involved. The stats in
pre-11.9.2 were rather rudimentary compared to later versions. Viewing
stats will not give you an indication of whether they're optimal or not.
That will depend on the SARG or join against the column(s). Optdiag is a
very powerful and flexible tool, it can help a lot. Take a look at this
white paper for some interesting uses:
http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=20472

4.>I also had to use the system procedure
>sp_modifystats to adjust the skew in one of my indexes.
Sp_modifystats is a brute-force 'shotgun' approach to getting around cases
where data skew within a column is effecting join costing. The optimizer
uses only one statistical value to cost joins, the Total density. If there
is data skew in the column (a few values occupying many rows each and many
values occupying a few rows each) the Total density value will become
pessimistic. That is, it will generally believe that more rows from the
column will qualify for the join than really will. Sp_modifystats does one
thing, it sets the Total density to equal the Range cell density. The
Range cell density is a measure of the density of all values that are not
highly duplicated (that are not in frequency count cells). In many cases
this works fine. But, you need to keep in mind that sp_modifystats can
result in join costing that is more optimistic, with the possibility of
also producing inefficient plans. In the next major release of ASE all
this changes and data skew will not have an effect on join costing. BTW -
if the column does not participate in any joins there is no need to even
consider sp_modifystats.

5. >It seems to me the problem is in incorrect statistics.
It is unlikely that the stats are 'incorrect'. However, they are more
accurate and flexible. Take a look at optdiag for the table. If the DB
went through upgrade and you did not delete stats the number of steps
(cells) should be very close to what was in 11.5. If you built extra stats
they'll have the default number of cells (20) you may want to increase the
number of steps. This will make the stats more granular and give the
optimizer a better picture of the column.

6. >However, my understanding is that the optimization in 12.0 is very
similar to 11.9.2.
In the area of the statistics there is little difference. However, there
is some new functionality in 12.0 optimization. Did you by chance enable
sort merge joins? There are changes to the optimization of LIKE
predicates. Join transitive closure has been added as has predicate
transformation (making ANDs out of ORs). BTW - you an use abstract plans
when testing the optimizer.

Hope this helps a bit,

Eric Miner
ASE Engineering
Optimizer Group

Eugene Valentinov wrote:

> Hi !
> We have two ASE installations on a NT box: 11.5 and 12.0. [To make it
> possible we run our servers from the DOS prompt ( run_***.bat ).]
> Configuation parameters are the same.
> Also we have PowerBuilder client working with them via the native 11
> driver.
>
> Question is: why 12 server can be 1.5-2.5 times slower in SELECT tasks
> then 11.5 ? [ both in data/allpages lock schemes ]
> UPDATES are not tested...
>
> So, how can I do this experiment in clearest way?
> Any comments/tips are welcome!
>
> Best regards, Eugene


Jim Egan Posted on 2000-07-11 03:25:42.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Mon, 10 Jul 2000 22:25:42 -0500
Message-ID: <MPG.13d43f21dfb6240398aa97@199.93.177.77>
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.general
Lines: 42
NNTP-Posting-Host: user-33qs1cg.dialup.mindspring.com 199.174.5.144
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7247
Article PK: 1078232

>>
Sp_modifystats is a brute-force 'shotgun' approach to getting around
cases where data skew within a column is effecting join costing.
<<

Call it what you want, but in my case where the skew was sufficient to
make the join a piece of crap sp_modifystats was a bullseye shot from a
marksman's rifle. But, in the optimizer's defense I will say that I
think the original database/business rule design that causes this skew
was poorly thought out and causes application problems also.

I look forward to the release where we don't have to run sp_modifystats
after UPDATE STATISTICS. The stuff you guys are working sounds exciting.
I do think that 12.0 was an awesome release. We have jumped all over
some of the new features. Enable/Disable Trigger is sooooo nice.

As long as I have your attention I would like to pose a question (or
two) to you. I have converted our tables with the most contention to
DataPage and DataRow locking. This has worked out pretty good and now
we're down to fine tuning. Why would I not just go ahead and convert all
my tables to DataPage locking? So far the forwarded rows have been
manageable and I don't expect them to be a problem in the future.

You asked if I have enabled the new sort-merge join. No, I haven't. I
did some research in the InfoBase to try and get a handle on what it was
and the potential problems. I'm still not very clear on it but I saw a
number of problems where the solution was to turn off sort-merge.
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Eric Miner Posted on 2000-07-11 21:04:15.0Z
Message-ID: <396B8BCF.2F625361@sybase.com>
Date: Tue, 11 Jul 2000 14:04:15 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: eganjp@compuserve.com
CC: eminer@sybase.com
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <MPG.13d43f21dfb6240398aa97@199.93.177.77>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 90
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7238
Article PK: 1078223

Hi Jim

Jim Egan wrote:

> >>
> Sp_modifystats is a brute-force 'shotgun' approach to getting around
> cases where data skew within a column is effecting join costing.
> <<
>
> Call it what you want, but in my case where the skew was sufficient to
> make the join a piece of crap sp_modifystats was a bullseye shot from a
> marksman's rifle. But, in the optimizer's defense I will say that I
> think the original database/business rule design that causes this skew
> was poorly thought out and causes application problems also.
>

>Well, my point is that one needs to keep in mind that setting the Total

density to a very low number is likely to work very well for some joins but
has an equal possibility of adversely effecting others. My suggestion is that
you use optdiag to change the total density value so a lower number than a
skewed Total density but not a number close to 0. Keep in mind that the Total
density value is used for all joins and if there's a SARG value that includes
the skew values the optimizer may be overly optimistic about the column.
BTW - did you get modstats from Vaughn in TS? I remember him calling me on a
case he had with you, I suggested he grab it and send it to you.

> I look forward to the release where we don't have to run sp_modifystats
> after UPDATE STATISTICS. The stuff you guys are working sounds exciting.

>It is fun stuff - costing of SARGs at the same time joins are being costed
(star join support), using the stats based on the SARG values and the join
statements (modifystats will be obsolete), much more powerful PLL
optimization with the ability to configure values for your various resources
(disc, cpu, memory, network), the ability to set 'degrees of optimization'
how much work do you want the optimizer to do before making a final decision,
much faster update statistics based on sampling....and there's more.

>
> I do think that 12.0 was an awesome release. We have jumped all over
> some of the new features. Enable/Disable Trigger is sooooo nice.
>
> As long as I have your attention I would like to pose a question (or
> two) to you. I have converted our tables with the most contention to
> DataPage and DataRow locking. This has worked out pretty good and now
> we're down to fine tuning. Why would I not just go ahead and convert all
> my tables to DataPage locking? So far the forwarded rows have been
> manageable and I don't expect them to be a problem in the future.

>I for one see no reason not to use all APL tables. One thing to keep in mind
is that on APL table update stats must use isolation level 1 rather than 0
for DOL table, but this is not all that big a deal. Another issue is that for
DOL tables unique indexes are no longer required by updateable cursors.

>
> You asked if I have enabled the new sort-merge join. No, I haven't. I
> did some research in the InfoBase to try and get a handle on what it was
> and the potential problems. I'm still not very clear on it but I saw a
> number of problems where the solution was to turn off sort-merge.

> Nobody's implementation of sort-merge is intended for all joins. There are
time when it will be less efficient than nested loop (primarily in OLTP
apps). I would suggest that if you want to use sort-merge that you test it
first. You may need to tune for it too. Don't turn it on in production just
to try it out ;)

>
> --
> Jim Egan [TeamSybase]
> Houston, TX
> http://www.eganomics.com
>
> Sybase Developers Network
> http://sdn.sybase.com/sdn/mec/mec_home.stm


Anthony Mandic <amandic Posted on 2000-07-11 01:17:23.0Z
Message-ID: <396A75A3.D0DBC66C@_start.com.au>
Date: Tue, 11 Jul 2000 11:17:23 +1000
From: Anthony Mandic <amandic@_start.com.au>
Organization: Mandic Consulting Pty. Ltd.
X-Mailer: Mozilla 4.06 [en] (X11; I; SunOS 5.5.1 sun4m)
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 87
NNTP-Posting-Host: pix208a.magna.com.au 203.111.111.208
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7248
Article PK: 1078233


Eric Miner wrote:
>
> Let's go over a few points.

Must we? :-)

> Since you're going from 11.5 to 12.0 the datatype mismatch issues is mute.
> But, if anyone reading this is going from 11.0.x to 11.9.2 or above check
> for mismatched dtattypes.

How do user defined datatypes fit into the picture with datatype
mismatches? I observed a problem recently in ASE 11.9.2 where a
PeopleSoft database had a user defined datatype for datetime.
A select into into a temp table from a table with several of
these defined had resulted in either the datetime or datetimn
datatype being used instead with no noticeable pattern. I suspect
that this might have just been a problem restricted to select into
but it may well go deeper.

> 1. It's not necessary, or advisable, to run update all statistics. The
> maintenance costs are massive and the pay off is low. It is advisable to
> run update index statistics on your composite indexes. There will be
> maintenance costs, but the pay off in efficient plans will make it
> worthwhile.

I looked at "update all statistics" once. It filled up tempdb.
The table was large, but tempdb was also large. Can you explain
what may have been going on here?

You may also want to mention running "update partition statistics"
on partitioned tables. I got caught out by that one due to the
documentation not being quite up to par. You might also want to
elaborate on the differences between the old "update statistics"
and "update index statistics". The docs may not be clear enough
on the distinction for some dbas. (Looks like the making of a
conference presentation doesn't it?)

> 2. How was the dataset moved to 12.0? Are page and row counts identical in
> both 11.5 and 12.0? Can you provide 302 output from the same query run on
> both server? That would be very useful in analyzing this for you.

Can you discuss the merits of various migration methods (in place,
dump/load or bcp) with respect to issues internal to the server
(sysgams comes to mind)?

> 3.>I made extensive use of OPTDIAG.EXE to find situations where the
> >statistics for indexed columns were either not correct or not optimal
> >when compared to 11.5.
> Statistics in 11.9.2 and above are far more accurate than in previous
> versions. There are also new statistics used in optimization (the cluster
> ratios, deleted rows, forwarded rows, empty pages). I have not seen a case
> where stats are 'not correct'. More accurate stats result in the optimizer
> having a clearer picture of the index(es) involved. The stats in
> pre-11.9.2 were rather rudimentary compared to later versions. Viewing
> stats will not give you an indication of whether they're optimal or not.

I suspect that the issue is more of which update stats method is
employed. This just boils down to how clear the docs are on the
topic.

> 4.>I also had to use the system procedure
> >sp_modifystats to adjust the skew in one of my indexes.
> Sp_modifystats is a brute-force 'shotgun' approach to getting around cases
> where data skew within a column is effecting join costing.
...
> In the next major release of ASE all
> this changes and data skew will not have an effect on join costing.

That is good to hear! But what other implications does it have?
(Reading between the lines, it seems to imply that a major change
is in the works for stats.)

-am


Eric McGrane Posted on 2000-07-12 13:13:20.0Z
From: "Eric McGrane" <emcgrane@docharbor.net>
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Wed, 12 Jul 2000 09:13:20 -0400
Lines: 97
X-Newsreader: Microsoft Outlook Express 5.00.2919.6700
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700
Message-ID: <Qh5$2QA7$GA.304@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.general
NNTP-Posting-Host: anacomp-ne.com 216.132.181.130
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7230
Article PK: 1078211

Anthony,

The issue with select into resulting in the underlying datatype or a
datetypen is a bug. I worked on this a few months ago for couple of
customers. Alas, I recently decided to move on from Sybase and am now in
the "real world" so I no longer have access to the bug database, etc.
Contact Tech Support and they should be able to provide you with the CR
number for the problem as well as the version of ASE it is fixed in.

Regards,
Eric

"Anthony Mandic" <amandic@_start.com.au> wrote in message
news:396A75A3.D0DBC66C@_start.com.au...
> Eric Miner wrote:
> >
> > Let's go over a few points.
>
> Must we? :-)
>
> > Since you're going from 11.5 to 12.0 the datatype mismatch issues is
mute.
> > But, if anyone reading this is going from 11.0.x to 11.9.2 or above
check
> > for mismatched dtattypes.
>
> How do user defined datatypes fit into the picture with datatype
> mismatches? I observed a problem recently in ASE 11.9.2 where a
> PeopleSoft database had a user defined datatype for datetime.
> A select into into a temp table from a table with several of
> these defined had resulted in either the datetime or datetimn
> datatype being used instead with no noticeable pattern. I suspect
> that this might have just been a problem restricted to select into
> but it may well go deeper.
>
> > 1. It's not necessary, or advisable, to run update all statistics. The
> > maintenance costs are massive and the pay off is low. It is advisable to
> > run update index statistics on your composite indexes. There will be
> > maintenance costs, but the pay off in efficient plans will make it
> > worthwhile.
>
> I looked at "update all statistics" once. It filled up tempdb.
> The table was large, but tempdb was also large. Can you explain
> what may have been going on here?
>
> You may also want to mention running "update partition statistics"
> on partitioned tables. I got caught out by that one due to the
> documentation not being quite up to par. You might also want to
> elaborate on the differences between the old "update statistics"
> and "update index statistics". The docs may not be clear enough
> on the distinction for some dbas. (Looks like the making of a
> conference presentation doesn't it?)
>
> > 2. How was the dataset moved to 12.0? Are page and row counts identical
in
> > both 11.5 and 12.0? Can you provide 302 output from the same query run
on
> > both server? That would be very useful in analyzing this for you.
>
> Can you discuss the merits of various migration methods (in place,
> dump/load or bcp) with respect to issues internal to the server
> (sysgams comes to mind)?
>
> > 3.>I made extensive use of OPTDIAG.EXE to find situations where the
> > >statistics for indexed columns were either not correct or not
optimal
> > >when compared to 11.5.
> > Statistics in 11.9.2 and above are far more accurate than in previous
> > versions. There are also new statistics used in optimization (the
cluster
> > ratios, deleted rows, forwarded rows, empty pages). I have not seen a
case
> > where stats are 'not correct'. More accurate stats result in the
optimizer
> > having a clearer picture of the index(es) involved. The stats in
> > pre-11.9.2 were rather rudimentary compared to later versions. Viewing
> > stats will not give you an indication of whether they're optimal or not.
>
> I suspect that the issue is more of which update stats method is
> employed. This just boils down to how clear the docs are on the
> topic.
>
> > 4.>I also had to use the system procedure
> > >sp_modifystats to adjust the skew in one of my indexes.
> > Sp_modifystats is a brute-force 'shotgun' approach to getting around
cases
> > where data skew within a column is effecting join costing.
> ...
> > In the next major release of ASE all
> > this changes and data skew will not have an effect on join costing.
>
> That is good to hear! But what other implications does it have?
> (Reading between the lines, it seems to imply that a major change
> is in the works for stats.)
>
> -am


Anthony Mandic Posted on 2000-07-13 06:14:51.0Z
Message-ID: <396D5E5B.A6CB6298@lumina.com.au>
Date: Thu, 13 Jul 2000 16:14:51 +1000
From: Anthony Mandic <am@lumina.com.au>
Organization: ... luminous thru all these years ...
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au> <Qh5$2QA7$GA.304@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 19
NNTP-Posting-Host: 210.9.51.46
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7218
Article PK: 1078203


Eric McGrane wrote:

> The issue with select into resulting in the underlying datatype or a
> datetypen is a bug. I worked on this a few months ago for couple of
> customers. Alas, I recently decided to move on from Sybase and am now in
> the "real world" so I no longer have access to the bug database, etc.
> Contact Tech Support and they should be able to provide you with the CR
> number for the problem as well as the version of ASE it is fixed in.

I had looked it up actually. But my hands are tied by PeopleSoft's
stupid EBF validation policy. You wouldn't believe how far behind
they are. And not only that, but they validate per PS version as well.
And these aren't in sync. What a headache.

-am


Eric Miner Posted on 2000-07-11 22:01:17.0Z
Message-ID: <396B992C.FFF38CED@sybase.com>
Date: Tue, 11 Jul 2000 15:01:17 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Anthony Mandic <amandic@_start.com.au>
CC: eminer@sybase.com
Subject: More - Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 120
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7236
Article PK: 1078222

Opps, I forgot something.

>You may also want to mention running "update partition statistics"
> on partitioned tables. I got caught out by that one due to the
> documentation not being quite up to par. You might also want to
> elaborate on the differences between the old "update statistics"
> and "update index statistics". The docs may not be clear enough
> on the distinction for some dbas. (Looks like the making of a
> conference presentation doesn't it?)

Update statistics (in both 11.9.2 and pre-11.9.2) will create or update stats
only on the leading column of the specified index or all indexes on the
specified table. In 11.9.2 update index stats will create or update stats on all

columns of the specified index or all indexes of the specified table.

Thanks I forgot about partition stats. You don't need to run update all stats to

do this. Run update partitions stats, it'll be a lot faster.

Eric

Anthony Mandic wrote:

> Eric Miner wrote:
> >
> > Let's go over a few points.
>
> Must we? :-)
>
> > Since you're going from 11.5 to 12.0 the datatype mismatch issues is mute.
> > But, if anyone reading this is going from 11.0.x to 11.9.2 or above check
> > for mismatched dtattypes.
>
> How do user defined datatypes fit into the picture with datatype
> mismatches? I observed a problem recently in ASE 11.9.2 where a
> PeopleSoft database had a user defined datatype for datetime.
> A select into into a temp table from a table with several of
> these defined had resulted in either the datetime or datetimn
> datatype being used instead with no noticeable pattern. I suspect
> that this might have just been a problem restricted to select into
> but it may well go deeper.
>
> > 1. It's not necessary, or advisable, to run update all statistics. The
> > maintenance costs are massive and the pay off is low. It is advisable to
> > run update index statistics on your composite indexes. There will be
> > maintenance costs, but the pay off in efficient plans will make it
> > worthwhile.
>
> I looked at "update all statistics" once. It filled up tempdb.
> The table was large, but tempdb was also large. Can you explain
> what may have been going on here?
>
> You may also want to mention running "update partition statistics"
> on partitioned tables. I got caught out by that one due to the
> documentation not being quite up to par. You might also want to
> elaborate on the differences between the old "update statistics"
> and "update index statistics". The docs may not be clear enough
> on the distinction for some dbas. (Looks like the making of a
> conference presentation doesn't it?)
>
> > 2. How was the dataset moved to 12.0? Are page and row counts identical in
> > both 11.5 and 12.0? Can you provide 302 output from the same query run on
> > both server? That would be very useful in analyzing this for you.
>
> Can you discuss the merits of various migration methods (in place,
> dump/load or bcp) with respect to issues internal to the server
> (sysgams comes to mind)?
>
> > 3.>I made extensive use of OPTDIAG.EXE to find situations where the
> > >statistics for indexed columns were either not correct or not optimal
> > >when compared to 11.5.
> > Statistics in 11.9.2 and above are far more accurate than in previous
> > versions. There are also new statistics used in optimization (the cluster
> > ratios, deleted rows, forwarded rows, empty pages). I have not seen a case
> > where stats are 'not correct'. More accurate stats result in the optimizer
> > having a clearer picture of the index(es) involved. The stats in
> > pre-11.9.2 were rather rudimentary compared to later versions. Viewing
> > stats will not give you an indication of whether they're optimal or not.
>
> I suspect that the issue is more of which update stats method is
> employed. This just boils down to how clear the docs are on the
> topic.
>
> > 4.>I also had to use the system procedure
> > >sp_modifystats to adjust the skew in one of my indexes.
> > Sp_modifystats is a brute-force 'shotgun' approach to getting around cases
> > where data skew within a column is effecting join costing.
> ...
> > In the next major release of ASE all
> > this changes and data skew will not have an effect on join costing.
>
> That is good to hear! But what other implications does it have?
> (Reading between the lines, it seems to imply that a major change
> is in the works for stats.)
>
> -am


Anthony Mandic Posted on 2000-07-13 06:12:03.0Z
Message-ID: <396D5DB3.9D25578D@lumina.com.au>
Date: Thu, 13 Jul 2000 16:12:03 +1000
From: Anthony Mandic <am@lumina.com.au>
Organization: ... luminous thru all these years ...
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: More - Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au> <396B992C.FFF38CED@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 20
NNTP-Posting-Host: 210.9.51.46
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7219
Article PK: 1078204


Eric Miner wrote:

> Update statistics (in both 11.9.2 and pre-11.9.2) will create or update stats
> only on the leading column of the specified index or all indexes on the
> specified table. In 11.9.2 update index stats will create or update stats on all
> columns of the specified index or all indexes of the specified table.

I think this distinction may need to be emphasised more clearly in the docs.
It seems that some people are being caught out by not understanding this.

> Thanks I forgot about partition stats. You don't need to run update all stats to
> do this. Run update partitions stats, it'll be a lot faster.

Yes, I know. But "all" will do both for you if you want both. But can you
explain what partition stats does?

-am


Eric Miner Posted on 2000-07-11 20:45:23.0Z
Message-ID: <396B8763.A023EF3A@sybase.com>
Date: Tue, 11 Jul 2000 13:45:23 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Anthony Mandic <amandic@_start.com.au>
CC: eminer@sybase.com
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 155
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7240
Article PK: 1078224

Hi Anthony,

See my comment following the --

Anthony Mandic wrote:

> Eric Miner wrote:
> >
> > Let's go over a few points.
>
> Must we? :-)

-- Yes Anthony, now open your book to page.... ;)

>
>
> > Since you're going from 11.5 to 12.0 the datatype mismatch issues is mute.
> > But, if anyone reading this is going from 11.0.x to 11.9.2 or above check
> > for mismatched dtattypes.
>
> How do user defined datatypes fit into the picture with datatype
> mismatches? I observed a problem recently in ASE 11.9.2 where a
> PeopleSoft database had a user defined datatype for datetime.
> A select into into a temp table from a table with several of
> these defined had resulted in either the datetime or datetimn
> datatype being used instead with no noticeable pattern. I suspect
> that this might have just been a problem restricted to select into
> but it may well go deeper.

--Hmmm...I honestly don't know what's happening here. I'm not surprised that the
select into used the actual datatype rather than the user defined. But, I'm not
sure why the datatypes would vary. Are you sure that the source table contains
only one form of datatime? Dumb question I know, but....

>
>
> > 1. It's not necessary, or advisable, to run update all statistics. The
> > maintenance costs are massive and the pay off is low. It is advisable to
> > run update index statistics on your composite indexes. There will be
> > maintenance costs, but the pay off in efficient plans will make it
> > worthwhile.
>
> I looked at "update all statistics" once. It filled up tempdb.
> The table was large, but tempdb was also large. Can you explain
> what may have been going on here?

--Yeah, this one is not well documented at all. Let's say we have a composite
index consisting of cols A and B with A as the leading col. If you run a the old
style update statistics (update stats table_name [index_name]) statistics are
updated for col A only. Since the index rows are sorted in col A order there's
no need do anything other than read the index rows to gather the stats. This is
still the case if you run a simple update stats in 11.9.2 +. However, (ain't
there always one?) since stats are now an attribute of a col and not an index we
can place stats on any col we want. Let's say we're on 11.9.2+ and we run update
index statistics on the above table. We can read the index rows for col A
without the need to sort them. But, we must read the index rows for col B into a
temp table, sort them and read them from there to gather the stats. This is
where the space consumption comes from.

>(Looks like the making of a conference presentation doesn't it?)
Well, if you don't mind a shameless plug ..... If anyone's interested I'll be
talking about this in DM214 and Techwave.

>
>
> You may also want to mention running "update partition statistics"
> on partitioned tables. I got caught out by that one due to the
> documentation not being quite up to par. You might also want to
> elaborate on the differences between the old "update statistics"
> and "update index statistics". The docs may not be clear enough
> on the distinction for some dbas. (Looks like the making of a
> conference presentation doesn't it?)
>
> > 2. How was the dataset moved to 12.0? Are page and row counts identical in
> > both 11.5 and 12.0? Can you provide 302 output from the same query run on
> > both server? That would be very useful in analyzing this for you.
>
> Can you discuss the merits of various migration methods (in place,
> dump/load or bcp) with respect to issues internal to the server
> (sysgams comes to mind)?

--Well, I'm not a migration expert. But, when you're comparing optimization in
two different versions, or are setting up an environment to test the optimizer
the best way to copy the dataset over is via dump and load (if possible). This
will keep things like fragmentiaon intact. If you BCP the data in you have two
different datasets as far as the optimizer is concernd. This is because things
like empty data/index pages have a direct effect on optimization.

>
>
> > 3.>I made extensive use of OPTDIAG.EXE to find situations where the
> > >statistics for indexed columns were either not correct or not optimal
> > >when compared to 11.5.
> > Statistics in 11.9.2 and above are far more accurate than in previous
> > versions. There are also new statistics used in optimization (the cluster
> > ratios, deleted rows, forwarded rows, empty pages). I have not seen a case
> > where stats are 'not correct'. More accurate stats result in the optimizer
> > having a clearer picture of the index(es) involved. The stats in
> > pre-11.9.2 were rather rudimentary compared to later versions. Viewing
> > stats will not give you an indication of whether they're optimal or not.
>
> I suspect that the issue is more of which update stats method is
> employed. This just boils down to how clear the docs are on the
> topic.

-- Yes, more stats and more accurate stats can now be obtained depending on what
you want.

>
>
> > 4.>I also had to use the system procedure
> > >sp_modifystats to adjust the skew in one of my indexes.
> > Sp_modifystats is a brute-force 'shotgun' approach to getting around cases
> > where data skew within a column is effecting join costing.
> ...
> > In the next major release of ASE all
> > this changes and data skew will not have an effect on join costing.
>
> That is good to hear! But what other implications does it have?
> (Reading between the lines, it seems to imply that a major change
> is in the works for stats.)

-- The stats are not going to change, that work has already been done. The basic
engine of the optimizer will be rewritten. I'll be talking about this at
Techwave too and our chief optimizer engineer will be doing a presentation on
the release in general.

>
>
> -am


Anthony Mandic Posted on 2000-07-13 06:03:20.0Z
Message-ID: <396D5BA8.D0985FC6@lumina.com.au>
Date: Thu, 13 Jul 2000 16:03:20 +1000
From: Anthony Mandic <am@lumina.com.au>
Organization: ... luminous thru all these years ...
X-Mailer: Mozilla 4.72 [en] (X11; U; Linux 2.2.14-5.0 i686)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au> <396B8763.A023EF3A@sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 117
NNTP-Posting-Host: 210.9.51.46
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7220
Article PK: 1078205


Eric Miner wrote:

> See my comment following the --
>
> Anthony Mandic wrote:
>
> > Eric Miner wrote:
> > >
> > > Let's go over a few points.
> >
> > Must we? :-)
>
> -- Yes Anthony, now open your book to page.... ;)

Yikes! What a flashback that brought on.

> --Hmmm...I honestly don't know what's happening here. I'm not surprised that the
> select into used the actual datatype rather than the user defined. But, I'm not
> sure why the datatypes would vary. Are you sure that the source table contains
> only one form of datatime? Dumb question I know, but....

Well, I wouldn't worry too much about it. It was CrippleSoft after all.
They defined a type called "PSDATE" which was just datetime. I did try
to define it in tempdb as an experiment but the select into didn't use
it. The resultant datatypes weren't a problem - just curiously odd.

> > > 1. It's not necessary, or advisable, to run update all statistics. The
> > > maintenance costs are massive and the pay off is low. It is advisable to
> > > run update index statistics on your composite indexes. There will be
> > > maintenance costs, but the pay off in efficient plans will make it
> > > worthwhile.
> >
> > I looked at "update all statistics" once. It filled up tempdb.
> > The table was large, but tempdb was also large. Can you explain
> > what may have been going on here?
>
> --Yeah, this one is not well documented at all. Let's say we have a composite
> index consisting of cols A and B with A as the leading col. If you run a the old
> style update statistics (update stats table_name [index_name]) statistics are
> updated for col A only. Since the index rows are sorted in col A order there's
> no need do anything other than read the index rows to gather the stats. This is
> still the case if you run a simple update stats in 11.9.2 +. However, (ain't
> there always one?) since stats are now an attribute of a col and not an index we
> can place stats on any col we want. Let's say we're on 11.9.2+ and we run update
> index statistics on the above table. We can read the index rows for col A
> without the need to sort them. But, we must read the index rows for col B into a
> temp table, sort them and read them from there to gather the stats. This is
> where the space consumption comes from.

OK, so its just an issue of ensuring that tempdb is large enough to
hadle this.

> >(Looks like the making of a conference presentation doesn't it?)
> Well, if you don't mind a shameless plug ..... If anyone's interested I'll be
> talking about this in DM214 and Techwave.

I think I'd better attend (when I'm not manning the ISUG booth)!

> > > 2. How was the dataset moved to 12.0? Are page and row counts identical in
> > > both 11.5 and 12.0? Can you provide 302 output from the same query run on
> > > both server? That would be very useful in analyzing this for you.
> >
> > Can you discuss the merits of various migration methods (in place,
> > dump/load or bcp) with respect to issues internal to the server
> > (sysgams comes to mind)?
>
> --Well, I'm not a migration expert. But, when you're comparing optimization in
> two different versions, or are setting up an environment to test the optimizer
> the best way to copy the dataset over is via dump and load (if possible). This
> will keep things like fragmentiaon intact. If you BCP the data in you have two
> different datasets as far as the optimizer is concernd. This is because things
> like empty data/index pages have a direct effect on optimization.

Well, we've both seen the posts concerning rebuilding indexes etc. after
a migration. I recall that elsewhere you suggested not doing that. This
was due to the stats. But what about new installations? What would their
issues be?

> -- Yes, more stats and more accurate stats can now be obtained depending on what
> you want.

I, for one, can't complain.

> > > In the next major release of ASE all
> > > this changes and data skew will not have an effect on join costing.
> >
> > That is good to hear! But what other implications does it have?
> > (Reading between the lines, it seems to imply that a major change
> > is in the works for stats.)
>
> -- The stats are not going to change, that work has already been done. The basic
> engine of the optimizer will be rewritten. I'll be talking about this at
> Techwave too and our chief optimizer engineer will be doing a presentation on
> the release in general.

Then I'd definitely better attend.

-am


Jim Egan Posted on 2000-07-13 11:28:56.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Thu, 13 Jul 2000 06:28:56 -0500
Message-ID: <MPG.13d763efcf92c1dd98aab4@199.93.177.77>
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au> <396B8763.A023EF3A@sybase.com> <396D5BA8.D0985FC6@lumina.com.au>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.general
Lines: 15
NNTP-Posting-Host: user-33qs0sb.dialup.mindspring.com 199.174.3.139
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7217
Article PK: 1078202

>>
Well, we've both seen the posts concerning rebuilding indexes etc. after
a migration. I recall that elsewhere you suggested not doing that. This
was due to the stats. But what about new installations? What would their
issues be?
<<

Your indexes may have enough histogram steps if there are a lot of
distinct values. The default number of steps is 20 and in my situation
that wasn't enough. My default (rule-of-thumb) is 200.
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Jim Egan Posted on 2000-07-14 01:43:54.0Z
From: Jim Egan <dbaguru@eganomics.com>
Subject: Re: Why 12.0 slower than 11.5 ?
Date: Thu, 13 Jul 2000 20:43:54 -0500
Message-ID: <MPG.13d82c51997a10cf98aab7@199.93.177.77>
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au> <396B8763.A023EF3A@sybase.com> <396D5BA8.D0985FC6@lumina.com.au> <MPG.13d763efcf92c1dd98aab4@199.93.177.77>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.20
Newsgroups: sybase.public.sqlserver.general
Lines: 6
NNTP-Posting-Host: user-33qs0of.dialup.mindspring.com 199.174.3.15
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7213
Article PK: 1078196

Oops. Make that "may NOT have enough histogram steps..."
--
Jim Egan [TeamSybase]
Houston, TX
http://www.eganomics.com

Sybase Developers Network
http://sdn.sybase.com/sdn/mec/mec_home.stm


Eric Miner Posted on 2000-07-14 18:09:58.0Z
Message-ID: <396F5776.B6FB713A@sybase.com>
Date: Fri, 14 Jul 2000 11:09:58 -0700
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: eganjp@compuserve.com
CC: eminer@sybase.com
Subject: Re: Why 12.0 slower than 11.5 ?
References: <3959CBD7.9A9EEAFF@citkami.ru> <396A257B.89D180BD@sybase.com> <396A75A3.D0DBC66C@_start.com.au> <396B8763.A023EF3A@sybase.com> <396D5BA8.D0985FC6@lumina.com.au> <MPG.13d763efcf92c1dd98aab4@199.93.177.77> <MPG.13d82c51997a10cf98aab7@199.93.177.77>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.general
Lines: 20
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.general:7203
Article PK: 1078186

To answer Jim and Anthony,

Yeah, there are likely to be times when increasing the step count is
what you'll need to do. This is particularly true when the default of 20
is not enough to produce frequency count cells (cells that represent a
single highly duplicated value). Also, if you're doing a lot of range
SARGs and your column has range cells more steps will result in narrower
cells and thus the estimate of how close to a boundary value a range
SARG value falls will be easier to arrive at.

LAter
Eric

Jim Egan wrote:

> Oops. Make that "may NOT have enough histogram steps..."
> --
> Jim Egan [TeamSybase]
> Houston, TX
> http://www.eganomics.com
>
> Sybase Developers Network
> http://sdn.sybase.com/sdn/mec/mec_home.stm