Hiyas,

We use ASE 11.5.1 on AIX 4.3. We've recently hit a problem (just started a
month ago) with the wrong index being used on a production server greatly
slowing down selects/updates. The strange thing is, copies of the database
to test servers dont have this problem. Selects on the test server use a
faster nonclustered index while the production server uses the clustered
index which is much slower in the selects. We've had little luck with sybase
support so far on this issue. Data distribution seems to be the only phrase
uttered to us. Fragmentation is also a favourite. No meaningful answers on
how we can check this or resolve it. Any ideas?

Martin

"How'd they do that?" <rmcgorman_usenet (AT) n__o__s__p__a__m__spamex (DOT) com> wrote
in message news:kK3yb.524996$9l5.394375 (AT) pd7tw2no (DOT) ..
Sorry. Forgot to say. We temporarily changed the sql to force the index, as
you've suggested.

Martin

"Martin" <postmaster (AT) SPAMIGNOREmovilla (DOT) IGNOREplus.com> wrote

The usual starting point for analyzing this kind of performance and
tuning issue is to look at the output of "set showplan on" as well as
traceflag 302 and 310 output. I recommend reading the 12.5.1 P&T
Guide, chapter 7 for an explanation of such output.
http://sybooks.sybase.com:80/onlineb...up-as/asg1251e

While there have been a number of changes in the optimizer since
11.5.x, the basic information given by these commands is still the
same (I believe there have been formatting changes, though). If that
doesn't help you make sense of the output, post it here and you may
get some good commentary on it.

Cheers,

-bret

"Bret Halford" <bret (AT) sybase (DOT) com> wrote

I'll post more detail tomorrow when I'm back in work.

Martin

"Bret Halford" <bret (AT) sybase (DOT) com> wrote


The table is C_daily_stats. The main fields are:

Column_name Type Length
----------- ---- -----------
C_series_no C_series_no_t 4 (integer)
stats_C_date C_date_t 8 (date)
stats_C_newdate C_newdate_t 4 integer eg
20031201)
....

index_name index_description index_keys
index_max_rows_per_page
---------- ----------------- ----------
----------
C_daily_stats_index clustered, unique located on default C_series_no,
stats_C_date 146
C_daily_stats_index2 nonclustered located on default stats_C_newdate,
C_series_no 0

The SQL to run is:

declare @today C_date_t
select C_logical_date_value
from C_system_dates
where C_logical_date_name = 'today'

select C_series_no,
@today,
convert(int,convert(varchar,@today,112) )
from V_valid_series_daily_stats valsds
where valsds.C_series_no not in (select dais.C_series_no
from C_daily_stats dais
where dais.stats_C_newdate =
convert(int,convert(varchar,@today,112)))

and the showplan is:
QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1
The type of query is DECLARE.

QUERY PLAN FOR STATEMENT 2 (at line 2).

STEP 1
The type of query is SELECT.

FROM TABLE
C_system_dates
Nested iteration.
Using Clustered Index.
Index : C_system_dates_index
Ascending scan.
Positioning by key.
Keys are:
C_logical_date_name
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.

QUERY PLAN FOR STATEMENT 3 (at line 6).

STEP 1
The type of query is SELECT.


FROM TABLE
C_system_dates
Nested iteration.
Using Clustered Index.
Index : C_system_dates_index
Ascending scan.
Positioning by key.
Keys are:
C_logical_date_name
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.

FROM TABLE
C_contract
Nested iteration.
Table Scan.
Ascending scan.
Positioning at start of table.
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.

FROM TABLE
C_series
Nested iteration.
Table Scan.
Ascending scan.
Positioning at start of table.


Run subquery 1 (at nesting level 1).
Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_delivery_month
Nested iteration.
Using Clustered Index.
Index : C_delivery_month_index
Ascending scan.
Positioning by key.
Keys are:
C_delivery_month_no
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_contract_type
Nested iteration.
Table Scan.
Ascending scan.
Positioning at start of table.
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_contract
Nested iteration.
Using Clustered Index.
Index : C_contract_index
Ascending scan.
Positioning by key.
Keys are:
C_contract_no
Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.


FROM TABLE
C_contract_month
Nested iteration.
Using Clustered Index.
Index : C_contract_month_index
Ascending scan.
Positioning by key.
Keys are:
C_contract_no
C_delivery_month_no
C_delivery_day_no
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.


NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 3.


QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 10).


Correlated Subquery.
Subquery under an IN predicate.

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


FROM TABLE
C_daily_stats
dais
EXISTS TABLE : nested iteration.
Using Clustered Index.
Index : C_daily_stats_index
Ascending scan.
Positioning by key.
Keys are:
C_series_no
Using I/O Size 16 Kbytes.
With LRU Buffer Replacement Strategy.


END OF QUERY PLAN FOR SUBQUERY 1.

===

The last bit has a different (faster) showplan on the test servers
STEP 1
The type of query is SELECT.
Evaluate Ungrouped ANY AGGREGATE.


FROM TABLE
C_daily_stats
dais
EXISTS TABLE : nested iteration.
Index : C_daily_stats_index2
Ascending scan.
Positioning by key.
Index contains all needed columns. Base table will not be read.
Keys are:
stats_C_newdate
C_series_no
Using I/O Size 2 Kbytes.
With LRU Buffer Replacement Strategy.