|
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! |
|
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 Ok @Nick Elson. Thanks. I'll try making those changes.
(04 Apr '13, 14:13)
Walmir Taques
|
|
Clarification questions:
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 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:
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' );
(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
|
