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
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.
answered 04 Apr '13, 13:03
Nick Elson S...
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.
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: