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?)

asked 09 Jan '13, 07:16

Walmir%20Taques's gravatar image

Walmir Taques
690374151
accept rate: 12%

edited 09 Jan '13, 09:20

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.

(10 Jan '13, 06:06) Volker Barth
Replies hidden

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

(10 Jan '13, 10:03) Walmir Taques

The three queries are equivalent for the optimizer and the execution part. You may need additional indexes and/or cache warming. Check the first query execuion using the Index Consultant and the Plan Viewer.

permanent link

answered 10 Jan '13, 05:58

Hartmut%20Branz's gravatar image

Hartmut Branz
40629
accept rate: 0%

  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.
permanent link

answered 10 Jan '13, 06:43

Dmitri's gravatar image

Dmitri
1.6k41133
accept rate: 11%

1

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.

(10 Jan '13, 10:50) Volker Barth

Walmir - how much memory have your machine and what is your command line to start server/db? if you have problem on first query after restart (and after that everything is fine), then it may be that on start there is in use to small -c XXX parameter for memory cache.

Have you tried to use Index Consultant (you can see as menu option it in ISQL)? Have you created all indexed suggested by Index Consultant?

An what is your version of ASA? i.e. ASA.12.0.1.XXXX

permanent link

answered 10 Jan '13, 05:11

JonJon's gravatar image

JonJon
31115
accept rate: 0%

edited 10 Jan '13, 05:12

@JonJon Database test. I am using the ASA Database Engine Version 9.0.2.3951

-ch 2048M

tbsales the table has a clustered index

CREATE CLUSTERED INDEX "idx_datesale" ON "DBA"."TBsales" ( "EMP" ASC, "DATE_SALE" ASC ) IN "SYSTEM";

Intel(R) core(TM) i3 CPU 550 @3.20GHz 4 GB SO 64 Bits

(10 Jan '13, 09:45) Walmir Taques
Replies hidden

What does dbsrv9 -o MyLog.txt reveal when the database is started - how much cache is used by default (as you don't specify this via -c)?

If this is much lower than the 2 GB limit you have set with -ch, I'd second Jon's suggestion to add -c XM...

(10 Jan '13, 10:56) Volker Barth
Comment Text Removed
Comment Text Removed

Well, given the 1 GB default initial cache size

1029920K of memory used for caching

and a rather low disk fragmentation

Performance warning: Database file "c:intesigoba-obaMirassolbdintesig.db" consists of 3 disk fragments

I don't think the cache size is the problem.

Does the slow execution also show up if you run "call sa_flush_cache()" before each query execution? - Then at least the problem may be due to a better usage of the cache contents for further runs...

(11 Jan '13, 07:50) Volker Barth
Comment Text Removed

I did a survey of some procedures to be performed / executed and followed the following steps:

• Change "Page Size": • Create a new database with 4096 bytes "Page Size". • Make unload / reload • Execute the command in isql: ALTER system dbspace ADD 512 MB;

• Reduce fragmentation: • If possible, place the database in a single partition on the hard drive. • Often run the disk defragmenter for Windows. • Defragment the file BDINTESIG.db when fragmented disks. (use the command in DOS: contig.exe "full_path_of_the_database")

• Cache Size • When starting the database using 2M cache and use the options "-1024M ch" (changed to this step-c2048M) 50% of RAM)

• Clear statistics • Run script to delete and recreate statistics.

Running "call sa_flush_cache()" still has slow, (only the first query)

***When I start the database with-c2048M displays the following message:

Note: A cache size of 2097152K exceeds the available physical memory of 1739876K. A performance penalty may result.

(11 Jan '13, 08:46) Walmir Taques
1

To assign 50 percent of the available RAM you might use -c 50P.
Trying 2 GB or more won't do, if you're in a 32 bit environment. The ASA engine can't allocate more than ~ 1.8 GB due to the OS limits.

(11 Jan '13, 11:57) Reimer Pods

Time to show the graphical plans of the slow and fast query executions, I'd think...

(11 Jan '13, 12:23) Volker Barth

how to get the execution plan for the query in the asa-9? I want to generate a file that I can edit it.

(30 Jan '13, 00:41) Walmir Taques

Hi,

Working with an ERP that features integration with Inventory, Sales, bank, cash, documents payable, receivable documents, accounting, tax paying employees. And every now and then is always showing signs of sluggishness in queries. Some here have suggested deleting some data (say the database is too "bloated"), but I do not think so because the databases have averaged about 3GBytes the 5GBytes.

To implement the Index Consultant. Leave for 10 minutes and running already been captured 8500 queries. How long you must leave this option running?

(03 Apr '13, 14:50) Walmir Taques
More comments hidden
showing 2 of 8 show all flat view

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

permanent link

answered 10 Jan '13, 11:31

JonJon's gravatar image

JonJon
31115
accept rate: 0%

1

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...

(10 Jan '13, 12:31) Volker Barth
Replies hidden

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

(10 Jan '13, 12:33) Volker Barth
Comment Text Removed

You are right, sorry, my mistake

(10 Jan '13, 12:45) JonJon

The version of Sybase ASA and ASE 12 is?

(11 Jan '13, 07:48) Walmir Taques
Replies hidden

latest ASA (SQL Anywhere) is 12.0.1 XXXXX

latest ASE is 15.7

(11 Jan '13, 09:26) JonJon

ok. Thanks.

(11 Jan '13, 12:20) Walmir Taques
2

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.

(14 Jan '13, 10:01) Costa
showing 4 of 7 show all flat view

Hi,

Working with an ERP that features integration with Inventory, Sales, bank, cash, documents payable, receivable documents, accounting, tax paying employees. And every now and then is always showing signs of sluggishness in queries. Some here have suggested deleting some data (say the database is too "bloated"), but I do not think so because the databases have averaged about 3GBytes the 5GBytes, good servers, and I'm always trying to improve their performance through REORGANIZE TABLES, delete and create statistics, initializing the memory bank with larger cache and are already used the contig.exe to defragment the database file already ran the index consultant, but, the last time (about 1 month or less) back to slow again, the databases of customers are increasing, and now who never complained of are calling for technical support and "burning down". I do not know what else to do. Are already thinking about changing to DBMS.

permanent link

answered 20 Feb '13, 09:46

Walmir%20Taques's gravatar image

Walmir Taques
690374151
accept rate: 12%

@Walmir: As you post these general experiences, I guess you current performance-related questions are still unanswered. (Sorry, I don't have the skills to answer them.)

In case my observation is correct, I'd recommend to re-activate these questions, say by adding a comment...

(21 Feb '13, 03:42) Volker Barth

To implement the Index Consultant. Leave for 10 minutes and running already been captured 8500 queries. How long you must leave this option running?

(03 Apr '13, 14:51) Walmir Taques
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21
×12

question asked: 09 Jan '13, 07:16

question was seen: 3,861 times

last updated: 03 Apr '13, 14:51