It is a known problem that LARGE tables when joined with another table,
even though indexed, will cause a scan of the table especially fields that
are in CHAR format. My example is SSN:

SELECT date(Max("dealsummary"."dealenddt"))
INTO :ldt_prevdealenddt
FROM "customersummary",
"dealsummary"
WHERE ( "dealsummary"."branchnbr" = "customersummary"."branchnbr" ) and
( "dealsummary"."customernbr" = "customersummary"."customernbr" ) and
( "customersummary"."ssn" = :ls_ssn ;

As odd as it may sound, by adding a GROUP BY and using the field in the same
index, in this example SSN, it forces the use of the index and in my case stopped
the scanning.

GROUP BY "customersummary"."ssn"

The row count for my tables are as follows:
DealSummary: 1,453,514
CustomerSummary: 102,999

The difference in retrieval time
Without the GROUP BY: 4 ½ minutes
With the GROUP BY: 2-3 seconds.

Hope this helps someone.
Regards,
Edward Palmer

Edward Palmer wrote:
Really?

Assuming you're talking about an issue with a query run against a Sybase
database (this *is* a Sybase-specific forum) ... you'd be better off
tracking down the real reason for the performance issues, ie, post the SQL
that actually gets sent to the server (this looks like client-side coding)
along with the showplans and 'statistics io' for said query(s), along with
the 'sp_help' info on all tables referenced in the query(s).

----------

Generally speaking the larger a table the greater the chance that an index
will be utilized ... if it *can* be utilized.

Did you run your original query several times in succession? Just
wondering if the faster times you've posted are based on index usage or the
fact that the data was in cache (first instance having to wait for physical
i/o's?).

--
Mark A. Parsons

Iron Horse, Inc.
iron_horse (AT) NOSPAM (DOT) compuserve.com