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 Member]

Walmir,

I will probably suggest you to start your engine using something like -c 1600M or a bit smaller (depending on size of your DB - if your DB is less that 1 GB then at least with -c SSSSM where SSS= size of db file). Now you are starting with -c 32M by default...

What is your OS? Win? Linux? it was a bit long time ago, but in ASA version 9 there was some limitations for use of memory depending on OS.

You do not like to move to version 10 or current version 12? Or at least to ASA 9.0.2 ? There was quite many limitations in 9.0.1 ( http://www.sybase.com/detail?id=1023009)

Still, I may recommend to use Index Consultant (In ASA 9 it was menu in Sybase Central). look page 69 http://download.sybase.com/pdfdocs/awg0901e/dbugen9.pdf

[VolkerBarth]

As I understand Walmir's response, he is using 9.0.2...

I'm not sure about the 32M default - the default is documented as following (here for v8):

If no -c option is provided, the database server computes the initial cache allocation as follows:

It uses the following operating-system-specific default cache sizes:
...Windows NT/2000/XP, Windows 95/98/Me, NetWare 2 Mb
...

It computes a runtime-specific minimum default cache size, which is the lesser of the following items:

25% of the machine's physical memory

The sum of the sizes of the main database files specified on the command line. Additional dbspaces apart from the main database files are not included in the calculation. If no files are specified, this value is zero.

It allocates the greater of the two values computed.

So the default cache size will be dependent on the database file size and the current RAM (which is rather huge for v9 here with 4 GB)...

I'd recommend Walmir to show us the server's start output...

[VolkerBarth]

That being said, I'd still second your suggestion to explicitly set -c=xxxM ...:)

[Former Member]

You are right, sorry, my mistake

[former_SQLA_member1694872]

The version of Sybase ASA and ASE 12 is?

[Former Member]

latest ASA (SQL Anywhere) is 12.0.1 XXXXX

latest ASE is 15.7

[former_SQLA_member1694872]

ok. Thanks.

[Former Member]

I see that an index exists on emp and date_sale. So you can test :

SELECT FIRST tbsales.date_sale as data_sale from tbsales JOIN tbsales_tribute ON tbsales.emp = tbsales_tribute.emp and tbsales.sale = tbsales_tribute.sale WHERE tbsales.emp = 1 and tbsales_tribute.type = 5 ORDER BY tbsales.emp DESC,tbsales.date_sale DESC;

I think the response must be quick and constant during the day.