Is this normal speed?

[philomalley]

Hi, I am running Sybase ASA 6.02 on an NT box with 1 P2 333MHz, 512MB RAM.
One of my tables has 500,000 rows in it. I have three columns forming the Primary Key. There is one index on this table for these keys in ascending order.

When I do a query on this table on one of these columns in ascending order it takes approx 10 secs to return. When the table goes up to 800,000 rows the delay could be up to 120 seconds, this seems to be too slow. Can anyone help me benchmark what I should be getting?
Thanks, Philo

 

[nickpark]

You only have a single composite index. A query will only use the index when the sarg specifies the leftmost (highest order) colum of the index.

I only have experience of ASE but i expect you could:
set noexec on
go
set showplan on
go
run query (select blah from a where ....)

Thatll show you whether it is doing a TABLE SCAN or an INDEX SCAN.

Sounds like you need to add a separate index(es) on the column(s) you are querying.
nick

 

[nickpark]

You only have a single composite index. A query will only use the index when the sarg specifies the leftmost (highest order) colum of the index.

I only have experience of ASE but i expect you could:
set noexec on
go
set showplan on
go
run query (select blah from a where ....)

Thatll show you whether it is doing a TABLE SCAN or an INDEX SCAN.

Sounds like you need to add a separate index on the column you are querying.