First Query

[former_SQLA_member1694872]

After starting the database when the first query takes longer to run. For example, a simple query to get the date of the last sale, after this consultation the other run faster:

select max(date_sale) as data_sale from tbsales, tbsales_tribute
where tbsales.emp = tbsales_tribute.emp
and tbsales.sale = tbsales_tribute.sale
and tbsales.emp = 1
and tbsales_tribute.type = 5

or

select max(date_sale) as data_sale
from tbsales
key join tbsales_tribute
where tbsales.emp = 1 
and tbsales_tribute.type = 5

or

select max (date_sale) as data_sale
from tbsales
join tbsales_tribute on
(tbsales_tribute.emp = tbsales.emp and tbsales_tribute.sale = tbsales.sale)
where tbsales.emp = 1
and tbsales_tribute.type = 5

Which of these queries is more optimized?

(a friend told me that we should always use the sybase key join instead of join, key join the reserved word always makes the query more optimized, so pe truth? where I can find material about?)

[VolkerBarth]

FWIW: "JOIN" and "KEY JOIN" are sematically identical if you don't specify a join condition (i.e. a " ON ...") afterwards. From the docs:

Key join is the default in SQL Anywhere when all the following apply:
* the keyword JOIN is used.
* the keywords CROSS, NATURAL or KEY are not specified.
* there is no ON clause.

You may tell that your friend.

[former_SQLA_member1694872]

Ok .. I'm reading the documentation and I will pass on to my friend this link.

[former_SQLA_member1694880]

1. Queries run slower after the database restart for the simple and obvious reason: you need to load data from HDD to RAM, which takes time.
2. SA is smart, so simple playing with query text won't change anything.

[VolkerBarth]

One should add that v9.0.1 introduced "cache warming", i.e. the server automatically reloads the pages that have been used immediately after the database was started the last time. That may help if the same queries are typically run every time the database is loaded, say, if an according application would usually use the same queries to prefetch data... However, it won't help much in cases where queries are not that homogeneous.