How to turn off engine optimization sybase?

[former_SQLA_member1694872]

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?

thank!

[Former Member · Accepted solution]

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.

HTH

[former_SQLA_member1694872 · Accepted solution]

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

[Former Member]

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

[former_SQLA_member1694872]

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?

[VolkerBarth]

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.

[former_SQLA_member1694872]

I'm using version 9.0.2.3951 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' );

[VolkerBarth]

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

[former_SQLA_member1694872]

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