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.

Query running slow in 15.5

8 posts in Performance and Tuning Last posting was on 2012-12-21 17:29:09.0Z
Sid. Posted on 2012-12-19 22:43:25.0Z
Sender: 199e.50d240a9.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.performance+tuning
Subject: Query running slow in 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d2430c.1a6f.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 19 Dec 2012 14:43:25 -0800
X-Trace: forums-1-dub 1355957005 172.20.134.41 (19 Dec 2012 14:43:25 -0800)
X-Original-Trace: 19 Dec 2012 14:43:25 -0800, 172.20.134.41
Lines: 13
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13327
Article PK: 1158501

ASE 15.5/EBF 19399 64 bit Linux
We are planning to upgrading one of the ASE 12.5.4 to ASE
15.5. We are in the phase of analysing our sp performance.
One specific sp is running very slow. We are able to
identify the exact query inside the proc. The plan between
1254 and 15.5 are similar. Same as the estimated cost. In
1254 the proc runs in a minute. In 15 it takes 10 minutes.
15.5 host is more powerful than 1254, has similar memory or
more than our 1254 prod server and mostly I am the only user
in 15. Any help is appreciated.

Thanks,
Sid.


"Mark A. Parsons" <iron_horse Posted on 2012-12-19 23:28:44.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
References: <50d2430c.1a6f.1681692777@sybase.com>
In-Reply-To: <50d2430c.1a6f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50d24dac$1@forums-1-dub>
Date: 19 Dec 2012 15:28:44 -0800
X-Trace: forums-1-dub 1355959724 172.20.134.152 (19 Dec 2012 15:28:44 -0800)
X-Original-Trace: 19 Dec 2012 15:28:44 -0800, vip152.sybase.com
Lines: 18
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13328
Article PK: 1158502

To *start* you'll need to post the query and the 2 query plans.

If you could post the output from 'set statistics time,io on' that would be good, too.

On 12/19/2012 15:43, Sid. wrote:
> ASE 15.5/EBF 19399 64 bit Linux
> We are planning to upgrading one of the ASE 12.5.4 to ASE
> 15.5. We are in the phase of analysing our sp performance.
> One specific sp is running very slow. We are able to
> identify the exact query inside the proc. The plan between
> 1254 and 15.5 are similar. Same as the estimated cost. In
> 1254 the proc runs in a minute. In 15 it takes 10 minutes.
> 15.5 host is more powerful than 1254, has similar memory or
> more than our 1254 prod server and mostly I am the only user
> in 15. Any help is appreciated.
>
> Thanks,
> Sid.


Bret Halford Posted on 2012-12-20 00:05:44.0Z
From: Bret Halford <bret.halford@sap.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20121026 Thunderbird/16.0.2
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
References: <50d2430c.1a6f.1681692777@sybase.com> <50d24dac$1@forums-1-dub>
In-Reply-To: <50d24dac$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50d25658@forums-1-dub>
Date: 19 Dec 2012 16:05:44 -0800
X-Trace: forums-1-dub 1355961944 172.20.134.152 (19 Dec 2012 16:05:44 -0800)
X-Original-Trace: 19 Dec 2012 16:05:44 -0800, vip152.sybase.com
Lines: 42
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13329
Article PK: 1158504

Why 15.5 rather than 15.7.x?

You might also run sp_sysmon for a minute for both
queries and look for any values that differ greatly
between the two.

Did you do an upgrade of a 12.5.4 server, or install
a new 15.5 server and load a dump of the database?
The question relates to whether configuration settings
were inherited through the upgrade or created from
scratch.

Does the table involve a partitioned table? The upgrade
does unpartition tables from the 12.5.4 round robin partitioning
and you have to manually repartition them after deciding
which of the partitioning methods to use (15.5 added several
other partitioning methods).

-bret

On 12/19/2012 4:28 PM, Mark A. Parsons wrote:
> To *start* you'll need to post the query and the 2 query plans.
>
> If you could post the output from 'set statistics time,io on' that would
> be good, too.
>
> On 12/19/2012 15:43, Sid. wrote:
>> ASE 15.5/EBF 19399 64 bit Linux
>> We are planning to upgrading one of the ASE 12.5.4 to ASE
>> 15.5. We are in the phase of analysing our sp performance.
>> One specific sp is running very slow. We are able to
>> identify the exact query inside the proc. The plan between
>> 1254 and 15.5 are similar. Same as the estimated cost. In
>> 1254 the proc runs in a minute. In 15 it takes 10 minutes.
>> 15.5 host is more powerful than 1254, has similar memory or
>> more than our 1254 prod server and mostly I am the only user
>> in 15. Any help is appreciated.
>>
>> Thanks,
>> Sid.


Sid. Posted on 2012-12-20 14:26:08.0Z
Sender: 67cd.50d31d1b.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d32000.68e2.1681692777@sybase.com>
References: <50d25658@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 20 Dec 2012 06:26:08 -0800
X-Trace: forums-1-dub 1356013568 172.20.134.41 (20 Dec 2012 06:26:08 -0800)
X-Original-Trace: 20 Dec 2012 06:26:08 -0800, 172.20.134.41
Lines: 32
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13330
Article PK: 1158516


> Why 15.5 rather than 15.7.x?
>> Our Engineering hasn't certified 15.7.x yet.
>
> You might also run sp_sysmon for a minute for both
> queries and look for any values that differ greatly
> between the two.
>
> Did you do an upgrade of a 12.5.4 server, or install
> a new 15.5 server and load a dump of the database?
> The question relates to whether configuration settings
> were inherited through the upgrade or created from
> scratch.

>> Upgraded thru dump & load to a new server.
Configuration setting are almost similay or better. New
server is in a larger machine. 'Default data cache' has been
doubled.This is the only server in the box.
2 Kb 122880 Kb 0.00 Mb 34000.00 Mb 10
16 Kb 122880 Kb 5000.00 Mb 5000.00 Mb 10


>
> Does the table involve a partitioned table? The upgrade
> does unpartition tables from the 12.5.4 round robin
> partitioning and you have to manually repartition them
> after deciding which of the partitioning methods to use
> (15.5 added several other partitioning methods).

>> No partitioning is involved in any table.
>
> -bret
>
>


Sid. Posted on 2012-12-20 15:28:07.0Z
Sender: 6ebf.50d32e30.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d32e87.6ee3.1681692777@sybase.com>
References: <50d32000.68e2.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 20 Dec 2012 07:28:07 -0800
X-Trace: forums-1-dub 1356017287 172.20.134.41 (20 Dec 2012 07:28:07 -0800)
X-Original-Trace: 20 Dec 2012 07:28:07 -0800, 172.20.134.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13332
Article PK: 1158520


> > Why 15.5 rather than 15.7.x?
> >> Our Engineering hasn't certified 15.7.x yet.
> >
> > You might also run sp_sysmon for a minute for both
> > queries and look for any values that differ greatly
> > between the two.
> >
> > Did you do an upgrade of a 12.5.4 server, or install
> > a new 15.5 server and load a dump of the database?
> > The question relates to whether configuration settings
> > were inherited through the upgrade or created from
> > scratch.
> >> Upgraded thru dump & load to a new server.
> Configuration setting are almost similay or better. New
> server is in a larger machine. 'Default data cache' has
> been doubled.This is the only server in the box.
> 2 Kb 122880 Kb 0.00 Mb 34000.00 Mb 10
> 16 Kb 122880 Kb 5000.00 Mb 5000.00 Mb 10
>
>
> >
> > Does the table involve a partitioned table? The upgrade
> > does unpartition tables from the 12.5.4 round robin
> > partitioning and you have to manually repartition them
> > after deciding which of the partitioning methods to use
> > (15.5 added several other partitioning methods).
>
> >> No partitioning is involved in any table.
> >
> > -bret
> >
> >

Also is there any reason why am I seeing the few portion
plane being repeated several time in showplan report?


Sid. Posted on 2012-12-20 15:01:58.0Z
Sender: 67cd.50d31d1b.1804289383@sybase.com
From: Sid.
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d32866.6c74.1681692777@sybase.com>
References: <50d24dac$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 20 Dec 2012 07:01:58 -0800
X-Trace: forums-1-dub 1356015718 172.20.134.41 (20 Dec 2012 07:01:58 -0800)
X-Original-Trace: 20 Dec 2012 07:01:58 -0800, 172.20.134.41
Lines: 266
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13331
Article PK: 1158518


> To *start* you'll need to post the query and the 2 query
> plans.
>
> If you could post the output from 'set statistics time,io
> on' that would be good, too.
>

This is the portion of the plan that has slownedd.

ASE 12.5 Plan:
QUERY PLAN FOR STATEMENT 200 (at line 1083).


STEP 1
The type of query is UPDATE.
The update mode is deferred_varcol.

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

FROM TABLE
fish_ref..ProductDerivative
EXISTS TABLE : nested iteration.
Index : UProdDervIdx1
Forward scan.
Positioning by key.
Keys are:
ProductKey ASC
ToDate ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
fish_ref..Product
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : Product_idx1
Forward scan.
Positioning by key.
Keys are:
ProductKey ASC
Using I/O Size 2 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
product..synonym
EXISTS TABLE : nested iteration.
Index : synonym_n1
Forward scan.
Positioning by key.
Keys are:
synonym_c ASC
synonym_type_cd_c ASC
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
Using I/O Size 16 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
product..entity_product_role
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : entity_pro_8480060521
Forward scan.
Positioning by key.
Keys are:
product_id_i ASC
role_cd_c ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
product..entity
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : entity_18720097001
Forward scan.
Positioning by key.
Keys are:
entity_id_i ASC
Using I/O Size 2 Kbytes for data pages.
With LRU Buffer Replacement Strategy for data pages.

FROM TABLE
product..product
EXISTS TABLE : nested iteration.
Index : idx_issr_id_i1
Forward scan.
Positioning at index start.
Index contains all needed columns. Base table will
not be read.
Using I/O Size 16 Kbytes for index leaf pages.
With LRU Buffer Replacement Strategy for index leaf
pages.
TO TABLE
#product
Using I/O Size 2 Kbytes for data pages.

Total estimated I/O cost for statement 200 (at line 1083):
9309146.
ASE 15 Plan
QUERY PLAN FOR STATEMENT 200 (at line 1083).
Optimized using Serial Mode


STEP 1
The type of query is UPDATE.

13 operator(s) under root

|ROOT:EMIT Operator (VA = 13)
|
| |UPDATE Operator (VA = 12)
| | The update mode is deferred_varcol.
| |
| | |NESTED LOOP JOIN Operator (VA = 11) (Join
Type: Left Semi Join)
| | |
| | | |NESTED LOOP JOIN Operator (VA = 8) (Join
Type: Left Semi Join)
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | #product
| | | | | prd
| | | | | Table Scan.
| | | | | Forward Scan.
| | | | | Positioning at start of table.
| | | | | Using I/O Size 2 Kbytes for data
pages.
| | | | | With LRU Buffer Replacement
Strategy for data pages.
| | | |
| | | | |N-ARY NESTED LOOP JOIN Operator (VA
= 7) has 4 children.
| | | | |
| | | | | |NESTED LOOP JOIN Operator (VA =
3) (Join Type: Left Semi Join)
| | | | | |
| | | | | | |SCAN Operator (VA = 1)
| | | | | | | FROM TABLE
| | | | | | |
fish_ref..ProductDerivative
| | | | | | | Index : UProdDervIdx1
| | | | | | | Forward Scan.
| | | | | | | Positioning by key.
| | | | | | | Keys are:
| | | | | | | ProductKey ASC
| | | | | | | ToDate ASC
| | | | | | | Using I/O Size 2 Kbytes
for index leaf pages.
| | | | | | | With LRU Buffer
Replacement Strategy for index leaf pages.
| | | | | | | Using I/O Size 2 Kbytes
for data pages.
| | | | | | | With LRU Buffer
Replacement Strategy for data pages.
| | | | | |
| | | | | | |SCAN Operator (VA = 2)
| | | | | | | FROM TABLE
| | | | | | | product..product
| | | | | | | Index : idx_mty_d
| | | | | | | Forward Scan.
| | | | | | | Positioning at index
start.
| | | | | | | Index contains all needed
columns. Base table will not be read.
| | | | | | | Using I/O Size 16 Kbytes
for index leaf pages.
| | | | | | | With LRU Buffer
Replacement Strategy for index leaf pages.
| | | | |
| | | | | |SCAN Operator (VA = 4)
| | | | | | FROM TABLE
| | | | | | product..synonym
| | | | | | Index : syn_type_cd_idx
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Keys are:
| | | | | | synonym_c ASC
| | | | | | synonym_type_cd_c ASC
| | | | | | Using I/O Size 16 Kbytes for
index leaf pages.
| | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.
| | | | | | Using I/O Size 16 Kbytes for
data pages.
| | | | | | With LRU Buffer Replacement
Strategy for data pages.
| | | | |
| | | | | |SCAN Operator (VA = 5)
| | | | | | FROM TABLE
| | | | | | product..entity_product_role
| | | | | | Using Clustered Index.
| | | | | | Index : entity_pro_idx1
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Index contains all needed
columns. Base table will not be read.
| | | | | | Keys are:
| | | | | | product_id_i ASC
| | | | | | role_cd_c ASC
| | | | | | Using I/O Size 2 Kbytes for
index leaf pages.
| | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.
| | | | |
| | | | | |SCAN Operator (VA = 6)
| | | | | | FROM TABLE
| | | | | | product..entity
| | | | | | Using Clustered Index.
| | | | | | Index : entity_idx1
| | | | | | Forward Scan.
| | | | | | Positioning by key.
| | | | | | Keys are:
| | | | | | entity_id_i ASC
| | | | | | Using I/O Size 2 Kbytes for
index leaf pages.
| | | | | | With LRU Buffer Replacement
Strategy for index leaf pages.
| | | | | | Using I/O Size 2 Kbytes for
data pages.
| | | | | | With LRU Buffer Replacement
Strategy for data pages.
| | |
| | | |RESTRICT Operator (VA =
10)(0)(0)(0)(7)(0)
| | | |
| | | | |SCAN Operator (VA = 9)
| | | | | FROM TABLE
| | | | | fish_ref..Product
| | | | | Using Clustered Index.
| | | | | Index : Product_idx1
| | | | | Forward Scan.
| | | | | Positioning by key.
| | | | | Keys are:
| | | | | ProductKey ASC
| | | | | Using I/O Size 2 Kbytes for index
leaf pages.
| | | | | With LRU Buffer Replacement
Strategy for index leaf pages.
| | | | | Using I/O Size 2 Kbytes for data
pages.
| | | | | With LRU Buffer Replacement
Strategy for data pages.
| |
| | TO TABLE
| | #product
| | Using I/O Size 2 Kbytes for data pages.


Total estimated I/O cost for statement 200 (at line 1083):
10695178.


"Mark A. Parsons" <iron_horse Posted on 2012-12-20 23:20:10.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
References: <50d24dac$1@forums-1-dub> <50d32866.6c74.1681692777@sybase.com>
In-Reply-To: <50d32866.6c74.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 121124-1, 11/24/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <50d39d2a$1@forums-1-dub>
Date: 20 Dec 2012 15:20:10 -0800
X-Trace: forums-1-dub 1356045610 172.20.134.152 (20 Dec 2012 15:20:10 -0800)
X-Original-Trace: 20 Dec 2012 15:20:10 -0800, vip152.sybase.com
Lines: 286
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13333
Article PK: 1158546

OK, so the obvious part ...

The 2 product tables (fish_ref..product, product..product) have switched places in the query plans, and in both cases
you have an inefficient index scan of the product..product table (though different indexes are being scanned in the
query plans). The ASE 15 query plan choosing to hit the product..product table first with that inefficient index scan.

As for how to improve the ASE 15 performance ... *shrug* ... just guessing at this point since you haven't provided much
to work with ...

- make sure you've run 'update index stats' on the tables in question in ASE 15

- you may need to rewrite/reformat the query in question

- since this is inside a stored proc you may need to look at your use of deferred compilation

- and on general principal I'd probably want to figure out why both ASE versions are choosing an inefficient index scan
of the product..product table

On 12/20/2012 08:01, Sid. wrote:
>> To *start* you'll need to post the query and the 2 query
>> plans.
>>
>> If you could post the output from 'set statistics time,io
>> on' that would be good, too.
>>
> This is the portion of the plan that has slownedd.
>
> ASE 12.5 Plan:
> QUERY PLAN FOR STATEMENT 200 (at line 1083).
>
>
> STEP 1
> The type of query is UPDATE.
> The update mode is deferred_varcol.
>
> FROM TABLE
> #product
> prd
> Nested iteration.
> Table Scan.
> Forward scan.
> Positioning at start of table.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> fish_ref..ProductDerivative
> EXISTS TABLE : nested iteration.
> Index : UProdDervIdx1
> Forward scan.
> Positioning by key.
> Keys are:
> ProductKey ASC
> ToDate ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> fish_ref..Product
> EXISTS TABLE : nested iteration.
> Using Clustered Index.
> Index : Product_idx1
> Forward scan.
> Positioning by key.
> Keys are:
> ProductKey ASC
> Using I/O Size 2 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> product..synonym
> EXISTS TABLE : nested iteration.
> Index : synonym_n1
> Forward scan.
> Positioning by key.
> Keys are:
> synonym_c ASC
> synonym_type_cd_c ASC
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> Using I/O Size 16 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> product..entity_product_role
> EXISTS TABLE : nested iteration.
> Using Clustered Index.
> Index : entity_pro_8480060521
> Forward scan.
> Positioning by key.
> Keys are:
> product_id_i ASC
> role_cd_c ASC
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> product..entity
> EXISTS TABLE : nested iteration.
> Using Clustered Index.
> Index : entity_18720097001
> Forward scan.
> Positioning by key.
> Keys are:
> entity_id_i ASC
> Using I/O Size 2 Kbytes for data pages.
> With LRU Buffer Replacement Strategy for data pages.
>
> FROM TABLE
> product..product
> EXISTS TABLE : nested iteration.
> Index : idx_issr_id_i1
> Forward scan.
> Positioning at index start.
> Index contains all needed columns. Base table will
> not be read.
> Using I/O Size 16 Kbytes for index leaf pages.
> With LRU Buffer Replacement Strategy for index leaf
> pages.
> TO TABLE
> #product
> Using I/O Size 2 Kbytes for data pages.
>
> Total estimated I/O cost for statement 200 (at line 1083):
> 9309146.
> ASE 15 Plan
> QUERY PLAN FOR STATEMENT 200 (at line 1083).
> Optimized using Serial Mode
>
>
> STEP 1
> The type of query is UPDATE.
>
> 13 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 13)
> |
> | |UPDATE Operator (VA = 12)
> | | The update mode is deferred_varcol.
> | |
> | | |NESTED LOOP JOIN Operator (VA = 11) (Join
> Type: Left Semi Join)
> | | |
> | | | |NESTED LOOP JOIN Operator (VA = 8) (Join
> Type: Left Semi Join)
> | | | |
> | | | | |SCAN Operator (VA = 0)
> | | | | | FROM TABLE
> | | | | | #product
> | | | | | prd
> | | | | | Table Scan.
> | | | | | Forward Scan.
> | | | | | Positioning at start of table.
> | | | | | Using I/O Size 2 Kbytes for data
> pages.
> | | | | | With LRU Buffer Replacement
> Strategy for data pages.
> | | | |
> | | | | |N-ARY NESTED LOOP JOIN Operator (VA
> = 7) has 4 children.
> | | | | |
> | | | | | |NESTED LOOP JOIN Operator (VA =
> 3) (Join Type: Left Semi Join)
> | | | | | |
> | | | | | | |SCAN Operator (VA = 1)
> | | | | | | | FROM TABLE
> | | | | | | |
> fish_ref..ProductDerivative
> | | | | | | | Index : UProdDervIdx1
> | | | | | | | Forward Scan.
> | | | | | | | Positioning by key.
> | | | | | | | Keys are:
> | | | | | | | ProductKey ASC
> | | | | | | | ToDate ASC
> | | | | | | | Using I/O Size 2 Kbytes
> for index leaf pages.
> | | | | | | | With LRU Buffer
> Replacement Strategy for index leaf pages.
> | | | | | | | Using I/O Size 2 Kbytes
> for data pages.
> | | | | | | | With LRU Buffer
> Replacement Strategy for data pages.
> | | | | | |
> | | | | | | |SCAN Operator (VA = 2)
> | | | | | | | FROM TABLE
> | | | | | | | product..product
> | | | | | | | Index : idx_mty_d
> | | | | | | | Forward Scan.
> | | | | | | | Positioning at index
> start.
> | | | | | | | Index contains all needed
> columns. Base table will not be read.
> | | | | | | | Using I/O Size 16 Kbytes
> for index leaf pages.
> | | | | | | | With LRU Buffer
> Replacement Strategy for index leaf pages.
> | | | | |
> | | | | | |SCAN Operator (VA = 4)
> | | | | | | FROM TABLE
> | | | | | | product..synonym
> | | | | | | Index : syn_type_cd_idx
> | | | | | | Forward Scan.
> | | | | | | Positioning by key.
> | | | | | | Keys are:
> | | | | | | synonym_c ASC
> | | | | | | synonym_type_cd_c ASC
> | | | | | | Using I/O Size 16 Kbytes for
> index leaf pages.
> | | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
> | | | | | | Using I/O Size 16 Kbytes for
> data pages.
> | | | | | | With LRU Buffer Replacement
> Strategy for data pages.
> | | | | |
> | | | | | |SCAN Operator (VA = 5)
> | | | | | | FROM TABLE
> | | | | | | product..entity_product_role
> | | | | | | Using Clustered Index.
> | | | | | | Index : entity_pro_idx1
> | | | | | | Forward Scan.
> | | | | | | Positioning by key.
> | | | | | | Index contains all needed
> columns. Base table will not be read.
> | | | | | | Keys are:
> | | | | | | product_id_i ASC
> | | | | | | role_cd_c ASC
> | | | | | | Using I/O Size 2 Kbytes for
> index leaf pages.
> | | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
> | | | | |
> | | | | | |SCAN Operator (VA = 6)
> | | | | | | FROM TABLE
> | | | | | | product..entity
> | | | | | | Using Clustered Index.
> | | | | | | Index : entity_idx1
> | | | | | | Forward Scan.
> | | | | | | Positioning by key.
> | | | | | | Keys are:
> | | | | | | entity_id_i ASC
> | | | | | | Using I/O Size 2 Kbytes for
> index leaf pages.
> | | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
> | | | | | | Using I/O Size 2 Kbytes for
> data pages.
> | | | | | | With LRU Buffer Replacement
> Strategy for data pages.
> | | |
> | | | |RESTRICT Operator (VA =
> 10)(0)(0)(0)(7)(0)
> | | | |
> | | | | |SCAN Operator (VA = 9)
> | | | | | FROM TABLE
> | | | | | fish_ref..Product
> | | | | | Using Clustered Index.
> | | | | | Index : Product_idx1
> | | | | | Forward Scan.
> | | | | | Positioning by key.
> | | | | | Keys are:
> | | | | | ProductKey ASC
> | | | | | Using I/O Size 2 Kbytes for index
> leaf pages.
> | | | | | With LRU Buffer Replacement
> Strategy for index leaf pages.
> | | | | | Using I/O Size 2 Kbytes for data
> pages.
> | | | | | With LRU Buffer Replacement
> Strategy for data pages.
> | |
> | | TO TABLE
> | | #product
> | | Using I/O Size 2 Kbytes for data pages.
>
>
> Total estimated I/O cost for statement 200 (at line 1083):
> 10695178.


Hank Du Posted on 2012-12-21 17:29:09.0Z
Sender: 56b8.50d48002.1804289383@sybase.com
From: Hank Du
Newsgroups: sybase.public.ase.performance+tuning
Subject: Re: Query running slow in 15.5
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50d49c65.5eed.1681692777@sybase.com>
References: <50d2430c.1a6f.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Dec 2012 09:29:09 -0800
X-Trace: forums-1-dub 1356110949 172.20.134.41 (21 Dec 2012 09:29:09 -0800)
X-Original-Trace: 21 Dec 2012 09:29:09 -0800, 172.20.134.41
Lines: 27
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13334
Article PK: 1158571

The query plans are different between v12.5.4 and v15.5 as
below.
1. the join order of the tables
2. the indexes for product..synonym, product..product,
product..entity and product..entity_product_role

Did you recreate the indexes in v15.5?
Please provice query plan for v15.5.
set statistics io,time,plancost on
go
run_your_query
go

> ASE 15.5/EBF 19399 64 bit Linux
> We are planning to upgrading one of the ASE 12.5.4 to ASE
> 15.5. We are in the phase of analysing our sp performance.
> One specific sp is running very slow. We are able to
> identify the exact query inside the proc. The plan between
> 1254 and 15.5 are similar. Same as the estimated cost. In
> 1254 the proc runs in a minute. In 15 it takes 10 minutes.
> 15.5 host is more powerful than 1254, has similar memory
> or more than our 1254 prod server and mostly I am the only
> user in 15. Any help is appreciated.
>
> Thanks,
> Sid.