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.

**Primary Key index search behaviour**

7 posts in General Discussion Last posting was on 2004-12-20 16:51:08.0Z
Ollie Posted on 2004-12-16 09:56:28.0Z
Sender: 757e.41c15a2d.1804289383@sybase.com
From: Ollie
Newsgroups: ianywhere.public.general
Subject: **Primary Key index search behaviour**
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41c15bc9.7581.1681692777@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 16 Dec 2004 01:56:25 -0800, 10.22.241.42
Lines: 70
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 16 Dec 2004 01:56:27 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 16 Dec 2004 01:56:28 -0800
X-Trace: forums-1-dub 1103190988 10.22.108.75 (16 Dec 2004 01:56:28 -0800)
X-Original-Trace: 16 Dec 2004 01:56:28 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3976
Article PK: 7868

Hi,

I'll try and keep this short and hopefully someone will
be able to shed some light on this.

Client System : Windows NT4 SP6
Sybase Version: 8.0.3.5144.
Database size: 2.6 gig

Table in question consists of 26 columns with approximately
8 million rows. The primary key on this table which I will
call
"column1" and is an integer data type is being used in a
simple
query as show below

select column1 from table
where column1 >= 5000 and column1 <= 6000
and column6 > 0

This query is used to iterate through the whole table in
batches of
a thousand using the exact same query but obviously changing
the
values used in the between part of the query.

Findings (This was tested using ISQL on several occasions)
---------------------------------------------------------------------

1 to 2.4 million rows : Primary key index used and results
were
returned semi instant (i.e milliseconds).

2.4 million to 3.6 million: The optimizer reported that the
database
server is doing a sequential search of the table and as
expected the
query takes longer to return (1 min 10 secs). Strangly this
only applies when the number of records is > 25 in this
segment, e.g
if the query is column1 >= 5000 and column1 <= 5024, it uses
the
primary key index.

3.6 million to 8 million: Primary key index used and results
were
returned semi instant (i.e milliseconds).

It strikes me as a bit odd that the database server choses
not to use the primary key index for the range of records
described above.

There is no fragmentation on the table (my first instinct)
as the database was rebuilt and the sa_table_fragmentation()
stored procedure returns segs_per_row = 1 on the table in
question, also
the disk volumn the database is loaded from is not
fragmented.

Is there any way to force the optimizer to always use the
primary
key index on a query.

My apologies that this turned out to be a bit long.

Thanks

Regards

Ollie


Breck Carter [TeamSybase] Posted on 2004-12-16 12:24:13.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: **Primary Key index search behaviour**
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <4tu2s09bhta9q6n4p6tj9vjs7jovjuheuh@4ax.com>
References: <41c15bc9.7581.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
X-Original-NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
Date: 16 Dec 2004 04:24:13 -0800
X-Trace: forums-1-dub 1103199853 67.93.228.42 (16 Dec 2004 04:24:13 -0800)
X-Original-Trace: 16 Dec 2004 04:24:13 -0800, ip67-93-228-42.z228-93-67.customer.algx.net
Lines: 110
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3979
Article PK: 7871

ISQL may not be the best choice for doing benchmarks, but that's
another issue.

You can try to force an index using a low explicit selectivity
estimate; e.g.:

select column1 from table
where ( column1 >= 5000 and column1 <= 6000, 0.001 )
and column6 > 0

Here are the relevant V8 Help section:

Adaptive Server Anywhere SQL Reference Manual
1. SQL Language Elements
Search conditions
Explicit selectivity estimates

Adaptive Server Anywhere SQL Reference Manual
1. SQL Language Elements
Search conditions

You might consider a clustered index since this a "range scan"; that
would require the table to be reorganized. Also, a cursor fetch loop
can be used on the *entire* set of rows, with periodic commits if
that's the reason you are doing batches.

FWIW version 9 is better, and has better syntax for forcing index
usage.

Breck

On 16 Dec 2004 01:56:28 -0800, Ollie wrote:

>Hi,
>
>I'll try and keep this short and hopefully someone will
>be able to shed some light on this.
>
>Client System : Windows NT4 SP6
>Sybase Version: 8.0.3.5144.
>Database size: 2.6 gig
>
>Table in question consists of 26 columns with approximately
>8 million rows. The primary key on this table which I will
>call
>"column1" and is an integer data type is being used in a
>simple
>query as show below
>
>select column1 from table
>where column1 >= 5000 and column1 <= 6000
>and column6 > 0
>
>This query is used to iterate through the whole table in
>batches of
>a thousand using the exact same query but obviously changing
>the
>values used in the between part of the query.
>
>Findings (This was tested using ISQL on several occasions)
>---------------------------------------------------------------------
>
>1 to 2.4 million rows : Primary key index used and results
>were
>returned semi instant (i.e milliseconds).
>
>2.4 million to 3.6 million: The optimizer reported that the
>database
>server is doing a sequential search of the table and as
>expected the
>query takes longer to return (1 min 10 secs). Strangly this
>only applies when the number of records is > 25 in this
>segment, e.g
>if the query is column1 >= 5000 and column1 <= 5024, it uses
>the
>primary key index.
>
>3.6 million to 8 million: Primary key index used and results
>were
>returned semi instant (i.e milliseconds).
>
>It strikes me as a bit odd that the database server choses
>not to use the primary key index for the range of records
>described above.
>
>There is no fragmentation on the table (my first instinct)
>as the database was rebuilt and the sa_table_fragmentation()
>stored procedure returns segs_per_row = 1 on the table in
>question, also
>the disk volumn the database is loaded from is not
>fragmented.
>
>Is there any way to force the optimizer to always use the
>primary
>key index on a query.
>
>My apologies that this turned out to be a bit long.
>
>Thanks
>
>Regards
>
>Ollie

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Ollie Posted on 2004-12-16 12:50:12.0Z
Sender: 6c8f.41c18287.1804289383@sybase.com
From: Ollie
Newsgroups: ianywhere.public.general
Subject: Re: **Primary Key index search behaviour**
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41c18484.6cba.1681692777@sybase.com>
References: <4tu2s09bhta9q6n4p6tj9vjs7jovjuheuh@4ax.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 16 Dec 2004 04:50:12 -0800
X-Trace: forums-1-dub 1103201412 10.22.241.41 (16 Dec 2004 04:50:12 -0800)
X-Original-Trace: 16 Dec 2004 04:50:12 -0800, 10.22.241.41
Lines: 119
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3980
Article PK: 7873

Thanks Breck. I'll do some benchmark tseting based on
your good advise.

Ollie

> ISQL may not be the best choice for doing benchmarks, but
> that's another issue.
>
> You can try to force an index using a low explicit
> selectivity estimate; e.g.:
>
> select column1 from table
> where ( column1 >= 5000 and column1 <= 6000, 0.001 )
> and column6 > 0
>
> Here are the relevant V8 Help section:
>
> Adaptive Server Anywhere SQL Reference Manual
> 1. SQL Language Elements
> Search conditions
> Explicit selectivity estimates
>
> Adaptive Server Anywhere SQL Reference Manual
> 1. SQL Language Elements
> Search conditions
>
> You might consider a clustered index since this a "range
> scan"; that would require the table to be reorganized.
> Also, a cursor fetch loop can be used on the *entire* set
> of rows, with periodic commits if that's the reason you
> are doing batches.
>
> FWIW version 9 is better, and has better syntax for
> forcing index usage.
>
> Breck
>
> On 16 Dec 2004 01:56:28 -0800, Ollie wrote:
>
> >Hi,
> >
> >I'll try and keep this short and hopefully someone will
> >be able to shed some light on this.
> >
> >Client System : Windows NT4 SP6
> >Sybase Version: 8.0.3.5144.
> >Database size: 2.6 gig
> >
> >Table in question consists of 26 columns with
> approximately >8 million rows. The primary key on this
> table which I will >call
> >"column1" and is an integer data type is being used in a
> >simple
> >query as show below
> >
> >select column1 from table
> >where column1 >= 5000 and column1 <= 6000
> >and column6 > 0
> >
> >This query is used to iterate through the whole table in
> >batches of
> >a thousand using the exact same query but obviously
> changing >the
> >values used in the between part of the query.
> >
> >Findings (This was tested using ISQL on several
> occasions)
> >---------------------------------------------------------
> ------------ >
> >1 to 2.4 million rows : Primary key index used and
> results >were
> >returned semi instant (i.e milliseconds).
> >
> >2.4 million to 3.6 million: The optimizer reported that
> the >database
> >server is doing a sequential search of the table and as
> >expected the
> >query takes longer to return (1 min 10 secs). Strangly
> this >only applies when the number of records is > 25 in
> this >segment, e.g
> >if the query is column1 >= 5000 and column1 <= 5024, it
> uses >the
> >primary key index.
> >
> >3.6 million to 8 million: Primary key index used and
> results >were
> >returned semi instant (i.e milliseconds).
> >
> >It strikes me as a bit odd that the database server
> choses >not to use the primary key index for the range of
> records >described above.
> >
> >There is no fragmentation on the table (my first
> instinct) >as the database was rebuilt and the
> sa_table_fragmentation() >stored procedure returns
> segs_per_row = 1 on the table in >question, also
> >the disk volumn the database is loaded from is not
> >fragmented.
> >
> >Is there any way to force the optimizer to always use the
> >primary
> >key index on a query.
> >
> >My apologies that this turned out to be a bit long.
> >
> >Thanks
> >
> >Regards
> >
> >Ollie
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


Greg Fenton Posted on 2004-12-16 17:53:28.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla Thunderbird 1.6.3.2f (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: **Primary Key index search behaviour**
References: <4tu2s09bhta9q6n4p6tj9vjs7jovjuheuh@4ax.com> <41c18484.6cba.1681692777@sybase.com>
In-Reply-To: <41c18484.6cba.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Message-ID: <41c1cb94$1@forums-2-dub>
X-Original-Trace: 16 Dec 2004 09:53:24 -0800, cpec2cdc91b1d31-cm000f212f9e50.cpe.net.cable.rogers.com
Lines: 29
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 16 Dec 2004 09:53:25 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 16 Dec 2004 09:53:28 -0800
X-Trace: forums-1-dub 1103219608 10.22.108.75 (16 Dec 2004 09:53:28 -0800)
X-Original-Trace: 16 Dec 2004 09:53:28 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3985
Article PK: 7877


Ollie wrote:
> Thanks Breck. I'll do some benchmark tseting based on
> your good advise.
>

As Breck points out, DBISQL is usually not a good tool for benchmarking
the engine because DBISQL does lots more "under the cover" work than
simply issueing the SQL statements you give it (pulls in result sets in
batches rather than all at once, formats data for display, etc...)

In ASA 8.x and higher there are the benchmarking tools that are very
good for just this task. See:

%ASANY8%\Samples\ASA\PerformanceFetch
%ASANY8%\Samples\ASA\PerformanceInsert
%ASANY8%\Samples\ASA\PerformanceTraceTime
%ASANY8%\Samples\ASA\PerformanceTransaction

There are readme.txt files in each.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/


Breck Carter [TeamSybase] Posted on 2004-12-17 10:46:47.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: **Primary Key index search behaviour**
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <f1e5s0d80b57obfpt2js1nm5ei2reddr0b@4ax.com>
References: <41c15bc9.7581.1681692777@sybase.com>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
X-Original-NNTP-Posting-Host: ip67-93-228-42.z228-93-67.customer.algx.net
Date: 17 Dec 2004 02:46:47 -0800
X-Trace: forums-1-dub 1103280407 67.93.228.42 (17 Dec 2004 02:46:47 -0800)
X-Original-Trace: 17 Dec 2004 02:46:47 -0800, ip67-93-228-42.z228-93-67.customer.algx.net
Lines: 89
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3992
Article PK: 7882

Oh, yeah, and either flush the cache (sa_flush_cache) between tests,
and/or repeat each test sequentially, to see the results with and
without pages in the cache.

Also note that (in 9.0.something) cache warming may confuse the issue;
I've noticed big databases with huge RAM can do TONS of I/O as they
start, as it warms the cache... you might want to turn it off.

Breck

On 16 Dec 2004 01:56:28 -0800, Ollie wrote:

>Hi,
>
>I'll try and keep this short and hopefully someone will
>be able to shed some light on this.
>
>Client System : Windows NT4 SP6
>Sybase Version: 8.0.3.5144.
>Database size: 2.6 gig
>
>Table in question consists of 26 columns with approximately
>8 million rows. The primary key on this table which I will
>call
>"column1" and is an integer data type is being used in a
>simple
>query as show below
>
>select column1 from table
>where column1 >= 5000 and column1 <= 6000
>and column6 > 0
>
>This query is used to iterate through the whole table in
>batches of
>a thousand using the exact same query but obviously changing
>the
>values used in the between part of the query.
>
>Findings (This was tested using ISQL on several occasions)
>---------------------------------------------------------------------
>
>1 to 2.4 million rows : Primary key index used and results
>were
>returned semi instant (i.e milliseconds).
>
>2.4 million to 3.6 million: The optimizer reported that the
>database
>server is doing a sequential search of the table and as
>expected the
>query takes longer to return (1 min 10 secs). Strangly this
>only applies when the number of records is > 25 in this
>segment, e.g
>if the query is column1 >= 5000 and column1 <= 5024, it uses
>the
>primary key index.
>
>3.6 million to 8 million: Primary key index used and results
>were
>returned semi instant (i.e milliseconds).
>
>It strikes me as a bit odd that the database server choses
>not to use the primary key index for the range of records
>described above.
>
>There is no fragmentation on the table (my first instinct)
>as the database was rebuilt and the sa_table_fragmentation()
>stored procedure returns segs_per_row = 1 on the table in
>question, also
>the disk volumn the database is loaded from is not
>fragmented.
>
>Is there any way to force the optimizer to always use the
>primary
>key index on a query.
>
>My apologies that this turned out to be a bit long.
>
>Thanks
>
>Regards
>
>Ollie

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com


Ollie Posted on 2004-12-20 16:51:08.0Z
Sender: 3c55.41c701ac.1804289383@sybase.com
From: Ollie
Newsgroups: ianywhere.public.general
Subject: Re: **Primary Key index search behaviour**
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <41c702f7.3c64.1681692777@sybase.com>
References: <f1e5s0d80b57obfpt2js1nm5ei2reddr0b@4ax.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 20 Dec 2004 08:51:03 -0800, 10.22.241.42
Lines: 99
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 20 Dec 2004 08:51:05 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 20 Dec 2004 08:51:08 -0800
X-Trace: forums-1-dub 1103561468 10.22.108.75 (20 Dec 2004 08:51:08 -0800)
X-Original-Trace: 20 Dec 2004 08:51:08 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:4000
Article PK: 7888

Thanks for your extra input guys, all information received
has been been taken into consideration during my benchmarks,
and has been very helpfull.

Ollie

> Oh, yeah, and either flush the cache (sa_flush_cache)
> between tests, and/or repeat each test sequentially, to
> see the results with and without pages in the cache.
>
> Also note that (in 9.0.something) cache warming may
> confuse the issue; I've noticed big databases with huge
> RAM can do TONS of I/O as they start, as it warms the
> cache... you might want to turn it off.
>
> Breck
>
> On 16 Dec 2004 01:56:28 -0800, Ollie wrote:
>
> >Hi,
> >
> >I'll try and keep this short and hopefully someone will
> >be able to shed some light on this.
> >
> >Client System : Windows NT4 SP6
> >Sybase Version: 8.0.3.5144.
> >Database size: 2.6 gig
> >
> >Table in question consists of 26 columns with
> approximately >8 million rows. The primary key on this
> table which I will >call
> >"column1" and is an integer data type is being used in a
> >simple
> >query as show below
> >
> >select column1 from table
> >where column1 >= 5000 and column1 <= 6000
> >and column6 > 0
> >
> >This query is used to iterate through the whole table in
> >batches of
> >a thousand using the exact same query but obviously
> changing >the
> >values used in the between part of the query.
> >
> >Findings (This was tested using ISQL on several
> occasions)
> >---------------------------------------------------------
> ------------ >
> >1 to 2.4 million rows : Primary key index used and
> results >were
> >returned semi instant (i.e milliseconds).
> >
> >2.4 million to 3.6 million: The optimizer reported that
> the >database
> >server is doing a sequential search of the table and as
> >expected the
> >query takes longer to return (1 min 10 secs). Strangly
> this >only applies when the number of records is > 25 in
> this >segment, e.g
> >if the query is column1 >= 5000 and column1 <= 5024, it
> uses >the
> >primary key index.
> >
> >3.6 million to 8 million: Primary key index used and
> results >were
> >returned semi instant (i.e milliseconds).
> >
> >It strikes me as a bit odd that the database server
> choses >not to use the primary key index for the range of
> records >described above.
> >
> >There is no fragmentation on the table (my first
> instinct) >as the database was rebuilt and the
> sa_table_fragmentation() >stored procedure returns
> segs_per_row = 1 on the table in >question, also
> >the disk volumn the database is loaded from is not
> >fragmented.
> >
> >Is there any way to force the optimizer to always use the
> >primary
> >key index on a query.
> >
> >My apologies that this turned out to be a bit long.
> >
> >Thanks
> >
> >Regards
> >
> >Ollie
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
>
http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com


Glenn Paulley Posted on 2004-12-16 18:55:32.0Z
Newsgroups: ianywhere.public.general
Subject: Re: **Primary Key index search behaviour**
From: Glenn Paulley <paulley@ianywhere.com>
References: <41c15bc9.7581.1681692777@sybase.com>
Organization: iAnywhere Solutions
Message-ID: <Xns95C18DA5C9A9Epaulleyianywherecom@10.22.241.106>
User-Agent: Xnews/5.04.25
X-Original-NNTP-Posting-Host: paulley-t41.sybase.com
X-Original-Trace: 16 Dec 2004 10:55:28 -0800, paulley-t41.sybase.com
Lines: 92
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 16 Dec 2004 10:55:30 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 16 Dec 2004 10:55:32 -0800
X-Trace: forums-1-dub 1103223332 10.22.108.75 (16 Dec 2004 10:55:32 -0800)
X-Original-Trace: 16 Dec 2004 10:55:32 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3988
Article PK: 7884

Did you recompute the table's statistics via the CREATE STATISTICS
statement between each of your test runs?

Glenn

Ollie wrote in news:41c15bc9.7581.1681692777@sybase.com:

> Hi,
>
> I'll try and keep this short and hopefully someone will
> be able to shed some light on this.
>
> Client System : Windows NT4 SP6
> Sybase Version: 8.0.3.5144.
> Database size: 2.6 gig
>
> Table in question consists of 26 columns with approximately
> 8 million rows. The primary key on this table which I will
> call
> "column1" and is an integer data type is being used in a
> simple
> query as show below
>
> select column1 from table
> where column1 >= 5000 and column1 <= 6000
> and column6 > 0
>
> This query is used to iterate through the whole table in
> batches of
> a thousand using the exact same query but obviously changing
> the
> values used in the between part of the query.
>
> Findings (This was tested using ISQL on several occasions)
> ---------------------------------------------------------------------
>
> 1 to 2.4 million rows : Primary key index used and results
> were
> returned semi instant (i.e milliseconds).
>
> 2.4 million to 3.6 million: The optimizer reported that the
> database
> server is doing a sequential search of the table and as
> expected the
> query takes longer to return (1 min 10 secs). Strangly this
> only applies when the number of records is > 25 in this
> segment, e.g
> if the query is column1 >= 5000 and column1 <= 5024, it uses
> the
> primary key index.
>
> 3.6 million to 8 million: Primary key index used and results
> were
> returned semi instant (i.e milliseconds).
>
> It strikes me as a bit odd that the database server choses
> not to use the primary key index for the range of records
> described above.
>
> There is no fragmentation on the table (my first instinct)
> as the database was rebuilt and the sa_table_fragmentation()
> stored procedure returns segs_per_row = 1 on the table in
> question, also
> the disk volumn the database is loaded from is not
> fragmented.
>
> Is there any way to force the optimizer to always use the
> primary
> key index on a query.
>
> My apologies that this turned out to be a bit long.
>
> Thanks
>
> Regards
>
> Ollie

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

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

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

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