I changed some queries and to make sure that the query is actually running at a lower cost (because regardless of how the query is written the optimizer performs the best plan), is off the engine optimization sybase? Or should verify otherwise, how would that be otherwise?


asked 27 Mar '13, 08:51

Walmir%20Taques's gravatar image

Walmir Taques
accept rate: 12%

There was no OPTION( ) clause suppport in the DML syntax of version 9.

You can try setting your optimization_level (ie low) with a

SET TEMPORARY OPTION optimization_level=0; // or something low

and/or SET TEMPORARY OPTION optimization_goal='all-rows';

ahead of your query (in dbisql for example) if you want to test those out.


permanent link

answered 04 Apr '13, 13:03

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
accept rate: 32%

Ok @Nick Elson. Thanks. I'll try making those changes.

(04 Apr '13, 14:13) Walmir Taques

Clarification questions:

  1. Are you asking how to confirm the execution plan for your query is optimal?
  2. Or are you asking how you can confirm the execution plan is different now?

For both questions I would start with DBISQL. Execute your query, then go to the menu Tools -> Plan Viewer Change the "Statistics Level" to "Detailed and node statistics". Execute the query again and look at the plan.

So, the plan can show you what your execution plan is. If you then make changes to your query, the plan will show you changes (#2).

For #1, that is more difficult as SQL Anywhere can change the plan on a whole variety of input. For example, if the engine knows most of the table is already in engine cache it might favour a table scan. If it is not all in cache, it might use an index lookup. It just really depends.

So for #1, probably timing query execution might be most appropriate. For that I use the SQL Anywhere example tool fetchtest: Samples\SQLAnywhere xx\PerformanceFetch

You typically do not use DBISQL for timings as it performs a lot of overhead inorder to display the data in a nice readable format. This can force it to run additional queries which affects your timings.

HTH, David

permanent link

answered 28 Mar '13, 08:02

David%20Fishburn's gravatar image

David Fishburn
accept rate: 0%

edited 28 Mar '13, 10:47

Reimer%20Pods's gravatar image

Reimer Pods

I want to confirm (change to the query) is actually doing the query returns at a lower cost, so I asked if you turn off the optimizer as sybase. how to turn off?

(02 Apr '13, 09:10) Walmir Taques

I do not really understand your question - but just in case you want to "turn off the optimizer" as your title says:

Cf. the (OPTION FORCE NO OPTIMIZATION) query hint that can be added to any SELECT (or even INSERT SELECT, UPDATE or DELETE) statement.

To cite the docs:

Specify the FORCE NO OPTIMIZATION clause if you want the statement to bypass the optimizer. If the statement is too complex to process in this way—possibly due to the setting of database options or characteristics of the schema or query—the statement fails and the database server returns an error.

permanent link

answered 28 Mar '13, 08:19

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 28 Mar '13, 08:20

I'm using version of ASA database.

You demonstrate how to use the clause FORCE IN OPTIMIZATION? I tried and did not work well: (it shows a syntax error near option on line 7)

select * 
from tbclient
key join tbgercity
group by nome
having count(*)>1 
and max(codigo) > 10
OPTION( optimization_goal = 'first-row' );
(02 Apr '13, 09:44) Walmir Taques
Replies hidden

Ah, sorry, it seems the option I've mentioned was introduced in v10.0 - a near miss:(

(02 Apr '13, 10:47) Volker Barth

@Volker Barth There is some way to do this test on ASA-9, or an equivalent way?

(04 Apr '13, 09:25) 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



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:


question asked: 27 Mar '13, 08:51

question was seen: 3,367 times

last updated: 04 Apr '13, 14:13