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.

using distinct causes scan

2 posts in Performance and Tuning Last posting was on 2012-03-08 15:05:38.0Z
Ericrad Posted on 2012-03-07 17:53:46.0Z
Sender: 3900.4f57a07d.1804289383@sybase.com
From: Ericrad
Newsgroups: sybase.public.ase.performance+tuning
Subject: using distinct causes scan
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f57a0aa.3906.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 7 Mar 2012 09:53:46 -0800
X-Trace: forums-1-dub 1331142826 172.20.134.41 (7 Mar 2012 09:53:46 -0800)
X-Original-Trace: 7 Mar 2012 09:53:46 -0800, 172.20.134.41
Lines: 159
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13092
Article PK: 155131

Hello,
A query my developer has been using for some time now in
ASE 12.5.4 is running differently in our 15.5 test server.
When the 'distinct' is in the query the estimated io is huge
(see below) and without the 'distinct' it is estimated at
201 ios (see below)

The main difference I see is the positioning statement in
the plans
distinct = Positioning at index start
no distinct = Positioning by key

I've listed the queries, the plans and the data in the
#batches table.

Please let me know if you need anything else

Thanks
eric

Version
Adaptive Server Enterprise/15.5/EBF 18657 SMP
ESD#4/P/Sun_svr4/OS 5.8/asear155/2545/64-bit/FBO/Thu Jun 16
10:29:40 2011

table #batches is created by a stored proc that runs before
the listed query. It only contains 1 record in this test.


grouping order_number ticket_number
1 43591239 930627

Query WITHOUT distinct:
select t1.order_number, t2.line_number, t2.comment
from #batches t1, production..order_comments t2
where t1.order_number = t2.order_number
and t1.grouping in (1)
and t2.comment_type = 'CUSTOMER'
order by t2.order_number, t2.line_number asc

PLAN:


QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode


STEP 1
The type of query is SELECT.

4 operator(s) under root

|ROOT:EMIT Operator (VA = 4)

|
| |SORT Operator (VA = 3)
| | Using Worktable1 for internal storage.
| |
| | |NESTED LOOP JOIN Operator (VA = 2) (Join
Type: Inner Join)
| | |
| | | |SCAN Operator (VA = 0)
| | | | FROM TABLE
| | | | #batches
| | | | t1
| | | | 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.
| | |
| | | |SCAN Operator (VA = 1)
| | | | FROM TABLE
| | | | production..order_comments
| | | | t2
| | | | Using Clustered Index.
| | | | Index : order_detail_constrnt
| | | | Forward Scan.
| | | | Positioning by key.
| | | | Keys are:
| | | | order_number ASC
| | | | comment_type 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.


Total estimated I/O cost for statement 1 (at line 1): 201.


PLAN WITH DISTINCT IN SELECT


QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using Serial Mode


STEP 1
The type of query is SELECT.

6 operator(s) under root

|ROOT:EMIT Operator (VA = 6)
|
| |SORT Operator (VA = 5)
| | Using Worktable4 for internal storage.
| |
| | |HASH DISTINCT Operator (VA = 4)
| | | Using Worktable3 for internal storage.
| | | Key Count: 3
| | |
| | | |MERGE JOIN Operator (Join Type: Inner
Join) (VA = 3)
| | | | Using Worktable2 for internal storage.
| | | | Key Count: 1
| | | | Key Ordering: ASC
| | | |
| | | | |SCAN Operator (VA = 0)
| | | | | FROM TABLE
| | | | | production..order_comments
| | | | | t2
| | | | | Using Clustered Index.
| | | | | Index : order_detail_constrnt
| | | | | Forward Scan.
| | | | | Positioning at index start.
| | | | | 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 MRU Buffer Replacement
Strategy for data pages.
| | | |
| | | | |SORT Operator (VA = 2)
| | | | | Using Worktable1 for internal
storage.
| | | | |
| | | | | |SCAN Operator (VA = 1)
| | | | | | FROM TABLE
| | | | | | #batches
| | | | | | t1
| | | | | | 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.


Total estimated I/O cost for statement 1 (at line 1):
197893081.


Cory Sane [TeamSybase] Posted on 2012-03-08 15:05:38.0Z
From: "Cory Sane [TeamSybase]" <cory!=sane>
Newsgroups: sybase.public.ase.performance+tuning
References: <4f57a0aa.3906.1681692777@sybase.com>
In-Reply-To: <4f57a0aa.3906.1681692777@sybase.com>
Subject: Re: using distinct causes scan
Lines: 179
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6002.18197
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6002.18463
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f58cac2@forums-1-dub>
Date: 8 Mar 2012 07:05:38 -0800
X-Trace: forums-1-dub 1331219138 10.22.241.152 (8 Mar 2012 07:05:38 -0800)
X-Original-Trace: 8 Mar 2012 07:05:38 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.performance+tuning:13093
Article PK: 91649

Eric,

The first query is doing a nest loop so it eliminating values of both columns.
The second query is using merge join, so it only cares about coluns that match the other table.

1. Are you using "update index statistics" on the production table?
2. Deffered temp table statistics came about recently... (you may not be using it yet)
in the proc run "update statistics #batches(grouping)" this will tell the optimizer that there is only one row in the table
(not 100 rows=10pagesX10rows)

3. Try the query with:
set merge_join off
before the line in question.

--
Cory Sane
[TeamSybase]
Certified Sybase Associate DBA for ASE 15.0

"Ericrad" wrote in message news:4f57a0aa.3906.1681692777@sybase.com...
> Hello,
> A query my developer has been using for some time now in
> ASE 12.5.4 is running differently in our 15.5 test server.
> When the 'distinct' is in the query the estimated io is huge
> (see below) and without the 'distinct' it is estimated at
> 201 ios (see below)
>
> The main difference I see is the positioning statement in
> the plans
> distinct = Positioning at index start
> no distinct = Positioning by key
>
> I've listed the queries, the plans and the data in the
> #batches table.
>
> Please let me know if you need anything else
>
> Thanks
> eric
>
> Version
> Adaptive Server Enterprise/15.5/EBF 18657 SMP
> ESD#4/P/Sun_svr4/OS 5.8/asear155/2545/64-bit/FBO/Thu Jun 16
> 10:29:40 2011
>
> table #batches is created by a stored proc that runs before
> the listed query. It only contains 1 record in this test.
>
>
> grouping order_number ticket_number
> 1 43591239 930627
>
> Query WITHOUT distinct:
> select t1.order_number, t2.line_number, t2.comment
> from #batches t1, production..order_comments t2
> where t1.order_number = t2.order_number
> and t1.grouping in (1)
> and t2.comment_type = 'CUSTOMER'
> order by t2.order_number, t2.line_number asc
>
> PLAN:
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
> Optimized using Serial Mode
>
>
> STEP 1
> The type of query is SELECT.
>
> 4 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 4)
> |
> | |SORT Operator (VA = 3)
> | | Using Worktable1 for internal storage.
> | |
> | | |NESTED LOOP JOIN Operator (VA = 2) (Join
> Type: Inner Join)
> | | |
> | | | |SCAN Operator (VA = 0)
> | | | | FROM TABLE
> | | | | #batches
> | | | | t1
> | | | | 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.
> | | |
> | | | |SCAN Operator (VA = 1)
> | | | | FROM TABLE
> | | | | production..order_comments
> | | | | t2
> | | | | Using Clustered Index.
> | | | | Index : order_detail_constrnt
> | | | | Forward Scan.
> | | | | Positioning by key.
> | | | | Keys are:
> | | | | order_number ASC
> | | | | comment_type 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.
>
>
> Total estimated I/O cost for statement 1 (at line 1): 201.
>
>
> PLAN WITH DISTINCT IN SELECT
>
>
> QUERY PLAN FOR STATEMENT 1 (at line 1).
> Optimized using Serial Mode
>
>
> STEP 1
> The type of query is SELECT.
>
> 6 operator(s) under root
>
> |ROOT:EMIT Operator (VA = 6)
> |
> | |SORT Operator (VA = 5)
> | | Using Worktable4 for internal storage.
> | |
> | | |HASH DISTINCT Operator (VA = 4)
> | | | Using Worktable3 for internal storage.
> | | | Key Count: 3
> | | |
> | | | |MERGE JOIN Operator (Join Type: Inner
> Join) (VA = 3)
> | | | | Using Worktable2 for internal storage.
> | | | | Key Count: 1
> | | | | Key Ordering: ASC
> | | | |
> | | | | |SCAN Operator (VA = 0)
> | | | | | FROM TABLE
> | | | | | production..order_comments
> | | | | | t2
> | | | | | Using Clustered Index.
> | | | | | Index : order_detail_constrnt
> | | | | | Forward Scan.
> | | | | | Positioning at index start.
> | | | | | 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 MRU Buffer Replacement
> Strategy for data pages.
> | | | |
> | | | | |SORT Operator (VA = 2)
> | | | | | Using Worktable1 for internal
> storage.
> | | | | |
> | | | | | |SCAN Operator (VA = 1)
> | | | | | | FROM TABLE
> | | | | | | #batches
> | | | | | | t1
> | | | | | | 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.
>
>
> Total estimated I/O cost for statement 1 (at line 1):
> 197893081.