My question is, how is the optimizer estimating that 300.000
Diskreads will result in 1278 seconds runtime compared to 1 Mio
Diskreads which will result in 113 seconds runtime?
So why is the optimizer thinking that a random read needs 30x times
the
sequential read?
By the way not till the selectivity falls below 0,1% SQL Anywhere
still
thinks, that reading all 9 Mio rows is more optimal than using the
index!

seq
RowsReturned 9.1038e+006
RunTime 113.57
CPUTime 22.76
DiskReadTime 90.813
DiskWriteTime 0
DiskRead 1.009e+006
DiskWrite 0



IdxImageInstance
Selectivity 10.725136846%
RowsReturned 9.764e+005
RunTime 1278.3
CPUTime 2.4412
DiskReadTime 1275.9
DiskWriteTime 0
DiskRead 3.2085e+005
DiskWrite 0

A typical disk drive will have a 40:1 ratio in performance between
sequential and random I/O. While disk throughput has improved (along
with density), the 40:1 ratio continues because random I/O requires a
SEEK operation and seek times have not improved nearly as much as
density and throughput have.

SQL Anywhere offers the ability to change the underlying disk cost model
if the disk hardware on YOUR system does not conform to the default cost
model that ships with the product. Look in the help under "calibrating"
or under "ALTER DATABASE CALIBRATE".

While this functionality exists, we have found it to be seldom necessary
to calibrate server-class hardware as the default cost model is fairly
representative - an exception is for devices such as flash that have
nearly equivalent sequential and random I/O times.

If your intention with a particular statement is to fetch only a few
rows from a cursor whose definition results in a significantly-sized
result set, then optimization controls (ie. the FIRST-ROW optimization
goal, possibly in conjunction with SELECT TOP N) may be appropriate.

Glenn

Martin wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport