Our application is built with Microsoft Visual Studio 2005
and SQL Anywhere v8.0.2.4218.

We have two tables, part_sec and component_sec, that we join
together to create a view. This join is performed on primary
key (with 3 fields) of component_sec and an index (with 3
fields) of part_sec. We executed the same query statement
with ESQL and in ISQL. ISQL took about 1 minute to return
the first set of result. ESQL took 40 minutes to return the
first fetched record.

CREATE view part_sec_view as select component_sec.compid as
portid,part_sec.prc_date as prc_date,part_sec.cusip as
cusip,component_sec.par as par, …
FROM component_sec JOIN part_sec on component_sec.masterid
= part_sec.portid and part_sec.compdate_key =
component_sec.prc_date and part_sec.cusip =
component_sec.cusip

SELECT part_sec_view.prc_date, part_sec_view.cusip, par,
price, yldm, mktval, coupon, issuer, maturity,
ISNULL(state,' '), …
FROM part_sec_view
WHERE part_sec_view.portid=:? AND
part_sec_view.prc_date>=:? AND
part_sec_view.prc_date<=:? ORDER BY part_sec_view.cusip,
part_sec_view.prc_date

I examined the “Plan” in ISQL, it indicated that the
primary key is used for table component_sec, and index is
used for table part_sec.

Is there anything we could try to improve the performance of
ESQL?

Thanks.

On Nov 18, 5:06*pm, Mei wrote:
What did you test in iSQL ? select from view or select with view and
where ... ?
1 minute for first fetched records is too much for me ... 40 minutes
is very long time. Because you wrote that both indexes are used.

componet table has primary key = masterid, prc_date, custip
part table has index = portid, compdate_key, cusip
RIGHT ?
Did you examine complete sql ? I mean : use select part from create
view and just add WHERE and ORDER clause from final sql command.